Martin Widlake's Yet Another Oracle Blog

More on COMMAND_TYPE values. January 8, 2010

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.

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 (+)

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

Describing tables you can’t DESC October 7, 2009

This is more an oddity than anything particularly useful. Sometimes you can’t use the sql*plus DESCRIBE {DESC} command on tables- but you might have an alternative.

I’m doing a lot of work for a client on a 10.2.0.3 database.  I have SELECT ANY DICTIONARY but not SELECT ANY TABLE privilege. This is because there is sensitive data in the database and it is duly protected {and this is certainly not the first time I have worked for a client with full dictionary access but not data access, it’s becoming normal}. I’m granted access to specific things as needs arise.

I knew I had to look at a table called AD_FAKE_STATS_COLUMNS.

select count(*) from mdw.AD_FAKE_STATS_COLUMNS
/
select count(*) from mdw.AD_FAKE_STATS_COLUMNS
                         *
ERROR at line 1:
ORA-00942: table or view does not exist

Have I got the table name wrong?

select table_name,owner
from dba_tables
where table_name = 'AD_FAKE_STATS_COLUMNS'

TABLE_NAME                     OWNER
------------------------------ -----------------
AD_FAKE_STATS_COLUMNS          MDW

OK, it is there, I don’t have access to it. Fine, I don’t want access to the data, I just want to see the structure of the table:

desc mdw.AD_FAKE_STATS_COLUMNS
ERROR:
ORA-04043: object mdw.AD_FAKE_STATS_COLUMNS does not exist

Oh. DESC in sql*plus does not work.

I can’t DESC a table I do not have access to carry out DML on. I’m going to have to go and ask someone to give me permission to see the table. How annoying.

Or do I?

@tab_desc
Enter value for tab_name: AD_FAKE_STATS_COLUMNS
old 21: where table_name like upper (nvl('&TAB_NAME','W')||'%')
new 21: where table_name like upper (nvl('AD_FAKE_STATS_COLUMNS','W')||'%')

TAB_OWN  TAB_NAME              COL_NAME              M COL_DEF
-----------------------------------------------------------------
MDW      AD_FAKE_STATS_COLUMNS TABLE_NAME            Y VARCHAR2(30)
                               COLUMN_NAME           Y VARCHAR2(30)
                               COPY_STATS_FROM       N VARCHAR2(61)
                               LOW_VALUE_SQL         N VARCHAR2(100)
                               HIGH_VALUE_SQL        N VARCHAR2(100)
                               DISTINCT_SQL          N VARCHAR2(100)
                               DAYS_HIST_NULL_AVGLEN N NUMBER(3,0)

I have access to the data dictionary. So I can see the structure of the table, which after all is what I wanted and is what the client is happy for me to have.{I’ve never much liked the DESC command in sql*plus, I replaced it with a little sql script against the data dictionary years ago}.

In case you want it, here is the script:

-- tab_desc.sql
-- Martin Widlake date? way back in the mists of time
-- my own replacement for desc.
-- 16/11/01 improved the data_type section
 SET PAUSE ON
 SET PAUSE 'Any Key...>'
 SET PAGES 24
col Tab_own form A10
col tab_name form a22 wrap
col col_name form a28 wrap
col col_def form A14
 break on tab_own skip 1 on tab_name skip 1
 spool tab_desc.lst
 select
 owner                               Tab_Own
,table_name             Tab_Name
,column_name            Col_Name
,decode(NULLABLE,'Y','N','Y') Mand
,data_type||decode(data_type
       ,'NUMBER','('
        ||decode(to_char(data_precision)
                ,null,'38'
                ,     to_char(data_precision)||
                      decode(data_scale,null,''
                                      ,      ','||data_scale)
                 )
                    ||')'
       ,'DATE',null
       ,'LONG',null
       ,'LONG RAW',null
,'('||Substr(DATA_LENGTH,1,5)||')'
				     )  col_def
from dba_tab_columns
where table_name like upper (nvl('&TAB_NAME','WHOOPS')||'%')
order by 1,2,column_id,3,4
/
spool off
clear col
--

Peeking under the Data Dictionary Hood July 7, 2009

Have you ever wondered how some people seem to be able to find out how the Oracle database does things internally? How they work out where some of the interesting stuff is contained within the data dictionary and can pull it out?

Well, one of the simpler ways to peek under the data dictionary hood is to just look at the objects in the data dictionary itself. I’ve found some interesting things by looking at the data dictionary views. A lot of the dictionary objects areviews, like DBA_TABLES and DBA_COLUMNS. I’ll look at DBA_TABLESPACES {as it’s a bit smaller!}

