Way to get Query execution plan in Oracle10g

traditional method before 10gselect * from v$sql where address='&ADDRESS' and hash_value=&HASH_VALUE;10g select * from v$sql where sql_id='&SQL_ID';select * from v$sqlstats where sql_id='&SQL_ID';select * from dba_hist_sqlstat where sql_id='&SQL_ID';select * from table(DBMS_XPLAN.DISPLAY_CURSOR('&SQL_ID', NULL, 'ALL'));select * from table(DBMS_XPLAN.DISPLAY_AWR('&SQL_ID',&PLAN_HASH,NULL,'ALL'));

any-longer-getting-ddl

--1 Get DDL of the object:declareclb CLOB; pos INTEGER:=1; amt INTEGER; len INTEGER; txt VARCHAR2(4000);beginclb := dbms_metadata.get_ddl ('$OBJ_TYPE','$OBJ_NAME','$OWNER');len := LENGTH(clb);LOOPamt := nvl(INSTR (clb, chr(10), pos),len) - pos;IF amt>0 THEN txt := NVL(SUBSTR (clb, pos, amt),' '); ELSE txt:=''; END IF;pos := pos + amt + 1;DBMS_OUTPUT.put_line (SUBSTR(txt,1,250));EXIT WHEN pos>=len

Typical-index-maintenance-tasks-9i10g

--1 Move index from one tablespace to anotheralter index &OWNER.&INDEX_NAME rebuild tablespace &NEW_TS_NAME;--2 Moving index partition from one tablespace to anotheralter index &OWNER.&INDEX_NAMErebuild partition &IND_PART_NAME tablespace &NEW_TS_NAME;--3 Moving all index subpartitions from one tablespace to anotheralter index &OWNER.&INDEX_NAMErebuild subpartition &IND_SUBPART_NAME tablespace &

keeping-eye-on-sessions

--Long operationsselect sid,username usern,serial#,opname,target,sofar,totalwork tot_work,units,time_remaining remaning,elapsed_seconds elapsed,last_update_time last_timefrom v&session_longops --where sid=73order by last_update_time desc;--All active sessionsselect * from v&session where status='ACTIVE'--and sid in (37,43)order by sid;--Find session's sid or process id by it's sid or process

moving-tablespartitions-9i-onwards

-1 Move table from one tablespace to another-- (check for unusable indexes after that).alter table $OWNER.$TABLE_NAME move tablespace $NEW_TS_NAME;--2 Move table partition from one tablespace to another-- (check for unusable indexes and partitoned indexes after that).alter table $OWNER.$TABLE_NAMEmove partition $TAB_PART_NAME tablespace $NEW_TS_NAME;--3 Move table subpartition from one tablespace