■
Note
When Data Pump runs in legacy mode, it doesn’t create an old exp/imp formatted file. Data Pump always creates a Data Pump file and can only read Data Pump files.
Data Pump Mapping to the exp Utility
If you’re used to the old exp/imp parameters, you may initially be confused by some of the syntax semantics. However, after you use Data Pump, you’ll find the newer syntax fairly easy to remember and use. Table 13–3 describes how the legacy export parameters map to Data Pump export.
In many instances, there isn’t a one-to-one mapping. Often, Data Pump automatically provides features that used to require a parameter in the legacy utilities. For example, whereas you used to have to specify DIRECT=Y to get a direct path export, Data Pump automatically uses direct path whenever possible.
341
CHAPTER 13 ■ DATA PUMP
Table 13–3.
Mapping of Old Export Parameters to Data Pump
Original exp Parameter
Similar Data Pump expdp Parameter
BUFFER
N/A
COMPRESS TRANSFORM
CONSISTENT
FLASHBACK_SCN or FLASHBACK_TIME
CONSTRAINTS EXCLUDE=CONSTRAINTS
DIRECT
N/A; Data Pump automatically uses direct path when possible
FEEDBACK
STATUS in client output
FILE
Database-directory object and DUMPFILE
GRANTS EXCLUDE=GRANT
INDEXES
INCLUDE=INDEXES, INCLUDE=INDEXES
LOG
Database-directory object and LOGFILE
OBJECT_CONSISTENT
N/A
OWNER SCHEMAS
RECORDLENGTH
N/A
RESUMABLE
N/A; Data Pump automatically provides functionality
RESUMABLE_NAME
N/A
RESUMABLE_TIMEOUT
N/A
ROWS CONTENT=ALL
STATISTICS
N/A; Data Pump export always exports statistics for tables
TABLESPACES TRANSPORT_TABLESPACES
TRANSPORT_TABLESPACE TRANSPORT_TABLESPACES
TRIGGERS EXCLUDE=TRIGGER
TTS_FULL_CHECK TRANSPORT_FULL_CHECK
VOLSIZE
N/A; Data Pump doesn’t support tape devices
342
CHAPTER 13 ■ DATA PUMP
Data Pump Mapping to the imp Utility
As with Data Pump export, Data Pump import often doesn’t have a one-to-one mapping of the legacy utility parameter. Data Pump import automatically provides many features of the old imp utility. For example, COMMIT=Y isn’t required because Data Pump import automatically commits after each table is imported. Table 13–4 describes how legacy import parameters map to Data Pump import.
Table 13–4.
Mapping of Old Import Parameters to Data Pump
Original imp Parameter
Similar Data Pump impdp Parameter
BUFFER
N/A
CHARSET
N/A
COMMIT
N/A; Data Pump import automatically commits after each table is
exported
COMPILE
N/A; Data Pump import compiles procedures after they’re created
CONSTRAINTS EXCLUDE=CONSTRAINT
DATAFILES TRANSPORT_DATAFILES
DESTROY REUSE_DATAFILES=y
FEEDBACK
STATUS in client output
FILE
Database-directory object and DUMPFILE
FILESIZE
N/A
FROMUSER REMAP_SCHEMA
GRANTS EXCLUDE=OBJECT_GRANT
IGNORE
TABLE_EXISTS_ACTION with APPEND, REPLACE, SKIP, or TRUNCATE
INDEXES EXCLUDE=INDEXES
INDEXFILE SQLFILE
LOG
Database-directory object and LOGFILE
RECORDLENGTH
N/A
RESUMABLE
N/A; this functionality is automatically provided
RESUMABLE_NAME
N/A
RESUMABLE_TIMEOUT
N/A
ROWS=N
CONTENT, with METADATA_ONLY or ALL
SHOW SQLFILE
343
CHAPTER 13 ■ DATA PUMP
Original imp Parameter
Similar Data Pump impdp Parameter
STATISTICS
N/A
STREAMS_CONFIGURATION
N/A
STREAMS_INSTANTIATION
N/A
TABLESPACES TRANSPORT_TABLESPACES
TOID_NOVALIDATE
N/A
TOUSER REMAP_SCHEMA
TRANSPORT_TABLESPACE TRANSPORT_TABLESPACES
TTS_OWNERS
N/A
VOLSIZE
N/A; Data Pump doesn’t support tape devices
Summary
Data Pump is an extremely powerful and feature-rich tool. If you haven’t used Data Pump much, then I implore you to take some time to read this chapter and work through the examples. This tool greatly simplifies tasks like moving users and data from one environment to another. You can export and import subsets of users, filter and remap data via SQL and PL/SQL, rename users and tablespaces, compress, encrypt, and parallelize, all with one command. It really is that powerful.
DBAs sometimes stick with the old exp/imp utilities because that’s what they’re familiar with (I’m occasionally guilty of this). If you’re running Oracle Database 11
g
release 2, you can directly use the old exp/imp parameters and options directly from the command line. Data Pump translates these parameters on the fly to Data Pump–specific syntax. This feature nicely facilitates the migration from the old to the new. For reference, I’ve also provided a mapping of the old exp/imp syntax and how it relates to Data Pump commands.
Although Data Pump is an excellent tool for moving database objects and data from one environment to another, sometimes you need to transfer large quantities of data to and from operating-system flat files. You use external tables to achieve this task. This is the topic of the next chapter in this book.
344
CHAPTER 14 ■ EXTERNAL TABLES
A quick comparison of using SQL*Loader and external tables highlights the differences. Listed next are the SQL*Loader steps that you use to load and transform of data:
1.
Create a parameter file that SQL*Loader uses to interpret the format of the data in the operating-system file.
2.
Create a regular database table into which SQL*Loader will insert records. The data will be staged here until it can be further processed.
3.
Run the SQL*Loader sqlldr utility to load data from the operating-system file into the database table (created in step 2). When loading data, SQL*Loader has some features that allow you to transform data. This step is sometimes frustrating because it can take several trial-and-error runs to correctly map the parameter file to the table and corresponding columns.
4.
Create another table that will contain the completely transformed data.
5.
First run SQL statements to load the data from the staging table (created in step 2); then transform and insert the data into the production table (created in step 4).
Compare the previous SQL*Loader list to the following steps to load and transform data using external tables:
1.
Execute a CREATE TABLE...ORGANIZATION EXTERNAL script that maps the
structure of the operating-system file to table columns. After this script is run, you can directly use SQL to query the contents of the operating-system file.
2.
Create a regular table to hold the completely transformed data.
3.
Run SQL statements to load and fully transform the data from the external table into the table created in step 2.
For many shops, SQL*Loader underpins large data-loading operations. It continues to be a good tool to use for that task. However, you may want to investigate using external tables. External tables have the following advantages:
•
•
SQL*Loader’s parameter-file interface.
•
•
The next several sections contain examples of using external tables to read from operating-system files.
346
CHAPTER 14 ■ EXTERNAL TABLES
Loading CSV Files into the Database
You can load small or very large CSV flat files into the database using external tables and SQL. Figure 14–
1 shows the architectural components involved with using an external table to view and load data from an operating-system file. A directory object is required that specifies the location of the operating-system file. The CREATE TABLE...ORGANIZATION EXTERNAL statement creates a database object that SQL*Plus can use to directly select from the operating-system file.
Figure 14–1.
Architectural components of an external table used to read a flat file
Here are the steps for using an external table to access an operating-system flat file: 1. Create a database-directory object that points to the location of the CSV file.
2. Grant read and write privileges on the directory object to the user creating the external table. I usually use a DBA privileged account, so I don’t need to perform this step.
3. Run the CREATE TABLE...ORGANIZATION EXTERNAL statement.
4. Use SQL*Plus to access the contents of the CSV file.
In this example, the flat file is named ex.csv and is located in the /oraet directory. It contains the following data:
5|2|0|0|12/04/2009|Half
6|1|0|1|03/31/2010|Quarter
7|4|0|1|05/07/2010|Full
8|1|1|0|04/30/2010|Quarter
■
Note
Some of the CSV file examples used in this chapter are actually separated by characters other than a comma, such as a pipe | character. The character used depends on the data and the user who supplies the CSV
file. CSV files are also often commonly referred to as flat files.
347
CHAPTER 14 ■ EXTERNAL TABLES
Creating a Directory Object and Granting Access
First, create a directory object that points to the location of the flat file on disk: SQL> create directory exa_dir as '/oraet';
This example uses a database account that has the DBA role granted to it; therefore, you don’t need to grant READ and WRITE to the directory object to the user (your account) that is accessing the directory object. If you’re not using a DBA account to read from the directory object, then grant these privileges to the account using this object:
SQL> grant read, write on directory exa_dir to reg_user;
Creating an External Table
Next, fashion the script that creates the external table that will reference the flat file. The CREATE
TABLE...ORGANIZATION EXTERNAL statement provides the database with the following information:
• How to interpret data in the flat file, and a mapping of data in file-to-column definitions in the database
• A DEFAULT DIRECTORY clause that identifies the directory object, which in turn specifies the directory of the flat file on disk
• The LOCATION clause, which identifies the name of the flat file
The next statement creates a database object that looks like a table yet is able to retrieve data directly from the flat file:
create table exadata_et(
exa_id NUMBER
,machine_count NUMBER
,hide_flag NUMBER
,oracle NUMBER
,ship_date DATE
,rack_type VARCHAR2(32)
)
organization external (
type oracle_loader
default directory exa_dir
access parameters
(
records delimited by newline
fields terminated by '|'
missing field values are null
(exa_id
,machine_count
,hide_flag
,oracle
,ship_date char date_format date mask "mm/dd/yyyy"
,rack_type)
)
location ('ex.csv')
)
reject limit unlimited;
348
CHAPTER 14 ■ EXTERNAL TABLES
An external table named EXADATA_ET is created when you execute this script. Now, use SQL*Plus to view the contents of the flat file:
SQL> select * from exadata_et;
EXA_ID MACHINE_COUNT HIDE_FLAG ORACLE SHIP_DATE RACK_TYPE
---------- ------------- ---------- ---------- --------- ---------
5 2 0 0 04-DEC-09 Half
6 1 0 1 31-MAR-10 Quarter
7 4 0 1 07-MAY-10 Full
8 1 1 0 30-APR-10 Quarter
Viewing External-Table Metadata
At this point, you can also view metadata regarding the external table. Query the DBA_EXTERNAL_TABLES
view for details:
select
owner
,table_name
,default_directory_name
,access_parameters
from dba_external_tables;
Here’s a partial listing of the output:
OWNER TABLE_NAME DEFAULT_DIRECTO ACCESS_PARAMETERS
--------- ----------------- ------------------ ---------------------------