iSelfSchooling.com  Since 1999     References  |  Search more  | Oracle Syntax  | Free Online Oracle Training

    Home      .Services     Login       Start Learning     Certification      .                 .Share your BELIEF(s)...

 

. Online Accounting        .Copyright & User Agreement   |
    .Vision      .Biography     .Acknowledgement

.Contact Us      .Comments/Suggestions       Email2aFriend    |

 

Oracle 10g New Features

 

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!

 

 

Google
 
Web web site