What is External Table?
External tables allow Oracle to query data that is stored outside the database in flat files.
No DML can be performed on external tables but they can be used for query, join and sort operations. Views and synonyms can be created against external tables.
They are useful in the ETL process of data warehouses since the data doesn't need to be staged and can be queried in parallel. They should not be used for frequently queried tables.
How to create a External Table
Create a directory object pointing to the location of the files.
CREATE OR REPLACE DIRECTORY ext_tab_data AS '/data';
Create the external table using the CREATE TABLE..ORGANIZATION EXTERNAL syntax. This defines the metadata for the table describing how it should appear and how the data is loaded.
CREATE TABLE emp_ext (
emp_code VARCHAR2(5),
emp_name VARCHAR2(50),
mgr_name VARCHAR2(50)
)
ORGANIZATION EXTERNAL (
TYPE ORACLE_LOADER
DEFAULT DIRECTORY ext_tab_data
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY ','
MISSING FIELD VALUES ARE NULL
(
emp_code CHAR(5),
emp_name CHAR(50),
mgr_name CHAR(50)
)
)
LOCATION ('Emp1.txt','Emp2.txt')
)
PARALLEL 5
REJECT LIMIT UNLIMITED;
