The
Oracle Data Pump Export and Import utilities
in the Oracle 10g database
"Hatred
ever kills, love never dies such is the vast difference between the two. What is
obtained by love is retained for all time. What is obtained by hatred
proves a burden in reality for it increases hatred. "
Gandhi
|
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 ISELF
schema.
#
expdp
FILE=/u02/oradata/ora10g/EXPDAT02.DMP
FILESIZE=2048M
LOG=/u02/oradata/ora10g/EXPDAT.LOG
TABLES=ISELF.CUSTOMER,ISELF.DEPT,ISELF.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 iself, outln and system schemas.
#
expdp
FILE=/u02/oradata/ora10g/EXPDAT05.DMP
FILESIZE=2048M
LOG=/u02/oradata/ora10g/EXPDAT.LOG
OWNER=ISELF,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=iself
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 iself and scott schemas.
$
expdp
SCHEMAS=iself,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=iself
REMAP_TABLESPACE=iself_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.
Also, with the Oracle 10g import utility, you can use a network connection so that you can directly import either a single or multiple table(s) from one server to another. Here are the setps you need to perform in order to import a table or tables from one server to another. First, assume that you have two servers, Server A and Server B, and you want to import a table or tables such as "EMP" and "DEPT" from server "A" to "B."
1. Go to Server B and create a database link that will access the database in Server A.
Example:
SQL> CREATE DATABASE LINK mylink2a CONNECT TO scott IDENTIFIED BY password USING 'mylink2a';
Note that 'scott' is a user in the database in Server "A" and that its password is 'password'.
2. Then, perform the following import dump command.
Example:
# impdp scott2/password2 TABLES=emp,dept DIRECTORY=dpump1 NETWORK_LINK=mylink2a
Note that 'scott2' is a user in the database in Server B and that its password is 'password2'.
That is all you need to do. Certainly, there are lots of other features that you can use with this command; however, they do not fit in the scope of this article.
Good Luck!
|