Introduction:
In this article, we can see what External Tables are and how to manage those tables within the Oracle Database.
About External Tables:
External tables are defined as tables that do not reside in the database, and can be in any format for which an access driver is provided. Only the Metadata is stored inside the database. The external data can be queried directly and in parallel using SQL.
You can, for example, select, join, or sort external table data. You can also create views and synonyms for external tables. However, no DML operations (UPDATE, INSERT, or DELETE) are possible, and no indexes can be created, on external tables.
Oracle Database provides two access drivers for external tables. The default access driver is ORACLE_LOADER, which allows the reading of data from external files using the Oracle loader technology. The ORACLE_LOADER access driver provides data mapping capabilities which are a subset of the control file syntax of SQL*Loader utility. The second access driver, ORACLE_DATAPUMP, lets you unload data—that is, read data from the database and insert it into an external table, represented by one or more external files—and then reload it into an Oracle Database.
External Table Restrictions:
The following are restrictions on external tables:
- The ANALYZE statement is not supported for gathering statistics for external tables. Use the DBMS_STATS package instead.
- Virtual columns are not supported
Creating External Tables:
You create external tables using the CREATE TABLE statement with an ORGANIZATION EXTERNAL clause. This statement creates only metadata in the data dictionary.
The following example creates an external table and then uploads the data to a database table. Alternatively, you can unload data through the external table framework by specifying the AS subquery clause of the CREATE TABLE statement. External table data pump unload can use only the ORACLE_DATAPUMP access driver.
EXAMPLE: Creating an External Table and Loading Data
In this example, the data for the external table resides in the two text files empxt1.dat and empxt2.dat.
The file empxt1.dat contains the following sample data:
360,xxx,yyy,ST_CLERK,121,17-MAY-2001,3000,0,50,xxyy
361,aaa,bbb,SA_REP,145,17-MAY-2001,8000,.1,80,aabb
362,ccc,ddd,AD_ASST,200,17-MAY-2001,5500,0,10,ccdd
363,eee,fff,AC_MGR,145,17-MAY-2001,9000,.15,80,eeff
The file empxt2.dat contains the following sample data:
401,kkk,jjj,HR_REP,203,17-MAY-2001,7000,0,40,ghghg
402,ggg,hhh,IT_PROG,103,17-MAY-2001,9000,.2,60,fjfjf
The following SQL statements create an external table named admin_ext_employees in the hr schema and load data from the external table into the hr.employees table.
CONNECT / AS SYSDBA;
— Set up directories and grant access to hr
CREATE OR REPLACE DIRECTORY admin_dat_dir
AS ‘/flatfiles/data’;
CREATE OR REPLACE DIRECTORY admin_log_dir
AS ‘/flatfiles/log’;
CREATE OR REPLACE DIRECTORY admin_bad_dir
AS ‘/flatfiles/bad’;
GRANT READ ON DIRECTORY admin_dat_dir TO hr;
GRANT WRITE ON DIRECTORY admin_log_dir TO hr;
GRANT WRITE ON DIRECTORY admin_bad_dir TO hr;
— hr connects. Provide the user password (hr) when prompted.
CONNECT hr
— create the external table
CREATE TABLE admin_ext_employees
(employee_id NUMBER(4),
first_name VARCHAR2(20),
last_name VARCHAR2(25),
job_id VARCHAR2(10),
manager_id NUMBER(4),
hire_date DATE,
salary NUMBER(8,2),
commission_pct NUMBER(2,2),
department_id NUMBER(4),
email VARCHAR2(25)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY admin_dat_dir
ACCESS PARAMETERS
(
records delimited by newline
badfile admin_bad_dir:’empxt%a_%p.bad’
logfile admin_log_dir:’empxt%a_%p.log’
fields terminated by ‘,’
missing field values are null
( employee_id, first_name, last_name, job_id, manager_id,
hire_date char date_format date mask “dd-mon-yyyy”,
salary, commission_pct, department_id, email
)
)
LOCATION (’empxt1.dat’, ’empxt2.dat’)
)
PARALLEL
REJECT LIMIT UNLIMITED;
— enable parallel for loading (good if lots of data to load)
ALTER SESSION ENABLE PARALLEL DML;
— load the data in hr employees table
INSERT INTO employees (employee_id, first_name, last_name, job_id, manager_id,
hire_date, salary, commission_pct, department_id, email)
SELECT * FROM admin_ext_employees;
Dropping External Tables:
For an external table, the DROP TABLE statement removes only the table metadata in the database. It has no affect on the actual data, which resides outside of the database.
System and Object Privileges for External Tables:
System and object privileges for external tables are a subset of those for regular table. Only the following system privileges are applicable to external tables:
- CREATE ANY TABLE
- ALTER ANY TABLE
- DROP ANY TABLE
- SELECT ANY TABLE
Only the following object privileges are applicable to external tables:
- ALTER
- SELECT
However, object privileges associated with a directory are:
- READ
- WRITE
For external tables, READ privileges are required on directory objects that contain data sources, while WRITE privileges are required for directory objects containing bad, log, or discard files.
Conclusion:
In this article, we have seen what External Tables are and how to manage those tables within the Oracle Database.
