Oracle DBA Place

Using Autotrace SQL> set autotrace traceonly explain SQL> select ename from emp where sal > 500; Execution Plan ---------------------------------------------------------- Plan hash value: 2872589290 ------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |Cost (%CPU)| Time | ------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 25 | 2 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| EMP | 1 | 25 | 2 (0)| 00:00:01 | ------------------------------------------------------------------------- Using DBMS_XPLAN Package SQL> explain plan for select ename from emp where sal > 500; Explained. SQL> select * from TABLE(dbms_xplan.display); PLAN_TABLE_OUTPUT ------------------------------ Plan hash value: 2872589290 ------------------------------------------------------------------------- |

Using the same technique I mentioned at " Tracing Commands " here is a code in a form of logging trigger, easier when you want to capture just a specific schema from the very first transaction. CREATE OR REPLACE TRIGGER SYS. LOGON_TRACE_CRYSTAL_TRG AFTER LOGON ON CRYSTAL. SCHEMA DECLARE cmd VARCHAR2(100); BEGIN cmd := 'ALTER SESSION SET max_dump_file_size = unlimited'; EXECUTE IMMEDIATE cmd; cmd := 'ALTER SESSION SET tracefile_identifier = ''10046'''; EXECUTE IMMEDIATE cmd; cmd := 'ALTER SESSION SET statistics_level = ALL'; EXECUTE IMMEDIATE cmd; cmd := 'ALTER SESSION SET events ''10046 trace name context forever, level 12'''; EXECUTE IMMEDIATE cmd; EXCEPTION WHEN OTHERS THEN --NULL; RAISE; END ; / optional: CREATE OR REPLACE TRIGGER  SYS. LOGOFF_TRACE_CRYSTAL_TRG BEFORE LOGOFF ON CRYSTAL. SCHEMA DECLARE cmd VARCHAR2(100); BEGIN cmd := 'ALTER SESSION SET EVENTS ''10046

Cool and simple code to format any kind of script in Unix environment awk.info » Format Shell Scripts Recommended

Background: Dynamicaly moving control files and redo log files from filesystem /dbdata1/ to /dbdata3/ and from /dbdata2/ to /dbdata4/ 1. run as sysdba export ORACLE_SID=orcl sqlplus / as sysdba create pfile='pfile_backup_orcl.ora' from spfile; SET pagesize 0 SET feedback off spool OS_command_orcl.sh SELECT 'cp ' || NAME || ' ' || CASE WHEN REGEXP_SUBSTR(NAME, '^\/[^/]+\/') = '/dbdata1/' THEN REPLACE (NAME, '/dbdata1/', '/dbdata3/') WHEN REGEXP_SUBSTR(NAME, '^\/[^/]+\/') = '/dbdata2/' THEN REPLACE (NAME, '/dbdata2/', '/dbdata4/') END OS_command FROM v$controlfile UNION ALL SELECT 'cp ' || MEMBER || ' ' ||CASE WHEN REGEXP_SUBSTR(MEMBER, '^\/[^/]+\/') = '/dbdata1/' THEN REPLACE (MEMBER, '/dbdata1/', '/dbdata3/') WHEN REGEXP_SUBSTR(MEMBER, '^\/[^/]+\/') = '/dbdata2/' THEN REPLACE (MEMBER, '/dbdata2/', &#