Troubleshooting tips

My collection of useful scripts and points read at different blogs and mailing lists.

===================================================================

SQL> oradebug setmypid
SQL> oradebug hanganalyze 12sqlplus -prelim -- Use this to open a sessionless sqlplus session when the box appears to hang

===================================================================

For debugging Oracle Applications, set profile options
Initialization SQL Statement - Custom to begin fnd_ctl.fnd_sess_ctl('','','TRUE','TRUE','LOG','ALTER SESSION SET EVENTS=''10046 TRACE NAME CONTEXT FOREVER, LEVEL 12'' tracefile_identifier=''Purchasing_Web_Trace'''); end;

=========================================================================

/* x$ktuxe is a useful view as it gives an indication of the state of the transaction tables in the rollback segment headers. The information here can be used to see the state of transactions requiring transaction recovery that do not show in v$transaction as the session executing the transaction has been killed, either through alert system or an os kill.
To see the transaction table in the header of the rollback segment dump the header block of the rollback segment.
ktuxeusn = rollback seg id
ktuxesiz = the number of blocks left to undo
ktuxerdbb = the current block# being undone
ktuxerdbf = the database file
*/
select b.segment_name, a.*
from x$ktuxe a,
dba_rollback_segs b
where a.ktuxeusn = b.segment_id and
ktuxecfl='DEAD' and -- Transaction flag
ktuxesta='ACTIVE' -- Transaction Status

=======================================================

You can also use this query to see if anything has a hold on undo space.

SELECT substr(s.sid','s.serial#,1,15) SID_SERIAL,
substr(NVL(s.username, 'None'),1,20) orauser,
substr(s.program,1,20) program,
substr(r.name,1,20) undoseg,
substr(t.used_ublk * TO_NUMBER(x.value)/1024'K',1,20) "Undo"
FROM sys.v_$rollname r,
sys.v_$session s,
sys.v_$transaction t,
sys.v_$parameter x
WHERE s.taddr = t.addr
AND r.usn = t.xidusn(+)
AND x.name = 'db_block_size'
/===========================================================

Query to show users of rollback segment:

select sess.username,sess.sid, sess.serial#,p.spid,sess.program,sess.osuser,sess.machine,

t.used_ublk,t.status, to_char(logon_time, 'MM/DD/YYYY HH24:MI'),sess.sql_hash_value

from v$session sess, v$process p, v$transaction t

where sess.saddr = t.ses_addr

and sess.paddr = p.addr

order by t.status, t.used_ublk desc;

Following query shows free space at datafile end (=possible for shrink)

column freem heading Free(MB) format 999G999D99


select /*+ RULE */ f.tablespace_name,f.file_id, sum(f.bytes)/1024/1024 freem from dba_free_space f,
(select file_id,max(block_id) max_block from dba_extents
group by file_id) e where
f.file_id=e.file_id(+) and f.block_id>nvl(e.max_block,0)
group by f.tablespace_name,f.file_id
order by f.tablespace_name,f.file_id;

================================================================

Issue: After datapump export and import of schema , if you do the count of object by object type, the number of LOBS dont match between source and target. No errors are reported during impdp or expdp process

Solution: There may be dropped LOB objects on the source database that are in the recylebin. When you query the recyclebin , you will find the additional objects there. These additional objects (that are in the recyclebin ) are not carried over during impdp.  purge recylebin on the source (connected as the schema which was exported) and then the counts match or purge recyclebin before the export and then import - the counts will match.

SQL> -- set the OS PID
SQL> oradebug setospid 864
Windows thread id: 864, image: ORACLE.EXE
SQL> -- or set the Oracle pid
SQL> oradebug setorapid 13
Windows thread id: 864, image: ORACLE.EXE
SQL> -- set the trace file size to unlimitd
SQL> oradebug unlimit
Statement processed.
SQL> -- now turn on trace for SCOTT
SQL> oradebug event 10046 trace name context forever, level 12
Statement processed.
SQL> -- run some queries in another session and then turn trace off
SQL> oradebug event 10046 trace name context off
Statement processed.
===================

Another way to get sql_texts

select kglnaobj from x$kglob where kglobt03 = '&sql_id';
===================
cluster commands list
http://www.oracle-home.ro/Oracle_Database/RAC/11gR2-RAC-CRSCTL-command.html