Calender,Dates,Objects created

Calender of a year


select lpad( Month, 20-(20-length(month))/2 ) month
, "Su" , "Mo" , "Tu" , "We" , "Th" , "Fr" , "Sa"
from ( select to_char(dt,'fmMonthfm YYYY') month
, to_char(dt+1,'iw') week
, max(decode(to_char(dt,'d'),'1',lpad(to_char(dt,'fmdd'),2))) "Su"
, max(decode(to_char(dt,'d'),'2',lpad(to_char(dt,'fmdd'),2))) "Mo"
, max(decode(to_char(dt,'d'),'3',lpad(to_char(dt,'fmdd'),2))) "Tu"
, max(decode(to_char(dt,'d'),'4',lpad(to_char(dt,'fmdd'),2))) "We"
, max(decode(to_char(dt,'d'),'5',lpad(to_char(dt,'fmdd'),2))) "Th"
, max(decode(to_char(dt,'d'),'6',lpad(to_char(dt,'fmdd'),2))) "Fr"
, max(decode(to_char(dt,'d'),'7',lpad(to_char(dt,'fmdd'),2))) "Sa"
from ( select trunc(sysdate,'y')-1+rownum dt
from all_objects
where rownum <= add_months(trunc(sysdate,'y'),12)
-
trunc(sysdate,'y')
)
group by to_char(dt,'fmMonthfm YYYY')
, to_char( dt+1, 'iw' )
)
order by to_date( month, 'Month YYYY' )
, to_number(week)

--------------------------------------------------
To get all Dates in a Year

select mydate,to_char(mydate,'Day') from(
select (level-1)+to_date('01-01-'||to_char(sysdate,'yyyy'),'dd-mm-yyyy') mydate from dual
connect by level <=to_date('31-12-'||to_char(sysdate,'yyyy'),'dd-mm-yyyy')-to_date('01-01-'||to_char(sysdate,'yyyy'),'dd-mm-yyyy') +1)

OR


select mydate,to_char(mydate,'Day') from(
select (rownum-1)+to_date('01-01-'||to_char(sysdate,'yyyy'),'dd-mm-yyyy') mydate from all_objects
where
rownum <=to_date('31-12-'||to_char(sysdate,'yyyy'),'dd-mm-yyyy') - to_date('01-01-'||to_char(sysdate,'yyyy'),'dd-mm-yyyy') +1)

The above query assumes that all_objects returns at least 365 records.

----------------------------------------------------------------------------
Number of objects created in a month of year

select to_char(trunc(created,'MONTH'),'YYYY-MON') "Month", count(*) "Nb"
from user_objects group by trunc(created,'MONTH')
order by trunc(created,'MONTH');