Oracle Import and Export operations Chapter

1. DIRECTORY

Specify the directory where the dump files and log files reside
Directory=directory_object
The directory_object is used to specify the directory object name. Note that directory objects are objects created using the Create DIRECTORY statement, not the OS directory

Create or replace directory Dumpdir as '/home/oracle/hpdata ';
Grant read,write on directory Dumpdir to ICS;

2. CONTENT

This option is used to specify what to export. The default value is all
Content={all | data_only | Metadata_only}
When content is set to all, the object definition and all its data are exported. When data_only, only the object data is exported, and only the object definition is exported as Metadata_only

EXPDP ics/ics Directory=dumpdir dumpfile=ics.dmp content=metadata_only
----------Export only Object definitions
EXPDP ics/ics Directory=dumpdir dumpfile=ics.dmp content=data_only
----------Export all the data

3. DumpFile

Used to specify the name of the dump file, the default name is Expdat.dmp
Dumpfile=[directory_object:]file_name [,....]
The directory_object is used to specify the directory object name, and file_name is used to specify the dump file name. Note that if you do not specify Directory_object, the export tool automatically uses the directory object specified by the directory option
Show parameter dump;--view variables

EXPDP ics/ics Directory=dumpdir dumpfile=ics.dmp

IMPDP oracle/oracle directory=dumpdir dumpfile=ics_141124_1.dmp remap_schema=ics:ics TABLE_EXISTS_ACTION=REPLACE

4. Various modes of data pump export:

(1), Export by Table mode:
EXPDP ics/ics tables=fntaccinfo,fntaccbalhis,fntaccjrnl dumpfile =ics_tables.dmp logfile=ics_tables.log directory= Dumpdir Job_name=my_job

(2), according to the query conditions to export:
EXPDP ics/ics tables=ics.membaseinfo dumpfile =ics_membaseinfo.dmp logfile=ics_membaseinfo.log Directory=DUMPDIR job_ Name=my_job query= ' "where ci_typ= ' 0 '" '

(3), export by table space:
EXPDP ics/ics dumpfile=expdp_tablespace.dmp tablespaces=user logfile=expdp_tablespace.log Directory=DUMPDIR job_name =my_job

(4), export scheme
EXPDP ics/ics Directory=dumpdir dumpfile=schema.dmp SCHEMAS=ICS,HPDM

(5), export the entire database:
EXPDP ics/icsics dumpfile =ics_141124_1.dmp full=n logfile=full.log directory=dumpdir job_name=my_job--plus one compression =all can compress backups but will be more time-consuming to restore

--------------------------------------------------------------------------------------------------------------- -------

IMPDP Import Mode:

1. Import by Table
The table in the P_street_area.dmp file, which is exported as a GWM user by Schemas=gwm:
IMPDP Gwm/[email protected] dumpfile =p_street_area.dmp logfile=imp_p_street_area.log DIRECTORY=DIR_DP tables=p_ Street_area Job_name=my_job

2, by user import (user information can be directly imported, that is, if the user information does not exist in the case can also be directly imported)
IMPDP Gwm/[email protected] schemas=gwm dumpfile =expdp_test.dmp logfile=expdp_test.log DIRECTORY=DIR_DP job_name=my_ Job

3. Methods for directly importing a DMP file without EXPDP steps:
--Import tables from the source database to the target database P_street_area
IMPDP gwm/gwm directory=dir_dp network_link=igisdb tables=p_street_area logfile=p_street_area.log job_name=my_job
Igisdb is the link name of the destination database and the source data, DIR_DP is the directory on the destination database

4. Change schema

--Export all data under the GWM user
EXPDP ics/ics Directory=dumpdir dumpfile=ics.dmp schemas=ics
Note: If user data is exported with SYS user, including user creation, authorization section, it is not included with own user export
--The following is a full import of the data under the ICS user into the user HPDM (formerly the Gmapdata table space)
IMPDP HPDM/HPDM Directory=dumpdir dumpfile=ics.dmp REMAP_SCHEMA=ICS:HPDM

5. Replace Tablespace
Using Remap_tablespace parameters

-----------------------------------
Create user test identified by test default tablespace users;
Grant resource to test;
Grant connect to test;
Grant DBA to test;

Sqlplus "/as sysdba"

Create directory Dumpdir as '/home/oracle/hpdata ';
Grant read,write on directory dumpdir to test;

------------------------------------------------
Database backup recovery for different schema data (you need to delete the target library first)
IMPDP test/test directory=dumpdir dumpfile=hp66_20140704.dmp remap_schema=hpdm:test FULL=Y IGNORE=Y
Full Library Export Backup
EXPDP ics/ics schemas=ics dumpfile=ics20140704.dmp directory=dumpdir

---update data if a table exists, applies to data structures that do not change, and only restores them to a backup, but stored procedures, views, functions, sequence updates fail
IMPDP test/test directory=dumpdir dumpfile=ics20140704.dmp remap_schema=ics:test table_exists_action=replace;
---basic ibid, but does not perform updates to stored procedures, views, functions, sequence
IMPDP test/test directory=dumpdir dumpfile=hp66_20140704.dmp remap_schema=hpdm:test table_exists_action=replace Exclude=sequence,view,function,procedure;

Oracle Import and Export operations Chapter