Managing External Tables

Go back to OCM 12c Preparation Project 

Hands On Lab – Index

Manage external tables -> 20.13 Managing External Tables
20.13.1 About External Tables
20.13.2 Creating External Tables
20.13.3 Altering External Tables
20.13.4 Preprocessing External Tables
20.13.5 Dropping External Tables
20.13.6 System and Object Privileges for External Tables
20.14 Tables Data Dictionary Views

Database Warehousing -> Database Utilities
Part III External Tables
14 External Tables Concepts
Behavior Differences Between SQL*Loader and External Tables
15 The ORACLE_LOADER Access Driver
16 The ORACLE_DATAPUMP Access Driver

External tables do not reside in the database.

20.13.1 About External Tables
Oracle Database allows you read-only access to data in 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.

By providing the database with metadata describing an external table, 
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.

External tables provide a framework to unload the result of an arbitrary 
SELECT statement into a platform-independent Oracle-proprietary format 
that can be used by Oracle Data Pump.

External tables provide a valuable means for performing basic extraction, 
transformation, and loading (ETL) tasks that are common for data warehousing.

The means of defining the metadata for external tables is through the 
CREATE TABLE...ORGANIZATION EXTERNAL statement.

This external table definition can be thought of as a view that allows 
running any SQL query against external data without requiring that the 
external data first be loaded into the database.

An access driver is the actual mechanism used to read the external data 
in the table. When you use external tables to unload data, the metadata 
is automatically created based on the data types in the SELECT statement.

Oracle Database provides two access drivers for external tables. 
[1] 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.

[2] 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.

Note:
The ANALYZE statement is not supported for gathering statistics for external tables. 
Use the DBMS_STATS package instead.

[1] ORACLE_LOADER Access Driver

20.13.2 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.

Note:
External tables cannot have virtual columns.

EXAMPLE: Creating an External Table and Loading Data

The file empxt1.dat contains the following sample data:

360,Jane,Janus,ST_CLERK,121,17-MAY-2001,3000,0,50,jjanus
361,Mark,Jasper,SA_REP,145,17-MAY-2001,8000,.1,80,mjasper
362,Brenda,Starr,AD_ASST,200,17-MAY-2001,5500,0,10,bstarr
363,Alex,Alda,AC_MGR,145,17-MAY-2001,9000,.15,80,aalda
The file empxt2.dat contains the following sample data:

401,Jesse,Cromwell,HR_REP,203,17-MAY-2001,7000,0,40,jcromwel
402,Abby,Applegate,IT_PROG,103,17-MAY-2001,9000,.2,60,aapplega
403,Carol,Cousins,AD_VP,100,17-MAY-2001,27000,.3,90,ccousins
404,John,Richardson,AC_ACCOUNT,205,17-MAY-2001,5000,0,110,jrichard
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;


Note:
Specifying a PARALLEL clause is of value only when dealing with large 
amounts of data. Otherwise, it is not advisable to specify a PARALLEL clause, 
and doing so can be detrimental.

20.13.3 Altering External Tables

[1] REJECT LIMIT
Changes the reject limit. The default value is 0.

ALTER TABLE admin_ext_employees REJECT LIMIT 100;

[2] PROJECT COLUMN
Determines how the access driver validates rows in subsequent queries:

SQL> ALTER TABLE admin_ext_employees PROJECT COLUMN REFERENCED;

PROJECT COLUMN REFERENCED: the access driver processes only the columns 
in the select list of the query. This setting may not provide a consistent
set of rows when querying a different column list from the same external 
table.

SQL> ALTER TABLE admin_ext_employees PROJECT COLUMN ALL;

PROJECT COLUMN ALL: the access driver processes all of the columns defined 
on the external table. This setting always provides a consistent set of rows 
when querying an external table. This is the default.

[3] DEFAULT DIRECTORY
Changes the default directory specification

ALTER TABLE admin_ext_employees DEFAULT DIRECTORY admin_dat2_dir;

20.13.4 Preprocessing External Tables

External tables can be preprocessed by user-supplied preprocessor programs. 
By using a preprocessing program, users can use data from a file that is 
not in a format supported by the driver.

For example, a user may want to access data stored in a compressed format. 
Specifying a decompression program for the ORACLE_LOADER access driver allows 
the data to be decompressed as the access driver processes the data.

To use the preprocessing feature, you must specify the PREPROCESSOR clause 
in the access parameters of the ORACLE_LOADER access driver.

The preprocessor must be a directory object, and the user accessing the 
external table must have EXECUTE privileges for the directory object.

Note: Make sure that copy zcat file in dictionary exec_file_dir.
cp /bin/zcat

