Oracle session doing what ?

Oracle session is doing what  ..???

Oracle session command

set linesize 300 pagesize  200
col tab               for a20
col killstmt          for a35
col laddr             for a12
col lockt             for a30
col command           for a12
col terminal          for a15
select nvl (s.username, 'internal') username, l.sid,nvl (s.terminal, 'none') terminal,decode (command,0, 'none',decode (l.id2,0, u1.name || '.' || substr (t1.name, 1, 20),'none')) tab,
s.last_call_et,
Decode (Command,
0,'UNKNOWN',
1,'CREATE TABLE',
2,'INSERT',
3,'SELECT',
4,'CREATE CLUSTER',
5,'ALTER CLUSTER',
6,'UPDATE',
7,'DELETE',
8,'DROP CLUSTER',
9,'CREATE INDEX',
10,'DROP INDEX',
11,'ALTER INDEX',
12,'DROP TABLE',
13,'CREATE SEQUENCE',
14,'ALTER SEQUENCE',
15,'ALTER TABLE',
16,'DROP SEQUENCE',
17,'GRANT OBJECT',
18,'REVOKE OBJECT',
19,'CREATE SYNONYM',
20,'DROP SYNONYM',
21,'CREATE VIEW',
22,'DROP VIEW',
23,'VALIDATE INDEX',
24,'CREATE PROCEDURE',
25,'ALTER PROCEDURE',
26,'LOCK',
27,'NO-OP',
28,'RENAME',
29,'COMMENT',
30,'AUDIT OBJECT',
31,'NOAUDIT OBJECT',
32,'CREATE DATABASE LINK',
33,'DROP DATABASE LINK',
34,'CREATE DATABASE',
35,'ALTER DATABASE',
36,'CREATE ROLLBACK SEG',
37,'ALTER ROLLBACK SEG',
38,'DROP ROLLBACK SEG',
39,'CREATE TABLESPACE',
40,'ALTER TABLESPACE',
41,'DROP TABLESPACE',
42,'ALTER SESSION',
43,'ALTER USER',
44,'COMMIT',
45,'ROLLBACK',
46,'SAVEPOINT',
47,'PL/SQL EXECUTE',
48,'SET TRANSACTION',
49,'ALTER SYSTEM',
50,'EXPLAIN',
51,'CREATE USER',
52,'CREATE ROLE',
53,'DROP USER',
54,'DROP ROLE',
55,'SET ROLE',
56,'CREATE SCHEMA',
57,'CREATE CONTROL FILE',
59,'CREATE TRIGGER',
60,'ALTER TRIGGER',
61,'DROP TRIGGER',
62,'ANALYZE TABLE',
63,'ANALYZE INDEX',
64,'ANALYZE CLUSTER',
65,'CREATE PROFILE',
66,'DROP PROFILE',
67,'ALTER PROFILE',
68,'DROP PROCEDURE',
70,'ALTER RESOURCE COST',
71,'CREATE MATERIALIZED VIEW LOG',
72,'ALTER MATERIALIZED VIEW LOG',
73,'DROP MATERIALIZED VIEW LOG',
74,'CREATE MATERIALIZED VIEW',
75,'ALTER MATERIALIZED VIEW',
76,'DROP MATERIALIZED VIEW',
77,'CREATE TYPE',
78,'DROP TYPE',
79,'ALTER ROLE',
80,'ALTER TYPE',
81,'CREATE TYPE BODY',
82,'ALTER TYPE BODY',
83,'DROP TYPE BODY',
84,'DROP LIBRARY',
85,'TRUNCATE TABLE',
86,'TRUNCATE CLUSTER',
88,'ALTER VIEW',
91,'CREATE FUNCTION',
92,'ALTER FUNCTION',
93,'DROP FUNCTION',
94,'CREATE PACKAGE',
95,'ALTER PACKAGE',
96,'DROP PACKAGE',
97,'CREATE PACKAGE BODY',
98,'ALTER PACKAGE BODY',
99,'DROP PACKAGE BODY',
100,'LOGON',
101,'LOGOFF',
102,'LOGOFF BY CLEANUP',
103,'SESSION REC',
104,'SYSTEM AUDIT',
105,'SYSTEM NOAUDIT',
106,'AUDIT DEFAULT',
107,'NOAUDIT DEFAULT',
108,'SYSTEM GRANT',
109,'SYSTEM REVOKE',
110,'CREATE PUBLIC SYNONYM',
111,'DROP PUBLIC SYNONYM',
112,'CREATE PUBLIC DATABASE LINK',
113,'DROP PUBLIC DATABASE LINK',
114,'GRANT ROLE',
115,'REVOKE ROLE',
116,'EXECUTE PROCEDURE',
117,'USER COMMENT',
118,'ENABLE TRIGGER',
119,'DISABLE TRIGGER',
120,'ENABLE ALL TRIGGERS',
121,'DISABLE ALL TRIGGERS',
122,'NETWORK ERROR',
123,'EXECUTE TYPE',
128,'FLASHBACK',
129,'CREATE SESSION',
130,'ALTER MINING MODEL',
131,'SELECT MINING MODEL',
133,'CREATE MINING MODEL',
134,'ALTER PUBLIC SYNONYM',
135,'DIRECTORY EXECUTE',
136,'SQL*LOADER DIRECT PATH LOAD',
137,'DATAPUMP DIRECT PATH UNLOAD',
138,'DATABASE STARTUP',
139,'DATABASE SHUTDOWN',
140,'CREATE SQL TXLN PROFILE',
141,'ALTER SQL TXLN PROFILE',
142,'USE SQL TXLN PROFILE',
143,'DROP SQL TXLN PROFILE',
144,'CREATE MEASURE FOLDER',
145,'ALTER MEASURE FOLDER',
146,'DROP MEASURE FOLDER',
147,'CREATE CUBE BUILD PROCESS',
148,'ALTER CUBE BUILD PROCESS',
149,'DROP CUBE BUILD PROCESS',
150,'CREATE CUBE',
151,'ALTER CUBE',
152,'DROP CUBE',
153,'CREATE CUBE DIMENSION',
154,'ALTER CUBE DIMENSION',
155,'DROP CUBE DIMENSION',
157,'CREATE DIRECTORY',
158,'DROP DIRECTORY',
159,'CREATE LIBRARY',
160,'CREATE JAVA',
161,'ALTER JAVA',
162,'DROP JAVA',
163,'CREATE OPERATOR',
164,'CREATE INDEXTYPE',
165,'DROP INDEXTYPE',
166,'ALTER INDEXTYPE',
167,'DROP OPERATOR',
168,'ASSOCIATE STATISTICS',
169,'DISASSOCIATE STATISTICS',
170,'CALL METHOD',
171,'CREATE SUMMARY',
172,'ALTER SUMMARY',
173,'DROP SUMMARY',
174,'CREATE DIMENSION',
175,'ALTER DIMENSION',
176,'DROP DIMENSION',
177,'CREATE CONTEXT',
178,'DROP CONTEXT',
179,'ALTER OUTLINE',
180,'CREATE OUTLINE',
181,'DROP OUTLINE',
182,'UPDATE INDEXES',
183,'ALTER OPERATOR',
190,'PASSWORD CHANGE',
192,'ALTER SYNONYM',
197,'PURGE USER_RECYCLEBIN',
198,'PURGE DBA_RECYCLEBIN',
199,'PURGE TABLESPACE',
200,'PURGE TABLE',
201,'PURGE INDEX',
202,'UNDROP OBJECT',
204,'FLASHBACK DATABASE',
205,'FLASHBACK TABLE',
206,'CREATE RESTORE POINT',
207,'DROP RESTORE POINT',
208,'PROXY AUTHENTICATION ONLY',
209,'DECLARE REWRITE EQUIVALENCE',
210,'ALTER REWRITE EQUIVALENCE',
211,'DROP REWRITE EQUIVALENCE',
212,'CREATE EDITION',
213,'ALTER EDITION',
214,'DROP EDITION',
215,'DROP ASSEMBLY',
216,'CREATE ASSEMBLY',
217,'ALTER ASSEMBLY',
218,'CREATE FLASHBACK ARCHIVE',
219,'ALTER FLASHBACK ARCHIVE',
220,'DROP FLASHBACK ARCHIVE',
225,'ALTER DATABASE LINK',
226,'CREATE PLUGGABLE DATABASE',
227,'ALTER PLUGGABLE DATABASE',
228,'DROP PLUGGABLE DATABASE',
229,'CREATE AUDIT POLICY',
230,'ALTER AUDIT POLICY',
231,'DROP AUDIT POLICY',
232,'CODE-BASED GRANT',
233,'CODE-BASED REVOKE',
238,'ADMINISTER KEY MANAGEMENT',
239,'CREATE MATERIALIZED ZONEMAP',
240,'ALTER MATERIALIZED ZONEMAP',
241,'DROP MATERIALIZED ZONEMAP',
305,'ALTER PUBLIC DATABASE LINK',
Command || ' - ???'
) Command
,s.sql_id
,Decode (L.Lmode,
1, 'No Lock',
2, 'Row Share',
3, 'Row Exclusive',
4, 'Share',
5, 'Share Row Exclusive',
6, 'Exclusive',
'NONE') Lmode,
Decode (L.Request,
1, 'No Lock',
2, 'Row Share',
3, 'Row Exclusive',
4, 'Share',
5, 'Share Row Exclusive',
6, 'Exclusive','NONE') Request,
L.Id1 || '-' || L.Id2 Laddr,
L.Type
|| ' - '
|| Decode
(L.Type,
'BL', 'Buffer hash table instance lock',
'CF', ' Control file schema global enqueue lock',
'CI', 'Cross-instance function invocation instance lock',
'CS', 'Control file schema global enqueue lock',
'CU', 'Cursor bind lock',
'DF', 'Data file instance lock',
'DL', 'Direct loader parallel index create',
'DM', 'Mount/startup db primary/secondary instance lock',
'DR', 'Distributed recovery process lock',
'DX', 'Distributed transaction entry lock',
'FI', 'SGA open-file information lock',
'FS', 'File set lock',
'HW', 'Space management operations on a specific segment lock',
'IN', 'Instance number lock',
'IR', 'Instance recovery serialization global enqueue lock',
'IS', 'Instance state lock',
'IV', 'Library cache invalidation instance lock',
'JQ', 'Job queue lock',
'KK', 'Thread kick lock',
'MB', 'Master buffer hash table instance lock',
'MM', 'Mount definition gloabal enqueue lock',
'MR', 'Media recovery lock',
'PF', 'Password file lock',
'PI', 'Parallel operation lock',
'PR', 'Process startup lock',
'PS', 'Parallel operation lock',
'RE', 'USE_ROW_ENQUEUE enforcement lock',
'RT', 'Redo thread global enqueue lock',
'RW', 'Row wait enqueue lock',
'SC', 'System commit number instance lock',
'SH', 'System commit number high water mark enqueue lock',
'SM', 'SMON lock',
'SN', 'Sequence number instance lock',
'SQ', 'Sequence number enqueue lock',
'SS', 'Sort segment lock',
'ST', 'Space transaction enqueue lock',
'SV', 'Sequence number value lock',
'TA', 'Generic enqueue lock',
'TD', 'DDL enqueue lock',
'TE', 'Extend-segment enqueue lock',
'TM', 'DML enqueue lock',
'TO', 'Temporary Table Object Enqueue',
'TT', 'Temporary table enqueue lock',
'TX', 'Transaction enqueue lock',
'UL', 'User supplied lock',
'UN', 'User name lock',
'US', 'Undo segment DDL lock',
'WL', 'Being-written redo log instance lock',
'WS', 'Write-atomic-log-switch global enqueue lock',
'TS', Decode (L.Id2,
0, 'Temporary segment enqueue lock (ID2=0)',
'New block allocation enqueue lock (ID2=1)'
),
'LA', 'Library cache lock instance lock (A=namespace)',
'LB', 'Library cache lock instance lock (B=namespace)',
'LC', 'Library cache lock instance lock (C=namespace)',
'LD', 'Library cache lock instance lock (D=namespace)',
'LE', 'Library cache lock instance lock (E=namespace)',
'LF', 'Library cache lock instance lock (F=namespace)',
'LG', 'Library cache lock instance lock (G=namespace)',
'LH', 'Library cache lock instance lock (H=namespace)',
'LI', 'Library cache lock instance lock (I=namespace)',
'LJ', 'Library cache lock instance lock (J=namespace)',
'LK', 'Library cache lock instance lock (K=namespace)',
'LL', 'Library cache lock instance lock (L=namespace)',
'LM', 'Library cache lock instance lock (M=namespace)',
'LN', 'Library cache lock instance lock (N=namespace)',
'LO', 'Library cache lock instance lock (O=namespace)',
'LP', 'Library cache lock instance lock (P=namespace)',
'LS', 'Log start/log switch enqueue lock',
'PA', 'Library cache pin instance lock (A=namespace)',
'PB', 'Library cache pin instance lock (B=namespace)',
'PC', 'Library cache pin instance lock (C=namespace)',
'PD', 'Library cache pin instance lock (D=namespace)',
'PE', 'Library cache pin instance lock (E=namespace)',
'PF', 'Library cache pin instance lock (F=namespace)',
'PG', 'Library cache pin instance lock (G=namespace)',
'PH', 'Library cache pin instance lock (H=namespace)',
'PI', 'Library cache pin instance lock (I=namespace)',
'PJ', 'Library cache pin instance lock (J=namespace)',
'PL', 'Library cache pin instance lock (K=namespace)',
'PK', 'Library cache pin instance lock (L=namespace)',
'PM', 'Library cache pin instance lock (M=namespace)',
'PN', 'Library cache pin instance lock (N=namespace)',
'PO', 'Library cache pin instance lock (O=namespace)',
'PP', 'Library cache pin instance lock (P=namespace)',
'PQ', 'Library cache pin instance lock (Q=namespace)',
'PR', 'Library cache pin instance lock (R=namespace)',
'PS', 'Library cache pin instance lock (S=namespace)',
'PT', 'Library cache pin instance lock (T=namespace)',
'PU', 'Library cache pin instance lock (U=namespace)',
'PV', 'Library cache pin instance lock (V=namespace)',
'PW', 'Library cache pin instance lock (W=namespace)',
'PX', 'Library cache pin instance lock (X=namespace)',
'PY', 'Library cache pin instance lock (Y=namespace)',
'PZ', 'Library cache pin instance lock (Z=namespace)',
'QA', 'Row cache instance lock (A=cache)',
'QB', 'Row cache instance lock (B=cache)',
'QC', 'Row cache instance lock (C=cache)',
'QD', 'Row cache instance lock (D=cache)',
'QE', 'Row cache instance lock (E=cache)',
'QF', 'Row cache instance lock (F=cache)',
'QG', 'Row cache instance lock (G=cache)',
'QH', 'Row cache instance lock (H=cache)',
'QI', 'Row cache instance lock (I=cache)',
'QJ', 'Row cache instance lock (J=cache)',
'QL', 'Row cache instance lock (K=cache)',
'QK', 'Row cache instance lock (L=cache)',
'QM', 'Row cache instance lock (M=cache)',
'QN', 'Row cache instance lock (N=cache)',
'QO', 'Row cache instance lock (O=cache)',
'QP', 'Row cache instance lock (P=cache)',
'QQ', 'Row cache instance lock (Q=cache)',
'QR', 'Row cache instance lock (R=cache)',
'QS', 'Row cache instance lock (S=cache)',
'QT', 'Row cache instance lock (T=cache)',
'QU', 'Row cache instance lock (U=cache)',
'QV', 'Row cache instance lock (V=cache)',
'QW', 'Row cache instance lock (W=cache)',
'QX', 'Row cache instance lock (X=cache)',
'QY', 'Row cache instance lock (Y=cache)',
'QZ', 'Row cache instance lock (Z=cache)',
'????'
) Lockt,
' alter system kill Session  ' || ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||''''||' immediate;' killstmt
from gv$lock l, gv$session s, sys.user$ u1, sys.obj$ t1
where l.sid = s.sid
and t1.obj# = decode (l.id2, 0, l.id1, 1)
and u1.user# = t1.owner#
and s.type != 'BACKGROUND'
and s.inst_id =l.inst_id
order by s.sid;


-- ==========================================


set linesize 300 pagesize 400
col PROGRAM      for a50
col killstmt     for a55
col killstmt     for a15
col sql_text     for a50 wrap
col NAME         for a25
col USERNAME     for a15
col COMMAND      for a15
col PROGRAM      for a20
col kill         for a15
select distinct 
-- ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill ,
substr(s.username,1,20) username,substr(s.program,1,50) program, s.sql_id,s.prev_sql_id,n.NAME,
decode(s.command,
0,'UNKNOWN',
1,'CREATE TABLE',
2,'INSERT',
3,'SELECT',
4,'CREATE CLUSTER',
5,'ALTER CLUSTER',
6,'UPDATE',
7,'DELETE',
8,'DROP CLUSTER',
9,'CREATE INDEX',
10,'DROP INDEX',
11,'ALTER INDEX',
12,'DROP TABLE',
13,'CREATE SEQUENCE',
14,'ALTER SEQUENCE',
15,'ALTER TABLE',
16,'DROP SEQUENCE',
17,'GRANT OBJECT',
18,'REVOKE OBJECT',
19,'CREATE SYNONYM',
20,'DROP SYNONYM',
21,'CREATE VIEW',
22,'DROP VIEW',
23,'VALIDATE INDEX',
24,'CREATE PROCEDURE',
25,'ALTER PROCEDURE',
26,'LOCK',
27,'NO-OP',
28,'RENAME',
29,'COMMENT',
30,'AUDIT OBJECT',
31,'NOAUDIT OBJECT',
32,'CREATE DATABASE LINK',
33,'DROP DATABASE LINK',
34,'CREATE DATABASE',
35,'ALTER DATABASE',
36,'CREATE ROLLBACK SEG',
37,'ALTER ROLLBACK SEG',
38,'DROP ROLLBACK SEG',
39,'CREATE TABLESPACE',
40,'ALTER TABLESPACE',
41,'DROP TABLESPACE',
42,'ALTER SESSION',
43,'ALTER USER',
44,'COMMIT',
45,'ROLLBACK',
46,'SAVEPOINT',
47,'PL/SQL EXECUTE',
48,'SET TRANSACTION',
49,'ALTER SYSTEM',
50,'EXPLAIN',
51,'CREATE USER',
52,'CREATE ROLE',
53,'DROP USER',
54,'DROP ROLE',
55,'SET ROLE',
56,'CREATE SCHEMA',
57,'CREATE CONTROL FILE',
59,'CREATE TRIGGER',
60,'ALTER TRIGGER',
61,'DROP TRIGGER',
62,'ANALYZE TABLE',
63,'ANALYZE INDEX',
64,'ANALYZE CLUSTER',
65,'CREATE PROFILE',
66,'DROP PROFILE',
67,'ALTER PROFILE',
68,'DROP PROCEDURE',
70,'ALTER RESOURCE COST',
71,'CREATE MATERIALIZED VIEW LOG',
72,'ALTER MATERIALIZED VIEW LOG',
73,'DROP MATERIALIZED VIEW LOG',
74,'CREATE MATERIALIZED VIEW',
75,'ALTER MATERIALIZED VIEW',
76,'DROP MATERIALIZED VIEW',
77,'CREATE TYPE',
78,'DROP TYPE',
79,'ALTER ROLE',
80,'ALTER TYPE',
81,'CREATE TYPE BODY',
82,'ALTER TYPE BODY',
83,'DROP TYPE BODY',
84,'DROP LIBRARY',
85,'TRUNCATE TABLE',
86,'TRUNCATE CLUSTER',
88,'ALTER VIEW',
91,'CREATE FUNCTION',
92,'ALTER FUNCTION',
93,'DROP FUNCTION',
94,'CREATE PACKAGE',
95,'ALTER PACKAGE',
96,'DROP PACKAGE',
97,'CREATE PACKAGE BODY',
98,'ALTER PACKAGE BODY',
99,'DROP PACKAGE BODY',
100,'LOGON',
101,'LOGOFF',
102,'LOGOFF BY CLEANUP',
103,'SESSION REC',
104,'SYSTEM AUDIT',
105,'SYSTEM NOAUDIT',
106,'AUDIT DEFAULT',
107,'NOAUDIT DEFAULT',
108,'SYSTEM GRANT',
109,'SYSTEM REVOKE',
110,'CREATE PUBLIC SYNONYM',
111,'DROP PUBLIC SYNONYM',
112,'CREATE PUBLIC DATABASE LINK',
113,'DROP PUBLIC DATABASE LINK',
114,'GRANT ROLE',
115,'REVOKE ROLE',
116,'EXECUTE PROCEDURE',
117,'USER COMMENT',
118,'ENABLE TRIGGER',
119,'DISABLE TRIGGER',
120,'ENABLE ALL TRIGGERS',
121,'DISABLE ALL TRIGGERS',
122,'NETWORK ERROR',
123,'EXECUTE TYPE',
128,'FLASHBACK',
129,'CREATE SESSION',
130,'ALTER MINING MODEL',
131,'SELECT MINING MODEL',
133,'CREATE MINING MODEL',
134,'ALTER PUBLIC SYNONYM',
135,'DIRECTORY EXECUTE',
136,'SQL*LOADER DIRECT PATH LOAD',
137,'DATAPUMP DIRECT PATH UNLOAD',
138,'DATABASE STARTUP',
139,'DATABASE SHUTDOWN',
140,'CREATE SQL TXLN PROFILE',
141,'ALTER SQL TXLN PROFILE',
142,'USE SQL TXLN PROFILE',
143,'DROP SQL TXLN PROFILE',
144,'CREATE MEASURE FOLDER',
145,'ALTER MEASURE FOLDER',
146,'DROP MEASURE FOLDER',
147,'CREATE CUBE BUILD PROCESS',
148,'ALTER CUBE BUILD PROCESS',
149,'DROP CUBE BUILD PROCESS',
150,'CREATE CUBE',
151,'ALTER CUBE',
152,'DROP CUBE',
153,'CREATE CUBE DIMENSION',
154,'ALTER CUBE DIMENSION',
155,'DROP CUBE DIMENSION',
157,'CREATE DIRECTORY',
158,'DROP DIRECTORY',
159,'CREATE LIBRARY',
160,'CREATE JAVA',
161,'ALTER JAVA',
162,'DROP JAVA',
163,'CREATE OPERATOR',
164,'CREATE INDEXTYPE',
165,'DROP INDEXTYPE',
166,'ALTER INDEXTYPE',
167,'DROP OPERATOR',
168,'ASSOCIATE STATISTICS',
169,'DISASSOCIATE STATISTICS',
170,'CALL METHOD',
171,'CREATE SUMMARY',
172,'ALTER SUMMARY',
173,'DROP SUMMARY',
174,'CREATE DIMENSION',
175,'ALTER DIMENSION',
176,'DROP DIMENSION',
177,'CREATE CONTEXT',
178,'DROP CONTEXT',
179,'ALTER OUTLINE',
180,'CREATE OUTLINE',
181,'DROP OUTLINE',
182,'UPDATE INDEXES',
183,'ALTER OPERATOR',
190,'PASSWORD CHANGE',
192,'ALTER SYNONYM',
197,'PURGE USER_RECYCLEBIN',
198,'PURGE DBA_RECYCLEBIN',
199,'PURGE TABLESPACE',
200,'PURGE TABLE',
201,'PURGE INDEX',
202,'UNDROP OBJECT',
204,'FLASHBACK DATABASE',
205,'FLASHBACK TABLE',
206,'CREATE RESTORE POINT',
207,'DROP RESTORE POINT',
208,'PROXY AUTHENTICATION ONLY',
209,'DECLARE REWRITE EQUIVALENCE',
210,'ALTER REWRITE EQUIVALENCE',
211,'DROP REWRITE EQUIVALENCE',
212,'CREATE EDITION',
213,'ALTER EDITION',
214,'DROP EDITION',
215,'DROP ASSEMBLY',
216,'CREATE ASSEMBLY',
217,'ALTER ASSEMBLY',
218,'CREATE FLASHBACK ARCHIVE',
219,'ALTER FLASHBACK ARCHIVE',
220,'DROP FLASHBACK ARCHIVE',
225,'ALTER DATABASE LINK',
226,'CREATE PLUGGABLE DATABASE',
227,'ALTER PLUGGABLE DATABASE',
228,'DROP PLUGGABLE DATABASE',
229,'CREATE AUDIT POLICY',
230,'ALTER AUDIT POLICY',
231,'DROP AUDIT POLICY',
232,'CODE-BASED GRANT',
233,'CODE-BASED REVOKE',
238,'ADMINISTER KEY MANAGEMENT',
239,'CREATE MATERIALIZED ZONEMAP',
240,'ALTER MATERIALIZED ZONEMAP',
241,'DROP MATERIALIZED ZONEMAP',
305,'ALTER PUBLIC DATABASE LINK', 
s.command||': Other') command ,
--' alter system kill Session  ' || ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||''''||' immediate;' killstmt
''''||s.sid||','||s.serial#||'@'||s.inst_id||'''' killstmt ,
sq.sql_text sql_text,state ,event,SECONDS_IN_WAIT 
from gv$session s,gv$process p,gv$transaction t,gv$rollstat r,v$rollname n,gv$sql sq
where s.paddr = p.addr
and s.taddr = t.addr (+)
and t.xidusn = r.usn (+)
and r.usn    = n.usn (+)
and s.inst_id =s.inst_id 
and p.inst_id =s.inst_id 
and sq.inst_id=s.inst_id
and s.type   != 'BACKGROUND'
and s.COMMAND!=0
and s.status ='INACTIVE'
and ( (sq.sql_id = s.sql_id  and sq.child_number = s.sql_child_number) or (sq.sql_id = s.prev_sql_id and sq.child_number = s.prev_child_number)  )
and s.USERNAME  Not  in  ( 'SYS' ,  'SYSTEM' ,  'DBSNMP' , 'SYSMAN' , 'OUTLN' , 'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' )
order by 1;



