Some Important Oracle Database scripts

To get SID from SPID:
select sid,program,process,username,machine,osuser,status,to_char(LOGON_TIME,'DD-MON-YYYY HH:MI:SS') from v$session where paddr in(select addr from v$process where spid=&spid);


To get spid from sid:
select spid from v$process where addr in ( select paddr from v$session where sid = &sid );


Who is locking what:
select
  oracle_username
  os_user_name,
  locked_mode,
  object_name,
  object_type
from
  v$locked_object a,dba_objects b
where
  a.object_id = b.object_id


To check blocking locks:
set lines 120
set pages 100
SELECT DECODE(request,0,'Holder: ','Waiter: ')|| sid sess, id1, id2, lmode,request, type
FROM V$LOCK
WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM V$LOCK WHERE request>0) ORDER BY id1, request;


To check database locks:
select c.owner,c.object_name,c.object_type,b.sid,b.serial#,b.status,b.osuser,b.machine from
  v$locked_object a,v$session b,dba_objects c where b.sid = a.session_id and a.object_id = c.object_id;

select oracle_username ,os_user_name,  locked_mode, object_name, object_type from v$locked_object a,dba_objects b where
 a.object_id = b.object_id;

Script gives the sql_text for a particular sid:

set lines 132

column sql_text format a80 wrap

select username, sql_text from v$sqlarea, v$session where hash_value = sql_hash_value and sid=&sid;

 

Check sessions exceeded 12 hrs:

SELECT  s.sid ,s.username,s.paddr,s.LOGON_TIME,round((s.LAST_CALL_ET/1200),2),s.program,s.osuser,s.process,

s.machine,s.terminal,p.spid

FROM v$session s, v$process p

WHERE s.username IS NOT NULL AND (LAST_CALL_ET / 60) > 720 

AND s.STATUS = 'INACTIVE' 

and s.paddr=p.addr

order by s.LAST_CALL_ET desc

Undo tablespace usage:

a)SELECT s.sid, s.serial#, s.username, s.program,t.used_ublk, t.used_urec

 FROM v$session s, v$transaction t

WHERE s.taddr = t.addr ORDER BY 5 desc, 6 desc, 1, 2, 3, 4;

b)select status,SUM(bytes/1024/1024) from dba_undo_extents GROUP BY STATUS;

c)select * from v$fast_start_transactions

d)select status,count(1) from dba_undo_extents group by status

e)select ((select (nvl(sum(bytes),0)) from dba_undo_extents where tablespace_name='UNDOTBS1'  and status in ('ACTIVE','UNEXPIRED')) *100) / (select sum(bytes) 

from dba_data_files where tablespace_name='UNDOTBS1') "PCT_INUSE" from dual;

f)select

( select sum(bytes)/1024/1024 from dba_data_files

where tablespace_name like 'UND%' ) allocated,

( select sum(bytes)/1024/1024 from dba_free_space

where tablespace_name like 'UND%') free,

( select sum(bytes)/1024/1024 from dba_undo_extents

where tablespace_name like 'UND%') USed

from dual;

Trace SQL session:

a) Set_Sql_Trace_In_Session

Used to set trace on or off in another users session:

EXEC DBMS_System.Set_Sql_Trace_In_Session(sid, serial#, true );

EXEC DBMS_System.Set_Sql_Trace_In_Session(31, 97, true );

b) Set_Ev

Used to set trace on for a specific event:

EXEC DBMS_System.Set_Ev(sid, serial#, event, level, name);

EXEC DBMS_System.Set_Ev(31, 97, 10046, 4, '');

Where level indicates the following levels of trace:

1 - Standard SQL_TRACE functionality. 

4 - As level 1 plus tracing of bind variables. 

8 - As level 1 plus wait events. 

12 - As level 1 plus bind variables and wait events.

CHECK FREE SPACE IN TEMPORARY TABLESPACE:

select TABLESPACE_NAME, BYTES_USED, BYTES_FREE from V$TEMP_SPACE_HEADER;

SELECT * FROM V$TEMPSEG_USAGE WHERE TABLESPACE='TEMP1'