More on COMMAND_TYPE values. January 8, 2010
Posted by mwidlake in internals.Tags: data dictionary, SQL
trackback
This is just a quick update on finding the values for COMMAND_TYPE as is held in V$SQL and V$SQLAREA. The original post is here. Quick sumary – table SYS.AUDIT_ACTIONS as an almost-complete list of COMMAND_TYPES.
In one of those little concidences of fate, I have been looking at use of SQL AUDIT for the last few days. I am not very knowledgeble about AUDIT or oracle security in general, so I do what I normally do when I don’t know much about an area. I look on the web, scan the manual and then go poking around in the data dictionary. I came across two potentially interesting tables, SYS.AUDIT_ACTIONS and SYS.STMT_AUDIT_OPTION_MAP. At first glance they look to hold similar information, things that can be audited. This seems to be what SYS.STMT_AUDIT_OPTION_MAP is:-
select * from SYS.STMT_AUDIT_OPTION_MAP order by option# OPTION# NAME PROPERTY ---------- ---------------------------------------- ---------- 3 ALTER SYSTEM 0 4 SYSTEM AUDIT 0 5 CREATE SESSION 0 6 ALTER SESSION 0 7 RESTRICTED SESSION 0 8 TABLE 0 9 CLUSTER 0 10 CREATE TABLESPACE 0 11 ALTER TABLESPACE 0 12 MANAGE TABLESPACE 0 13 DROP TABLESPACE 0 14 TABLESPACE 0 15 UNLIMITED TABLESPACE 0 -- lets look at dba_audit_trail that underlys many of the audit views. @vw_txt Enter value for vw_name: dba_audit_trail Any Key...> OWNER VIEW_NAME TEXT_LENGTH ---------- ------------------------------ ----------- TEXT ------------------------------------------------------------------ SYS DBA_AUDIT_TRAIL 3424 select spare1 /* OS_USERNAME */, userid /* USERNAME */, userhost /* USERHOST */, terminal /* TERMINAL */, cast ( /* TIMESTAMP */ (from_tz(ntimestamp#,'00:00') at local) as date), obj$creator /* OWNER */, obj$name /* OBJECT_NAME */, aud.action# /* ACTION */, act.name /* ACTION_NAME */, .... from sys.aud$ aud, system_privilege_map spm, system_privilege_map spx, STMT_AUDIT_OPTION_MAP aom, audit_actions act where aud.action# = act.action (+) and - aud.logoff$dead = spm.privilege (+) and aud.logoff$dead = aom.option# (+) and - aud.priv$used = spx.privilege (+)
What about AUDIT_ACTIONS? Well, I looked at the table and thought “this looks very similar to the list of values for COMMAND_TYPE – I should check that out”.
I don’t have to. The very next day Christian Antognini posted a quick comment on the posting about COMMAND_TYPE telling me where one could find a list of these commands in the DB – Yep, AUDIT_ACTIONS.
Christian’s posting warned me that a couple of commands are missing (our old favorite from the original post MERGE being one and CREATE_DISKGROUP being the other he cites) but it includes several more that are not listed in the manual. If anyone is aware of COMMAND_TYPES not included in AUDIT_ACTIONS then let me know and I can maintain a list here.
{Thanks to Chris for providing many more missing actions and two new to 11.2, included below}
New ones to those liste in the manual are:-
128 – FLASHBACK
129 – CREATE_SESSION
197 – PURGE USER_RECYCLEBIN
198 – PURGE DBA_RECYCLEBIN
199 – PURGE TABLESAPCE
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
and new in the table for 11.2
225 – ALTER DATABASE LINK
305 – ALTER PUBLIC DATABASE LINK
Missing codes are:-
88 – ALTER VIEW
90 – SET CONSTRAINTS
187 – CREATE SPFILE
188 – CREATE PFILE
189 – MERGE
192 – ALTER SYNONYM
193 – ALTER DISKGROUP
194 – CREATE DISKGROUP
195 – DROP DISKGROUP
XXX – FLASHBACK
Here is the list form AUDIT_ACTIONS from 10.2.0.3 (an eyeball check with 11.1.0.1 indicates the list has not increased in that version at least, which surprised me):
select * from audit_actions order by action ACTION NAME ---------- ---------------------------- 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 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 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 197 PURGE USER_RECYCLEBIN 198 PURGE DBA_RECYCLEBIN 199 PURGE TABLESAPCE 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
Hi Martin
I just checked the list I have implemented in TVD$XTAT (my profiler…). I added the following codes that do not appear in AUDIT_ACTIONS:
88 ALTER VIEW
90 SET CONSTRAINTS
187 CREATE SPFILE
188 CREATE PFILE
189 MERGE
192 ALTER SYNONYM
193 ALTER DISKGROUP
194 CREATE DISKGROUP
195 DROP DISKGROUP
In addition, in 11.2, there are two new entries:
SQL> select * from audit_actions@dba112
2 minus
3 select * from audit_actions;
ACTION NAME
———- —————————-
225 ALTER DATABASE LINK
305 ALTER PUBLIC DATABASE LINK
Cheers,
Chris
Wish I’d read these posts sooner. I had a conversation with Jeff Moss about this a few years ago …. http://www.oramoss.com/blog/2007/02/using-awr-to-summarise-sql-operations.html
[…] 10-How to get list of available command types ? Martin Widlake-More on COMMAND_TYPE values […]
191 = UPD_JOININDEX, which is an internal operation for updating bitmap join indexes.