select * from AUDIT_ACTIONS ;

set lines 300  pagesize 200
break on Kill on sid on  username on terminal
col Kill heading 'Kill String'     for a15
col res heading 'Resource Type'    for 999
col id1       for 9999999
col id2       for 9999999
col locking heading 'Lock Held/Lock Requested'   for a40
col lmode heading 'Lock Held'     for a15
col request heading 'Lock Requested'    for a15
col username        for a10  heading "Username"
col terminal heading Term     for a12
col tab       for a30 heading "Table Name"
col owner       for a10
col LAddr heading "ID1 - ID2"     for a18
col Lockt heading "Lock Type"     for a30
col command       for a25
col sid       for 9999
col kill       for a15
select distinct
''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill ,
nvl(S.USERNAME,'Internal') username,
 --       L.SID,
        nvl(S.TERMINAL,'None') terminal,
        decode(command,
0,'None',decode(l.id2,0,U1.NAME||'.'||substr(T1.NAME,1,20),'None')) tab,
decode(command,
0, 'NO COMMAND',
1, 'CREATE TABLE',
2, 'INSERT', 
3,'ALTER SYSTEM',
4,'SYSTEM AUDIT',
5,'CREATE SESSION',
6,'ALTER SESSION',
7,'RESTRICTED SESSION',
8,'TABLE',
9,'CLUSTER',
10,'CREATE TABLESPACE',
11,'ALTER TABLESPACE',
12,'MANAGE TABLESPACE',
13,'DROP TABLESPACE',
14,'TABLESPACE',
15,'UNLIMITED TABLESPACE',
16,'USER',
17,'ROLLBACK SEGMENT',
18,'TYPE',
19,'INDEX',
20,'CREATE USER',
21,'BECOME USER',
22,'ALTER USER',
23,'DROP USER',
24,'SYNONYM',
25,'PUBLIC SYNONYM',
26,'VIEW',
27,'SEQUENCE',
28,'DATABASE LINK',
29,'PUBLIC DATABASE LINK',
30,'CREATE ROLLBACK SEGMENT',
31,'ALTER ROLLBACK SEGMENT',
32,'DROP ROLLBACK SEGMENT',
33,'ROLE',
34,'DIMENSION',
35,'PROCEDURE',
36,'TRIGGER',
37,'PROFILE',
38,'DIRECTORY',
39,'MATERIALIZED VIEW',
40,'CREATE TABLE',
41,'CREATE ANY TABLE',
42,'ALTER ANY TABLE',
43,'BACKUP ANY TABLE',
44,'DROP ANY TABLE',
45,'LOCK ANY TABLE',
46,'COMMENT ANY TABLE',
47,'SELECT ANY TABLE',
48,'INSERT ANY TABLE',
49,'UPDATE ANY TABLE',
50,'DELETE ANY TABLE',
51,'OUTLINE',
52,'MINING MODEL',
54,'ALTER TABLE',
57,'LOCK TABLE',
58,'COMMENT TABLE',
60,'CREATE CLUSTER',
61,'CREATE ANY CLUSTER',
62,'ALTER ANY CLUSTER',
63,'DROP ANY CLUSTER',
65,'SELECT TABLE',
66,'INSERT TABLE',
67,'UPDATE TABLE',
68,'DELETE TABLE',
69,'GRANT TABLE',
71,'CREATE ANY INDEX',
72,'ALTER ANY INDEX',
73,'DROP ANY INDEX',
77,'NOT EXISTS',
78,'ALL STATEMENTS',
80,'CREATE SYNONYM',
81,'CREATE ANY SYNONYM',
82,'DROP ANY SYNONYM',
83,'SYSDBA',
84,'SYSOPER',
85,'CREATE PUBLIC SYNONYM',
86,'DROP PUBLIC SYNONYM',
90,'CREATE VIEW',
91,'CREATE ANY VIEW',
92,'DROP ANY VIEW',
93,'CREATE JAVA SOURCE',
94,'CREATE JAVA CLASS',
95,'CREATE JAVA RESOURCE',
96,'ALTER JAVA SOURCE',
97,'ALTER JAVA CLASS',
98,'ALTER JAVA RESOURCE',
99,'DROP JAVA SOURCE',
100,'DROP JAVA CLASS',
101,'DROP JAVA RESOURCE',
103,'ALTER SEQUENCE',
104,'SELECT SEQUENCE',
105,'CREATE SEQUENCE',
106,'CREATE ANY SEQUENCE',
107,'ALTER ANY SEQUENCE',
108,'DROP ANY SEQUENCE',
109,'SELECT ANY SEQUENCE',
111,'GRANT SEQUENCE',
115,'CREATE DATABASE LINK',
120,'CREATE PUBLIC DATABASE LINK',
121,'DROP PUBLIC DATABASE LINK',
125,'CREATE ROLE',
126,'DROP ANY ROLE',
127,'GRANT ANY ROLE',
128,'ALTER ANY ROLE',
130,'AUDIT ANY',
131,'SYSTEM GRANT',
135,'ALTER DATABASE',
138,'FORCE TRANSACTION',
139,'FORCE ANY TRANSACTION',
140,'CREATE PROCEDURE',
141,'CREATE ANY PROCEDURE',
142,'ALTER ANY PROCEDURE',
143,'DROP ANY PROCEDURE',
144,'EXECUTE ANY PROCEDURE',
146,'EXECUTE PROCEDURE',
147,'GRANT PROCEDURE',
151,'CREATE TRIGGER',
152,'CREATE ANY TRIGGER',
153,'ALTER ANY TRIGGER',
154,'DROP ANY TRIGGER',
160,'CREATE PROFILE',
161,'ALTER PROFILE',
162,'DROP PROFILE',
163,'ALTER RESOURCE COST',
165,'ANALYZE ANY',
167,'GRANT ANY PRIVILEGE',
172,'CREATE MATERIALIZED VIEW',
173,'CREATE ANY MATERIALIZED VIEW',
174,'ALTER ANY MATERIALIZED VIEW',
175,'DROP ANY MATERIALIZED VIEW',
176,'NETWORK',
177,'CREATE ANY DIRECTORY',
178,'DROP ANY DIRECTORY',
179,'GRANT DIRECTORY',
180,'CREATE TYPE',
181,'CREATE ANY TYPE',
182,'ALTER ANY TYPE',
183,'DROP ANY TYPE',
184,'EXECUTE ANY TYPE',
186,'UNDER ANY TYPE',
187,'GRANT TYPE',
188,'CREATE LIBRARY',
189,'CREATE ANY LIBRARY',
190,'ALTER ANY LIBRARY',
191,'DROP ANY LIBRARY',
192,'EXECUTE ANY LIBRARY',
193,'EXECUTE LIBRARY',
200,'CREATE OPERATOR',
201,'CREATE ANY OPERATOR',
202,'ALTER ANY OPERATOR',
203,'DROP ANY OPERATOR',
204,'EXECUTE ANY OPERATOR',
205,'CREATE INDEXTYPE',
206,'CREATE ANY INDEXTYPE',
207,'ALTER ANY INDEXTYPE',
208,'DROP ANY INDEXTYPE',
209,'UNDER ANY VIEW',
210,'QUERY REWRITE',
211,'GLOBAL QUERY REWRITE',
212,'EXECUTE ANY INDEXTYPE',
213,'UNDER ANY TABLE',
214,'CREATE DIMENSION',
215,'CREATE ANY DIMENSION',
216,'ALTER ANY DIMENSION',
217,'DROP ANY DIMENSION',
218,'MANAGE ANY QUEUE',
219,'ENQUEUE ANY QUEUE',
220,'DEQUEUE ANY QUEUE',
221,'CONTEXT',
222,'CREATE ANY CONTEXT',
223,'DROP ANY CONTEXT',
224,'CREATE ANY OUTLINE',
225,'ALTER ANY OUTLINE',
226,'DROP ANY OUTLINE',
227,'ADMINISTER RESOURCE MANAGER',
228,'ADMINISTER DATABASE TRIGGER',
233,'MERGE ANY VIEW',
234,'ON COMMIT REFRESH',
235,'EXEMPT ACCESS POLICY',
236,'RESUMABLE',
237,'SELECT ANY DICTIONARY',
238,'DEBUG CONNECT SESSION',
241,'DEBUG ANY PROCEDURE',
242,'DEBUG PROCEDURE',
243,'FLASHBACK ANY TABLE',
244,'GRANT ANY OBJECT PRIVILEGE',
245,'CREATE EVALUATION CONTEXT',
246,'CREATE ANY EVALUATION CONTEXT',
247,'ALTER ANY EVALUATION CONTEXT',
248,'DROP ANY EVALUATION CONTEXT',
249,'EXECUTE ANY EVALUATION CONTEXT',
250,'CREATE RULE SET',
251,'CREATE ANY RULE SET',
252,'ALTER ANY RULE SET',
253,'DROP ANY RULE SET',
254,'EXECUTE ANY RULE SET',
255,'EXPORT FULL DATABASE',
256,'IMPORT FULL DATABASE',
257,'CREATE RULE',
258,'CREATE ANY RULE',
259,'ALTER ANY RULE',
260,'DROP ANY RULE',
261,'EXECUTE ANY RULE',
262,'ANALYZE ANY DICTIONARY',
263,'ADVISOR',
264,'CREATE JOB',
265,'CREATE ANY JOB',
266,'EXECUTE ANY PROGRAM',
267,'EXECUTE ANY CLASS',
268,'MANAGE SCHEDULER',
269,'SELECT ANY TRANSACTION',
270,'DROP ANY SQL PROFILE',
271,'ALTER ANY SQL PROFILE',
272,'ADMINISTER SQL TUNING SET',
273,'ADMINISTER ANY SQL TUNING SET',
274,'CREATE ANY SQL PROFILE',
275,'EXEMPT IDENTITY POLICY',
276,'MANAGE FILE GROUP',
277,'MANAGE ANY FILE GROUP',
278,'READ ANY FILE GROUP',
279,'CHANGE NOTIFICATION',
280,'CREATE EXTERNAL JOB',
281,'CREATE ANY EDITION',
282,'DROP ANY EDITION',
283,'ALTER ANY EDITION',
284,'CREATE ASSEMBLY',
285,'CREATE ANY ASSEMBLY',
286,'ALTER ANY ASSEMBLY',
287,'DROP ANY ASSEMBLY',
288,'EXECUTE ANY ASSEMBLY',
289,'EXECUTE ASSEMBLY',
290,'CREATE MINING MODEL',
291,'CREATE ANY MINING MODEL',
292,'DROP ANY MINING MODEL',
293,'SELECT ANY MINING MODEL',
294,'ALTER ANY MINING MODEL',
295,'COMMENT ANY MINING MODEL',
296,'ALTER MINING MODEL',
297,'COMMENT MINING MODEL',
298,'GRANT MINING MODEL',
299,'SELECT MINING MODEL',
301,'CREATE CUBE DIMENSION',
302,'ALTER ANY CUBE DIMENSION',
303,'CREATE ANY CUBE DIMENSION',
304,'DELETE ANY CUBE DIMENSION',
305,'DROP ANY CUBE DIMENSION',
306,'INSERT ANY CUBE DIMENSION',
307,'SELECT ANY CUBE DIMENSION',
308,'CREATE CUBE',
309,'ALTER ANY CUBE',
310,'CREATE ANY CUBE',
311,'DROP ANY CUBE',
312,'SELECT ANY CUBE',
313,'UPDATE ANY CUBE',
314,'CREATE MEASURE FOLDER',
315,'CREATE ANY MEASURE FOLDER',
316,'DELETE ANY MEASURE FOLDER',
317,'DROP ANY MEASURE FOLDER',
318,'INSERT ANY MEASURE FOLDER',
319,'CREATE CUBE BUILD PROCESS',
320,'CREATE ANY CUBE BUILD PROCESS',
321,'DROP ANY CUBE BUILD PROCESS',
322,'UPDATE ANY CUBE BUILD PROCESS',
323,'COMMENT EDITION',
324,'GRANT EDITION',
325,'USE EDITION',
326,'UPDATE ANY CUBE DIMENSION',
327,'ADMINISTER SQL MANAGEMENT OBJECT',
328,'ALTER PUBLIC DATABASE LINK',
329,'ALTER DATABASE LINK',
330,'DIRECT_PATH LOAD',
331,'DIRECT_PATH UNLOAD',
350,'FLASHBACK ARCHIVE ADMINISTER',
351,'EXEMPT REDACTION POLICY',
command||' - ???') COMMAND,
        decode(L.LMODE,1,'No Lock',
                2,'Row Share',
                3,'Row Exclusive',
                4,'Share',
                5,'Share Row Exclusive',
                6,'Exclusive','NONE') lmode,
        decode(L.REQUEST,1,'No Lock',
                2,'Row Share',
                3,'Row Exclusive',
                4,'Share',
                5,'Share Row Exclusive',
                6,'Exclusive','NONE') request,
