|
‘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
Introduction
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 platform 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.
Hands-On
The following are some
example of using exp/imp or expdp/impdp utilities:
The expdp utility
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
The impdp utility
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.
Remote access
Now, you can import to
different directory (REMOTE).
$ impdp
DIRECTORY=remote
PARALLEL=2
DUMPFILE=exp1%U.dmp,
exp2%U.dmp
Export with limited
Resources
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.
Generating SQL script
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
Moving objects
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
Access to an objects with
no import
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);
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 uses 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.
|
“Our nettlesome
task is to discover how to organize our strength into compelling
power.” Martin Luther King Jr. |
Questions:
Questions on
The Oracle
Data Pump Export and Import utilities in
the Oracle 10g database
Q: Why do you use Data Pump
Export and Import?
Q: Export the DEPT and EMP
records that deptno is 10 or 30 from the ISELF schema.
Q: Export the iself, outln
and system schemas.
Q: How do you import the
DEPT and EMP tables with recalculating statistics and committing after
each array insert?
Q: Perform a Parallel Full
Export on the DIR1, DIR2 directory objects and make sure that each
file be 2 GB in size.
Q: Export only all
functions, tables, procedures (proc1 and proc2 only), and all views
that starts with the ‘EMP’ characters from the iself and SCOTT
schemas.
Q: Generate a SQL script
from an existing export dump file.
Q: Move objects from one
tablespace to another by using the REMAP_TABLESPACE option.
Q: How can you read from
your exported file directly without importing them into your database?
Q: What is an endian
format?
|