Als PDF Downloaden!

Einsatz externer Tabellen

Bei externen Tabellen handelt es sich um ASCII-Datendateien, die ausserhalb der Datenbank gehalten werden. Die Daten können von Oracle über einen Treiber ("Access Driver") gelesen werden, ohne erst in die Datenbank geladen werden zu müssen. Dieser Treiber ist mit dem SQL*LOADER vergleichbar und bedient sich eines Teils seines zur Verfügung stehenden Befehlssatzes. 

Oracle External Tables ist somit ein interessantes Feature für den Einsatz in komplexen Datenbanklandschaften und DataWarehouse-Umgebungen. 

Merkmale externer Tabellen

Grundsätzlich mit "normalen" SQL-Tabellen vergleichbar, sind im Umgang mit externen Tabellen einige Einschränkungen

• Beim Erstellen einer externen Tabelle wird keine physische Tabelle angelegt, sondern es werden lediglich die Metadaten im Data Dictionary gespeichert. Über diese Metadaten können dann die eigentlichen Daten wie über eine Tabelle angesprochen werden.
• Externe Tabellen werden über den Befehl CREATE TABLE ... ORGANIZATION EXTERNAL erstellt.
• Es besteht nur READ ONLY-Zugriff auf die Daten. Es können weder DML-Operationen durchgeführt, noch Indizes angelegt werden.
• Man kann die Daten selektieren, joinen und sortieren und des weiteren können Views und Synonyme für externe Tabellen erstellt werden.
• Eine Analyse von externen Tabellen ist nur mittels DBMS_STATS möglich. Setzen Sie dazu ab Version 10g den Parameter ESTIMATE_PERCENT explizit auf NULL, ansonsten kommt es zu folgender Fehlermeldung:

ORA-20000: Unable to analyze TABLE "<owner>"."<tab>", sampling on external table is not supported

Beispiel zum Erstellen externer Tabellen

Vor dem Erstellen einer externen Tabelle müssen noch folgende Voraussetzungen geschaffen werden:

1. Es bestehen eine oder mehrere ASCII-Dateien, in denen die Daten in einer vom SQL*LOADER lesbaren Form vorliegen:

Beispiel:

emp1.dat:
7369,SMITH,CLERK,7902,17-DEZ-80,100,20
7499,ALLEN,SALESMAN,7698,20-FEB-81,250,30
7521,WARD,SALESMAN,7698,22-FEB-81,450,30
7566,JONES,MANAGER,7839,02-APR-81,1150,20

emp2.dat:
7654,MARTIN,SALESMAN,7698,28-SEP-81,1250,30
7698,BLAKE,MANAGER,7839,01-MAI-81,1550,30
7839,KING,PRESIDENT,,17-NOV-81,2500,10

2. Es müssen Directory-Objekte mit den Pfaden für die Daten-, Bad-, Log- und/oder Discard-Dateien existieren:

CREATE OR REPLACE DIRECTORY emp_dir as '\...\data';
CREATE OR REPLACE DIRECTORY bad_dir as '\...\data\bad';
CREATE OR REPLACE DIRECTORY log_dir as '\...\data\log';

3. Dem Benutzer, der die externe Tabelle erzeugen soll, müssen die entsprechenden Berechtigungen auf die Directory-Objekte zugewiesen werden:

GRANT READ ON DIRECTORY emp_dir TO scott;
GRANT WRITE ON DIRECTORY bad_dir TO scott;
GRANT WRITE ON DIRECTORY log_dir TO scott;

Anschließend kann die externe Tabelle erzeugt werden:    

Beispiel:

CREATE TABLE scott.emp_ext
  (emp_id number(4),
   name varchar2(12),
   job varchar2(12),
   mgr_id number(4),
   hiredate date,
   salary number(5),
   dept_id number(2))
 ORGANIZATION EXTERNAL
   (type oracle_loader
    default directory emp_dir
    access parameters
      (records delimited by newline
       logfile log_dir:'extern%a_%p.log'
       badfile bad_dir:'extern%a_%p.bad'
       fields terminated by ','
       missing field values are null)
    location ('emp1.dat','emp2.dat'))
  REJECT LIMIT UNLIMITED
  PARALLEL;

Ein abschließender SELECT auf die gerade angelegte Tabelle ergibt die folgende Ausgabe:

SELECT * FROM scott.emp_ext;

EMP_ID NAME       JOB        MGR_ID HIREDATE   SALARY  DEPT_ID
------ ---------- --------- ------- -------- -------- --------
  7369 SMITH      CLERK        7902 17.12.80      100       20
  7499 ALLEN      SALESMAN     7698 20.02.81      250       30
  7521 WARD       SALESMAN     7698 22.02.81      450       30
  7566 JONES      MANAGER      7839 02.04.81     1150       20
  7654 MARTIN     SALESMAN     7698 28.09.81     1250       30
  7698 BLAKE      MANAGER      7839 01.05.81     1550       30
  7839 KING       PRESIDENT         17.11.81     2500       10

7 Zeilen ausgewählt.

Beschreibung der Parameter

• TYPE: legt den Treiber für das Laden der Daten fest; Angabe ist optional (Default: ORACLE_LOADER)
• DEFAULT DIRECTORY: Speicherort der Datendateien
• ACCESS  PARAMETERS: Untermenge derer, die beim Erstellen einer SQL*LOADER-Kontrolldatei verwendet werden
• LOCATION: legt die Namen der Datendateien fest
• REJECT LIMIT: gibt die Anzahl der erlaubten verworfenen Sätze an
• PARALLEL: aktiviert paralleles Laden; ist bei großen Datenmengen von Vorteil

Änderung von externen Tabellen

Die folgenden Eigenschaften von externen Tabellen können geändert werden:

• DEFAULT DIRECTORY   

ALTER TABLE scott.emp_ext DEFAULT DIRECTORY neu_dir;

• ACCESS PARAMETERS     

ALTER TABLE scott.emp_ext ACCESS PARAMETERS (fields terminated by ':');

•  LOCATION     

ALTER TABLE scott.emp_ext LOCATION ('emp1.txt','emp2.txt');

•  REJECT LIMIT     

ALTER TABLE scott.emp_ext REJECT LIMIT 400;

•  PARALLEL   

ALTER TABLE scott.emp_ext NOPARALLEL;

•  RENAME TO      

ALTER TABLE scott.emp_ext RENAME TO scott.ext_table;

•  MODIFY COLUMN     

ALTER TABLE scott.emp_ext MODIFY (col1 varchar2(100));

•  ADD COLUMN     

ALTER TABLE scott.emp_ext ADD (col2 number);

• DROP COLUMN     

ALTER TABLE scott.emp_ext DROP COLUMN col2;

Data Dictionary-Views zu externen Tabellen

Die folgenden Views zeigen die Attribute der externen Tabelle

• USER_EXTERNAL_TABLES
• ALL_EXTERNAL_TABLES
• DBA_EXTERNAL_TABLES

Beispiel:

SELECT * FROM user_external_tables;

Mit diesen Views können die Datenquellen der externen Tabellen abgefragt werd

• USER_EXTERNAL_LOCATIONS
• ALL_EXTERNAL_LOCATIONS
• DBA_EXTERNAL_LOCATIONS

Beispiel:

SELECT * FROM dba_external_locations;

Suche