l.id1||'-'||l.id2 Laddr,
l.type||' - '||
decode(l.type,
'BL','Buffer hash table instance lock',
'CF',' Control file schema global enqueue lock',
'CI','Cross-instance function invocation instance lock',
'CS','Control file schema global enqueue lock',
'CU','Cursor bind lock',
'DF','Data file instance lock',
'DL','Direct loader parallel index create',
'DM','Mount/startup db primary/secondary instance lock',
'DR','Distributed recovery process lock',
'DX','Distributed transaction entry lock',
'FI','SGA open-file information lock',
'FS','File set lock',
'HW','Space management operations on a specific segment lock',
'IN','Instance number lock',
'IR','Instance recovery serialization global enqueue lock',
'IS','Instance state lock',
'IV','Library cache invalidation instance lock',
'JQ','Job queue lock',
'KK','Thread kick lock',
'MB','Master buffer hash table instance lock',
'MM','Mount definition gloabal enqueue lock',
'MR','Media recovery lock',
'PF','Password file lock',
'PI','Parallel operation lock',
'PR','Process startup lock',
'PS','Parallel operation lock',
'RE','USE_ROW_ENQUEUE enforcement lock',
'RT','Redo thread global enqueue lock',
'RW','Row wait enqueue lock',
'SC','System commit number instance lock',
'SH','System commit number high water mark enqueue lock',
'SM','SMON lock',
'SN','Sequence number instance lock',
'SQ','Sequence number enqueue lock',
'SS','Sort segment lock',
'ST','Space transaction enqueue lock',
'SV','Sequence number value lock',
'TA','Generic enqueue lock',
'TD','DDL enqueue lock',
'TE','Extend-segment enqueue lock',
'TM','DML enqueue lock',
'TO','Temporary Table Object Enqueue',
'TT','Temporary table enqueue lock',
'TX','Transaction enqueue lock',
'UL','User supplied lock',
'UN','User name lock',
'US','Undo segment DDL lock',
'WL','Being-written redo log instance lock',
'WS','Write-atomic-log-switch global enqueue lock',
'TS',decode(l.id2,0,'Temporary segment enqueue lock (ID2=0)',
                    'New block allocation enqueue lock (ID2=1)'),
'LA','Library cache lock instance lock (A=namespace)',
'LB','Library cache lock instance lock (B=namespace)',
'LC','Library cache lock instance lock (C=namespace)',
'LD','Library cache lock instance lock (D=namespace)',
'LE','Library cache lock instance lock (E=namespace)',
'LF','Library cache lock instance lock (F=namespace)',
'LG','Library cache lock instance lock (G=namespace)',
'LH','Library cache lock instance lock (H=namespace)',
'LI','Library cache lock instance lock (I=namespace)',
'LJ','Library cache lock instance lock (J=namespace)',
'LK','Library cache lock instance lock (K=namespace)',
'LL','Library cache lock instance lock (L=namespace)',
'LM','Library cache lock instance lock (M=namespace)',
'LN','Library cache lock instance lock (N=namespace)',
'LO','Library cache lock instance lock (O=namespace)',
'LP','Library cache lock instance lock (P=namespace)',
'LS','Log start/log switch enqueue lock',
'PA','Library cache pin instance lock (A=namespace)',
'PB','Library cache pin instance lock (B=namespace)',
'PC','Library cache pin instance lock (C=namespace)',
'PD','Library cache pin instance lock (D=namespace)',
'PE','Library cache pin instance lock (E=namespace)',
'PF','Library cache pin instance lock (F=namespace)',
'PG','Library cache pin instance lock (G=namespace)',
'PH','Library cache pin instance lock (H=namespace)',
'PI','Library cache pin instance lock (I=namespace)',
'PJ','Library cache pin instance lock (J=namespace)',
'PL','Library cache pin instance lock (K=namespace)',
'PK','Library cache pin instance lock (L=namespace)',
'PM','Library cache pin instance lock (M=namespace)',
'PN','Library cache pin instance lock (N=namespace)',
'PO','Library cache pin instance lock (O=namespace)',
'PP','Library cache pin instance lock (P=namespace)',
'PQ','Library cache pin instance lock (Q=namespace)',
'PR','Library cache pin instance lock (R=namespace)',
'PS','Library cache pin instance lock (S=namespace)',
'PT','Library cache pin instance lock (T=namespace)',
'PU','Library cache pin instance lock (U=namespace)',
'PV','Library cache pin instance lock (V=namespace)',
'PW','Library cache pin instance lock (W=namespace)',
'PX','Library cache pin instance lock (X=namespace)',
'PY','Library cache pin instance lock (Y=namespace)',
'PZ','Library cache pin instance lock (Z=namespace)',
'QA','Row cache instance lock (A=cache)',
'QB','Row cache instance lock (B=cache)',
'QC','Row cache instance lock (C=cache)',
'QD','Row cache instance lock (D=cache)',
'QE','Row cache instance lock (E=cache)',
'QF','Row cache instance lock (F=cache)',
'QG','Row cache instance lock (G=cache)',
'QH','Row cache instance lock (H=cache)',
'QI','Row cache instance lock (I=cache)',
'QJ','Row cache instance lock (J=cache)',
'QL','Row cache instance lock (K=cache)',
'QK','Row cache instance lock (L=cache)',
'QM','Row cache instance lock (M=cache)',
'QN','Row cache instance lock (N=cache)',
'QO','Row cache instance lock (O=cache)',
'QP','Row cache instance lock (P=cache)',
'QQ','Row cache instance lock (Q=cache)',
'QR','Row cache instance lock (R=cache)',
'QS','Row cache instance lock (S=cache)',
'QT','Row cache instance lock (T=cache)',
'QU','Row cache instance lock (U=cache)',
'QV','Row cache instance lock (V=cache)',
'QW','Row cache instance lock (W=cache)',
'QX','Row cache instance lock (X=cache)',
'QY','Row cache instance lock (Y=cache)',
'QZ','Row cache instance lock (Z=cache)','????') Lockt,
s.sql_id,s.PREV_SQL_ID,FINAL_BLOCKING_SESSION_STATUS
from    GV$LOCK L, GV$SESSION S,SYS.USER$ U1, SYS.OBJ$ T1
where   L.SID = S.SID
and     l.inst_id=s.inst_id
and     T1.OBJ#  = decode(L.ID2,0,L.ID1,1)
and     U1.USER# = T1.OWNER#
and     S.TYPE != 'BACKGROUND'
-- and S.TERMINAL is not null
and command!=0
and S.USERNAME not in ( 'SYS' , 'SYSTEM' , 'DBSNMP' , 'SYSMAN' , 'OUTLN' ,'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' )
order by 1,2,5;

