Regular scripts for Oracle DBA

select prev_hash_value from v$session where sid='&sid;
SQL> select prev_hash_value from v$session where sid='466'
select sql_text from v$sqlarea where hash_value='&hash_value'
SQL>select sql_text from v$sqlarea where hash_value='590983'now kill the holders session;select * from v$lock where block=1;
select username ,status,logon_time,process,osuser,sid,serial#,sql_hash_value from v$session where status='ACTIVE' order by logon_time;

+++++++++++++++++Session WAIT's :+++++++++++++++++
set pages 1000
set linesize 180
col username for a10
col module for a10
col event for a30
select a.sid, b.username, substr(b.module,1,10) Module, a.event, a.p1, substr(a.p1raw,1,25) p1raw, a.p2, a.p3, a.state, a.wait_time from v$session_wait a, v$session b where a.event not in ('SQL*Net message from client','wakeup time manager','pipe get','rdbms ipc message','smon timer','pmon timer','null event','jobq slave wait')
and a.sid=b.sid;
++++++++++++++++++++SQL TEXT for SID :++++++++++++++++++++
select sql_text,HASH_VALUE from v$sqltext t, v$session s where t.address = s.sql_addressand s.sid = &1 order by piece/CLEAR COLUMNS
+++++++++++++++++++LOCK DETAIL :+++++++++++++++++++
select * from v$lock where block=1;
+++++++++++++++++LOCK Details :+++++++++++++++++
col ORACLE_USERNAME for a13
col OS_USER_NAME for a10
col object_name for a40
PROMPT "PRESSS ENTER FOR ALL SIDs"
select a.ORACLE_USERNAME,a.OS_USER_NAME,a.SESSION_ID,b.object_name,c.NAME "RBS NAME",decode(a.LOCKED_MODE,1,'No Lock',2,'Row Share',3,'Row Excl',4,'Share',5,'Shr Row Excl',6,'Exclusive',null) "LOCK MODE",d.modulefrom v$locked_object a,dba_objects b,v$rollname c,v$session d where a.OBJECT_ID=b.object_idand a.XIDUSN=c.USNand a.session_id=d.sidand a.SESSION_ID like '%&sid%'order by session_id
+++++++++++++++++++++++long running query:+++++++++++++++++++++++
SELECT to_char(start_time,'hh24:mi:ss') stime,message,( sofar/totalwork)* 100 percent FROM v$session_longops where sid ='&1'SELECT sid,SQL_ID,to_char(start_time,'hh24:mi:ss') stime,message,( sofar/totalwork)* 100 percent FROM v$session_longops where message like '%WIP%';
For RMAN :
===========
SELECT sid, serial#, context, sofar, totalwork, round(sofar/totalwork*100,2) "% Complete" FROM v$session_longops WHERE opname LIKE 'RMAN%' AND opname NOT LIKE '%aggregate%' AND totalwork != 0 AND sofar <> totalwork;

For perticular SID :
=====================
SELECT sid, serial#, context, sofar, totalwork, round(sofar/totalwork*100,2) "% Complete" FROM v$session_longops WHERE sid='&1' AND totalwork != 0 AND sofar <> totalwork

For All Sessions :
=============
SELECT sid, serial#, context, sofar, totalwork, round(sofar/totalwork*100,2) "% Complete" FROM v$session_longops WHERE totalwork != 0 AND sofar <> totalwork

+++++++++++++++++++++++++++
Rollback Segments Usage :
+++++++++++++++++++++++++++
select a.sid, a.username, a.osuser, b.USED_UBLK "UNDO BLOCKS", c.Name "UNDO NAME"from v$session a, v$transaction b, v$rollname cwhere a.saddr = b.ses_addr and b.XIDUSN = c.USN ;
++++++++++++++++++++++++++++++++
Find Unix Process ID from SID:
++++++++++++++++++++++++++++++++
set pages 1000
set linesize 190
col username for a10
select a.PID "Ora PID",a.SPID "Server PID",a.LATCHWAIT,b.Program,b.Username,b.Osuser,b.Process "Clinet Process"from v$process a,v$session b where a.addr=b.paddr and b.sid=&1

++++++++++++++++++++++++++++++++
Find SID from Unix Process ID :
++++++++++++++++++++++++++++++++
undefine unixprocessid
set pages 5000
set verify offset feed on
set long 20000
set pages 5000
SELECT b.osuser,
b.username,
a.pid,
a.spid,
c.sql_text,
b.saddr,
b.sid,
b.serial#,
to_char(b.logon_time,'DD-MON-YY HH24:MI:SS') "LOGON_TIME",
b.username,
b.program,
b.process client,
a.spid server
FROM v$sqlarea c,v$session b,v$process a
WHERE a.spid = '&1'
AND b.paddr = a.addr
AND b.sql_hash_value = c.hash_value(+)
AND b.sql_address = c.address(+) ;

