How to read oracle Release No ?

read oracle version
Oracle Release Numbers




Release Number Format

Oracle database server labeled "Release 10.2.0.1.0." The significance of each number

10 is the version number
2 is the new features release number
0 is the maintenance release number
1 is the generic patch set number
0 is the platform specific patch set number




Version Number : This is the most general identifier. It represents a major new edition (or version) of the
software and contains significant new functionality.
New Features Release Number : This number represents a new features release level.
Maintenance Release Number : This number represents a maintenance release level. A few new features may also be included.
Generic Patch Set Number : This number identifies a generic patch set. The patch set is applicable across all operating system and
hardware platforms.


SELECT * FROM PRODUCT_COMPONENT_VERSION;

oracle recover option

recover database until cancel
recover database until time '2004-03-21:22:59:04'
recover database until change 123456

recover datafile 'filename' until cancel
recover datafile 'filename' until time '2004-03-21:22:59:04'
recover datafile 'filename' until change 123456

recover tablespace ts_name until cancel
recover tablespace ts_name until time '2004-03-21:22:59:04'
recover tablespace ts_name until change 123456

recover database using backup controlfile

recover database until cancel
recover database until time '2004-03-21:22:59:04'
recover database until change 123456

recover datafile 'filename' until cancel
recover datafile 'filename' until time '2004-03-21:22:59:04'
recover datafile 'filename' until change 123456

recover tablespace ts_name until cancel
recover tablespace ts_name until time '2004-03-21:22:59:04'
recover tablespace ts_name until change 123456

recover database using backup controlfile

Until time
Performs a incomplete recovery (=Point in time recovery).
The format of the time is 'YYYY-MM-DD:HH24:MI:SS'


recover automatic database until time '2005-02-14:15:45:00';



recover database UNTIL TIME '2010-05-08:08:42:00'


recover database UNTIL TIME '2010-05-08:08:42:00';
recover database until time '2004-03-21:22:59:04'

Oracle Disable/Enable constraints for schema

Oracle Disable/Enable constraints for schema
Oracle constraint Disable
Disable all table constraints in Oracle




spool disable_cons.sql
select 'alter table '||owner||'.'||table_name||' disable constraint '
||constraint_name||'CASCADE ;'
from all_constraints
where status= 'ENABLED'
and owner =upper('&&Owner')
and constraint_type ='P'
union
select 'alter table '||owner||'.'||table_name||' disable constraint '
||constraint_name||' CASCADE ;'
from all_constraints
where status = 'ENABLED'
and CONSTRAINT_TYPE ='R'
and r_constraint_name in
(select constraint_name from all_constraints
where owner =upper('&&Owner')
and CONSTRAINT_TYPE ='P'
)

/
spool off
spool disable_triggers.sql
select 'alter trigger '||owner||'.'||trigger_name||' disable;'
from all_triggers
where owner =upper('&&Owner')
and status ='ENABLED'
/
spool off
set verify on timing on echo on heading on
set feedback on pagesize 20 linesize 80


@disable_cons.sql ---- run two time
@disable_triggers.sql

check

select OWNER,
TABLE_NAME,
CONSTRAINT_NAME,
decode(CONSTRAINT_TYPE, 'C','Check',
'P','Primary Key',
'U','Unique',
'R','Foreign Key',
'V','With Check Option') type,
STATUS
from dba_constraints
where STATUS = 'ENABLED'
and owner =upper('&&Owner')
order by OWNER, TABLE_NAME, CONSTRAINT_NAME







after diable the constraints then enable them via this script




spool enable_cons.sql
select 'alter table '||owner||'.'||table_name||' ENABLE constraint '
||constraint_name||';'
from all_constraints
where status= 'DISABLED'
and owner =upper('&&Owner')
and constraint_type ='P'
union
select 'alter table '||owner||'.'||table_name||' ENABLE constraint '
||constraint_name||';'
from all_constraints
where status = 'DISABLED'
and CONSTRAINT_TYPE ='R'
and r_constraint_name in
(select constraint_name from all_constraints
where owner =upper('&&Owner')
and CONSTRAINT_TYPE ='P'
)
/
spool off
spool enable_triggers.sql
select 'alter trigger '||owner||'.'||trigger_name||' enable;'
from all_triggers
where owner =upper('&&Owner')
and status ='DISABLED'
/
spool off
set verify on timing on echo on heading on
set feedback on pagesize 20 linesize 80