Kill String     Username   Term         Table Name                     COMMAND                   Lock Held       Lock Requested  ID1 - ID2          Lock Type                      SQL_ID        PREV_SQL_ID
--------------- ---------- ------------ ------------------------------ ------------------------- --------------- --------------- ------------------ ------------------------------ ------------- -------------
'179,3983,@1'   ANSPRINT   XXXXXXXXXXX SYS.ORA$BASE                   ALTER SYSTEM              Share           NONE            100-0              AE - ????                      1mtxshuygvxuj 459f3z9u4fb3u
'466,4335,@1'   ANSPRINT   XXXXXXXXXXX SYS.ORA$BASE                   ALTER SYSTEM              Share           NONE            100-0              AE - ????                      1mtxshuygvxuj 459f3z9u4fb3u
'495,41851,@1'  ANSPRINT   XXXXXXXXXXX SYS.ORA$BASE                   ALTER SYSTEM              Share           NONE            100-0              AE - ????                      1mtxshuygvxuj 459f3z9u4fb3u
'61,14015,@1'   ANSPPP     UNKNOWN      None                          ALTER SYSTEM              Exclusive       NONE            1114137-102911270  TX - Transaction enqueue lock  cv664khgc0nhy 0pwgs492bf10h
                                        SYS.ORA$BASE                  ALTER SYSTEM              Share           NONE            100-0              AE - ????                      cv664khgc0nhy 0pwgs492bf10h
                                        ANSPPP.LOT_BATCH_MASTER_CFT   SELECT ANY TABLE          Row Exclusive   NONE            123175-0           TM - DML enqueue lock          6h1r9ap9zhnjv 5tnn2kwbquuj4

