First, data pump introduction

OracleDatabase 10g introduces the latest data pump technology, data pump export imports (EXPDP and

IMPDP)

① Implement logical backup and logical recovery.

② Move objects between database users.

③ Move objects between databases

④ Implement tablespace migration.

Two, data pump technology

1. Advantages of data pump:

① Improved performance (1-2 orders of magnitude faster than traditional EXP/IMP)

② Ability to restart operations

③ Parallel execution ability

(4) Associated operation ability

(5) Ability to estimate space demand

⑥ Operating network mode

2. Components of data pump:

① Data pump core part of the program package: DBMS_DATAPUMP

② The package that provides metadata is DBMS_MATADATA

③ Command line client (utility program) : EXPDP,IMPDP

3. Data pump file:

1 Dump file: This file contains object data

② Log file: Records operation information and results

③SQL file: Write DDL statements in the import job to the parameter file specified in SQLFILE

4. Data pump directory and file location

When importing and exporting data pumps as the SYS or SYSTEM user, you can use the default directory, DATA_PUMP_DIR

    SQL> select * from dba_directories;Copy the code

If the environment variable ORACLE_BASE is set, the default directory location for DATA_PUMP_DIR is: 

    $ORACLE_BASE/admin/database_name/dpdumpCopy the code

It is: 

   $ORACLE_HOME/admin/database_name/dpdumpCopy the code

5. Help

You can use help to view the usage of expDP and IMPDP

     $expdp help=y
     $impdp help=y
Copy the code

Three, data pump example

1. View the location where the exported data is stored

① Use the default directory of the data pump

 SYS@ prod>select * from dba_directories where directory_name='DATA_PUMP_DIR';Copy the code

        OWNER            DIRECTORY_NAME            DIRECTORY_PATH          

      SYS             DATA_PUMP_DIR           /u01/admin/prod/dpdump/

② Grant directory permissions to Scott

   SQL> grant read,write on directory DATA_PUMP_DIR to scott;Copy the code

2. Data pump guide chart

Derived tables

$expdp scott/scott directory=DATA_PUMP_DIR dumpfile=emp1_dept1.dmp tables=emp1,dept1Copy the code



View the exported files in the directory

Import table (inverse of export) 

$impdp scott/scott directory=DATA_PUMP_DIR dumpfile=emp1_dept1.dmpCopy the code



3. Data pump guide users

Exporting User Scott

$expdp system/oracle directory=DATA_PUMP_DIR dumpfile=scott.dmp schemas=scottCopy the code



Import User Scott 

$impdp system/oracle directory=DATA_PUMP_DIR dumpfile=scott.dmp remap_schema=scott:scottCopy the code

   
Import user Scott and set the password to Scott

4. Data pump transportable table space (suitable for large-scale data migration)

Export table space

$expdp \'/ as sysdba\' DIRECTORY=DATA_PUMP_DIR DUMPFILE=TB1.DMP tablespaces=TB1Copy the code



Importing a tablespace

$impdp system/oracle  DIRECTORY=DATA_PUMP_DIR DUMPFILE=TB1.DMP tablespaces=TB1;Copy the code

5. Data pumping database

Exporting the Database

$expdp system/oracle DIRECTORY=DATA_PUMP_DIR dumpfile=full.dmp full=y;Copy the code

Importing a Database 

$impdb system/oracle DIRECTORY=DATA_PUMP_DIR dumpfile=full.dmp full=y;Copy the code

Additional data

$impdp system/oracle DIRECTORY=DATA_PUMP_DIR dumpfile=expdp.dmp schemas=systemtable_exists_actionCopy the code



(Steal a small lazy graph I will not give you a demonstration, to have a heart of self-practice. – _ -)