@enable_cons.sql
@enable_triggers.sql



Then check

select OWNER,
TABLE_NAME,
CONSTRAINT_NAME,
decode(CONSTRAINT_TYPE, 'C','Check',
'P','Primary Key',
'U','Unique',
'R','Foreign Key',
'V','With Check Option') type,
STATUS
from dba_constraints
where STATUS = 'DISABLED'
and owner =upper('&&Owner')
order by OWNER, TABLE_NAME, CONSTRAINT_NAME


or






BEGIN
FOR c IN
(SELECT c.owner, c.table_name, c.constraint_name
FROM user_constraints c, user_tables t
WHERE c.table_name = t.table_name
AND c.status = 'ENABLED'
ORDER BY c.constraint_type DESC)
LOOP
dbms_utility.exec_ddl_statement('alter table ' || c.owner || '.' || c.table_name || ' disable constraint ' || c.constraint_name||' CASCADE ;' );
END LOOP;
END;
/




BEGIN
FOR c IN
(SELECT c.owner, c.table_name, c.constraint_name
FROM user_constraints c, user_tables t
WHERE c.table_name = t.table_name
AND c.status = 'DISABLED'
ORDER BY c.constraint_type)
LOOP
dbms_utility.exec_ddl_statement('alter table ' || c.owner || '.' || c.table_name || ' enable constraint ' || c.constraint_name);
END LOOP;
END;
/

Oracle Unix Pid to oracle sql..

http://anuj-singh.blogspot.com/2017/08/oracle-process-info.html

Unix pid to sql
Unix pid to oraclesql
pid to sql

------

set serveroutput on size 50000
set echo off feed off veri off
accept 1 prompt 'Enter Unix process id: '


DECLARE
v_sid number;
s sys.v_$session%ROWTYPE;
p sys.v_$process%ROWTYPE;
BEGIN
begin
select sid into v_sid
from sys.v_$process p, sys.v_$session s
where p.addr = s.paddr
and (p.spid = &&1
or s.process = '&&1');
exception
when no_data_found then
dbms_output.put_line('Unable to find process id &&1!!!');
return;
when others then
dbms_output.put_line(sqlerrm);
return;
end;

select * into s from sys.v_$session where sid = v_sid;
select * into p from sys.v_$process where addr = s.paddr;

