|
The Oracle Data
Pump Export and Import utilities
in the Oracle 10g database
Why Data Pump Export
and Import?
The expdp and impdp
tools support all the original exp and imp
functionalities plus many new features. With
previous release, you could only move the
transportable tablespace across Oracle databases
that were running on the same architecture and
operating system. With Data Pump, you are able to
transport data files from one plateform to another.
Only you have to make sure that both source and
target databases set their COMPATIBLE initialization
parameter to 10.0.0 or greater.
The following are some
example of using exp/imp or expdp/impdp utilities:
Assume that you need
to export the DEPT and EMP records that deptno is 10
or 30 from the ORACLE schema.
# expdp
FILE=/u02/oradata/ora10g/EXPDAT02.DMP
FILESIZE=2048M
LOG=/u02/oradata/ora10g/EXPDAT.LOG
TABLES=ORACLE.CUSTOMER,ORACLE.DEPT,ORACLE.EMP
GRANTS=y
INDEXES=y
ROWS=y
CONSTRAINTS=y
CONSISTENT=n
RECORD=n
QUERY='WHERE deptno IN
(10, 30)'
Assume that you need
to export the oracle, outln and system schemas.
# expdp
FILE=/u02/oradata/ora10g/EXPDAT05.DMP
FILESIZE=2048M
LOG=/u02/oradata/ora10g/EXPDAT.LOG
OWNER=ORACLE,OUTLN,SYSTEM
GRANTS=y
INDEXES=y
ROWS=y
CONSTRAINTS=y
CONSISTENT=n
RECORD=n
To import the DEPT and
EMP tables with recalculating statistics and
committing after each array insert, we do the
following Oracle command.
# impdp
FILE=/u02/oradata/ora10g/EXPDAT.DMP
LOG=/u02/oradata/ora10g/IMPORT.LOG
FROMUSER=oracle
TABLES=emp,dept
GRANTS=y
INDEXES=y
ROWS=y
CONSTRAINTS=y
IGNORE=y
COMMIT=y
RECALCULATE_STATISTICS=y
DATAFILES=n
Parallel Full Export
and Import:
Assuming you have
created DIR1, DIR2 directory objects and you want
each file be 2 GB in size.
$ expdp
FULL=y
PARALLEL=2
DUMPFILE=DIR1:exp1%U.dmp,
DIR2:exp2%U.dmp
FILESIZE=2G
The %u implies that
multiple files may be generated and start at 01 with
a two-digital number.
Now, you can import to
different directory (REMOTE).
$ impdp
DIRECTORY=remote
PARALLEL=2
DUMPFILE=exp1%U.dmp,
exp2%U.dmp
You can also export
with limited resources in a schema. The following
example exports all functions, tables, procedures
(proc1 and proc2 only), and all views that starts
with the �EMP� characters from the oracle and scott
schemas.
$ expdp
SCHEMAS=oracle,scott
DIRECTORY=private_exp_space
DUMPFILE=expdat01.dmp
INCLUDE=function
INCLUDE=table
INCLUDE=procedure:�in
(�proc1�,�proc2�)�
INCLUDE=view:�like �EMP%��
Either you should use
INCLUDE or EXCLUDE.
You can generate a SQL
script from an existing export dump file. The SQL is
executed, and the target system remains unchanged.
$ impdp
DIRECTORY=private_exp_space
DUMPFILE=expdat01.dmp
SQLFILE=MyScript.sql
You can also move
objects from one tablespace to another by using the
REMAP_TABLESPACE option.
Example:
$ impdp
SCHEMAS=oracle
REMAP_TABLESPACE=oracle_tablespace:urself_tablespace
Now, you can read from
your exported file directly without importing them
into your database.
SQL> CREATE TABLE
external_emp
(ename, sal,
comm)
ORGANIZATION
EXTERNAL
(
TYPE
ORACLE_DATAPUMP
DEFAULT
DIRECTORY private_exp_space
LOCATION (
�expdat01.dmp�)
)
PARALLEL
AS
SELECT
ename, sal, comm.
FROM emp
WHERE deptno IN (10, 30);
What is an endian
format?
The endian format or
Byte ordering is a format that will affect the
results when data is written and read. For example,
the 2-bytes integer value 1 is written as 0x0001 on
a big-endian system and as 0x0100 on a little-endian
system. To determine the endian format of a platform
do the following query:
SQL> SELECT
p.endian_format
FROM
v$transportable_platform p, v$database d
WHERE
p.platform_name = d.platform_name
/
The
v$transportable_platform view contains all supported
platforms. In order to convert form one platform to
another platform use the rman utility. The following
is an example of how to convert from one platform to
another.
$ rman TARGET=/
RMAN> CONVERT DATAFILE
�/local/oradata/school/*�
FROM
PLATFORM = �Solari [tm] OE (32-bit)�
DB_FILE_NAME_CONVERT =
�/local/oradata/school/data� ,
�/remote/oradata/data�;
The
DB_FILE_NAME_CONVERT clause will take in a file
name, perform the required conversion, and place the
converted file in the new location.
|
