Autotrace in SQLPLUS

From Thomas Kyte about autotrace:

Here is what I like to do to get autotrace working:

• cd $oracle_home/rdbms/admin
• log into sqlplus as system
• run SQL> @utlxplan
• run SQL> create public synonym plan_table for plan_table
• run SQL> grant all on plan_table to public
• exit sqlplus and cd $oracle_home/sqlplus/admin
• log into sqlplus as SYS
• run SQL> @plustrce
• run SQL> grant plustrace to public

You can replace public with some user if you want. by making it public, you let anyone trace using sqlplus (not a bad thing in my opinion).

About Autotrace

You can automatically get a report on the execution path used by the SQL optimizer and the statement execution statistics. The report is generated after successful SQL DML (that is, SELECT, DELETE, UPDATE and INSERT) statements. It is useful for monitoring and tuning the performance of these statements. For complete articles please take a look at http://asktom.oracle.com/.

Read more!

How to change database character set

Catat/backup nilai dari parameter sebelum melakukan perubahan, dengan tujuan agar apabila diinginkan dapat mengembalikan nilai parameter tersebut ke nilai awalnya.

show parameters JOB_QUEUE_PROCESSES;
show parameters AQ_TM_PROCESSES;

Kemudian dilanjutkan dengan process ALTER DATABASE untuk mengubah character set.

STARTUP MOUNT;
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
ALTER SYSTEM SET AQ_TM_PROCESSES=0;
ALTER DATABASE OPEN;
ALTER DATABASE CHARACTER SET new_character_set;
SHUTDOWN IMMEDIATE; -- or SHUTDOWN NORMAL;
STARTUP;

Jika dibutuhkan kita dapat mengubah kembali nilai dari JOB_QUEUE_PROCESSES dan AQ_TM_PROCESSES.

ALTER SYSTEM SET JOB_QUEUE_PROCESSES = old_job_queue_processes_value;
ALTER SYSTEM SET AQ_TM_PROCESSES = old_aq_tm_processes_value;

Dictionary Table
- select * from v$nls_parameters

Read more!