adrci - running sql?

Discovered by accident that you can query some aspects of ADR via SQL - for example

adrci> select * from DFW_CONFIG_ITEM;

returns various details from whats contained in ADR.

The list of available 'tables' is located under

$DIAG_DEST/rdbms/SID/SID/metadata

DUAL also seems to work

desc also works just fine - for example

adrci> desc DFW_CONFIG_ITEM
Name                          Type            NULL?
----------------------------- --------------- -----------
HASH_VALUE                    number
CATEGORY                      number
SOURCE                        text(64)
ITEM_NAME                     text(64)
ITEM_VALUE                    text(512)
adrci>
You can even create views:

adrci> create view test as select item_value from DFW_CONFIG_ITEM where ITEM_NAME='control_files';
View TEST Created
adrci> select * from test;
ADR Home = /oracle/diag/rdbms/emza3/EMZA3:
*************************************************************************
ITEM_VALUE                                                                                                                                                                                                                                                                                                                                                                                               
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/oracle/EIPODS/recovery_area/EMZA3/EMZA3/controlfile/o1_mf_88yzq9bc_.ctl, /oracle/EIPODS/oradata/EMZA3/EMZA3/controlfile/o1_mf_88yzq9ds_.ctl, /oracle/EIPODS/recovery_area/EMZA3/EMZA3/controlfile/o1_mf_88yzq9hm_.ctl                                                                                                                                                                                   
1 rows fetched
adrci>

Not sure what the point of all this would be - but it's possible.......

The view defitinion seems to be stored in VIEW.ams/VIEWCOL.ams in the directory mentioned previously

Comments