CREATE TABLE sales_transactions_ext
(PROD_ID NUMBER,
CUST_ID NUMBER,
TIME_ID DATE,
CHANNEL_ID CHAR,
PROMO_ID NUMBER,
QUANTITY_SOLD NUMBER,
AMOUNT_SOLD NUMBER(10,2),
UNIT_COST NUMBER(10,2),
UNIT_PRICE NUMBER(10,2))
ORGANIZATION external
(TYPE oracle_loader
DEFAULT DIRECTORY data_file_dir
ACCESS PARAMETERS
(RECORDS DELIMITED BY NEWLINE
CHARACTERSET AL32UTF8
PREPROCESSOR exec_file_dir:'zcat'
BADFILE log_file_dir:'sh_sales.bad_xt'
LOGFILE log_file_dir:'sh_sales.log_xt'
FIELDS TERMINATED BY "|" LDRTRIM
( PROD_ID,
CUST_ID,
TIME_ID,
CHANNEL_ID,
PROMO_ID,
QUANTITY_SOLD,
AMOUNT_SOLD,
UNIT_COST,
UNIT_PRICE))
location ('sh_sales.dat.gz')
)REJECT LIMIT UNLIMITED;

Note: The PREPROCESSOR clause is not available for databases that use 
Oracle Database Vault.

20.13.5 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.

20.13.6 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:
ALTER ANY TABLE
CREATE ANY TABLE
DROP ANY TABLE
READ ANY TABLE
SELECT ANY TABLE

Only the following object privileges are applicable to external tables:
ALTER
READ
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.

20.14 Tables Data Dictionary Views
USER_EXTERNAL_TABLES 
-> These views list the specific attributes of external tables in the schema.
USER_EXTERNAL_LOCATIONS 
-> These views list the data sources for external tables.

Database Warehousing -> Database Utilities
Part III External Tables
14 External Tables Concepts
Behavior Differences Between SQL*Loader and External Tables

TYPE - specifies the type of external table. The two available types are 
the ORACLE_LOADER type and the ORACLE_DATAPUMP type. 
Each type of external table is supported by its own access driver.
DEFAULT DIRECTORY - specifies the default directory to use for all input 
and output files that do not explicitly name a directory object.

ACCESS PARAMETERS - describe the external data source and implement the 
type of external table that was specified Each type of external table has 
its own access driver that provides access parameters unique to 
that type of external table. Access parameters are optional.

LOCATION - specifies the data files for the external table. The files are 
named in the form directory:file. The directory portion is optional. 
If it is missing, then the default directory is used as the directory 
for the file. If you are using the ORACLE_LOADER access driver, then you 
can use wildcards in the file name: an asterisk (*) signifies multiple
characters, a question mark (?) signifies a single character.

15 The ORACLE_LOADER Access Driver

The ORACLE_LOADER access driver which provides a set of access parameters 
unique to external tables of the type ORACLE_LOADER. You can use the access 
parameters to modify the default behavior of the access driver. 
The information you provide through the access driver ensures that data 
from the data source is processed so that it matches the definition of 
the external table.

You may find it helpful to use the EXTERNAL_TABLE=GENERATE_ONLY parameter 
in SQL*Loader to get the proper access parameters for a given SQL*Loader 
control file. When you specify GENERATE_ONLY, all the SQL statements needed 
to do the load using external tables, as described in the control file, 
are placed in the SQL*Loader log file. These SQL statements can be edited
and customized. The actual load can be done later without the use of 
SQL*Loader by executing these statements in SQL*Plus.

This section lists restrictions to be aware of then you use the 
ORACLE_LOADER access driver.

1. Exporting and importing of external tables with encrypted columns is not supported.
2. An external table cannot load data into a LONG column.
3. The use of the backslash character (\) within strings is not supported in external tables.
4. SQL strings cannot be specified in access parameters for the ORACLE_LOADER access driver. 
As a workaround, you can use the DECODE clause in the SELECT clause of the statement that 
is reading the external table. Alternatively, you can create a view of the external table 
that uses the DECODE clause and select from that view rather than the external table.
5. If a reserved word is used as an identifier, then it must be enclosed in double quotation marks.

--------
Hands On
--------
Sometimes we must specify the character set of the input data. 
Spanish has accented characters and symbols like 'Ñ' that can 
cause problems when creating external tables. In the following 
example we will create an external table that contains specific Spanish characters.

$ export NLS_LANG=SPANISH_SPAIN.WE8ISO8859P15
$ cd /u01/app/oracle/admin/mydb/dpdump/
$ vi test_cs.dat

Jose MaríRodríez Gó|(57-1) 600-1122|josemario@nomail.co|Carrera 7|28050|Bogotáogotáolombia
Guadalupe|Gonzales Sáhez|2124312|guadalupe@nomail.mx|Jesúrranza|50130|Toluca|Toluca|Méco
Raúáz Peral|660542983|raul@dbajunior.com|Calle Palestina|28100|Alcobendas|Madrid|Españ