6 rows selected.

===

set pagesize 200 linesize 500
column osuser heading 'OS|Username' format a7 truncate
column process heading 'OS|Process' format a7 truncate
column machine heading 'OS|Machine' format a10 truncate
column program heading 'OS|Program' format a25 truncate
column object heading 'Database|Object' format a25 truncate
column lock_type heading 'Lock|Type' format a4 truncate
column mode_held heading 'Mode|Held' format a15 truncate
column mode_requested heading 'Mode|Requested' format a10 truncate
column sid heading 'SID' format 999
column username heading 'Oracle|Username' format a7 truncate
column image heading 'Active Image' format a20 truncate
column sid format 99999
col waiting_session head 'WAITER' format 9999
col holding_session head 'BLOCKER' format 9999
col kill for a18
col event for a20
select /*+ ordered */
	--b.kaddr,
	--c.sid, c.inst_id,
''''||c.sid ||','|| c.serial#||',@'||c.inst_id ||''''  kill,
	c.con_id,
	c.sql_id,
c.event,
	lock_waiter.waiting_session,
	lock_blocker.holding_session,
	c.program,
	c.osuser,
	c.machine,
	c.process,
	decode(u.name,
		null,'',
		u.name||'.'||o.name
	) object,
	c.username,
	decode
	(
		b.type,
		'BL', 'Buffer hash table instance lock',
		'CF', 'Control file schema global enqueue lock',
		'CI', 'Cross-instance function invocation instance lock',
		'CU', 'Cursor bind lock',
		'DF', 'Data file instance lock',
		'DL', 'direct loader parallel index create lock',
		'DM', 'Mount/startup db primary/secondary instance lock',
		'DR', 'Distributed recovery process lock',
		'DX', 'Distributed transaction entry lock',
		'FS', 'File set lock',
		'IN', 'Instance number lock',
		'IR', 'Instance recovery serialization global enqueue lock',
		'IS', 'Instance state lock',
		'IV', 'Library cache invalidation instance lock',
		'JQ', 'Job queue lock',
		'KK', 'Thread kick lock',
		'LA','Library cache lock instance lock (A..P=namespace);',
		'LB','Library cache lock instance lock (A..P=namespace);',
		'LC','Library cache lock instance lock (A..P=namespace);',
		'LD','Library cache lock instance lock (A..P=namespace);',
		'LE','Library cache lock instance lock (A..P=namespace);',
		'LF','Library cache lock instance lock (A..P=namespace);',
		'LG','Library cache lock instance lock (A..P=namespace);',
		'LH','Library cache lock instance lock (A..P=namespace);',
		'LI','Library cache lock instance lock (A..P=namespace);',
		'LJ','Library cache lock instance lock (A..P=namespace);',
		'LK','Library cache lock instance lock (A..P=namespace);',
		'LL','Library cache lock instance lock (A..P=namespace);',
		'LM','Library cache lock instance lock (A..P=namespace);',
		'LN','Library cache lock instance lock (A..P=namespace);',
		'LO','Library cache lock instance lock (A..P=namespace);',
		'LP','Library cache lock instance lock (A..P=namespace);',
		'MM', 'Mount definition global enqueue lock',
		'MR', 'Media recovery lock',
		'NA', 'Library cache pin instance lock (A..Z=namespace)',
		'NB', 'Library cache pin instance lock (A..Z=namespace)',
		'NC', 'Library cache pin instance lock (A..Z=namespace)',
		'ND', 'Library cache pin instance lock (A..Z=namespace)',
		'NE', 'Library cache pin instance lock (A..Z=namespace)',
		'NF', 'Library cache pin instance lock (A..Z=namespace)',
		'NG', 'Library cache pin instance lock (A..Z=namespace)',
		'NH', 'Library cache pin instance lock (A..Z=namespace)',
		'NI', 'Library cache pin instance lock (A..Z=namespace)',
		'NJ', 'Library cache pin instance lock (A..Z=namespace)',
		'NK', 'Library cache pin instance lock (A..Z=namespace)',
		'NL', 'Library cache pin instance lock (A..Z=namespace)',
		'NM', 'Library cache pin instance lock (A..Z=namespace)',
		'NN', 'Library cache pin instance lock (A..Z=namespace)',
		'NO', 'Library cache pin instance lock (A..Z=namespace)',
		'NP', 'Library cache pin instance lock (A..Z=namespace)',
		'NQ', 'Library cache pin instance lock (A..Z=namespace)',
		'NR', 'Library cache pin instance lock (A..Z=namespace)',
		'NS', 'Library cache pin instance lock (A..Z=namespace)',
		'NT', 'Library cache pin instance lock (A..Z=namespace)',
		'NU', 'Library cache pin instance lock (A..Z=namespace)',
		'NV', 'Library cache pin instance lock (A..Z=namespace)',
		'NW', 'Library cache pin instance lock (A..Z=namespace)',
		'NX', 'Library cache pin instance lock (A..Z=namespace)',
		'NY', 'Library cache pin instance lock (A..Z=namespace)',
		'NZ', 'Library cache pin instance lock (A..Z=namespace)',
		'PF', 'Password File lock',
		'PI', 'Parallel operation locks',
		'PS', 'Parallel operation locks',
		'PR', 'Process startup lock',
		'QA','Row cache instance lock (A..Z=cache)',
		'QB','Row cache instance lock (A..Z=cache)',
		'QC','Row cache instance lock (A..Z=cache)',
		'QD','Row cache instance lock (A..Z=cache)',
		'QE','Row cache instance lock (A..Z=cache)',
		'QF','Row cache instance lock (A..Z=cache)',
		'QG','Row cache instance lock (A..Z=cache)',
		'QH','Row cache instance lock (A..Z=cache)',
		'QI','Row cache instance lock (A..Z=cache)',
		'QJ','Row cache instance lock (A..Z=cache)',
		'QK','Row cache instance lock (A..Z=cache)',
		'QL','Row cache instance lock (A..Z=cache)',
		'QM','Row cache instance lock (A..Z=cache)',
		'QN','Row cache instance lock (A..Z=cache)',
		'QP','Row cache instance lock (A..Z=cache)',
		'QQ','Row cache instance lock (A..Z=cache)',
		'QR','Row cache instance lock (A..Z=cache)',
		'QS','Row cache instance lock (A..Z=cache)',
		'QT','Row cache instance lock (A..Z=cache)',
		'QU','Row cache instance lock (A..Z=cache)',
		'QV','Row cache instance lock (A..Z=cache)',
		'QW','Row cache instance lock (A..Z=cache)',
		'QX','Row cache instance lock (A..Z=cache)',
		'QY','Row cache instance lock (A..Z=cache)',
		'QZ','Row cache instance lock (A..Z=cache)',
		'RT', 'Redo thread global enqueue lock',
		'SC', 'System commit number instance lock',
		'SM', 'SMON lock',
		'SN', 'Sequence number instance lock',
		'SQ', 'Sequence number enqueue lock',
		'SS', 'Sort segment locks',
		'ST', 'Space transaction enqueue lock',
		'SV', 'Sequence number value lock',
		'TA', 'Generic enqueue lock',
		'TS', 'Temporary segment enqueue lock (ID2=0)',
		'TS', 'New block allocation enqueue lock (ID2=1)',
		'TT', 'Temporary table enqueue lock',
		'UN', 'User name lock',
		'US', 'Undo segment DDL lock',
		'WL', 'Being-written redo log instance lock',
		b.type
	) lock_type,
	decode
	(
		b.lmode,
		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 (SRX)',  /* C */
		6, 'Exclusive',      /* X */
		to_char(b.lmode)
	) mode_held,
	decode
	(
		b.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(b.request)
	) mode_requested
