Oracle External Table
External tables are defined as tables that do not resides in the database allows you to access data that is stored outside the database in files. Oracle provides a couple of access driver “ORACLE_LOADER” and “ORACLE_DATAPUMP” to read this external data. You can select, join, or sort the external table but you can't perform DML operation ( INSERT, UPDATE and DELETE). External tables also support view and synonyms creation but you can not create indexes on external tables.
External table feature provides a valuable mean for performing ETL task in data warehousing. External tables support 3 DDL operations.
- Create external tables
- Alter external tables
- Drop externa tables
Creating External table and Load data
Sample data (dept.txt)
deptno,dname,location
10,Accounting,New York
20,Research,Dallas
30,Sales,Chicago
40,Operations,Boston
CONN / AS SYSDBA
CREATE OR REPLACE DIRECTORY ext_data_dir as “c:/users/xxx/oracle/data”;
GRANT READ, WRITE ON DIRECTORY ext_data_dir TO SCOTT;
CONN SCOTT/TIGER
CREATE TABLE DEPT_EXTERNAL (
deptno NUMBER(3),
dname VARCHAR2(40),
location VARCHAR2(60)
) ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY ext_data_dir
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY ‘,’
MISSING FIELD VALUES ARE NULL
(
deptno, dname, location)
)
)
LOCATION(‘dept.txt’) /* can add more than one file */
)
PARALLEL 5
REJECT LIMIT UNLIMITED;
if you omit the TYPE specification, ORACLE_LOADER is used by default as access driver. you can use ORALCE_DATAPUMP access driver
External tables can be altered using the heap table alter commands for PARALLEL, ADD COLUMN, MODIFY COLUMN, SET UNUSED, DROP COLUMN and RENAME COLUMN. Apart from this you can alter the reject limit, default directory, access parameters and location
ALTER TABLE DEPT_EXTERNAL REJECT LIMIT 50;
ALTER TABLE DEPT_EXTERNAL DEFAULT DIRECTORY <new_directory>;
ALTER TABLE DEPT_EXTERNAL ACCESS PARAMETERS( FIELDS TERMINATED BY ‘:’);
ALTER TABLE DEPT_EXTERNAL LOCATION(‘dept2.txt’,’dept3.txt’,…);
Drop External table is same as normal heap table drop statement.
