Ayyappa Yelburgi

Subscribe to Ayyappa Yelburgi feed
The Moto behind for Creating this Blog is to share the concepts Of Oracle Database.In This Blog,The Information is gathered from Metalink,Expert's Blog and Oracle Documentaion.It Includes Real Time Scenarios,Oracle9i concepts,Oracle10g Concepts,RAC,Streams,Replication... Please do visit my blog and post your comments & advice please.ayyudbahttp://www.blogger.com/profile/00046200352601718598noreply@blogger.comBlogger163125
Updated: 2 weeks 3 days ago

Way to get Query execution plan in Oracle10g

Mon, 2007-12-24 04:24
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'));ayyudbahttp://www.blogger.com/profile/00046200352601718598noreply@blogger.com24


Sun, 2007-12-23 04:06
--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>=lenayyudbahttp://www.blogger.com/profile/00046200352601718598noreply@blogger.com6


Sun, 2007-12-23 04:03
--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 &ayyudbahttp://www.blogger.com/profile/00046200352601718598noreply@blogger.com31


Sun, 2007-12-23 04:01
--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 ayyudbahttp://www.blogger.com/profile/00046200352601718598noreply@blogger.com6


Sun, 2007-12-23 04:00
-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 tablespaceayyudbahttp://www.blogger.com/profile/00046200352601718598noreply@blogger.com20