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!