Query to determine type of lock

Create below function

create or replace function enqueue_decode( l_p1 in number ) return varchar2
as
l_str varchar2(25);
begin
select chr(bitand(l_p1,-16777216)/16777215)||
chr(bitand(l_p1, 16711680)/65535) || ' ' ||
decode( bitand(l_p1, 65535),
          0, 'No lock',
          1, 'No lock',
          2, 'Row-Share',
          3, 'Row-Exclusive',
          4, 'Share',
          5, 'Share Row-Excl',
          6, 'Exclusive' )
 into l_str
 from dual;
 return l_str;
end;

Then pass P1 value for a wait event to determine type of lock

select  enqueue_decode(P1) from dual;

Table Sizing Information

1) Get an estimate of the number of records for the table, both now, and
   projected to 6 months from now
2) Find the average length of each record. If you cannot do this
   perfectly, then try to make a good guess.
3) Determine how often the table is updated/inserted/deleted. If it is
   changed often (and the record sizes vary), then the PCTUSED would be
   low, something like 40-60. If the table is static, then I'd make the
   table PCTUSED 85 or 90.
4) Determine my database's block size:
   select value from v$parameter where name = 'db_block_size';

Now I have all of the information needed to size my table:

Records per block = (block size - 110 bytes for overhead) * (PCTUSED/100)/
                    Average Record Size

Total blocks = (records in table) / (records per block)
Total table size = blocks * block size
   
Give a little fudge factor and make this your INITIAL extent size.
Your NEXT will be determined by your 6 month growth projection.

Oracle Query to produce UNIX cal command like calendar

break on month skip 1
set linesize 20
column month 20
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)

Generate intermediate values using subquery factoring

Today I wrote a query generate missing values for example table bank which contained data as show below
ACCNO    DOD              AMT
100          02-JAN-10     2000
200          10-JAN-10     1000
100          08-JAN-10     4000


If you observe for accno 100 there are no entries after 02-JAN-2010 till 08-JAN-2010 and amt in this account will be 2000 till 07-JAN-2010 but if we require to generate intermediate values then following query is written to generate values 

with date_range as
(
select max(dod) maxdt,min(dod) mindt from bank
),
date_seq as
(
select mindt+level-1 final_date from date_range
connect by level  <= (maxdt-mindt)+1
)
select to_number( substr(
            max( case when accno is not null
                      then to_char(date_seq.final_date,'yyyymmdd')||accno
                  end ) over (order by date_seq.final_date)
           , 9 ) ) accno,date_seq.final_date
,to_number( substr(
            max( case when amt is not null
                      then to_char(date_seq.final_date,'yyyymmdd')||amt
                  end ) over (order by date_seq.final_date)
           , 9 ) ) amt
from bank,date_seq where date_seq.final_date=bank.dod(+) order by final_date