col e_time format 999,999,990 heading "ELAPSED|TIME(s)"
col s_text heading "SQL TEXT (PARTIAL)"
col c_type format a20 heading "SQL TYPE"
col module format a15 trunc
col action format a15 trunc
col service format a15 trunc
col execs format 999,999,990 heading "EXECUTES(k)"
col toprank noprint
select * from ( select SQL_ID,elapsed_time/1000000 e_time,dense_rank() over(order by elapsed_time desc) toprank,
DECODE(command_type,
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',
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',
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',
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'
,command_type) c_type,
substr(sql_text,1,20) s_text,
module, executions/1000 execs, action, service
from v$sql
where 1=1
-- and command_type in (2,6,7,44)
and parsing_schema_name
not in ('SYS','SYSTEM','SPOT','TOAD','OUTLN','TSMSYS','DBSNMP','ORACLE_OCM','WMSYS','EXFSYS','ORDSYS','ORDPLUGINS','SI_INFORMTN_SCHEMA',
'MDSYS','OPS$ORACLE','XDB','ANONYMOUS','OLAPSYS','DMSYS','BACKUP')
) where 1=1
and toprank <= 10 ;
ELAPSED
SQL_ID TIME(s) SQL TYPE SQL TEXT (PARTIAL) MODULE EXECUTES(k) ACTION SERVICE
------------- ------------ -------------------- -------------------- --------------- ------------ --------------- ---------------
2nszajb0qbyvp 5 PL/SQL EXECUTE DECLARE job BINARY_I 0 SYS$USERS
6z9uusgaz5z9q 4 PL/SQL EXECUTE DECLARE job BINARY_I 0 SYS$USERS
8rpn8jtjnuu73 1 DELETE DELETE FROM WWV_FLOW 0 SYS$USERS
cfnrd41661bgr 1 PL/SQL EXECUTE BEGIN -- Pin all P sqlplus@anuj.ku 0 SYS$USERS
9s0phgvjjnz0k 1 SELECT SELECT ID FROM WWV_F 0 SYS$USERS
aukfj0ur6962z 1 SELECT SELECT VALUE V FROM 0 SYS$USERS
1k4n9zzsk1vc4 0 SELECT select sysdate + 8/2 0 SYS$USERS
0rnt8v06tb3sp 0 SELECT select sysdate + 10/ 0 SYS$USERS
62m44bym1fdhs 0 SELECT SELECT ID FROM WWV_F 0 SYS$USERS
9 rows selected.
===============
--- select OPTION#||','''||NAME ||''''||',' from STMT_AUDIT_OPTION_MAP ;
set linesize 200 pagesize 200
col e_time format 999,999,990 heading "ELAPSED|TIME(s)"
col s_text heading "SQL TEXT (PARTIAL)"
col c_type format a20 heading "SQL TYPE"
col module format a15 trunc
col action format a15 trunc
col service format a15 trunc
col execs format 999,999,990 heading "EXECUTES(k)"
col toprank noprint
select * from ( select inst_id,SQL_ID,elapsed_time/1000000 e_time,dense_rank() over(order by elapsed_time desc) toprank,
DECODE(command_type,
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',
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',
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',
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'
,command_type) c_type,
substr(sql_text,1,20) s_text,
module, executions/1000 execs, action, service
from gv$sql
where 1=1
-- and command_type in (2,6,7,44)
and parsing_schema_name
not in ('SYS','SYSTEM','SPOT','TOAD','OUTLN','TSMSYS','DBSNMP','ORACLE_OCM','WMSYS','EXFSYS','ORDSYS','ORDPLUGINS','SI_INFORMTN_SCHEMA',
'MDSYS','OPS$ORACLE','XDB','ANONYMOUS','OLAPSYS','DMSYS','BACKUP')
) where 1=1
and toprank <= 10 ;
======================================================
Oracle 18c
select OPTION#||','''||NAME ||''''||',' from STMT_AUDIT_OPTION_MAP ;
set linesize 200 pagesize 200
col e_time format 999,999,990 heading "ELAPSED|TIME(s)"
col s_text heading "SQL TEXT (PARTIAL)"
col c_type format a20 heading "SQL TYPE"
col module format a15 trunc
col action format a15 trunc
col service format a15 trunc
col execs format 999,999,990 heading "EXECUTES(k)"
col toprank noprint
select * from ( select inst_id,SQL_ID,elapsed_time/1000000 e_time,dense_rank() over(order by elapsed_time desc) toprank,
DECODE(command_type,
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',
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',
53,'INDEX',
54,'ALTER TABLE',
56,'REDEFINE ANY 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',
240,'DEBUG CONNECT ANY',
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',
334,'CREATE SQL TRANSLATION PROFILE',
335,'CREATE ANY SQL TRANSLATION PROFILE',
336,'ALTER ANY SQL TRANSLATION PROFILE',
337,'USE ANY SQL TRANSLATION PROFILE',
338,'DROP ANY SQL TRANSLATION PROFILE',
339,'SQL TRANSLATION PROFILE',
340,'SYSBACKUP',
341,'SYSDG',
342,'SYSKM',
343,'ADMINISTER KEY MANAGEMENT',
344,'KEEP DATE TIME',
345,'KEEP SYSGUID',
346,'EM EXPRESS CONNECT',
347,'PURGE DBA_RECYCLEBIN',
348,'ALTER SQL TRANSLATION PROFILE',
349,'GRANT SQL TRANSLATION PROFILE',
350,'FLASHBACK ARCHIVE ADMINISTER',
351,'EXEMPT REDACTION POLICY',
352,'INHERIT ANY PRIVILEGES',
353,'INHERIT PRIVILEGES',
354,'GRANT USER',
355,'TRANSLATE ANY SQL',
356,'TRANSLATE SQL',
357,'PLUGGABLE DATABASE',
358,'LOCKDOWN PROFILE',
359,'ATTRIBUTE DIMENSION',
360,'HIERARCHY',
361,'ANALYTIC VIEW',
362,'READ DIRECTORY',
363,'WRITE DIRECTORY',
364,'EXECUTE DIRECTORY',
365,'INHERIT ANY REMOTE PRIVILEGES',
366,'INHERIT REMOTE PRIVILEGES',
367,'DEBUG CONNECT',
375,'CREATE PLUGGABLE DATABASE',
377,'SET CONTAINER',
378,'CREATE LOCKDOWN PROFILE',
379,'DROP LOCKDOWN PROFILE',
380,'ALTER LOCKDOWN PROFILE',
385,'USE SQL TRANSLATION PROFILE',
386,'DROP SQL TRANSLATION PROFILE',
387,'CREATE CREDENTIAL',
388,'CREATE ANY CREDENTIAL',
389,'LOGMINING',
390,'USE ANY JOB RESOURCE',
393,'SELECT ANY MEASURE FOLDER',
394,'ALTER ANY MEASURE FOLDER',
395,'SELECT ANY CUBE BUILD PROCESS',
396,'ALTER ANY CUBE BUILD PROCESS',
397,'READ ANY TABLE',
398,'SYSRAC',
399,'CREATE ATTRIBUTE DIMENSION',
400,'CREATE ANY ATTRIBUTE DIMENSION',
401,'ALTER ANY ATTRIBUTE DIMENSION',
402,'DROP ANY ATTRIBUTE DIMENSION',
403,'CREATE HIERARCHY',
404,'CREATE ANY HIERARCHY',
405,'ALTER ANY HIERARCHY',
406,'DROP ANY HIERARCHY',
407,'CREATE ANALYTIC VIEW',
408,'CREATE ANY ANALYTIC VIEW',
409,'ALTER ANY ANALYTIC VIEW',
410,'DROP ANY ANALYTIC VIEW',
411,'READ ANY ANALYTIC VIEW CACHE',
412,'WRITE ANY ANALYTIC VIEW CACHE',
414,'TEXT DATASTORE ACCESS',command_type) c_type,
module, executions/1000 execs, action, service,
substr(sql_text,1,20) s_text
from gv$sql
where 1=1
-- and command_type in (2,6,7,44)
and parsing_schema_name
not in ('SYS','SYSTEM','SPOT','TOAD','OUTLN','TSMSYS','DBSNMP','ORACLE_OCM','WMSYS','EXFSYS','ORDSYS','ORDPLUGINS','SI_INFORMTN_SCHEMA','MDSYS','OPS$ORACLE','XDB','ANONYMOUS','OLAPSYS','DMSYS','BACKUP')
) where 1=1
and toprank <= 10 ;
==========
set linesize 500 pagesize 300
col parsing_schema_name for a15
col sql_txt for a60
col command for a25
select parsing_schema_name,
decode(command_type,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',
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',
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',
305,'ALTER PUBLIC DATABASE LINK',
command_type||' - ???') COMMAND,
sql_id,hash_value,executions,sql_text sql_txt from gv$sqlarea
where 1=1
-- and command_type in (1,6)
-- and PARSING_SCHEMA_NAME not in ( 'SYS','SYSTEM','DBSNMP','SYSMAN','OUTLN','MDSYS','ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC','WWV_FLOW_PLATFORM' )
-- AND UPPER(sql_text) LIKE UPPER('%&sqltext%')
and upper(sql_text) not like UPPER('%+Anuj%')
and rownum < 10
;
from !!
select ACTION||','''|| NAME||''''||',' from AUDIT_ACTIONS ;