CREATE TABLE hr.ocm_students
(FIRST_NAME VARCHAR2(22),
LAST_NAME VARCHAR2(22),
PHONE VARCHAR2(20),
EMAIL VARCHAR2(30),
STREET VARCHAR2(40),
CP NUMBER,
TOWN VARCHAR2(20),
CITY VARCHAR2(20),
COUNTRY VARCHAR2(20)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY data_pump_dir
ACCESS PARAMETERS
(
records delimited by newline characterset WE8ISO8859P15
badfile data_pump_dir:'test_cs.bad'
logfile data_pump_dir:'test_cs.log'
fields terminated by '|'
missing field values are null
( FIRST_NAME, LAST_NAME, PHONE, EMAIL,
STREET, CP, TOWN, CITY , COUNTRY
)
)
LOCATION ('test_cs.dat')
)
PARALLEL
REJECT LIMIT UNLIMITED;

SQL> SELECT * FROM HR.OCM_STUDENTS;

FIRST_NAME LAST_NAME PHONE
EMAIL STREET
------------------------------ ----------------------------------------
CP TOWN CITY COUNTRY
---------- -------------------- -------------------- --------------------
Guadalupe Gonzales Sáhez 2124312
guadalupe@nomail.mx Jesúrranza
50130 Toluca Toluca Méco

[2] ORACLE_DATAPUMP Access Driver 
16 The ORACLE_DATAPUMP Access Driver
The ORACLE_DATAPUMP access driver provides a set of access parameters 
unique to external tables of the type ORACLE_DATAPUMP. You can use the 
access parameters to modify the default behavior of the access driver. 
The information you provide through the access driver ensures that data 
from the data source is processed so that it matchesthe definition of 
the external table.

Hands On : Unloading and Loading Data with the ORACLE_DATAPUMP Access Driver

-> An external table will populate a file with data only as part of creating 
the external table with the AS SELECT clause. 
The following example creates an external table named inventories_xt 
and populates the dump file for the external table 
with the data from table inventories in the oe schema.

CREATE TABLE inventories_xt
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY def_dir1
LOCATION ('inv_xt.dmp')
)
AS SELECT * FROM inventories;

DESCRIBE inventories
DESCRIBE inventories_xt
SELECT COUNT(*) FROM inventories_xt;
SELECT * FROM inventories MINUS SELECT * FROM inventories_xt;


-> The dump file created for the external table can now be moved and 
used as the dump file for another external table in the same database 
or different database. Note that when you create an external table that 
uses an existing file, there is no AS SELECT clause for the CREATE TABLE 
statement.

CREATE TABLE inventories_xt2
(
product_id NUMBER(6),
warehouse_id NUMBER(3),
quantity_on_hand NUMBER(8)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY def_dir1
LOCATION ('inv_xt.dmp')
);

SELECT * FROM inventories MINUS SELECT * FROM inventories_xt2;

#Parallel Loading and Unloading

-> Create an external table with three dump files and with a degree of 
parallelism of three.

CREATE TABLE inventories_xt3
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY def_dir1
LOCATION ('inv_xt1.dmp', 'inv_xt2.dmp', 'inv_xt3.dmp')
)
PARALLEL 3
AS SELECT * FROM inventories;

SELECT * FROM inventories MINUS SELECT * FROM inventories_xt3;

#Combining Dump Files

-> Create an external table containing some rows from table inventories.
CREATE TABLE inv_part_xt
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY def_dir1
LOCATION ('inv_p1_xt.dmp')
)
AS SELECT * FROM inventories WHERE warehouse_id < 5;

-> Create another external table containing the rest of the rows from inventories.
drop table inv_part_xt;

CREATE TABLE inv_part_xt
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY def_dir1
LOCATION ('inv_p2_xt.dmp')
)
AS SELECT * FROM inventories WHERE warehouse_id >= 5;

-> Create an external table that uses the two dump files created in last two Steps.
CREATE TABLE inv_part_all_xt
(
product_id NUMBER(6),
warehouse_id NUMBER(3),
quantity_on_hand NUMBER(8)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY def_dir1
LOCATION ('inv_p1_xt.dmp','inv_p2_xt.dmp')
);

SELECT * FROM inventories MINUS SELECT * FROM inv_part_all_xt;

Hands On: 
Unloading and Loading BFILE Data Types
Unloading LONG and LONG RAW Data Types
Unloading and Loading Columns Containing Final Object Types
Tables of Final Object Types

-----------------------
Patch to Documentation
-----------------------
Database Administration Guide --> Manage external tables -> 20.13 Managing External Tables
Data Warehousing and Business Intelligence --> Utilities --> Part III External Tables
14 External Tables Concepts
15 The ORACLE_LOADER Access Driver
16 The ORACLE_DATAPUMP Access Driver

Thank you for visiting this blog…