- External Tables
- Data Pump
Entry level bulk-load utility that is taking data from feeder file and moving into Oracle table.
- Conventional path: standard INSERTs and COMMIT to commit the entire data
- Direct path: injection from source data to table (much faster), not working if there are constraints on the target table, there is no UNDO functionality
There are log files produced when SQL*Loader is used: bad file is created wen there is a constraint violation and reject file is made for all the rows that were not affected by the operation.
sql*plus — create table for the example
create table regions (
infile ‘reg.txt’ — file from which we take data
append — add o existing rows
into table regions
fields terminated by ‘,’
trailing nullcols — any missing fields in the file will be replaced with the NULL value
(id integer external(2),
regname) — describing the columns
in CMD we navigate to the directory where the feeder file is and we invoke sql*loader with appropriate parameters. User should have SYSDBA or SYSOPER privileges
O:\feeder>sqllder user=userName/userPassword@serverID control=reg.ctl direct=true
User also needs to have insert permissions on the table where the data in inserted.
Those ar File System directories (folders) referenced in Oracle.
They are created by setting UTL_FILE_DIR parameter.
They are necessary to use UTL_FILE package, external tables and Data Pump.
It presents an alternative to using sql*loader in building tables from external data.
It is a directory object. One can query the external tables but can not create indexes on them which is not good when the table has lots of data. One can not do DML statements to external data, but can add data using Data Pump.
It is mostly used to make hgh performance DB to DB movement.
It is started with users processes: expdp and impdp
An example of DataPump import:
SQL> CREATE USER testuser IDENTIFIED BY ***PASSWORD***
DEFAULT TABLESPACE TEST
QUOTA UNLIMITED ON TEST;
SQL> CREATE OR REPLACE DIRECTORY test_dir AS ‘/newdrive/dump’;
SQL> GRANT READ, WRITE ON DIRECTORY test_dir TO testuser;
SQL> GRANT CONNECT TO testuser;
SQL> GRANT RESOURCE TO testuser;
OS> impdp testuser/***PASSWORD***@orcl DIRECTORY=test_dir DUMPFILE=’staging_db_expdp_dbplan_20130628.dmp’ SCHEMAS=testuser
logfile=’impdpStaging.log’ remap_tablespace=DATA_BG:TEST remap_tablespace=SRCTSMALL:TEST
Example of external table
checking existing utl_file
SQL> show parameter utl_file
SQL> alter system set utl_file_dir=’O:\feeder’ scope=both;
checking existing directories
SQL> select * from dba_directories;
SQL> create directory feeder as ‘O:\feeder’;
create table depts (
organization external (
default directory feeder
(records delimited by newline
fields terminated by ‘,’
missing field values are null)
Example of Data Pump
create table dptable as select * from all users;
oem -> Data Movement -> Export to Export files -> Tables…
The Data Pump API is exposed through the PL/SQL package DBMS_DATAPUMP