May 28, 2009

Locks , Dummy Table

To get the locks on an object

SELECT oracle_username USERNAME,owner OBJECT_OWNER,object_name, object_type, s.osuser,s.SID SID,s.SERIAL# SERIAL,DECODE(l.block,
0, 'Not Blocking', 1, 'Blocking', 2, 'Global') STATUS, DECODE(v.locked_mode, 0, 'None', 1, 'Null', 2, 'Row-S (SS)', 3, 'Row-X (SX)', 4, 'Share', 5, 'S/Row-X (SSX)', 6, 'Exclusive', TO_CHAR(lmode) ) MODE_HELD
FROM gv$locked_object v, dba_objects d,gv$lock l, gv$session s WHERE v.object_id = d.object_id AND (v.object_id = l.id1) and v.session_id = s.sid ORDER BY oracle_username, session_id;

-------------------------------------------------------------
To create our own much faster DUMMY Table


CREATE TABLE MYDUAL(DUMMY VARCHAR2(1) PRIMARY KEY CONSTRAINT ONE_ROW
CHECK(DUMMY='X')) ORGANIZATION INDEX;

No comments: