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;
/

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

CREATE OR REPLACE PROCEDURE control_fkeys(

 p_table_name IN dba_constraints.table_name%TYPE ,

 p_owner IN dba_constraints.table_name%TYPE,

 p_enable_flag IN NUMBER

,p_status IN OUT NUMERIC)

AUTHID DEFINER IS

 -- constants

 k_enable dba_constraints.status%TYPE := 'ENABLE';

 k_disable dba_constraints.status%TYPE := 'DISABLE';

 -- identify fkeys on pkey for given table

 CURSOR id_fkeys (

 c_table_name dba_constraints.table_name%TYPE

 ,c_owner dba_constraints.owner%TYPE

 ,c_status dba_constraints.status%TYPE

  )

IS

  SELECT table_name, constraint_name fkey, r_constraint_name pkey,status

 FROM dba_constraints

WHERE ( constraint_type='R' and table_name = c_table_name and owner=c_owner) 

--outbound fk constraints

 or

 ( r_constraint_name IN (

 --inbound fk constraints

 SELECT constraint_name

 FROM dba_constraints

 WHERE table_name = c_table_name

 and owner=p_owner

 AND constraint_type='P')

 )

 AND status!=c_status

 ORDER BY table_name, constraint_name;

 /* Note: this select only gets FK constraints that are inbound, meaning

the specified table contains parent records.

 */

-- SELECT table_name, constraint_name fkey, r_constraint_name pkey,status

-- FROM user_constraints

-- WHERE constraint_type='R'

-- AND status!=c_status

-- AND r_constraint_name IN (

-- SELECT constraint_name

-- FROM user_constraints

-- WHERE table_name=c_table_name

-- AND constraint_type='P')

-- ORDER BY table_name, constraint_name;

 -- record variables

 --

 rec_id_fkeys id_fkeys%ROWTYPE;

 --

 -- variables

  l_status dba_constraints.status%TYPE;

 l_table_name dba_constraints.table_name%TYPE;

 l_owner dba_constraints.table_name%TYPE;

 l_stmt VARCHAR2(255);

 l_pkey_name dba_constraints.constraint_name%TYPE;

 BEGIN

 p_status := 0;

 l_table_name := UPPER(p_table_name);

l_owner  := UPPER(p_owner);

 IF (p_enable_flag = 1) THEN

 l_status := k_enable;

 ELSIF (p_enable_flag = 0) THEN

 l_status := k_disable;

 ELSE

 DBMS_OUTPUT.put_line(

 '-- control_fkeys: enable_flag must be 1 or 0 [' || p_enable_flag

|| ']');

 p_status := 1001;

 END IF;

 IF (p_status = 0) THEN -- validated enable flag

 -- a primary key for the given table must exist

 SELECT constraint_name

 INTO l_pkey_name

 FROM dba_constraints

 WHERE table_name=l_table_name

 AND constraint_type='P'

 and owner=l_owner

 ;

 DBMS_OUTPUT.put_line( '-- control_fkeys: ' || l_status || ' foreign key constraints on table ' || l_table_name || ' whose primary key is ' || l_pkey_name);

 OPEN id_fkeys(l_table_name, l_owner,l_status || 'D');

 LOOP -- process foreign keys

 FETCH id_fkeys INTO rec_id_fkeys;

 EXIT WHEN id_fkeys%NOTFOUND;

 IF l_status = k_enable THEN

l_stmt := 'ALTER TABLE ' || rec_id_fkeys.table_name ||' ENABLE NOVALIDATE CONSTRAINT ' ||rec_id_fkeys.fkey;

 DBMS_OUTPUT.put_line(l_stmt);

 --EXECUTE IMMEDIATE l_stmt;

 l_stmt := 'ALTER TABLE ' || rec_id_fkeys.table_name || 'MODIFY ' ||' CONSTRAINT ' || rec_id_fkeys.fkey || 'VALIDATE' ;

 DBMS_OUTPUT.put_line(l_stmt);

 --EXECUTE IMMEDIATE l_stmt;

 ELSE

 l_stmt := 'ALTER TABLE ' || rec_id_fkeys.table_name ||' DISABLE CONSTRAINT ' || rec_id_fkeys.fkey;

 DBMS_OUTPUT.put_line(l_stmt);

 --EXECUTE IMMEDIATE l_stmt;

 END IF;

 END LOOP; -- process foreign keys

 IF (id_fkeys%ROWCOUNT = 0) THEN -- no fkeys found that weren't enabled/disabled

 DBMS_OUTPUT.put_line( '-- control_fkeys: No foreign keys found against table ' || l_table_name || ' to ' || l_status);

 END IF; -- no rows found

 CLOSE id_fkeys;

 END IF; -- validated enable flag

EXCEPTION

WHEN NO_DATA_FOUND THEN -- primary key lookup failed

 p_status := 1002;

 DBMS_OUTPUT.put_line( '-- control_fkeys: no primary key exists for table ' ||l_table_name);

WHEN OTHERS THEN

 p_status := SQLCODE;

 DBMS_OUTPUT.put_line('-- control_fkeys: ' || SQLERRM(p_status));

 IF (id_fkeys%ISOPEN) THEN

 CLOSE id_fkeys;

 END IF;

END control_fkeys;

/

=====

set serveroutput on ;

variable ret_val number;

execute sys.control_fkeys('EMP','SYS',1,:ret_val);

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

--DISABLE constraints

for i in (select 'ALTER TABLE '||ac.owner||'.'||ac.table_name||' DISABLE CONSTRAINT '||constraint_name  as sql_string,constraint_name,ac.table_name

          from all_constraints ac,all_tables at

          where ac.table_name = at.table_name

          and ac.owner=at.owner

          and ac.owner=p_owner 

order by constraint_type desc

) loop

  dbms_output.put_line(current_timestamp||':'||i.sql_string);

 --execute immediate i.sql_string;

  dbms_output.put_line( 'Constraint '||constraint_name ||' on '||table_name ||' is ' || ' Disabled.');

end loop; 

 

-- Enable 

for i in (select 'ALTER TABLE '||ac.owner||'.'||ac.table_name||' ENABLE novalidate CONSTRAINT  '||

          constraint_name  as sql_string,constraint_name,ac.table_name

          from all_constraints ac ,all_tables at

          where ac.table_name = at.table_name

          and ac.owner=at.owner

          and ac.owner=p_owner 

 order by constraint_type

) loop

        dbms_output.put_line(i.sql_string);

        -- execute immediate i.sql_string;

  end loop;

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;