from
	GV$lock b
	,GV$session c
	,sys.user$ u
	,sys.obj$ o
	,( select * from sys.dba_waiters) lock_blocker
	,( select * from sys.dba_waiters) lock_waiter
where
b.sid = c.sid
and u.user# = c.user#
and o.obj#(+) = b.id1
and lock_blocker.waiting_session(+) = c.sid
and lock_waiter.holding_session(+) = c.sid
and c.username != 'SYS'
order by kaddr, lockwait
/


     

Oracle 11g baseline

If a query is "hard" parsed, oracle produces several execution plan and compares it with the accepted plan in a baseline. If a match is found, oracle picks that plan.



Two Parameter for baseline

optimizer_capture_sql_plan_baseline if set to true, let oracle automatically capture sql plan baselines. The default value is false

Optimizer_use_sql_plan_baseline, if set to true, force oracle to use the sql plan baseline (if present). The default is true.




SQL> drop table test;

Table dropped.

SQL> create table test as select * from dba_objects ;

Table created.

SQL> select /*+withoutindex */ * from test where GENERATED='ANUJ';

no rows selected

SQL> select sql_id,sql_text from v$sqlarea where sql_text like '%withoutindex%' ;

SQL_ID
-------------
SQL_TEXT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
b5dgh4v84xjdq
select /*+withoutindex */ * from test where GENERATED='ANUJ'

