Oracle Scripts

Reports users waiting for locks

SELECT sn.username, m.sid, m.type,
DECODE(m.lmode, 0, 'None',
1, 'Null',
2, 'Row Share',
3, 'Row Excl.',
4, 'Share',
5, 'S/Row Excl.',
6, 'Exclusive',
lmode, ltrim(to_char(lmode,'990'))) lmode,
DECODE(m.request,0, 'None',
1, 'Null',
2, 'Row Share',
3, 'Row Excl.',
4, 'Share',
5, 'S/Row Excl.',
6, 'Exclusive',
request, ltrim(to_char(m.request,
'990'))) request, m.id1, m.id2
FROM v$session sn, v$lock m
WHERE (sn.sid = m.sid AND m.request != 0)
OR (sn.sid = m.sid
AND m.request = 0 AND lmode != 4
AND (id1, id2) IN (SELECT s.id1, s.id2
FROM v$lock s
WHERE request != 0
AND s.id1 = m.id1
AND s.id2 = m.id2)
)
ORDER BY id1, id2, m.request;

-------------------------------------------------------
This script will generate information about Rollback Segments

select segment_name sn, decode(owner,'PUBLIC','Publ','Priv') ow,
tablespace_name ts, name fn
from sys.dba_rollback_segs d, v$datafile f
where d.file_id = f.file#;

prompt
prompt Online Rollback Segments:
select d.segment_name nm,
s.extents ex,
(s.rssize/1024)||'K' rs,
d.initial_extent init,
d.next_extent next,
d.pct_increase pct,
d.min_extents mi,
d.max_extents ma,
optsize op,
decode(d.status,'ONLINE','OnL','OFFLINE','OffL') st
from v$rollname n, v$rollstat s, sys.dba_rollback_segs d
where n.usn = s.usn
and d.segment_name = n.name(+);


-------------------------------------------------------
Datafile Information

select substr(FILE_ID,1,3) "ID#",
substr(FILE_NAME,1,52) "DataFile Name",
substr(TABLESPACE_NAME,1,25) "Related Tablespace",
BYTES "Bytes",
BLOCKS "SQL Blks",
BYTES/512 "VMS Blocks",
STATUS
from sys.dba_data_files
order by TABLESPACE_NAME, FILE_NAME;

-----------------------------------------------------------

Table Information

--- This Gives the tables size information

select substr(OWNER,1,15) "Owner",
substr(TABLESPACE_NAME,1,32) "Tablespace Name",
substr(TABLE_NAME,1,24) "Table Name",
substr(PCT_FREE,1,3) "%F",
substr(PCT_USED,1,3) "%U",
substr(INI_TRANS,1,2) "IT",
substr(MAX_TRANS,1,3) "MTr",
substr(INITIAL_EXTENT,1,10) "INI_Extent",
substr(NEXT_EXTENT,1,10) "Next Exts",
substr(MIN_EXTENTS,1,5) "MinEx",
substr(MAX_EXTENTS,1,5) "MaxEx",
substr(PCT_INCREASE,1,5) "%Incr"
from sys.dba_tables
order by owner, tablespace_name, table_name;


-----------------------------------------------------------

Other Objects Information

--- This Gives the Other Objects Information

select substr(owner,1,10) Owner,
substr(OBJECT_NAME,1,25) Object_Name,
object_type, status, created
from sys.dba_objects
where object_type not in
('INDEX', 'SYNONYM', 'TABLE', 'VIEW')
AND OWNER not in ('SYS', 'SYSTEM')
order by owner, object_type;


Note:

PROOFREAD THIS SCRIPT BEFORE USING IT!

Determine the Locks in Oracle

To list out the Locked Objects

select c.owner, c.object_name, b.sid, b.serial#, b.status, b.osuser, b.machine ,a.process
from v$locked_object a , v$session b, dba_objects c
where
b.sid = a.session_id
and a.object_id = c.object_id;

Session Blocking Other session
Query 1:

select l1.sid, ' IS BLOCKING ', l2.sid
from v$lock l1, v$lock l2
where l1.block =1 and l2.request > 0
and l1.id1=l2.id1
and l1.id2=l2.id2;

Query 2:

select s1.username || '@' || s1.machine
|| ' ( SID=' || s1.sid || ' ) is blocking '
|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2

Displaying Information About Running Jobs - Oracle

Displaying Information About Running Jobs

You can also display information about only the jobs currently running. The following query lists the session identifier, job number, user, who submitted the job, and the start times for all currently running jobs:

SELECT SID, r.JOB, LOG_USER, r.THIS_DATE, r.THIS_SEC
FROM DBA_JOBS_RUNNING r, DBA_JOBS j WHERE r.JOB = j.JOB;

Check Job Queue in Oracle

Displaying Information about a Job

The following query creates a listing of the job number, description, next execution time and broken status for each job you have submitted:

SELECT JOB, SUBSTR(WHAT,1,35), NEXT_DATE, NEXT_SEC, BROKEN FROM DBA_JOBS;