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 (
ORGANIZATION EXTERNAL (
DEFAULT DIRECTORY ext_tab_data
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY ','
MISSING FIELD VALUES ARE NULL
REJECT LIMIT UNLIMITED;