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;

Lock,Month,Week Query

Query to identify Locked Tables

SELECT e.*, (select terminal from v$session r where e.SID = r.SID) terminal,
(select name from sys.obj$ where obj# = e.ID1) objname
FROM sys.v_$lock e

-----------------------------------------------------------------
To get all months in a year

select to_char(add_months(TRUNC(SYSDATE,'Y'), level-1), 'Month') from dual
connect by level <= months_between(sysdate, TRUNC(SYSDATE,'Y'))+1
order by add_months(TRUNC(SYSDATE,'Y'), level-1);

select TO_CHAR(TO_DATE(rownum,'MM'),'Mon') Month_Val from dual connect by rownum <= 12;

-----------------------------------------------------------------
To get Weeks in a year

select trunc(dte,'W') week_start,trunc(dte,'W')+6 week_end,to_char(dte,'Month') Mnth
,to_char(dte,'YYYY') Yr
from (select trunc(sysdate,'YYYY')+(7*(level-1)) dte
from dual
connect by level <= ceil((add_months(trunc(sysdate,'YYYY'),12)-trunc(sysdate,'YYYY'
))/7));
-----------------------------------------------------------------