Oracle Data Pump (Dump) export, import ____oracle

* This import export method is fully applicable to the partition table * prepare before exporting, importing

Note: Use System user actions such as SYS, systems, and so on.
1) Create directory objects

CREATE DIRECTORY exp_dir as '/oracle/db/zone '; --Storing DMP, log files

2 to enable the user user_local authorize the directory

GRANT READ, WRITE on DIRECTORY exp_dir to user_local;

Local export, local import

1: Local export
1) export the specified table

EXPDP user_local/user_local directory=exp_dir dumpfile=tab.dmp logfile=tab.log tables=user_office  
table 1, Table 2, table 3, etc.

2) Export the whole library (scheme)

EXPDP user_local/user_local directory=exp_dir  dumpfile=tab.dmp logfile=tab.log schemas=user_local  
-- schemas= User (all operable tables, etc.)

3) Export the whole library

EXPDP system/orcl directory=exp_dir dumpfile=tab.dmp logfile=tab.log full=y

2: Local import
1) Import the specified table

IMPDP user_local/user_local directory=exp_dir dumpfile=tab.dmp logfile=tab.log table_exists_action=append User_office

Description: Tables=user_office: Can not write
Table_exists_action=append: Append data, no repeat data can be imported multiple times
When Table_exists_action=replace is in full import, the method deletes the table directly and then creates the table again and writes the data to all

2) Import Whole library (scheme)

IMPDP user_local/user_local directory=exp_dir dumpfile=tab.dmp logfile=tab.log schemas=user_local

3) Import Whole library

IMPDP system/orcl directory=exp_dir dumpfile=tab.dmp logfile=tab.log full=y

Import without landing

1: Description
The remote database data is imported into the local database.
1) Remote database:
ip:192.168.1.200
User name: User_remote
Password: user_remote_
Instance Name: ORCL
Table Space: Remote_space

2 Local Database:
User name: user_local
Password: user_local
Table Space: Local_space

2: Local increased listening (REMOTE_ORCL)

REMOTE_ORCL =  
  (DESCRIPTION = (  
    address_list =  
      (address = (PROTOCOL = TCP) (HOST = 192.168.1.200) (PORT = 1521)) c4/>)  
    (connect_data =  
      (sid = Orcl)  
    )  
  )

3: Create Remote Link Database link (source_db_link)

Create DATABASE link Source_db_link connect to user_remote identified by User_remote_ using ' REMOTE_ORCL '

REMOTE_ORCL: Increased listener name in the previous step

4: Remote library table (user_office) Import local library

IMPDP  user_local/user_local directory=exp_dir  logfile=tab.log network_link=source_db_link  TABLE_ Exists_action=append 
Tables=user_remote. User_office remap_schema=user_remote:user_local Remap_tablespace=remote_space:local_space

Note: If the prompt is insufficient, the SYS system user can either give DBA authority to the local user user_local to resolve it, or change the imported user connection to ' SYSTEM/ORCL '
1 network_link=source_db_link : The connection name created in the previous step
2) Table_exists_action=append: Append data, do not duplicate data can be imported multiple times
Note: You can change ' APPEND ' to ' replace ', which deletes the original table and then creates the table again and imports the data
3) Tables=user_remote. User_office: Table name, multiple tables with ', ' separated from
4) remap_schema=user_remote:user_local: Data removal between different users, writing: remote User: Local Users, if the same can not write
5 remap _tablespace=remote_space:local_space: The move of data between different table spaces, writing: Remote table space Name: The name of this surface space, if the same can not write