ps aux head
ps -ef sort +3 tail -10

++++++++++++++++++++++
SORT Segment Usage :
++++++++++++++++++++++
select rpad(sid,5,' ')as sid,username,rpad(program,15,' ') as prgm,rpad(blocks*16384/(1024*1024)' MB ',10,' ') as tempsize, segtype from v$session,v$sort_usage where saddr=session_addr order by blocks ;

SELECT s.sid, s.serial#, s.username, u.tablespace, u.contents, u.extents, u.blocksFROM v$session s, v$sort_usage u WHERE s.saddr=u.session_addr
/
CLEAR COLUMNS

column TABLESPACE_NAME format a10
column USERNAME format a10
prompt Total Temp spaceprompt
=======================================
select sum(bytes/1024/1024) Tot_MB,tablespace_name from v$temp_extent_mapgroup by tablespace_nameorder by tablespace_name/ prompt Used Temp spaceprompt =======================================
select (bytes_used/1024/1024) USED_MB,tablespace_name from v$temp_extent_poolorder by tablespace_name
/
prompt Actual user who is using sort segment
prompt =====================================
SELECT s.username,s.sid, u.tablespace, u.contents, u.extents, u.blocks,u.segtypeFROM v$session s, v$sort_usage uWHERE s.saddr=u.session_addr;

prompt Actual usage of sort segment
prompt ============================
select TABLESPACE_NAME,TOTAL_BLOCKS,USED_BLOCKS,FREE_BLOCKS from v$sort_segment;

prompt extent info for sort segement
prompt =============================
SELECT tablespace_name, extent_size, total_extents, used_extents, free_extents, max_used_size FROM v$sort_segment;

prompt sort ratio
prompt ==========
select ((mem.value)/(mem.value+disk.value)) "Sort Ratio"from v$sysstat mem, v$sysstat diskwhere mem.name = 'sorts (memory)' and disk.name = 'sorts (disk)';

prompt temp tablepsace actual usage ratio
prompt ==================================
select (b.value/a.value)*100 temp_use_ratio from(select sum(bytes) value from v$temp_extent_map) a,(select sum(BYTES_USED) value from v$temp_extent_pool) b;

+++++++++++++++++++++++++++++++++++++
MVIEW refresh Status on MASTER Site :
+++++++++++++++++++++++++++++++++++++
set pages 1000
set linesize 180
col SNAPSHOT_SITE for a30
select r.name,
r.snapshot_site,
l.current_snapshots
from dba_registered_snapshots r, dba_snapshot_logs l
where r.snapshot_id = l.snapshot_id
and
l.master in (select master from dba_snapshot_logs) ;
CLEAR COLUMNS
++++++++++++++++++++++++
LOCK Session Details :
++++++++++++++++++++++++
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;

TX type:6 ==> this is transaction exclusive lock
( due to update where other transaction waiting for the update of same )

++++++++++++++++++++++++++++++++++++++++++++
Query To Get the SID FROM UNIX PROCESS ID
++++++++++++++++++++++++++++++++++++++++++++
select sid, serial# from v$session where process='&1';
column schemaname format a10
heading "Oracle User"
column osuser format a15
heading "Osuser"
column module format a18
heading "Module"
column terminal format a10
heading "Terminal"
column status format a10
heading "Status"
column sid format 99999
heading "Sid"
column serial# format 9999999
heading "Serial#"
column program format a20
heading "Program"
col username format a8
SELECT p.spid,
s.osuser,
s.username,
s.sid,
s.serial#,
s.module,
s.status,
to_char(s.logon_time, 'DD-MON-YY HH24:MI:SS') logontime,
s.program,
s.LAST_CALL_ET/60 Since_Updated
FROM v$process p, v$session s WHERE s.sid= '&1'

++++++++++++++++++++++++++++++++++
Query To Check The LogOn Time
+++++++++++++++++++++++++++++++++++
select 'Sid, Serial# ' s.sid' , 's.serial#chr(10) 'Form User : ' fu.user_namechr(10) 'Machine - Terminal : ' s.machine' - ' s.terminalchr(10)'OS Process Ids : ' s.process'(Client) 'p.spid' (Server)' ' (Since)' to_char(s.logon_time,'DD-MON-YYYY HH24:MI:SS')chr(10) 'Client Program Name : 's.programchr(10) 'Action / Module : 's.action' / 's.modulechr(10) 'User Description : ' fu.descriptionchr(10)
'-----------------------------------------------------------------'
from gv$process p,gv$session s, apps.fnd_logins f, apps.fnd_user fu where p.addr = s.paddr and p.inst_id=s.inst_id and f.spid (+) = s.process and p.spid='&1' and fu.user_id (+) = f.user_id and f.end_time is null order by f.start_time;