Dictionary System Info

[Michael42]

Hello,

I am writing a trigger that grabs\audits some system info and performs an INSERT into a table. This is being fired AFTER DDL database events.

I am grabbing:
* sys.dictionary_obj_type
* sys.dictionary_obj_name
* sys.dictionary_obj_owner
* sys.login_user

What are some other system info that can be captured from the dictionary, i.e. how can I list all the possible dictionary fields?

I know, I know this is a really basic DBA question but I cannot find this anywhere (Internet, books). Maybe I am not searching on the correct string.

Thanks,

Michael42

 

[SantaMufasa]

...how can I list all the possible dictionary fields?

Since there are approximately 1200 Dictionary views, each with many columns, your output will be significant.

But, since you asked for it, here is a query that will give you what you want:

col a heading "Dictionary View Name" format a30
col b heading "Column Name" format a30
col c heading "Data Type" format a15
break on a
select table_name a, column_name b, data_type c
from dba_tab_columns a
where owner = 'SYS'
  and exists (select 'x' from dictionary where a.table_name = table_name)
order by table_name,column_id;

Dictionary View Name           Column Name                    Data Type
------------------------------ ------------------------------ --------------
ALL_ALL_TABLES                 OWNER                          VARCHAR2
                               TABLE_NAME                     VARCHAR2
                               TABLESPACE_NAME                VARCHAR2
                               CLUSTER_NAME                   VARCHAR2
                               IOT_NAME                       VARCHAR2
                               PCT_FREE                       NUMBER
                               PCT_USED                       NUMBER
...et cetera

Let us know if this is what you wanted


Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.