By mapping an external table to some text file, you can view the file contents as if it were
data in a database table. External tables are available since Oracle 9i Database, and from Oracle
11gR2 Database on, it is even possible to do some inline preprocessing on the file.
The following example of this feature picks up on standard output of shell script “get_alert_xml.sh”.
It isn’t referencing any file, but take notice of the fact that an empty “dummyfile” must still be
present and readable by oracle. By pre-executing some ADRCI commands and redirecting output to screen,
external table Alert_XML will show the last 7 days of entries of the xml version of the alert log.
su - oracle . oraenv [ orcl ] $ cd /u01/app/oracle/admin/scripts $ touch dummyfile $ echo '#!/bin/sh' > get_alert_xml.sh $ echo 'ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1' >> get_alert_xml.sh $ echo 'DIAG_HOME=diag/rdbms/orcl/orcl' >> get_alert_xml.sh $ echo 'DAYS=\\"originating_timestamp > systimestamp-7\\"' >> get_alert_xml.sh $ echo '$ORACLE_HOME/bin/adrci exec="set home $DIAG_HOME;show alert -p $DAYS -term;"' >> get_alert_xml.sh $ chmod 744 get_alert_xml.sh
$ sqlplus / as sysdba SQL> create directory exec_dir as '/u01/app/oracle/admin/scripts'; SQL> grant read,execute on directory exec_dir to harry; SQL> connect harry/**** SQL> CREATE TABLE ALERT_XML ( TEXT VARCHAR2(400 CHAR) ) ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER DEFAULT DIRECTORY EXEC_DIR ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE PREPROCESSOR EXEC_DIR:'get_alert_xml.sh' nobadfile nodiscardfile nologfile ) LOCATION ('dummyfile') ) REJECT LIMIT UNLIMITED NOPARALLEL NOMONITORING; SQL> select * from alert_xml; TEXT -------------------------------------------------------------------------------- ADR Home = /u01/app/oracle/diag/rdbms/orcl/orcl: ************************************************************************* 2014-08-26 10:21:19.018000 +02:00 Starting ORACLE instance (normal) LICENSE_MAX_SESSION = 0 LICENSE_SESSIONS_WARNING = 0 Picked latch-free SCN scheme 2 Using LOG_ARCHIVE_DEST_1 parameter default value as USE_DB_RECOVERY_FILE_DEST 2014-08-26 10:21:20.066000 +02:00 > snip < SQL>
Check out Alert_XML_Errors here.
—–Add-on 5 September 2014——
if confronted with more than 1 database instance on the database server, you can either use 1 dummyfile for all instances and a different shell script for each instance in order to set the correct DIAG HOME, or you use a different LOCATION file for each instance, and reference this in one shell script. I’d like to opt for the latter, because the LOCATION file only has to contain the instance name, so it’s lesser code in total.
For instance, with LOCATION (‘orcl.txt’), and file orcl.txt just containing the instance name orcl, the following shell script code:
ORACLE_SID=`/bin/cat $1` ORACLE_DBS=`/usr/bin/expr $ORACLE_SID | /usr/bin/tr '[:lower:]' '[:upper:]' ` DIAG_HOME=diag/rdbms/$ORACLE_DBS/$ORACLE_SID
generates this DIAG_HOME: diag/rdbms/ORCL/orcl
3 thoughts on “SQL> Select * From Alert_XML;”
Thanks, Marco and Polarbear., very useful stuff!
Main aim of this short blog however is showing off this wonderful preprocessing feature of external tables. There is really no limit in what you can do with this feature, as long as your shell scripts use full paths for executable s, There are security issues though, that should be taken into account… Oracle provides a nice whitepaper about this subject – http://download.oracle.com/otndocs/products/database/enterprise_edition/utilities/pdf/xtables_preproc11g_1009.pdf
Useful stuff as always, thanks.
Alternatives, Harry, could be
You might also query the fixed view X$DBGALERTEXT, unfortunately not available as a GV$ view.
Comments are closed.