^[pRoUd]^ 작업실

select a.sid,
       decode(a.type,'MR', 'Media Recovery',
                     'RT', 'Redo Thread',
                     'UN', 'User Name',
                     'TX', 'Transaction',
                     'TM', 'DML',
                     'UL', 'PL/SQL User Lock',
                     'DX', 'Distributed Xaction',
                     'CF', 'Control File',
                     'IS', 'Instance State',
                     'FS', 'File Set',
                     'IR', 'Instance Recovery',
                     'ST', 'Disk Space Transaction',
                     'IR', 'Instance Recovery',
                     'TS', 'Temp Segment',
                     'IV', 'Library Cache Invalidation',
                     'LS', 'Log Start or Switch',
                     'RW', 'Row Wait',
                     'SQ', 'Sequence Number',
                     'TE', 'Extend Table',
                     'TT', 'Temp Table',
                     a.type) lock_type,
       decode(a.lmode,0, 'None', /* Non Lock equivalent */
                      1, 'Null', /* N */
                      2, 'Row-S (SS)', /* L */
                      3, 'Row-X (SX)', /* R */
                      3, 'Row-X (SX)', /* R */
                      4, 'Share', /* S */
                      5, 'S/Row-X (SSX)', /* C */
                      6, 'Exclusive', /* X */
                      to_char(a.lmode)) mode_held,
                      decode(a.request,
                      0, 'None', /* Mon Lock equivalent */
                      1, 'Null', /* N */
                      2, 'Row-S (SS)', /* L */
                      3, 'Row-X (SX)', /* R */
                      4, 'Share', /* S */
                      5, 'S/Row-X (SSX)', /* C */
                      6, 'Exclusive', /* X */
                      to_char(a.request)) mode_requested,
       to_char(a.id1) lock_id1, to_char(a.id2) lock_id2
  from v$lock a
 where (id1,id2) in (select b.id1, b.id2
                       from v$lock b
                      where b.id1=a.id1
                        and b.id2=a.id2
                        and b.request>0)
;

select dbms_metadata.get_ddl('OBJECT_TYPE','OBJECT_NAME','OBJECT_OWNER') from dual;

/* the following hex conversion code is from Tom Kyte */
create or replace function to_base( p_dec in number, p_base in number ) 
return varchar2
is
l_str varchar2(255) default NULL;
l_num number default p_dec;
l_hex varchar2(16) default '0123456789ABCDEF';
begin
if ( trunc(p_dec) <> p_dec OR p_dec < 0 ) then
raise PROGRAM_ERROR;
end if;
loop
l_str := substr( l_hex, mod(l_num,p_base)+1, 1 ) || l_str;
l_num := trunc( l_num/p_base );
exit when ( l_num = 0 );
end loop;
return l_str;
end to_base;
/


create or replace function to_dec
( p_str in varchar2, 
  p_from_base in number default 16 ) return number
is
l_num   number default 0;
l_hex   varchar2(16) default '0123456789ABCDEF';
begin
for i in 1 .. length(p_str) loop
l_num := l_num * p_from_base + instr(l_hex,upper(substr(p_str,i,1)))-1;
end loop;
return l_num;
end to_dec;
/
show errors

create or replace function to_hex( p_dec in number ) return varchar2
is
begin
return to_base( p_dec, 16 );
end to_hex;
/
create or replace function to_bin( p_dec in number ) return varchar2
is
begin
return to_base( p_dec, 2 );
end to_bin;
/
create or replace function to_oct( p_dec in number ) return varchar2
is
begin
return to_base( p_dec, 8 );
end to_oct;
/