3yq0rqy2zrx3v
select sql_id,sql_text from v$sqlarea where sql_text like '%withoutindex%'


SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor('b5dgh4v84xjdq'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID b5dgh4v84xjdq, child number 0
-------------------------------------
select /*+withoutindex */ * from test where GENERATED='ANUJ'

Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 282 (100)| |
|* 1 | TABLE ACCESS FULL| TEST | 11 | 2277 | 282 (1)| 00:00:04 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("GENERATED"='ANUJ')

Note
-----
- dynamic sampling used for this statement (level=2)


22 rows selected.

SQL> select sql_text, sql_handle, plan_name, enabled, accepted from dba_sql_plan_baselines;

no rows selected

SQL> var v_num number;
SQL> exec :v_num:=dbms_spm.load_plans_from_cursor_cache (sql_id => 'b5dgh4v84xjdq',plan_hash_value => 1357081020 );

PL/SQL procedure successfully completed.

SQL> select sql_text, sql_handle, plan_name, enabled, accepted from dba_sql_plan_baselines;

SQL_TEXT SQL_HANDLE PLAN_NAME ENA ACC
-------------------------------------------------------------------------------- ------------------------------ ------------------------------ --- ---
select /*+withoutindex */ * from test where GENERATED='ANUJ' SYS_SQL_c7f6b95762b4c6a9 SQL_PLAN_cgxptaxjb9jp997bbe3d0 YES YES

SQL> create index test_idx on test(GENERATED) parallel 8;

Index created.

SQL> select /*+withoutindex */ * from test where GENERATED='ANUJ';

no rows selected



SQL> select /*+withoutindex */ * from test where GENERATED='ANUJ';

no rows selected

SQL> select sql_id,sql_text from v$sqlarea where sql_text like '%withoutindex%' ;

SQL_ID
-------------
SQL_TEXT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
b5dgh4v84xjdq
select /*+withoutindex */ * from test where GENERATED='ANUJ'

3yq0rqy2zrx3v
select sql_id,sql_text from v$sqlarea where sql_text like '%withoutindex%'





SQL> select sql_id,sql_text from v$sqlarea where sql_text like '%GENERATED=%';

SQL_ID
-------------
SQL_TEXT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
b5dgh4v84xjdq
select /*+withoutindex */ * from test where GENERATED='ANUJ'

2s1gzn4d03qts
select sql_id,sql_text from v$sqlarea where sql_text like '%GENERATED=%'






SQL> select * from table(dbms_xplan.display_cursor('b5dgh4v84xjdq', 1, 'basic note'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select /*+withoutindex */ * from test where GENERATED='ANUJ'

Plan hash value: 1357081020

----------------------------------
| Id | Operation | Name |
----------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS FULL| TEST |
----------------------------------

Note
-----
- SQL plan baseline SQL_PLAN_cgxptaxjb9jp997bbe3d0 used for this statement


17 rows selected.



SQL> select /*+index(test_idx test) */ * from test where GENERATED='ANUJ';

no rows selected

SQL> select sql_id,sql_text from v$sqlarea where sql_text like '%GENERATED=%';

SQL_ID
-------------
SQL_TEXT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
b5dgh4v84xjdq
select /*+withoutindex */ * from test where GENERATED='ANUJ'

2s1gzn4d03qts
select sql_id,sql_text from v$sqlarea where sql_text like '%GENERATED=%'

8wgw4b0agmvv8
select /*+index(test_idx test) */ * from test where GENERATED='ANUJ'


SQL> select * from table(dbms_xplan.display_cursor('b5dgh4v84xjdq', 0, 'basic note'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID: b5dgh4v84xjdq, child number: 0 cannot be found


SQL> select * from table(dbms_xplan.display_cursor('8wgw4b0agmvv8',0, 'basic note'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select /*+index(test_idx test) */ * from test where GENERATED='ANUJ'

Plan hash value: 1389866015

------------------------------------------------
| Id | Operation | Name |
------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST |
| 2 | INDEX RANGE SCAN | TEST_IDX |
------------------------------------------------

Note
-----
- dynamic sampling used for this statement (level=2)


18 rows selected.

SQL> exec :v_num:=dbms_spm.load_plans_from_cursor_cache(sql_id => '8wgw4b0agmvv8',plan_hash_value => '1389866015' );

PL/SQL procedure successfully completed.

SQL> select sql_text, sql_handle, plan_name, enabled, accepted from dba_sql_plan_baselines;

SQL_TEXT SQL_HANDLE PLAN_NAME ENA ACC
-------------------------------------------------------------------------------- ------------------------------ ------------------------------ --- ---
select /*+index(test_idx test) */ * from test where GENERATED='ANUJ' SYS_SQL_22655d99f2ac5a60 SQL_PLAN_24taxm7tasqm0a5063692 YES YES
select /*+withoutindex */ * from test where GENERATED='ANUJ' SYS_SQL_c7f6b95762b4c6a9 SQL_PLAN_cgxptaxjb9jp997bbe3d0 YES YES
select /*+withoutindex */ * from test where GENERATED='ANUJ' SYS_SQL_c7f6b95762b4c6a9 SQL_PLAN_cgxptaxjb9jp9a5063692 YES NO

SQL> exec :v_num:=dbms_spm.load_plans_from_cursor_cache(sql_id => '8wgw4b0agmvv8',plan_hash_value => 1389866015 ,sql_handle => 'SYS_SQL_c7f6b95762b4c6a9');

PL/SQL procedure successfully completed.

SQL> select sql_text, sql_handle, plan_name, enabled, accepted from dba_sql_plan_baselines;

SQL_TEXT SQL_HANDLE PLAN_NAME ENA ACC
-------------------------------------------------------------------------------- ------------------------------ ------------------------------ --- ---
select /*+index(test_idx test) */ * from test where GENERATED='ANUJ' SYS_SQL_22655d99f2ac5a60 SQL_PLAN_24taxm7tasqm0a5063692 YES YES
select /*+withoutindex */ * from test where GENERATED='ANUJ' SYS_SQL_c7f6b95762b4c6a9 SQL_PLAN_cgxptaxjb9jp997bbe3d0 YES YES
select /*+withoutindex */ * from test where GENERATED='ANUJ' SYS_SQL_c7f6b95762b4c6a9 SQL_PLAN_cgxptaxjb9jp9a5063692 YES YES

SQL>










#1

Step1)

Disable affecting plan and moniter for 2 hours.In case if the error has not been stopped or baseline plan is not available then go to step2.



DECLARE
v_number pls_integer;
BEGIN
v_number:=dbms_spm.alter_sql_plan_baseline(
sql_handle=>'SYS_SQL_357e8c14c551f65b',
plan_name=>'SYS_SQL_PLAN_a55df45c695bc033',
attribute_name=>'ACCECPTED',
attribute_value=>'NO');
END;
/


Step2)

If the baseline plan is not available for the affected SQL then just load it using the below SQL.


DECLARE

my_plans pls_integer;


BEGIN

my_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id => 'b5dgh4v84xjdq');

END;
/

#2

1) Drop the affecting plan and moniter for 2 hours.In case if the error has not been stopped or baseline plan is not available then go to step2.


DECLARE
v_plans_dropped pls_integer;
BEGIN
v_plans_dropped:=dbms_spm.drop_sql_plan_baseline(
sql_handle=>'SYS_SQL_22655d99f2ac5a60',
plan_name=>'SQL_PLAN_24taxm7tasqm0a5063692');
END;
/




2) If the baseline plan is not available for the affected SQL then just load it using the below SQL.

DECLARE
my_plans pls_integer;
BEGIN
my_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE( sql_id => '99twu5t2dn5xd');
END;
/



SQL_ID is available in top section of the trace file or you can find it using the v$sql view.
sql_handle and plan_name can be found using the below SQL.

select sql_handle, plan_name, enabled, accepted, fixed from dba_sql_plan_baselines WHERE SQL_TEXT LIKE '%&SQL_TEXT%';

How to create oracle scott user ...

GRANT CONNECT,RESOURCE,UNLIMITED TABLESPACE TO SCOTT IDENTIFIED BY TIGER;
ALTER USER SCOTT DEFAULT TABLESPACE USERS;
ALTER USER SCOTT TEMPORARY TABLESPACE TEMP;

CONNECT SCOTT/TIGER

DROP TABLE DEPT;

CREATE TABLE DEPT (DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
DNAME VARCHAR2(14) ,
LOC VARCHAR2(13) ) ;

DROP TABLE EMP;

CREATE TABLE EMP
(EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT);

INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES (30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES(40,'OPERATIONS','BOSTON');


INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO EMP VALUES(7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO EMP VALUES(7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87')-85,3000,NULL,20);
INSERT INTO EMP VALUES(7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO EMP VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO EMP VALUES(7876,'ADAMS','CLERK',7788,to_date('13-JUL-87')-51,1100,NULL,20);
INSERT INTO EMP VALUES(7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO EMP VALUES(7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);

DROP TABLE BONUS;

CREATE TABLE BONUS
(
ENAME VARCHAR2(10) ,
JOB VARCHAR2(9) ,
SAL NUMBER,
COMM NUMBER
) ;

DROP TABLE SALGRADE;

CREATE TABLE SALGRADE
( GRADE NUMBER,
LOSAL NUMBER,
HISAL NUMBER );

INSERT INTO SALGRADE VALUES (1,700,1200);
INSERT INTO SALGRADE VALUES (2,1201,1400);
INSERT INTO SALGRADE VALUES (3,1401,2000);
INSERT INTO SALGRADE VALUES (4,2001,3000);
INSERT INTO SALGRADE VALUES (5,3001,9999);
COMMIT;

SET TERMOUT ON ECHO ON
 

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

Create EMP and DEPT tables in Oracle


create tablespace test datafile '+DATA' size 10M autoextend on maxsize  200M ;

Tablespace created.


create table dept(
  deptno number   (2,0),
  dname  varchar2 (14),
  loc    varchar2 (13),
  constraint pk_dept primary key (deptno)
)
 tablespace test
;
 
insert into dept values(10, 'ACCOUNTING', 'NEW YORK');
insert into dept values(20, 'RESEARCH',   'DALLAS');
insert into dept values(30, 'SALES',      'CHICAGO');
insert into dept values(40, 'OPERATIONS', 'BOSTON');


create table emp(
  empno    number(4,0),
  ename    varchar2(10),
  job      varchar2(9),
  mgr      number(4,0),
  hiredate date,
  sal      number(7,2),
  comm     number(7,2),
  deptno   number(2,0),
  constraint pk_emp primary key (empno),
  constraint fk_deptno foreign key (deptno) references dept (deptno)
) 
tablespace test
;

insert into emp values( 7839, 'KING',  'PRESIDENT', null, to_date('17-11-1981','dd-mm-yyyy'), 5000,   null, 10);
insert into emp values( 7698, 'BLAKE',  'MANAGER',  7839, to_date('1-5-1981','dd-mm-yyyy'), 2850,     null, 30);
insert into emp values( 7782, 'CLARK',  'MANAGER',  7839, to_date('9-6-1981','dd-mm-yyyy'), 2450,     null, 10);
insert into emp values( 7566, 'JONES',  'MANAGER',  7839, to_date('2-4-1981','dd-mm-yyyy'), 2975,     null, 20);
insert into emp values( 7788, 'SCOTT',  'ANALYST',  7566, to_date('13-JUL-87','dd-mm-rr') - 85, 3000, null, 20);
insert into emp values( 7902, 'FORD',   'ANALYST',  7566, to_date('3-12-1981','dd-mm-yyyy'), 3000,    null, 20);
insert into emp values( 7369, 'SMITH',  'CLERK',    7902, to_date('17-12-1980','dd-mm-yyyy'), 800,    null, 20);
insert into emp values( 7499, 'ALLEN',  'SALESMAN', 7698, to_date('20-2-1981','dd-mm-yyyy'), 1600, 300, 30);
insert into emp values( 7521, 'WARD',   'SALESMAN', 7698, to_date('22-2-1981','dd-mm-yyyy'), 1250, 500, 30);
insert into emp values( 7654, 'MARTIN', 'SALESMAN', 7698, to_date('28-9-1981','dd-mm-yyyy'), 1250, 1400, 30);
insert into emp values( 7844, 'TURNER', 'SALESMAN', 7698, to_date('8-9-1981','dd-mm-yyyy'), 1500, 0, 30);
insert into emp values( 7876, 'ADAMS',  'CLERK',    7788, to_date('13-JUL-87', 'dd-mm-rr') - 51, 1100, null, 20);
insert into emp values( 7900, 'JAMES',  'CLERK',    7698, to_date('3-12-1981','dd-mm-yyyy'), 950,      null, 30);
insert into emp values( 7934, 'MILLER', 'CLERK',    7782, to_date('23-1-1982','dd-mm-yyyy'), 1300,     null, 10);
 

 ====

On default tablespace ..



create table dept(
  deptno number   (2,0),
  dname  varchar2 (14),
  loc    varchar2 (13),
  constraint pk_dept primary key (deptno)
)
-- tablespace test
;
 
insert into dept values(10, 'ACCOUNTING', 'NEW YORK');
insert into dept values(20, 'RESEARCH',   'DALLAS');
insert into dept values(30, 'SALES',      'CHICAGO');
insert into dept values(40, 'OPERATIONS', 'BOSTON');


create table emp(
  empno    number(4,0),
  ename    varchar2(10),
  job      varchar2(9),
  mgr      number(4,0),
  hiredate date,
  sal      number(7,2),
  comm     number(7,2),
  deptno   number(2,0),
  constraint pk_emp primary key (empno),
  constraint fk_deptno foreign key (deptno) references dept (deptno)
) 
--tablespace test
;

insert into emp values( 7839, 'KING',  'PRESIDENT', null, to_date('17-11-1981','dd-mm-yyyy'), 5000,   null, 10);
insert into emp values( 7698, 'BLAKE',  'MANAGER',  7839, to_date('1-5-1981','dd-mm-yyyy'), 2850,     null, 30);
insert into emp values( 7782, 'CLARK',  'MANAGER',  7839, to_date('9-6-1981','dd-mm-yyyy'), 2450,     null, 10);
insert into emp values( 7566, 'JONES',  'MANAGER',  7839, to_date('2-4-1981','dd-mm-yyyy'), 2975,     null, 20);
insert into emp values( 7788, 'SCOTT',  'ANALYST',  7566, to_date('13-JUL-87','dd-mm-rr') - 85, 3000, null, 20);
insert into emp values( 7902, 'FORD',   'ANALYST',  7566, to_date('3-12-1981','dd-mm-yyyy'), 3000,    null, 20);
insert into emp values( 7369, 'SMITH',  'CLERK',    7902, to_date('17-12-1980','dd-mm-yyyy'), 800,    null, 20);
insert into emp values( 7499, 'ALLEN',  'SALESMAN', 7698, to_date('20-2-1981','dd-mm-yyyy'), 1600, 300, 30);
insert into emp values( 7521, 'WARD',   'SALESMAN', 7698, to_date('22-2-1981','dd-mm-yyyy'), 1250, 500, 30);
insert into emp values( 7654, 'MARTIN', 'SALESMAN', 7698, to_date('28-9-1981','dd-mm-yyyy'), 1250, 1400, 30);
insert into emp values( 7844, 'TURNER', 'SALESMAN', 7698, to_date('8-9-1981','dd-mm-yyyy'), 1500, 0, 30);
insert into emp values( 7876, 'ADAMS',  'CLERK',    7788, to_date('13-JUL-87', 'dd-mm-rr') - 51, 1100, null, 20);
insert into emp values( 7900, 'JAMES',  'CLERK',    7698, to_date('3-12-1981','dd-mm-yyyy'), 950,      null, 30);
insert into emp values( 7934, 'MILLER', 'CLERK',    7782, to_date('23-1-1982','dd-mm-yyyy'), 1300,     null, 10);
 

Oracle pfile from sqlplus re-create

re-create pfile from sqlplus



set head off
set feed off
set pages 0
spool $ORACLE_HOME/dbs/initPROD.ora.anuj
SELECT
'######################################' ||CHR(10)||
'# file : init'||upper(value)||'.ora #' ||CHR(10)||
'# Date : '||to_char(sysdate,'DD/MM/YYYY HH24:MI:SS')|| ' #' ||CHR(10)||
'######################################' ||CHR(10) ||CHR(10)
FROM v$parameter
WHERE name = 'db_name';


SELECT '# '|| DESCRIPTION ||CHR(10) ,
'*.'||NAME||'='|| DECODE(VALUE,'TRUE',VALUE,'FALSE',VALUE,
DECODE(SIGN(INSTR(VALUE,' ')),
1,'( "'||REPLACE(VALUE,', ','",'||CHR(10)||' "')||'" )', '"'||VALUE||'"'))||
DECODE(upper(ISDEFAULT),'TRUE',' # (Default value) - ',NULL)|| CHR(10)
FROM v$parameter
WHERE ISDEFAULT = 'FALSE'
ORDER BY num;

spool off

*******************************************************
ISDEFAULT='FALSE' ---- means value via paramater file

*******************************************************