Utiliser des tables externes pour accéder à des fichiers Oracle sous Linux - Use external tables to access Oracle files on Linux

23 juillet 2019

Utiliser des tables externes pour accéder à des fichiers Oracle sous Linux - Use external tables to access Oracle files on Linux

Introduction
Parfois vous ne pouvez pas vous connecter au serveur Linux qui héberge votre base Oracle : pas de compte, serveur dans un Cloud hors de votre société etc etc. C'est très gênant pour accéder au contenu de fichiers Oracle de configuration comme le listener.ora, tnsname.ora etc etc car ces fichiers ne sont pas interfacés avec la base Oracle, comme l'est par exemple le fichier alert.log (voir ici : "Utiliser SQL pour lire le fichier ALERT.log"). La solution que nous allons explorer ici est l'utilisation d'une table externe.

Points d'attention
Le user Oracle qui veut lire les fichiers doit avoir les droits de le faire non seulement au niveau de la base (droit READ sur le Directory contenant ces fichiers) et le droit Linux sur ces fichiers.

Attention, il est impossible de faire des INSERT, UPDATE, DELETE sur des tables externes ni de construire des index, du fait que ces fichiers sont gérés par l'OS et non pas par Oracle.

Base de tests
Une base Oracle 12.

Exemples
============================================================================================
Le fichier listener.ora
============================================================================================
Emplacement du listener.ora pour ma base.
     [oracle@vbgeneric admin]$ pwd
     /u01/app/oracle/product/12.2/db_1/network/admin

Les fichiers appartiennent au user Linux oracle. C'est important car en créant une table externe, il faut que le ou les users Oracle puissent lire dans ce répertoire Linux, via un Directory Oracle.
     [oracle@vbgeneric admin]$ ls -lrt
     total 28
     -rw-r--r-- 1 oracle oinstall 1441 Aug 28 2015 shrept.lst
     drwxr-xr-x 2 oracle oinstall 4096 Mar 2 2017 samples
     -rw-r--r-- 1 oracle oinstall   53 Mar 2 2017 sqlnet.ora
     -rw-r--r-- 1 oracle oinstall 462 Mar 2 2017 listener.ora
     -rw-r--r-- 1 oracle oinstall 579 Mar 2 2017 tnsnames.ora

