External tables are read-only tables that allow Oracle to query data that is stored outside the database in flat files. You can use external table feature to access external files as if they are tables inside the database. When you create an external table, you define its structure and location within oracle. Basically you just store the metadata inside the oracle. When you query the table, oracle reads the external table and returns the results just as if the data had been stored with in the database.
- By default, a log of load operations is created in the same directory as the load files, but this can be changed using the LOGFILE parameter.
- Any rows that fail to load are written to a bad file. By default, the bad file is created in the same directory as the load files, but this can be changed using the BADFILE parameter.
They should not be used for frequently queried tables because every query of the external table causes the file(s) to be read again, so try to make as few passes over the external table as possible.
LIMITATIONS ON EXTERNAL TABLES :::
- You cannot perform DML operations.
- Oracle Indexing not possible.
- Constraints not possible.
BENEFITS OF EXTERNAL TABLES :::
- Queries of external tables complete very quickly even though a full table scan id required with each access.
- You can join external tables to each other or to standard oracle tables.
- We can create views & synonyms on the external table.
ORGANIZATION EXTERNAL ::: It tells that data is stored outside the database.
TYPE ::: The Keyword TYPE specifies which access driver to use from the following :-
• ORACLE_LOADER (Default) - ORACLE_LOADER access driver loads data from an external table (flat text OS file) to an internal oracle table. However, it cannot unload the data i.e. it cannot move data from oracle internal table to the external table (flat text OS file).It is alternative to SQL*Loader.
• ORACLE_DATAPUMP - ORACLE_DATAPUMP access driver can perform both loads and unloads. It requires the data in the binary dump file format.
• ORACLE_HDFS - Read datafiles from HDFS by creating a HIVE table.
• ORACLE_HIVE - Read datafiles from HDFS by querying a HIVE catalog.
DEFAULT DIRECTORY ::: The DEFAULT DIRECTORY clause allows you to specify the default directory for storing all input and output files. It accepts a directory object, not a directory path.
ACCESS PARAMETERS ::: The ACCESS PARAMETERS clause allows you to describe the external data source. Note that each access driver has its own access parameters.
LOCATION ::: The LOCATION clause specifies the data file for the external table.You specify the data file in the form directory:file. If you omit the directory part, Oracle uses the DEFAULT DIRECTORY for the file.
REJECT LIMIT ::: It let you specify how many conversion errors can occur before oracle server abort the operation and return the error.Countries1.txt :::
ENG,England,English
SCO,Scotland,English
IRE,Ireland,English
WAL,Wales,Welsh
Countries2.txt :::
FRA,France,French
GER,Germany,German
USA,Unites States of America,SQL> CREATE OR REPLACE DIRECTORY ext_tab_data AS 'D:\SHOUMADIP_PERSONAL';
Directory created.
SQL> GRANT READ,WRITE ON DIRECTORY ext_tab_data TO course;
Grant succeeded.
--EXTERNAL TABLE From Flat Files.
SQL> CREATE TABLE ext_tab_oracle_loader (
2 country_code VARCHAR2(5),
3 country_name VARCHAR2(50),
4 country_language VARCHAR2(50)
5 )
6 ORGANIZATION EXTERNAL (
7 TYPE ORACLE_LOADER
8 DEFAULT DIRECTORY ext_tab_data
9 ACCESS PARAMETERS (
10 RECORDS DELIMITED BY NEWLINE
11 FIELDS TERMINATED BY ','
12 MISSING FIELD VALUES ARE NULL
13 (
14 country_code CHAR(5),
15 country_name CHAR(50),
16 country_language CHAR(50)
17 )
18 )
19 LOCATION ('Countries1.txt','Countries2.txt')
20 )
21 PARALLEL 5
22 REJECT LIMIT UNLIMITED;
Table created.
SQL> SELECT * FROM ext_tab_oracle_loader ORDER BY country_name;
COUNT COUNTRY_NAME COUNTRY_LANGUAGE
----- -------------------------------------------------- -----------------
ENG England English
FRA France French
GER Germany German
IRE Ireland English
SCO Scotland English
USA Unites States of America
WAL Wales Welsh
7 rows selected.
--View From EXTERNAL TABLE.
SQL> CREATE OR REPLACE VIEW v_ext_tab_oracle_loader AS
2 SELECT *
3 FROM ext_tab_oracle_loader
4 WHERE country_language = 'English'
5 ORDER BY country_name;
View created.
SQL> SELECT * FROM v_ext_tab_oracle_loader;
COUNT COUNTRY_NAME COUNTRY_LANGUAGE
----- -------------------------------------------------- ------------------
ENG England English
IRE Ireland English
SCO Scotland EnglishThe [DBA|ALL|USER]_EXTERNAL_TABLES views display information about external tables :-
SQL> SELECT COUNT(*) FROM user_external_tables;
COUNT(*)
----------
1
--EXTERNAL TABLE From Another EXTERNAL TABLE And Create Data Dump.
SQL> CREATE TABLE int_tab_oracle_datapump
2 ORGANIZATION EXTERNAL
3 ( TYPE ORACLE_DATAPUMP
4 DEFAULT DIRECTORY ext_tab_data
5 LOCATION ('int_tab_oracle_datapump.dmp')) AS
6 SELECT *
7 FROM ext_tab_oracle_loader;
Table created.
SQL> SELECT * FROM int_tab_oracle_datapump ORDER BY country_name;
COUNT COUNTRY_NAME COUNTRY_LANGUAGE
----- -------------------------------------------------- ------------------
ENG England English
FRA France French
GER Germany German
IRE Ireland English
SCO Scotland English
USA Unites States of America
WAL Wales Welsh
7 rows selected.
--EXTERNAL TABLE From Previously Created Data Dump File.
SQL> CREATE TABLE ext_tab_oracle_datapump (
2 country_code VARCHAR2(5),
3 country_name VARCHAR2(50),
4 country_language VARCHAR2(50)
5 )
6 ORGANIZATION EXTERNAL
7 ( TYPE ORACLE_DATAPUMP
8 DEFAULT DIRECTORY ext_tab_data
9 LOCATION ('int_tab_oracle_datapump.dmp'));
Table created.
SQL> SELECT * FROM ext_tab_oracle_datapump ORDER BY country_name;
COUNT COUNTRY_NAME COUNTRY_LANGUAGE
----- -------------------------------------------------- ------------------
ENG England English
FRA France French
GER Germany German
IRE Ireland English
SCO Scotland English
USA Unites States of America
WAL Wales Welsh
7 rows selected.
--EXTERNAL TABLE From Another TABLE And Create Data Dump File.
SQL> CREATE TABLE ext_tab_oracle_datapump_emp
2 ORGANIZATION EXTERNAL
3 ( TYPE ORACLE_DATAPUMP
4 DEFAULT DIRECTORY ext_tab_data
5 LOCATION ('ext_tab_oracle_datapump_emp.dmp')) AS
6 SELECT *
7 FROM emp;
Table created.
SQL> SELECT * FROM ext_tab_oracle_datapump_emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7839 KING PRESIDENT 17-NOV-81 20004 10
7698 BLAKE MANAGER 7839 01-MAY-81 5702 30
7782 CLARK MANAGER 7839 09-JUN-81 9804 10
7566 JONES MANAGER 7839 02-APR-81 5952 20
7788 SCOTT ANALYST 7566 19-APR-87 6002 20
7902 FORD ANALYST 7566 03-DEC-81 6002 20
7369 SMITH CLERK 7902 17-DEC-80 1602 20
7499 ALLEN SALESMAN 7698 20-FEB-81 3202 300 30
7521 WARD SALESMAN 7698 22-FEB-81 2502 500 30
7654 MARTIN SALESMAN 7698 28-SEP-81 2502 1400 30
7844 TURNER SALESMAN 7698 08-SEP-81 3002 0 30
7876 ADAMS CLERK 7788 23-MAY-87 2202 20
7900 JAMES CLERK 7698 03-DEC-81 1902 30
7934 MILLER CLERK 7782 23-JAN-82 5204 10
14 rows selected.
--EXTERNAL TABLE From Previously Created Data Dump File.
SQL> CREATE TABLE ext_tab_oracle_datapump_fnl (
2 empno NUMBER(4),
3 ename VARCHAR2(10),
4 job VARCHAR2(9),
5 mgr NUMBER(4),
6 hiredate DATE,
7 sal NUMBER(7,2),
8 comm NUMBER(7,2),
9 deptno NUMBER(2)
10 )
11 ORGANIZATION EXTERNAL
12 ( TYPE ORACLE_DATAPUMP
13 DEFAULT DIRECTORY ext_tab_data
14 LOCATION ('ext_tab_oracle_datapump_emp.dmp'));
Table created.
SQL> SELECT * FROM ext_tab_oracle_datapump_fnl;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7839 KING PRESIDENT 17-NOV-81 20004 10
7698 BLAKE MANAGER 7839 01-MAY-81 5702 30
7782 CLARK MANAGER 7839 09-JUN-81 9804 10
7566 JONES MANAGER 7839 02-APR-81 5952 20
7788 SCOTT ANALYST 7566 19-APR-87 6002 20
7902 FORD ANALYST 7566 03-DEC-81 6002 20
7369 SMITH CLERK 7902 17-DEC-80 1602 20
7499 ALLEN SALESMAN 7698 20-FEB-81 3202 300 30
7521 WARD SALESMAN 7698 22-FEB-81 2502 500 30
7654 MARTIN SALESMAN 7698 28-SEP-81 2502 1400 30
7844 TURNER SALESMAN 7698 08-SEP-81 3002 0 30
7876 ADAMS CLERK 7788 23-MAY-87 2202 20
7900 JAMES CLERK 7698 03-DEC-81 1902 30
7934 MILLER CLERK 7782 23-JAN-82 5204 10
14 rows selected.Oracle 11g Release 2 introduced the PREPROCESSOR clause to identify a directory object and script used to process the files before they are read by the external table. This feature was backported to 11gR1 (11.1.0.7). The PREPROCESSOR clause is especially useful for reading compressed files, since they are unzipped and piped straight into the external table process without ever having to be unzipped on the file system.
SQL> CREATE TABLE ext_tab_oracle_loader_zip (
2 country_code VARCHAR2(5),
3 country_name VARCHAR2(50),
4 country_language VARCHAR2(50)
5 )
6 ORGANIZATION EXTERNAL (
7 TYPE ORACLE_LOADER
8 DEFAULT DIRECTORY ext_tab_data
9 ACCESS PARAMETERS (
10 RECORDS DELIMITED BY NEWLINE
11 PREPROCESSOR ext_tab_data:'unzip.bat'
12 FIELDS TERMINATED BY ','
13 MISSING FIELD VALUES ARE NULL
14 (
15 country_code CHAR(5),
16 country_name CHAR(50),
17 country_language CHAR(50)
18 )
19 )
20 LOCATION ('Countries1.zip')
21 )
22 PARALLEL 5
23 REJECT LIMIT UNLIMITED;
Table created.
SQL> SELECT * FROM ext_tab_oracle_loader_zip;
COUNT COUNTRY_NAME COUNTRY_LANGUAGE
----- -------------------------------------------------- ----------
ENG England English
SCO Scotland English
IRE Ireland English
WAL Wales WelshTo DROP a EXTERNAL TABLE,issue the command :-
SQL> DROP TABLE <external_table_name>;