dbms_output.put_line('=====================================================================');
dbms_output.put_line('SID/Serial : '|| s.sid||','||s.serial#);
dbms_output.put_line('Foreground : '|| 'PID: '||s.process||' - '||s.program);
dbms_output.put_line('Shadow : '|| 'PID: '||p.spid||' - '||p.program);
dbms_output.put_line('Terminal : '|| s.terminal || '/ ' || p.terminal);
dbms_output.put_line('OS User : '|| s.osuser||' on '||s.machine);
dbms_output.put_line('Ora User : '|| s.username);
dbms_output.put_line('Status Flags: '|| s.status||' '||s.server||' '||s.type);
dbms_output.put_line('Tran Active : '|| nvl(s.taddr, 'NONE'));
dbms_output.put_line('Login Time : '|| to_char(s.logon_time, 'Dy HH24:MI:SS'));
dbms_output.put_line('Last Call : '|| to_char(sysdate-(s.last_call_et/60/60/24), 'Dy HH24:MI:SS') || ' - ' || to_char
(s.last_call_et/60, '990.0') || ' min');
dbms_output.put_line('Lock/ Latch : '|| nvl(s.lockwait, 'NONE')||'/ '||nvl(p.latchwait, 'NONE'));
dbms_output.put_line('Latch Spin : '|| nvl(p.latchspin, 'NONE'));
dbms_output.put_line('Current SQL statement:');
for c1 in ( select * from sys.v_$sqltext
where HASH_VALUE = s.sql_hash_value order by piece) loop
dbms_output.put_line(chr(9)||c1.sql_text);
end loop;
dbms_output.put_line('Previous SQL statement:');
for c1 in ( select * from sys.v_$sqltext
where HASH_VALUE = s.prev_hash_value order by piece) loop
dbms_output.put_line(chr(9)||c1.sql_text);
end loop;
dbms_output.put_line('Session Waits:');
for c1 in ( select * from sys.v_$session_wait where sid = s.sid) loop
dbms_output.put_line(chr(9)||c1.state||': '||c1.event);
end loop;
-- dbms_output.put_line('Connect Info:');
-- for c1 in ( select * from sys.v_$session_connect_info where sid = s.sid) loop
-- dbms_output.put_line(chr(9)||': '||c1.network_service_banner);
-- end loop;
dbms_output.put_line('Locks:');
for c1 in ( select
decode(l.type,
-- Long locks
'TM', 'DML/DATA ENQ', 'TX', 'TRANSAC ENQ',
'UL', 'PLS USR LOCK',
-- Short locks
'BL', 'BUF HASH TBL', 'CF', 'CONTROL FILE',
'CI', 'CROSS INST F', 'DF', 'DATA FILE ',
'CU', 'CURSOR BIND ',
'DL', 'DIRECT LOAD ', 'DM', 'MOUNT/STRTUP',
'DR', 'RECO LOCK ', 'DX', 'DISTRIB TRAN',
'FS', 'FILE SET ', 'IN', 'INSTANCE NUM',
'FI', 'SGA OPN FILE',
'IR', 'INSTCE RECVR', 'IS', 'GET STATE ',
'IV', 'LIBCACHE INV', 'KK', 'LOG SW KICK ',
'LS', 'LOG SWITCH ',
'MM', 'MOUNT DEF ', 'MR', 'MEDIA RECVRY',
'PF', 'PWFILE ENQ ', 'PR', 'PROCESS STRT',
'RT', 'REDO THREAD ', 'SC', 'SCN ENQ ',
'RW', 'ROW WAIT ',
'SM', 'SMON LOCK ', 'SN', 'SEQNO INSTCE',
'SQ', 'SEQNO ENQ ', 'ST', 'SPACE TRANSC',
'SV', 'SEQNO VALUE ', 'TA', 'GENERIC ENQ ',
'TD', 'DLL ENQ ', 'TE', 'EXTEND SEG ',
'TS', 'TEMP SEGMENT', 'TT', 'TEMP TABLE ',
'UN', 'USER NAME ', 'WL', 'WRITE REDO ',
'TYPE='||l.type) type,
decode(l.lmode, 0, 'NONE', 1, 'NULL', 2, 'RS', 3, 'RX',
4, 'S', 5, 'RSX', 6, 'X',
to_char(l.lmode) ) lmode,
decode(l.request, 0, 'NONE', 1, 'NULL', 2, 'RS', 3, 'RX',
4, 'S', 5, 'RSX', 6, 'X',
to_char(l.request) ) lrequest,
decode(l.type, 'MR', o.name,
'TD', o.name,
'TM', o.name,
'RW', 'FILE#='||substr(l.id1,1,3)||
' BLOCK#='||substr(l.id1,4,5)||' ROW='||l.id2,
'TX', 'RS+SLOT#'||l.id1||' WRP#'||l.id2,
'WL', 'REDO LOG FILE#='||l.id1,
'RT', 'THREAD='||l.id1,
'TS', decode(l.id2, 0, 'ENQUEUE', 'NEW BLOCK ALLOCATION'),
'ID1='||l.id1||' ID2='||l.id2) objname
from sys.v_$lock l, sys.obj$ o
where sid = s.sid
and l.id1 = o.obj#(+) ) loop
dbms_output.put_line(chr(9)||c1.type||' H: '||c1.lmode||' R: '||c1.lrequest||' - '||c1.objname);
end loop;
dbms_output.put_line('=====================================================================');
END;
/

undef 1

/*

FINA: ORA7> @\tmp\u
Enter Unix process id: 13263
=====================================================================
SID/Serial : 67,558
Foreground : PID: 13262 - runform30@bel2 (TNS interface)
Shadow : PID: 13263 - oracle@bel2 (TNS V1-V2)
Terminal : ttyq5/ ttyq5
OS User : e8677 on bel2
Ora User : ADMN
Status Flags: INACTIVE DEDICATED USER
Tran Active : NONE
Login Time : Mon 14:15:14
Last Call : Mon 14:38:05 - 1.2 min
Lock/ Latch : NONE/ NONE
Latch Spin : NONE
Current SQL statement:
SELECT TO_CHAR(DECODE(LENGTH(:b1),8,TO_DATE(:b2,'DDMMRRRR'),9,TO
_DATE(:b3,'DDMONRRRR'),10,TO_DATE(:b4,'DD/MM/RRRR'),11,TO_DATE(:
b5,'DD-MON-RRRR')),'DD-MON-RRRR') FROM DUAL
Previous SQL statement:
Session Waits:
WAITING: SQL*Net message from client
Locks:
=========================

Oracle 10g +

set serveroutput on size 50000 echo off feed off veri off
accept 1 prompt 'Enter Unix process id: '

DECLARE
v_sid number;
s v$session%ROWTYPE;
p v$process%ROWTYPE;
BEGIN
begin
select sid into v_sid from v$process p, v$session s
where p.addr = s.paddr
and (p.spid = &&1
or s.process = '&&1');
exception
when no_data_found then
dbms_output.put_line('Unable to find process id &&1!!!');
return;
when others then
dbms_output.put_line(sqlerrm);
return;
end;

select * into s from v$session where sid = v_sid;
select * into p from v$process where addr = s.paddr;

dbms_output.put_line('=====================================================================');
dbms_output.put_line('SID/Serial   : '|| s.sid||','||s.serial#);
dbms_output.put_line('Foreground   : '|| 'PID: '||s.process||' - '||s.program);
dbms_output.put_line('Shadow       : '|| 'PID: '||p.spid||' - '||p.program);
dbms_output.put_line('Terminal     : '|| s.terminal || '/ ' || p.terminal);
dbms_output.put_line('OS User      : '|| s.osuser||' on '||s.machine);
dbms_output.put_line('Ora User     : '|| s.username);
dbms_output.put_line('Status Flags : '|| s.status||' '||s.server||' '||s.type);
dbms_output.put_line('Tran Active  : '|| nvl(s.taddr, 'NONE'));
dbms_output.put_line('Login Time   : '|| to_char(s.logon_time, 'Dy HH24:MI:SS'));
dbms_output.put_line('Last Call    : '|| to_char(sysdate-(s.last_call_et/60/60/24), 'Dy HH24:MI:SS') || ' - ' || to_char (s.last_call_et/60, '990.0') || ' min');
dbms_output.put_line('Lock/ Latch  : '|| nvl(s.lockwait, 'NONE')||'/ '||nvl(p.latchwait, 'NONE'));
dbms_output.put_line('Latch Spin   : '|| nvl(p.latchspin, 'NONE'));
dbms_output.put_line('Session status  : '|| s.status);
dbms_output.put_line('Session event   : '|| s.event);
dbms_output.put_line('Blocking Session: '|| s.blocking_session);
dbms_output.put_line('Sql Id       : '|| s.sql_id);
dbms_output.put_line('Prev Sql Id  : '|| s.PREV_SQL_ID);
dbms_output.put_line('Current SQL statement:');
for c1 in ( select * from v$sqltext
where HASH_VALUE = s.sql_hash_value order by piece) loop
dbms_output.put_line(chr(9)||c1.sql_text);
end loop;

dbms_output.put_line('Previous SQL statement:');
for c1 in ( select * from v$sqltext where HASH_VALUE = s.prev_hash_value order by piece) loop
dbms_output.put_line(chr(9)||c1.sql_text);
end loop;
-- dbms_output.put_line('Connect Info:');
-- for c1 in ( select * from v$session_connect_info where sid = s.sid) loop
-- dbms_output.put_line(chr(9)||': '||c1.network_service_banner);
-- end loop;

dbms_output.put_line('Locks:');
for c1 in ( select
decode(l.type,
-- Long locks
'TM', 'DML/DATA ENQ', 'TX', 'TRANSAC ENQ',
'UL', 'PLS USR LOCK',
-- Short locks
'BL', 'BUF HASH TBL', 'CF', 'CONTROL FILE',
'CI', 'CROSS INST F', 'DF', 'DATA FILE ',
'CU', 'CURSOR BIND ',
'DL', 'DIRECT LOAD ', 'DM', 'MOUNT/STRTUP',
'DR', 'RECO LOCK ', 'DX', 'DISTRIB TRAN',
'FS', 'FILE SET ', 'IN', 'INSTANCE NUM',
'FI', 'SGA OPN FILE',
'IR', 'INSTCE RECVR', 'IS', 'GET STATE ',
'IV', 'LIBCACHE INV', 'KK', 'LOG SW KICK ',
'LS', 'LOG SWITCH ',
'MM', 'MOUNT DEF ', 'MR', 'MEDIA RECVRY',
'PF', 'PWFILE ENQ ', 'PR', 'PROCESS STRT',
'RT', 'REDO THREAD ', 'SC', 'SCN ENQ ',
'RW', 'ROW WAIT ',
'SM', 'SMON LOCK ', 'SN', 'SEQNO INSTCE',
'SQ', 'SEQNO ENQ ', 'ST', 'SPACE TRANSC',
'SV', 'SEQNO VALUE ', 'TA', 'GENERIC ENQ ',
'TD', 'DLL ENQ ', 'TE', 'EXTEND SEG ',
'TS', 'TEMP SEGMENT', 'TT', 'TEMP TABLE ',
'UN', 'USER NAME ', 'WL', 'WRITE REDO ',
'TYPE='||l.type) type,
decode(l.lmode, 0, 'NONE', 1, 'NULL', 2, 'RS', 3, 'RX',4, 'S', 5, 'RSX', 6, 'X',to_char(l.lmode) ) lmode,
decode(l.request, 0, 'NONE', 1, 'NULL', 2, 'RS', 3, 'RX',4, 'S', 5, 'RSX', 6, 'X',to_char(l.request) ) lrequest,
decode(l.type, 'MR', o.name,
'TD', o.name,
'TM', o.name,
'RW', 'FILE#='||substr(l.id1,1,3)||' BLOCK#='||substr(l.id1,4,5)||' ROW='||l.id2,'TX', 'RS+SLOT#'||l.id1||' WRP#'||l.id2,
'WL', 'REDO LOG FILE#='||l.id1,
'RT', 'THREAD='||l.id1,
'TS', decode(l.id2, 0, 'ENQUEUE', 'NEW BLOCK ALLOCATION'),
'ID1='||l.id1||' ID2='||l.id2) objname
from v$lock l, obj$ o
where sid = s.sid
and l.id1 = o.obj#(+) ) loop
dbms_output.put_line(chr(9)||c1.type||' H: '||c1.lmode||' R: '||c1.lrequest||' - '||c1.objname);
end loop;

dbms_output.put_line('=====================================================================');

END;
/
undef 1

============

SID to SQL 

 set serveroutput on
define inst_id=1
define sid=2650

DECLARE
 v_sid number;
 vs_cnt number;
 s sys.gv_$session%ROWTYPE;
 p sys.gv_$process%ROWTYPE;
 cursor cur_c1 is select sid from sys.gv_$process p,sys.gv_$session s where p.addr = s.paddr and sid = &SID 
 and s.inst_id='&inst_id' and p.inst_id=s.inst_id;
BEGIN
 dbms_output.put_line('=====================================================================');
 select nvl(count(sid),0) into vs_cnt from sys.gv_$process p, sys.gv_$session s where p.addr = s.paddr and sid = &SID 
 and s.inst_id='&inst_id' and p.inst_id=s.inst_id;
 open cur_c1;
 LOOP
 FETCH cur_c1 INTO v_sid;
 EXIT WHEN (cur_c1%NOTFOUND);
 select * into s from sys.gv_$session where sid = v_sid and inst_id='&inst_id';
 select * into p from sys.gv_$process where addr = s.paddr and inst_id='&inst_id';
 dbms_output.put_line('INST_ID : '|| s.inst_id);
 dbms_output.put_line('SID/Serial : '|| s.sid||','||s.serial#);
 dbms_output.put_line('Foreground : '|| 'PID: '||s.process||' - '||s.program);
 dbms_output.put_line('Shadow : '|| 'PID: '||p.spid||' - '||p.program);
 dbms_output.put_line('Terminal : '|| s.terminal || '/ ' || p.terminal);
 dbms_output.put_line('OS User : '|| s.osuser||' on '||s.machine);
 dbms_output.put_line('Ora User : '|| s.username);
 dbms_output.put_line('Details : '|| s.action||' - '||s.module);
 dbms_output.put_line('Status Flags: '|| s.status||' '||s.server||' '||s.type);
 dbms_output.put_line('Tran Active : '|| nvl(s.taddr, 'NONE'));
 dbms_output.put_line('Login Time : '|| to_char(s.logon_time, 'Dy HH24:MI:SS'));
 dbms_output.put_line('Last Call : '|| to_char(sysdate-(s.last_call_et/60/60/24), 'Dy HH24:MI:SS') || ' - ' || to_char(s.last_call_et/60, '99999990.0') || ' min');
 dbms_output.put_line('Lock/ Latch : '|| nvl(s.lockwait, 'NONE')||'/ '||nvl(p.latchwait, 'NONE'));
 dbms_output.put_line('Latch Spin : '|| nvl(p.latchspin, 'NONE'));
 dbms_output.put_line('Current SQL statement:');
 for c1 in ( select * from sys.gv_$sqltext where HASH_VALUE = s.sql_hash_value and inst_id='&inst_id' order by piece)
 loop
 dbms_output.put_line(chr(9)||c1.sql_text);
 end loop;
 dbms_output.put_line('Previous SQL statement:');
 for c1 in ( select * from sys.gv_$sqltext where HASH_VALUE = s.prev_hash_value and inst_id='&inst_id' order by piece)
 loop
 dbms_output.put_line(chr(9)||c1.sql_text);
 end loop;
 dbms_output.put_line('Session Waits:');
 for c1 in ( select * from sys.gv_$session_wait where sid = s.sid and inst_id='&inst_id')
 loop
 dbms_output.put_line(chr(9)||c1.state||': '||c1.event);
 end loop;
 dbms_output.put_line('Connect Info:');
 for c1 in ( select * from sys.gv_$session_connect_info where sid = s.sid and inst_id='&inst_id') loop
 dbms_output.put_line(chr(9)||': '||c1.network_service_banner);
 end loop;
 dbms_output.put_line('Locks:');
 for c1 in ( select /*+ RULE */ decode(l.type,
 -- Long locks
 'TM', 'DML/DATA ENQ', 'TX', 'TRANSAC ENQ',
 'UL', 'PLS USR LOCK',
 -- Short locks
 'BL', 'BUF HASH TBL', 'CF', 'CONTROL FILE',
 'CI', 'CROSS INST F', 'DF', 'DATA FILE ',
 'CU', 'CURSOR BIND ',
 'DL', 'DIRECT LOAD ', 'DM', 'MOUNT/STRTUP',
 'DR', 'RECO LOCK ', 'DX', 'DISTRIB TRAN',
 'FS', 'FILE SET ', 'IN', 'INSTANCE NUM',
 'FI', 'SGA OPN FILE',
 'IR', 'INSTCE RECVR', 'IS', 'GET STATE ',
 'IV', 'LIBCACHE INV', 'KK', 'LOG SW KICK ',
 'LS', 'LOG SWITCH ',
 'MM', 'MOUNT DEF ', 'MR', 'MEDIA RECVRY',
 'PF', 'PWFILE ENQ ', 'PR', 'PROCESS STRT',
 'RT', 'REDO THREAD ', 'SC', 'SCN ENQ ',
 'RW', 'ROW WAIT ',
 'SM', 'SMON LOCK ', 'SN', 'SEQNO INSTCE',
 'SQ', 'SEQNO ENQ ', 'ST', 'SPACE TRANSC',
 'SV', 'SEQNO VALUE ', 'TA', 'GENERIC ENQ ',
 'TD', 'DLL ENQ ', 'TE', 'EXTEND SEG ',
 'TS', 'TEMP SEGMENT', 'TT', 'TEMP TABLE ',
 'UN', 'USER NAME ', 'WL', 'WRITE REDO ',
 'TYPE='||l.type) type,
 decode(l.lmode, 0, 'NONE', 1, 'NULL', 2, 'RS', 3, 'RX',
 4, 'S', 5, 'RSX', 6, 'X',
 to_char(l.lmode) ) lmode,
 decode(l.request, 0, 'NONE', 1, 'NULL', 2, 'RS', 3, 'RX',
 4, 'S', 5, 'RSX', 6, 'X',
 to_char(l.request) ) lrequest,
 decode(l.type, 'MR', o.name,
 'TD', o.name,
 'TM', o.name,
 'RW', 'FILE#='||substr(l.id1,1,3)||
 ' BLOCK#='||substr(l.id1,4,5)||' ROW='||l.id2,
 'TX', 'RS+SLOT#'||l.id1||' WRP#'||l.id2,
 'WL', 'REDO LOG FILE#='||l.id1,
 'RT', 'THREAD='||l.id1,
 'TS', decode(l.id2, 0, 'ENQUEUE', 'NEW BLOCK ALLOCATION'),
 'ID1='||l.id1||' ID2='||l.id2) objname
 from sys.gv_$lock l, sys.obj$ o
 where sid = s.sid
 and inst_id='&inst_id'
 and l.id1 = o.obj#(+) )
 loop
 dbms_output.put_line(chr(9)||c1.type||' H: '||c1.lmode||' R: '||c1.lrequest||' - '||c1.objname);
 end loop;
 dbms_output.put_line('=====================================================================');
 END LOOP;

close cur_c1;
exception
 when no_data_found then
 dbms_output.put_line('Unable to find process id &&SID for the instance number '||'&inst_id'||' !!!');
 dbms_output.put_line('=====================================================================');
 return;
 when others then
 dbms_output.put_line(sqlerrm);
 return;
END;
/

with sql id

define 1=58982


set serveroutput on size 50000
DECLARE
v_sid number;
s sys.v_$session%ROWTYPE;
p sys.v_$process%ROWTYPE;
BEGIN
begin
select distinct sid into v_sid
from sys.v_$process p, sys.v_$session s
where p.addr = s.paddr
and (p.spid = &&1
or s.process = '&&1')
and rownum<2;
exception
when no_data_found then
dbms_output.put_line('Unable to find process id &&1!!!');
return;
when others then
dbms_output.put_line(sqlerrm);
return;
end;

select * into s from sys.v_$session where sid = v_sid;
select * into p from sys.v_$process where addr = s.paddr;

dbms_output.put_line('=====================================================================');
dbms_output.put_line('SID/Serial : '|| s.sid||','||s.serial#);
dbms_output.put_line('Foreground : '|| 'PID: '||s.process||' - '||s.program);
dbms_output.put_line('Sql_id/Previous: '|| 'Sql_id: '||s.SQL_ID||' - '||s.PREV_SQL_ID);
dbms_output.put_line('Shadow : '|| 'PID: '||p.spid||' - '||p.program);
dbms_output.put_line('Terminal : '|| s.terminal || '/ ' || p.terminal);
dbms_output.put_line('OS User : '|| s.osuser||' on '||s.machine);
dbms_output.put_line('Ora User : '|| s.username);
dbms_output.put_line('Status Flags: '|| s.status||' '||s.server||' '||s.type);
dbms_output.put_line('Tran Active : '|| nvl(s.taddr, 'NONE'));
dbms_output.put_line('Login Time : '|| to_char(s.logon_time, 'Dy HH24:MI:SS'));
dbms_output.put_line('Last Call : '|| to_char(sysdate-(s.last_call_et/60/60/24), 'Dy HH24:MI:SS') || ' - ' || to_char
(s.last_call_et/60, '990.0') || ' min');
dbms_output.put_line('Lock/ Latch : '|| nvl(s.lockwait, 'NONE')||'/ '||nvl(p.latchwait, 'NONE'));
dbms_output.put_line('Latch Spin : '|| nvl(p.latchspin, 'NONE'));
dbms_output.put_line('Current SQL statement:');
for c1 in ( select * from sys.v_$sqltext
where HASH_VALUE = s.sql_hash_value order by piece) loop
dbms_output.put_line(chr(9)||c1.sql_text);
end loop;
dbms_output.put_line('Previous SQL statement:');
for c1 in ( select * from sys.v_$sqltext
where HASH_VALUE = s.prev_hash_value order by piece) loop
dbms_output.put_line(chr(9)||c1.sql_text);
end loop;
dbms_output.put_line('Session Waits:');
for c1 in ( select * from sys.v_$session_wait where sid = s.sid) loop
dbms_output.put_line(chr(9)||c1.state||': '||c1.event);
end loop;
-- dbms_output.put_line('Connect Info:');
-- for c1 in ( select * from sys.v_$session_connect_info where sid = s.sid) loop
-- dbms_output.put_line(chr(9)||': '||c1.network_service_banner);
-- end loop;
dbms_output.put_line('Locks:');
for c1 in ( select
decode(l.type,
-- Long locks
'TM', 'DML/DATA ENQ', 'TX', 'TRANSAC ENQ',
'UL', 'PLS USR LOCK',
-- Short locks
'BL', 'BUF HASH TBL', 'CF', 'CONTROL FILE',
'CI', 'CROSS INST F', 'DF', 'DATA FILE ',
'CU', 'CURSOR BIND ',
'DL', 'DIRECT LOAD ', 'DM', 'MOUNT/STRTUP',
'DR', 'RECO LOCK ', 'DX', 'DISTRIB TRAN',
'FS', 'FILE SET ', 'IN', 'INSTANCE NUM',
'FI', 'SGA OPN FILE',
'IR', 'INSTCE RECVR', 'IS', 'GET STATE ',
'IV', 'LIBCACHE INV', 'KK', 'LOG SW KICK ',
'LS', 'LOG SWITCH ',
'MM', 'MOUNT DEF ', 'MR', 'MEDIA RECVRY',
'PF', 'PWFILE ENQ ', 'PR', 'PROCESS STRT',
'RT', 'REDO THREAD ', 'SC', 'SCN ENQ ',
'RW', 'ROW WAIT ',
'SM', 'SMON LOCK ', 'SN', 'SEQNO INSTCE',
'SQ', 'SEQNO ENQ ', 'ST', 'SPACE TRANSC',
'SV', 'SEQNO VALUE ', 'TA', 'GENERIC ENQ ',
'TD', 'DLL ENQ ', 'TE', 'EXTEND SEG ',
'TS', 'TEMP SEGMENT', 'TT', 'TEMP TABLE ',
'UN', 'USER NAME ', 'WL', 'WRITE REDO ',
'TYPE='||l.type) type,
decode(l.lmode, 0, 'NONE', 1, 'NULL', 2, 'RS', 3, 'RX',
4, 'S', 5, 'RSX', 6, 'X',
to_char(l.lmode) ) lmode,
decode(l.request, 0, 'NONE', 1, 'NULL', 2, 'RS', 3, 'RX',
4, 'S', 5, 'RSX', 6, 'X',
to_char(l.request) ) lrequest,
decode(l.type, 'MR', o.name,
'TD', o.name,
'TM', o.name,
'RW', 'FILE#='||substr(l.id1,1,3)||
' BLOCK#='||substr(l.id1,4,5)||' ROW='||l.id2,
'TX', 'RS+SLOT#'||l.id1||' WRP#'||l.id2,
'WL', 'REDO LOG FILE#='||l.id1,
'RT', 'THREAD='||l.id1,
'TS', decode(l.id2, 0, 'ENQUEUE', 'NEW BLOCK ALLOCATION'),
'ID1='||l.id1||' ID2='||l.id2) objname
from sys.v_$lock l, sys.obj$ o
where sid = s.sid
and l.id1 = o.obj#(+) ) loop
dbms_output.put_line(chr(9)||c1.type||' H: '||c1.lmode||' R: '||c1.lrequest||' - '||c1.objname);
end loop;
dbms_output.put_line('=====================================================================');
END;
/

undef 1

====

set linesize 500 pagesize 300
col SESSION  for a50
SELECT to_char(ssn.sid, '9999') || ' - ' || nvl(ssn.username, nvl(bgp.name, 'background')) ||
nvl(lower(ssn.machine), ins.host_name) "SESSION",
to_char(prc.spid, '999999999') "PID/THREAD",
to_char((se1.value/1024)/1024, '999G999G990D00') || ' MB' " CURRENT SIZE",
to_char((se2.value/1024)/1024, '999G999G990D00') || ' MB' " MAXIMUM SIZE",ssn.sql_id,prev_sql_id
FROM v$sesstat se1, v$sesstat se2, v$session ssn, v$bgprocess bgp, v$process prc,
v$instance ins, v$statname stat1, v$statname stat2
WHERE se1.statistic# = stat1.statistic# and stat1.name = 'session pga memory'
AND se2.statistic# = stat2.statistic# and stat2.name = 'session pga memory max'
AND se1.sid = ssn.sid
AND se2.sid = ssn.sid
AND ssn.paddr = bgp.paddr
AND ssn.paddr = prc.addr (+)
order by 3 desc;