Son contenu.
     [oracle@vbgeneric admin]$ more /u01/app/oracle/product/12.2/db_1/network/admin/listener.ora
     SID_LIST_LISTENER =
       (SID_LIST =
         (SID_DESC =
           (GLOBAL_DBNAME = orcl12c)
           (SID_NAME = orcl12c)
           (ORACLE_HOME = /u01/app/oracle/product/12.2/db_1)
         )
       )

     LISTENER =
       (DESCRIPTION_LIST =
         (DESCRIPTION =
           (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
           (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
         )
       )

     #HOSTNAME by pluggable not working rstriction or configuration error.
     DEFAULT_SERVICE_LISTENER = (orcl12c)

============================================================================================
Création de la table externe
============================================================================================
On commence par créer un directory Oracle sur le répertoire Linux puis la table externe.
     SQL> CREATE DIRECTORY listener AS '/u01/app/oracle/product/12.2/db_1/network/admin';
     Directory LISTENER created.

     SQL> CREATE TABLE SYSTEM.listener_ora
     (
        ligne VARCHAR2(4000) -- chaque ligne du fichier fait certainement moins de 4000 caractères
     )
        ORGANIZATION EXTERNAL -- c'est une table externe
        (
           TYPE ORACLE_LOADER
           DEFAULT DIRECTORY LISTENER -- répertoire Linux du listener.ora
           ACCESS PARAMETERS
           (
            records delimited by newline -- je crée un enregistrement dans ma table par ligne du fichier
            badfile LISTENER:'LISTENER_table_externe.bad' -- fichier des enregistrements posant problème
            logfile LISTENER:'LISTENER_table_externe.log' -- fichier de log
            fields terminated by eof
           )
           LOCATION ('listener.ora') -- le nom du fichier à lire
        )
        PARALLEL
        REJECT LIMIT UNLIMITED ;

     Table SYSTEM.LISTENER_ORA created.

Et si maintenant on fait un SELECT sur cette table? BINGO, on a bien créé un lien entre une table Oracle et un fichier Linux : le contenu du fichier est maintenant accessible directement depuis Oracle, sans que l'on ait à se connecter sur le serveur Linux.
     SQL> select * from SYSTEM.LISTENER_ORA;
     LIGNE
     --------------------------------------------------------------------------------
     SID_LIST_LISTENER =
       (SID_LIST =
         (SID_DESC =
           (GLOBAL_DBNAME = orcl12c)
           (SID_NAME = orcl12c)
           (ORACLE_HOME = /u01/app/oracle/product/12.2/db_1)
         )
       )
     LISTENER =
       (DESCRIPTION_LIST =
         (DESCRIPTION =
           (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
           (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
         )
       )
     #HOSTNAME by pluggable not working rstriction or configuration error.
     DEFAULT_SERVICE_LISTENER = (orcl12c)

     17 rows selected.

Regardons les logs, au cas où il y aurait des problèmes.
     [oracle@vbgeneric admin]$ pwd
     /u01/app/oracle/product/12.2/db_1/network/admin

     [oracle@vbgeneric admin]$ ls -lrt
     total 28
     -rw-r--r-- 1 oracle oinstall 1441 Aug 28 2015 shrept.lst
     drwxr-xr-x 2 oracle oinstall 4096 Mar 2 2017 samples
     -rw-r--r-- 1 oracle oinstall   53 Mar 2 2017 sqlnet.ora
     -rw-r--r-- 1 oracle oinstall 462 Mar 2 2017 listener.ora
     -rw-r--r-- 1 oracle oinstall 579 Mar 2 2017 tnsnames.ora
     -rw-r--r-- 1 oracle oinstall 936 Jul 22 07:43 LISTENER_table_externe.log
     -rw-r--r-- 1 oracle oinstall    3 Jul 22 07:43 LISTENER_table_externe.bad

Il semble y avoir des erreurs mais en réalité le contenu de la table est bien conforme à celui du fichier.
Les lignes 9, 17 et 20 en erreur sont les lignes vides du fichier donc pas de panique, tout le contenu a bien été récupéré :-)
     [oracle@vbgeneric admin]$ more LISTENER_table_externe.log

      LOG file opened at 07/22/19 07:43:10

     Field Definitions for table LISTENER_ORA
       Record format DELIMITED BY NEWLINE
       Data in file has same endianness as the platform
       Rows with all null fields are accepted

       Fields in Data Source:

         LIGNE                           CHAR (4000)
           Terminated by EOF
           Trim whitespace same as SQL Loader
     KUP-04021: field formatting error for field LIGNE
     KUP-04023: field start is after end of record
     KUP-04101: record 9 rejected in file /u01/app/oracle/product/12.2/db_1/network/admin/listener.ora
     KUP-04021: field formatting error for field LIGNE
     KUP-04023: field start is after end of record
     KUP-04101: record 17 rejected in file /u01/app/oracle/product/12.2/db_1/network/admin/listener.ora
     KUP-04021: field formatting error for field LIGNE
     KUP-04023: field start is after end of record
     KUP-04101: record 20 rejected in file /u01/app/oracle/product/12.2/db_1/network/admin/listener.ora

Quant au fichier .bad, il est vide : tout est OK!
     [oracle@vbgeneric admin]$ more LISTENER_table_externe.bad

     [oracle@vbgeneric admin]$

Et, cerise sur le gâteau, on peut interroger cette table avec toute la puissance du SQL, chose plus délicate à faire directement sous Linux. Par exemple, si on veut la liste des services utilisés dans le fichier, on fait le SELECT ci-dessous.
     SQL> desc system.listener_ora
     Name                       Null?    Type
     ----------------------------------------- -------- ----------------------------
     LIGNE                            VARCHAR2(4000)

     SQL> select LIGNE from system.listener_ora where LIGNE like '%SERVICE%';
     LIGNE
     --------------------------------------------------------------------------------
     DEFAULT_SERVICE_LISTENER = (orcl12c)