Using SQL commands on ADRCI

While working with ADRCI , I came to know that we can run normal SQL commands for some of the operation.e.g To display current purge policy , you can run select * from adr_control instead of show control.

adrci> desc ADR_CONTROL
Name                          Type            NULL?      
----------------------------- --------------- -----------
ADRID                         number                     
SHORTP_POLICY                 number                     
LONGP_POLICY                  number                     
LAST_MOD_TIME                 timestamp                  
LAST_AUTOPRG_TIME             timestamp                  
LAST_MANUPRG_TIME             timestamp                  
ADRDIR_VERSION                number                     
ADRSCHM_VERSION               number                     
ADRSCHMV_SUMMARY              number                     
ADRALERT_VERSION              number                     
CREATE_TIME                   timestamp

Since I am only interested in few columns, I can create a view

adrci> create view my_control as select SHORTP_POLICY,LONGP_POLICY,LAST_AUTOPRG_TIME,LAST_MANUPRG_TIME from ADR_CONTROL;

View MY_CONTROL Created

Now you can query this new view

adrci> select * from my_control;

ADR Home = /home/oracle/diag/rdbms/tintin/tintin1:
*************************************************************************
SHORTP_POLICY        LONGP_POLICY         LAST_AUTOPRG_TIME                        LAST_MANUPRG_TIME                        
-------------------- -------------------- ---------------------------------------- ---------------------------------------- 
720                  8760                 2012-12-27 23:09:47.140711 +00:00                                                
1 rows fetched

You can find the list of all metadata tables under ADR_HOME/metadata.

oradbdev01:tintin1:/home/oracle/diag/rdbms/tintin/tintin1/metadata>ls -ltr HM*
-rw-r----- 1 oracle dba   65536 Jan 13  2012 HM_INFO.ams
-rw-r----- 1 oracle dba   65536 Jan 13  2012 HM_FDG_SET.ams
-rw-r----- 1 oracle dba   65536 Jan 13  2012 HM_MESSAGE.ams
-rw-r----- 1 oracle dba   65536 Dec 27 23:09 HM_RECOMMENDATION.ams
-rw-r----- 1 oracle dba   65536 Dec 27 23:09 HM_FINDING.ams
-rw-r----- 1 oracle dba 9895936 Jan  3 05:44 HM_RUN.ams

We can run query on HM_RUN table like this

adrci> select RUN_ID,RUN_NAME,CHECK_NAME,MODE,START_TIME,END_TIME,STATUS from HM_RUN where rownum

I tried running substr command to format the column but this gives error 🙂

adrci> select RUN_ID,substr(RUN_NAME,1,20),CHECK_NAME,MODE,START_TIME,END_TIME,STATUS from HM_RUN where rownum <2;
DIA-48001: internal error code, arguments: [dbgrmdmmr_marshall_rec_null_terminate], [1], [8], [8], [], [], [], []

There is SET COLUMN text size command, but it sets length for all Text columns and can’t be used to size individual column. This is useful if you are querying big field like “Text” column of VIEW. e.g

adrci> set column text 100
adrci> select text from view where name='MY_CONTROL';

ADR Home = /home/oracle/diag/rdbms/tintin/tintin1:
*************************************************************************
TEXT                                                                                                 
---------------------------------------------------------------------------------------------------- 
select SHORTP_POLICY,LONGP_POLICY,LAST_AUTOPRG_TIME,LAST_MANUPRG_TIME from ADR_CONTROL;

Tags: 11gadrcisql