- External tables do not reside in the database, and can be in any format for which an access driver is provided.
- You can select, join, or sort external table data.
- You can also create views and synonyms for external tables.
- You CANNOT execute DML operations (UPDATE, INSERT, or DELETE) , and CANNOT create indexes on external tables.
External tables and Data Warehouse
- Exporting data from a database:
- 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.
Creating External tables
CREATE TABLE...ORGANIZATION EXTERNAL statement.- This statement creates only metadata in the data dictionary.
- External tables can be thought of as views that allows running any SQL query against external data without requiring that the external data first be loaded into the database.
Accessing External Data
- 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:
- ORACLE_LOADER driver (default)
- allows the reading of data from external files using the Oracle loader technology.
- provides data mapping capabilities which are a subset of the control file syntax of SQL*Loader utility.
- ORACLE_DATAPUMP driver
- lets you unload data: 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.
Scenario 1:
Unload data to flat files and create External Table.
ORACLE_LOADER driver
Step 1 - Export data from table sh.customers
/* Step 1 - Export data from table sh.customers
Procedure UNLOAD_DATA receives table_name and table_owner as parameter and
unloads the table data to a flat file */
set serveroutput on
DECLARE
procedure unload_data(
p_table_name in varchar2,
p_owner in varchar2)
IS
sqltext varchar2(1000);
v_textrecord varchar2(2000);
cv sys_refcursor;
fdest utl_file.file_type;
type FileAttrRec is Record (
vfilexists BOOLEAN,
vfilelength number,
vblocksize binary_integer);
vfilerec fileattrrec;
vfilename varchar2(30);
procedure get_sql (
p_sqltext in out varchar2,
p_table_name in varchar2,
p_owner in varchar2)
IS
cursor c is
select column_name
from dba_tab_columns
where table_name = p_table_name and owner = p_owner
order by column_id;
i integer;
sql_stmt varchar2(2000);
from_clause varchar2(100);
begin
i :=0;
sql_stmt := 'SELECT ';
from_clause := 'FROM ' || p_owner ||'.' || p_table_name;
for r in c loop
if i = 0 then
sql_stmt := sql_stmt || ' ' || r.column_name;
i := 1;
else
sql_stmt := sql_stmt || '|| ''|''|| ' || r.column_name;
end if;
end loop;
sql_stmt := sql_stmt || ' as qrystr ' || chr(10) || from_clause;
p_sqltext := sql_stmt;
-- dbms_output.put_line( sql_stmt);
end get_sql;
begin
-- 1. Get Query SQL
dbms_output.enable(20000);
sqltext := '';
get_sql(sqltext, p_table_name, p_owner);
--dbms_output.put_line('Select stmt:' || sqltext);
-- 2. Open cursor for query. Write each row in the OS file
vfilename := 'unload_' || p_owner ||'_'|| p_table_name || '.dat';
utl_file.fgetattr('DATA_DUMP_DIR', vfilename, vfilerec.vfilexists,
vfilerec.vfilelength, vfilerec.vblocksize);
if vfilerec.vfilexists then
fdest := utl_file.fopen('DATA_PUMP_DIR', vfilename, 'a', 2048);
dbms_output.put_line('Destination file exists. Appending..');
else
fdest := utl_file.fopen('DATA_PUMP_DIR', vfilename, 'w', 2048);
end if;
open cv for sqltext;
LOOP
fetch cv into v_textrecord;
EXIT WHEN cv%NOTFOUND;
utl_file.put_line(fdest, v_textrecord, true);
END LOOP;
utl_file.fclose(fdest);
close cv;
END unload_data;
BEGIN
unload_data('CUSTOMERS' , 'SH');
END;
Step 2 - Split unloaded file and create external table using the resulting files
$ ls -ltr
total 482928
...
-rw-r--r-- 1 oracle oinstall 10661903 2012-03-20 22:01 unload_SH_CUSTOMERS.dat
-- spliting the file in two smaller files
$ split -30000 unload_SH_CUSTOMERS.dat unload_SH_CUSTOMERS.dat
$ ls -ltr
total 503760
...
-rw-r--r-- 1 oracle oinstall 10661903 2012-03-20 22:01 unload_SH_CUSTOMERS.dat
-rw-r--r-- 1 oracle oinstall 5768182 2012-03-20 22:15 unload_SH_CUSTOMERS.dataa
-rw-r--r-- 1 oracle oinstall 4893721 2012-03-20 22:15 unload_SH_CUSTOMERS.datab
Step 2 - Create external table using unloaded file(s)
-- create the external table
SQL> Create table ext_customers1
(
"CUST_ID" NUMBER,
"CUST_FIRST_NAME" VARCHAR2(20 BYTE),
"CUST_LAST_NAME" VARCHAR2(40 BYTE),
"CUST_GENDER" CHAR(1 BYTE),
"CUST_YEAR_OF_BIRTH" NUMBER(4,0),
"CUST_MARITAL_STATUS" VARCHAR2(20 BYTE),
"CUST_STREET_ADDRESS" VARCHAR2(40 BYTE),
"CUST_POSTAL_CODE" VARCHAR2(10 BYTE),
"CUST_CITY" VARCHAR2(30 BYTE),
"CUST_CITY_ID" NUMBER,
"CUST_STATE_PROVINCE" VARCHAR2(40 BYTE),
"CUST_STATE_PROVINCE_ID" NUMBER,
"COUNTRY_ID" NUMBER,
"CUST_MAIN_PHONE_NUMBER" VARCHAR2(25 BYTE),
"CUST_INCOME_LEVEL" VARCHAR2(30 BYTE),
"CUST_CREDIT_LIMIT" NUMBER,
"CUST_EMAIL" VARCHAR2(30 BYTE),
"CUST_TOTAL" VARCHAR2(14 BYTE),
"CUST_TOTAL_ID" NUMBER,
"CUST_SRC_ID" NUMBER,
"CUST_EFF_FROM" DATE,
"CUST_EFF_TO" DATE,
"CUST_VALID" VARCHAR2(1 BYTE)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY data_pump_dir
ACCESS PARAMETERS
(
records delimited by newline
badfile data_pump_dir:'cust1ext%a_%p.bad'
logfile data_pump_dir:'cust1ext%a_%p.log'
fields terminated by '|'
missing field values are null
)
LOCATION ('unload_SH_CUSTOMERS.dataa', 'unload_SH_CUSTOMERS.datab')
)
PARALLEL
REJECT LIMIT UNLIMITED;
table EXT_CUSTOMERS1 created.
SQL> select table_name, type_name, default_directory_name, reject_limit, access_parameters, property
from dba_external_tables;
TABLE_NAME TYPE_NAME DEFAULT_DIRECTORY_NAME REJECT_LIMIT ACCESS_PARAMETERS
--------------- --------------- ------------------------ --------------- ------------------------------------------
EXT_CUSTOMERS1 ORACLE_LOADER DATA_PUMP_DIR UNLIMITED records delimited by newline
badfile data_pump_dir:'cust1ext%a_%p.bad'
logfile data_pump_dir:'cust1ext%a_%p.log'
fields terminated by '|'
missing filed values are null
SQL> select count(*) from ext_customers1;
COUNT(*)
----------
55500
Scenario 2: Unloading and Loading Data with the ORACLE_DATAPUMP Access Driver
Step 1 - (a) Use CREATE TABLE ... ORGANIZATION EXTERNAL ... AS SELECT ...
- This will create a file with the data resulting from the specified query.
- The file is created with a binary format that can only be read by the ORACLE_DATAPUMP access driver
- The example below creates an external table and populates the dump file for the external table with the data from table sh.customers.
connect sh/**;
SQL> create table customers1_xt
organization external
(
type oracle_datapump
default directory data_pump_dir
location ('customers1_xt.dmp')
)
as select * from sh.customers;
table created.
SQL>
(b) Check the OS files created:
$ ls -ltr
total 60672
-rw-r----- 1 oracle oinstall 10334208 2012-03-21 00:33 customers1_xt.dmp
-rw-r--r-- 1 oracle oinstall 123 2012-03-21 00:36 CUSTOMERS1_XT_7165.log
(c) Compare descriptions of customers and customers1_xt tables.
SQL> desc customers;
Name Null Type
---------------------- -------- ------------
CUST_ID NOT NULL NUMBER
CUST_FIRST_NAME NOT NULL VARCHAR2(20)
CUST_LAST_NAME NOT NULL VARCHAR2(40)
CUST_GENDER NOT NULL CHAR(1)
CUST_YEAR_OF_BIRTH NOT NULL NUMBER(4)
CUST_MARITAL_STATUS VARCHAR2(20)
CUST_STREET_ADDRESS NOT NULL VARCHAR2(40)
CUST_POSTAL_CODE NOT NULL VARCHAR2(10)
CUST_CITY NOT NULL VARCHAR2(30)
CUST_CITY_ID NOT NULL NUMBER
CUST_STATE_PROVINCE NOT NULL VARCHAR2(40)
CUST_STATE_PROVINCE_ID NOT NULL NUMBER
COUNTRY_ID NOT NULL NUMBER
CUST_MAIN_PHONE_NUMBER NOT NULL VARCHAR2(25)
CUST_INCOME_LEVEL VARCHAR2(30)
CUST_CREDIT_LIMIT NUMBER
CUST_EMAIL VARCHAR2(30)
CUST_TOTAL NOT NULL VARCHAR2(14)
CUST_TOTAL_ID NOT NULL NUMBER
CUST_SRC_ID NUMBER
CUST_EFF_FROM DATE
CUST_EFF_TO DATE
CUST_VALID VARCHAR2(1)
SQL> desc customers1_xt;
Name Null Type
---------------------- -------- ------------
CUST_ID NOT NULL NUMBER
CUST_FIRST_NAME NOT NULL VARCHAR2(20)
CUST_LAST_NAME NOT NULL VARCHAR2(40)
CUST_GENDER NOT NULL CHAR(1)
CUST_YEAR_OF_BIRTH NOT NULL NUMBER(4)
CUST_MARITAL_STATUS VARCHAR2(20)
CUST_STREET_ADDRESS NOT NULL VARCHAR2(40)
CUST_POSTAL_CODE NOT NULL VARCHAR2(10)
CUST_CITY NOT NULL VARCHAR2(30)
CUST_CITY_ID NOT NULL NUMBER
CUST_STATE_PROVINCE NOT NULL VARCHAR2(40)
CUST_STATE_PROVINCE_ID NOT NULL NUMBER
COUNTRY_ID NOT NULL NUMBER
CUST_MAIN_PHONE_NUMBER NOT NULL VARCHAR2(25)
CUST_INCOME_LEVEL VARCHAR2(30)
CUST_CREDIT_LIMIT NUMBER
CUST_EMAIL VARCHAR2(30)
CUST_TOTAL NOT NULL VARCHAR2(14)
CUST_TOTAL_ID NOT NULL NUMBER
CUST_SRC_ID NUMBER
CUST_EFF_FROM DATE
CUST_EFF_TO DATE
CUST_VALID VARCHAR2(1)
(d) Check data on customers1_xt and compare with original table
SQL> select count(*) from customers1_xt;
COUNT(*)
----------------------
55500
SQL> select * from customers minus select * from customers1_xt;
no rows selected
(e) You CANNOT perform DML on the external table:
SQL> delete from customers1_xt where cust_id = 100055;
delete from customers1_xt where cust_id = 100055
*
ERROR at line 1:
ORA-30657: operation not supported on external organized table
(f) You can also Create an external table with multiple dump files and with a degree of parallelism higher than one.
SQL> create table customers1_xtp3
organization external
(
type oracle_datapump
default directory data_pump_dir
location('customers1_xtp31.dmp' ,'customers1_xtp32.dmp' ,'customers1_xtp33.dmp' )
)
parallel 3
as select * from sh.customers;
$ ls -ltr
total 60672
-rw-r----- 1 oracle oinstall 10334208 2012-03-21 00:33 customers1_xt.dmp
-rw-r--r-- 1 oracle oinstall 123 2012-03-21 00:36 CUSTOMERS1_XT_7165.log
-rw-r--r-- 1 oracle oinstall 41 2012-03-21 10:13 CUSTOMERS1_XTP3_2690.log
-rw-r--r-- 1 oracle oinstall 41 2012-03-21 10:13 CUSTOMERS1_XTP3_2737.log
-rw-r--r-- 1 oracle oinstall 41 2012-03-21 10:13 CUSTOMERS1_XTP3_2739.log
-rw-r--r-- 1 oracle oinstall 41 2012-03-21 10:13 CUSTOMERS1_XTP3_2741.log
-rw-r----- 1 oracle oinstall 2990080 2012-03-21 10:13 customers1_xtp32.dmp
-rw-r----- 1 oracle oinstall 3792896 2012-03-21 10:13 customers1_xtp33.dmp
-rw-r----- 1 oracle oinstall 3592192 2012-03-21 10:13 customers1_xtp31.dmp
(g) You can transfer or copy the dump file(s) and use them for another external table either in the same or in a different database.
SQL> Create table customers2_xt
(
"CUST_ID" NUMBER,
"CUST_FIRST_NAME" VARCHAR2(20 BYTE),
"CUST_LAST_NAME" VARCHAR2(40 BYTE),
"CUST_GENDER" CHAR(1 BYTE),
"CUST_YEAR_OF_BIRTH" NUMBER(4,0),
"CUST_MARITAL_STATUS" VARCHAR2(20 BYTE),
"CUST_STREET_ADDRESS" VARCHAR2(40 BYTE),
"CUST_POSTAL_CODE" VARCHAR2(10 BYTE),
"CUST_CITY" VARCHAR2(30 BYTE),
"CUST_CITY_ID" NUMBER,
"CUST_STATE_PROVINCE" VARCHAR2(40 BYTE),
"CUST_STATE_PROVINCE_ID" NUMBER,
"COUNTRY_ID" NUMBER,
"CUST_MAIN_PHONE_NUMBER" VARCHAR2(25 BYTE),
"CUST_INCOME_LEVEL" VARCHAR2(30 BYTE),
"CUST_CREDIT_LIMIT" NUMBER,
"CUST_EMAIL" VARCHAR2(30 BYTE),
"CUST_TOTAL" VARCHAR2(14 BYTE),
"CUST_TOTAL_ID" NUMBER,
"CUST_SRC_ID" NUMBER,
"CUST_EFF_FROM" DATE,
"CUST_EFF_TO" DATE,
"CUST_VALID" VARCHAR2(1 BYTE)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY data_pump_dir
LOCATION ('customers1_xtp31.dmp' ,'customers1_xtp32.dmp' ,'customers1_xtp33.dmp')
);
SQL> select count(*) from customers2_xt;
COUNT(*)
----------------------
55500
SQL> Select * from sh.customers minus select * from customers2_xt;
no rows selected.
