Dicas ORACLE DBA

I found this bug several times using version 10.2.0.2 until 10.2.0.5, i used to solve it just by changing the optimizer mode to RULE in the RMAN session. Solution: sql "alter session set optimizer_mode=RULE"; Execute your RMAN backup scripts.

Select /*+ ORDERED */  substr(s.ksusemnm, 1, 10) || '-' || substr(s.ksusepid, 1, 10) "ORIGIN",  substr(g.K2GTITID_ORA, 1, 35) "GTXID",  substr(s.indx, 1, 4) || '.' || substr(s.ksuseser, 1, 5) "LSESSION",  substr(decode(bitand(ksuseidl, 11),                1,                'ACTIVE',                0,                decode(bitand(ksuseflg, 4096), 0, 'INACTIVE', 'CACHED'),                2,                'SNIPED',                3,                'SNIPED',                'KILLED'),         1,         1) "S",  substr(event, 1, 10) "WAITING"   from ...

set linesize 300 set serveroutput on size 100000 set feedback off set timing on set term off set head off spool rebuild.log begin dbms_output.enable('9999999'); for rIndex in (select s.owner,s.segment_name,sum(s.bytes/1024/1024) from dba_segments s , dba_indexes i where s.segment_type = 'INDEX' and s.segment_name = i.index_name and s.owner = i.owner and i.index_type = 'NORMAL' and s.owner not in ('SYS','SYSTEM','SYSMAN','SYSAUX','APPLSYS') group by s.owner,s.segment_name having sum(bytes/1024/1024) > 100) loop execute immediate 'analyze index '||rIndex.owner||'.'||rIndex.segment_name||' validate structure'; end loop; dbms_output.put_line('--- Reorganizando os indices ---'); for rRebuild in(SELECT name, height, lf_rows, owner FROM INDEX_STATS st, dba_indexes di where st.name = di.index_name) loop execute immediate 'alter index '||rRebuild.owner||'....