You can get my script here – vw_txt.sql . As you will see, it is a terribly complex script… The only trick is to make sure that “set long 32767″ as the view text is held in column of the ancient LONG datatype.

> @vw_txt
Enter value for vw_name: dba_tablespaces
old 6: where view_name like upper (nvl('&vw_name','WHOOPS')||'%')
new   6: where view_name like upper (nvl('dba_tablespaces','WHOOPS')||'%')
Any Key...>

OWNER                          VIEW_NAME                      TEXT_LENGTH
------------------------------ ------------------------------ -----------
TEXT
--------------------------------------------------------------------------------
SYS DBA_TABLESPACES 1724
select ts.name, ts.blocksize, ts.blocksize * ts.dflinit,
          decode(bitand(ts.flags, 3), 1, to_number(NULL),
ts.blocksize * ts.dflincr),
ts.dflminext,
          decode(ts.contents$, 1, to_number(NULL), ts.dflmaxext),
decode(bitand(ts.flags, 4096), 4096, ts.affstrength, NULL),
decode(bitand(ts.flags, 3), 1, to_number(NULL), ts.dflextpct),
ts.blocksize * ts.dflminlen,
decode(ts.online$, 1, 'ONLINE', 2, 'OFFLINE',
                 4, 'READ ONLY', 'UNDEFINED'),
decode(ts.contents$, 0, (decode(bitand(ts.flags, 16), 16, 'UNDO',
                 'PERMANENT')), 1, 'TEMPORARY'),
decode(bitand(ts.dflogging, 1), 0, 'NOLOGGING', 1, 'LOGGING'),
decode(bitand(ts.dflogging, 2), 0, 'NO', 2, 'YES'),
          decode(ts.bitmapped, 0, 'DICTIONARY', 'LOCAL'),
decode(bitand(ts.flags, 3), 0, 'USER', 1, 'SYSTEM', 2, 'UNIFORM',
                 'UNDEFINED'),
          decode(ts.plugged, 0, 'NO', 'YES'),
decode(bitand(ts.flags,32), 32,'AUTO', 'MANUAL'),
decode(bitand(ts.flags,64), 64,'ENABLED', 'DISABLED'),
decode(bitand(ts.flags,16), 16, (decode(bitand(ts.flags, 512), 512,
                 'GUARANTEE', 'NOGUARANTEE')), 'NOT APPLY'),
decode(bitand(ts.flags,256), 256, 'YES', 'NO'),
decode(tsattr.storattr, 1, 'STORAGE', 'HOST'),
decode(bitand(ts.flags,16384), 16384, 'YES', 'NO'),
decode(bitand(ts.flags,64), 0, null,
decode(bitand(ts.flags,65536), 65536,'FOR ALL OPERATIONS',
                 'DIRECT LOAD ONLY'))
from sys.ts$ ts, sys.x$kcfistsa tsattr
where ts.online$ != 3
and bitand(flags,2048) != 2048
and ts.ts# = tsattr.tsid

1 row selected.

You see a lot of decodes of bitand functions in these internal views. What is more interesting is to see what they are decoded into as it confirms what possible options there are.

Are you curious as to what all the possible object types in the database are? Whether your database happens to have examples of them or not? Peek inside the DBA_OBJECTS view {the following is part of that view}:-

