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;