External table is one of the important feature in oracle , we will exploring external table in oracle with example in this post
What are EXTERNAL TABLES in Oracle?
1)External tables are read only tables where the data is stored in flat files outside the database
2) You can use external table feature to access external files as if they are tables inside the database.
3) When you create an external table, you define its structure and location with in oracle. Basically you just store the metadata inside the oracle
4) 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.
5) The oracle server provides two major oracle driver to read the flat files
- Oracle_loader: This is used for the reading the flat files using the oracle loader technology. It basically allows to read the files which can be interpreted using SQL loader technology
- Oracle_datapump: This is used for both importing and exporting of data using platform independent format
ACCESSING EXTERNAL TABLE DATA
There are few steps need to be performed in order to access the external table
- We must first use the create directory command to define a directory object pointing to the external file location
sqlplus "/ as sysdba" Create directory ext_dir as ‘/export/home/ora/external’;
- Users who will access the external files must have the read and write privilege on the directory.
Grant all on directory ext_dir to user;
- Let us now create or put some files under the directory
SQL> Conn user/user SQL> Spool city.lst SQL> Select city || ‘,’ || state || ‘,’ || country from countries; SQL> Spool off
Now you should be able to see the city.lst file in the directory
- Now we should create the external table using the below command
Create table city_ext (city varchar(10), state varchar(14), country varchar(13)) Organization external ( type oracle_loader Default directory ext_dir Access parameters ( records delimited by newline Fields terminated by “,” ( city char(10), state char(14), country char(13))) Location (‘city.lst’)) PARALLEL 5 REJECT LIMIT UNLIMITED;
Explanation of each term
|Organization external||It tells that data is stored outside the database|
|Type||It is the oracle driver or API which interpret the external data for the database.If you don’t specify anything then oracle_loader is the default|
|Default directory||It is the directory location where the flat files can be stored. Default directory is also used for storing the auxiliary files created from the access of oracle driver such as log file and bad files. We Can specify multiple directories to facilitate load balancing on multiple drives|
|Access parameters||It let u specify values of the parameter of the specific access driver for this external table|
|Location||It let you specify the external flat file name|
|REJECT LIMIT||It let you specify how many conversion errors can occur before oracle server abort the operation and return the error|
5) SELECTING DATA FROM EXTERNAL TABLE
SQL> select * from city_ext;
This will read from city.lst which is an operating system level file.
If the file city.lst is not present, the select statement would throw this error
SELECT * * ERROR at line 1: ORA-29913: error in executing ODCIEXTTABLEOPEN callout ORA-29400: data cartridge error KUP-04040: file city.lst in EXT_DIR not found ORA-06512: at "SYS.ORACLE_LOADER", line 14 ORA-06512: at line 1
LIMITATIONS ON EXTERNAL TABLES
- You cannot perform insert, update, and delete operations
- Oracle Indexing not possible
- Constraints not possible
BENEFITS OF EXTERNAL TABLES IN ORACLE
- a) Queries of external tables complete very quickly even though a full table scan id required with each access
- b) You can join external tables to each other or to standard oracle tables
- c) We can create views on the external table
New Update in 11gR2 for External Table
With Oracle 11g Release 2, new PREPROCESSOR clause is introduced which can 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 (22.214.171.124).
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.
Let us take an example to better understand this parameter.
Suppose the city.list is compressed, then file name would be city.list.Z
Create table city_ext (city varchar(10), state varchar(14), country varchar(13)) Organization external ( type oracle_loader Default directory ext_dir PREPROCESSOR ext_dir:uncompress Access parameters ( records delimited by newline Fields terminated by “,” ( city char(10), state char(14), country char(13))) Location (‘city.lst.Z’)) PARALLEL 5 REJECT LIMIT UNLIMITED;
Here the PREPROCESSOR clause will first uncompress the file city.lst.Z using command uncompress before looking for the data. All would happen on the fly and you wont need to uncompress the file city.lst.Z
Unloading/Loading data using Oracle External tables
Oracle also provided support for datapump technology in external table.
We can unload the table using oracle_datadump access driver
CREATE TABLE countries_xt ORGANIZATION EXTERNAL ( TYPE ORACLE_DATAPUMP DEFAULT DIRECTORY ext_dir LOCATION ('countries.dmp') ) AS SELECT * FROM countries;
This will create countries.dmp in the directory. Now even the table countries is dropped, we can still watch the data using countries_xt table.
We can even take dump file to another database and then create external table to watch the data
CREATE TABLE countries_xt ( city VARCHAR2(10), state VARCHAR2(9), countries VARCHAR2(9)) ORGANIZATION EXTERNAL ( TYPE ORACLE_DATAPUMP DEFAULT DIRECTORY ext_dir LOCATION ('countries.dmp ') ); Select * from countries_xt;
Enhancements in External tables in 12c database
Location clause can have wild character. * stands for multiple character and ? for single character.
Fields csv clause has been introduced
We can unload the data using advanced compression option