OWNER                          VIEW_NAME                      TEXT_LENGTH
------------------------------ ------------------------------ -----------
TEXT
-------------------------------------------------------------------------------
SYS DBA_OBJECTS 2992
select u.name, o.name, o.subname, o.obj#, o.dataobj#,
       decode(o.type#, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER',
                      4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE',
                      7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',
                      11, 'PACKAGE BODY', 12, 'TRIGGER',
                      13, 'TYPE', 14, 'TYPE BODY',
                      19, 'TABLE PARTITION', 20, 'INDEX PARTITION', 21, 'LOB',
                      22, 'LIBRARY', 23, 'DIRECTORY', 24, 'QUEUE',
                      28, 'JAVA SOURCE', 29, 'JAVA CLASS', 30, 'JAVA RESOURCE',
                      32, 'INDEXTYPE', 33, 'OPERATOR',
                      34, 'TABLE SUBPARTITION', 35, 'INDEX SUBPARTITION',
                      40, 'LOB PARTITION', 41, 'LOB SUBPARTITION',
42, NVL((SELECT 'REWRITE EQUIVALENCE'
                               FROM sum$ s
                               WHERE s.obj#=o.obj#
and bitand(s.xpflags, 8388608) = 8388608),
                              'MATERIALIZED VIEW'),
                      43, 'DIMENSION',
                      44, 'CONTEXT', 46, 'RULE SET', 47, 'RESOURCE PLAN',
                      48, 'CONSUMER GROUP',
                      51, 'SUBSCRIPTION', 52, 'LOCATION',
                      55, 'XML SCHEMA', 56, 'JAVA DATA',
                      57, 'EDITION', 59, 'RULE',
                      60, 'CAPTURE', 61, 'APPLY',
                      62, 'EVALUATION CONTEXT',
                      66, 'JOB', 67, 'PROGRAM', 68, 'JOB CLASS', 69, 'WINDOW',
                      72, 'WINDOW GROUP', 74, 'SCHEDULE', 79, 'CHAIN',
                      81, 'FILE GROUP', 82, 'MINING MODEL', 87, 'ASSEMBLY',
                      90, 'CREDENTIAL', 92, 'CUBE DIMENSION', 93, 'CUBE',
                      94, 'MEASURE FOLDER', 95, 'CUBE BUILD PROCESS',
'UNDEFINED')

Scanning the view definitions for something you become interested in is a little more tricky as the text is, as I said, stored in a long and you can’t run sql functions against a long. I use a bit of PL/SQL to get around the problem :-

find_vw_txt.sql .

-- find_vw_txt
-- martin widlake 13/8/08
-- can't search view text as it is in a long.
-- so let's try PL/SQL
set serveroutput on size unlimited
spool find_vw_txt.lst
set trims on  lines 120 pages 32
declare
v_name varchar2(4000) :='&viewname';
v_search varchar2(100) :='&search_txt';
cursor get_vw_txt is
select u.name owner, o.name name, v.textlength textlen, v.text text
from sys.obj$ o
, sys.view$ v
   , sys.user$ u
where o.obj#     = v.obj#
and   o.owner#   = u.user#
and   o.name     like upper(v_name)||'%'
and v.textlength <32000; -- cant think how I stop a numeric
                          -- error trying to pull a larger long
-- into a varchar. bloody longs
v_text varchar2(32000);
v_where number;
begin
  v_name :=upper (v_name);
  v_search := upper(v_search);
for vtr in get_vw_txt loop
--dbms_output.put_line('looking at '||vtr.name);
    v_text := vtr.text;
    v_text := upper(v_text);
    v_where := instr(v_text,v_search);
    if v_where !=0 then
dbms_output.put_line('view '||vtr.owner||'.'||vtr.name||':** '
||substr(v_text,greatest(0,v_where),80)||' **'
                           );
    end if;
  end loop;
end;
/
spool off

It can be fun to dig into the internals this way. And useful. A few times I’ve been able to find out one or two things about how oracle is storing information.

It might occur to you that there are lots of internal pl/sql packages and you can look into them too:-

pkg_txt.sql will show you the contents of a package or stored function/procedure.
find_plsql_txt.sql will scan the stored PL/SQL for a text string.

However, a lot of the internal PL/SQL is wrapped, ie converted into a form you can’t peek into easily {well, in my case, at all, but some people know how}.

One last word of warning. You can find what look like interesting undocumented features when you peek under the covers. In V10 I came across the REVERSE function:


select reverse('Martin was here')
from dual

REVERSE(‘MARTIN
—————
ereh saw nitraM

Nice .

That could be useful, yes?

I’ve checked in the 11g documentation and it is still not documented. I googled “oracle reverse function” and several people have also found it. A couple ask why such a useful thing is not documented…

Well, I was “lucky” enough to find out why it is undocumented. I tried to use the REVERSE function on a number { this wason v10.1 I think}.

My session core-dumped.

I was on a production system when I tried this {Yes I KNOW!!! It was a momentary lapse}!

It seems to work OK on 10.2.0.4 and 11.1 but don’t. You have no idea if it will always work OK and no come-back to Oracle Corp if it blows up on you. My guess is they wrote the REVERSE function for internal use and did not test it for people using it “not as intended” {as I had done}.

So, my warning is, though it is fun to look under the covers and try things out, never, ever ever, ever do so on a production system. Or a test system used for proper testing. Or a development system those touchy developers are using. Or any system that you can’t afford to trash and recreate on a whim. Try it at home on your own PC, OK?