Sep 22, 2008

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'

No comments: