Steps of experiment(Expdp and Impdp):
- Create a dump directory named dmp_dir;
- Check the user’s privilege on the directory,if not,grant the right system/object privilege to user;
- Excute the expdp process;
- Excute the impdp process;
Experiment environment:
There are 4 tables named by test01,t1,t2,plan_table in the ann’s schema, two tablespaces:tsp01 and tsp02, two users:ann(defaut tablespace tsp01) and attacker(defaut tablespace tsp02).
Scripts of step 1 and stp 2:
sql> create directory dmp_dir as '/u02/oradata/dmpdest'; SELECT privilege, directory_name, DIRECTORY_PATH FROM user_tab_privs t, all_directories d WHERE t.table_name(+) = d.directory_name ORDER BY 2, 1; sql> Grant read,write on directory dmp_dir to ann;
Unload data:
All objects in the schema:
expdp ann/123456@ann01 schemas=ann directory=dmp_dir dumpfile=20121205.dmp logfile=dmp.log;
include/exclude the table which name start with 'plan':
expdp ann/123456@ann01 schemas=ann directory=dmp_dir dumpfile=20121205_2.dmp logfile=dmp.log [include/exclude]=table:\"like \'PLAN%\'\";
include/exclude the specific type object:(note:it will export the definition of the index rather than the index data)
expdp ann/123456@ann01 schemas=ann directory=dmp_dir dumpfile=20121205.dmp logfile=dmp.log exclude=index;
export the target objects’ definition only:
expdp ann/123456@ann01 tables=t1 content=metadata_only directory=dmp_dir dumpfile=20121205.dmp logfile=dmp.log;
export the data with where clause:
expdp ann/123456@ann01 tables=t1 directory=dmp_dir dumpfile=20121205.dmp logfile=dmp.log query=\"where c_name=\'Ann01\'\";
load data:
load t1 from dump file to attacker schema in tsp01:
impdp ann/123456@ann01 tables=t1 directory=dmp_dir dumpfile=20121205.dmp logfile=dmp.log remap_schema=ann:attacker
load t1 from dump file to attacker schema in tsp02:
impdp ann/123456@ann01 tables=t1 directory=dmp_dir dumpfile=20121205.dmp logfile=dmp.log remap_schema=ann:attacker remap_tablespace=tsp01:tsp02
if table t1 exists in tsp02 then you can use “TABLE_EXISTS_ACTION” parameter:
impdp ann/123456@ann01 tables=t1 directory=dmp_dir dumpfile=20121205.dmp logfile=dmp.log remap_schema=ann:attacker remap_tablespace=tsp01:tsp02 table_exists_action=skip
write the ddl operate in an sql file:
impdp ann/123456@ann01 tables=t1 directory=dmp_dir dumpfile=20121205.dmp logfile=dmp.log sqlfile=a.sql
different parameter from expdp:
1、REMAP_DATAFILE
Changes the name of the source datafile to the target datafile name in all SQL statements where the source datafile is referenced: CREATE TABLESPACE, CREATE LIBRARY, and CREATE DIRECTORY.
Syntax :REMAP_DATAFILE=source_datafie:target_datafile
2、REMAP_SCHEMA
Loads all objects from the source schema into a target schema.
Syntax :REMAP_SCHEMA=source_schema:target_schema
3、REMAP_TABLESPACE
Remaps all objects selected for import with persistent data in the source tablespace to be created in the target tablespace.
Syntax :REMAP_TABLESPACE=source_tablespace:target_tablespace
4、REUSE_DATAFILES
Specifies whether or not the import job should reuse existing datafiles for tablespace creation .
Syntax :REUSE_DATAFIELS={Y | N}
5、SKIP_UNUSABLE_INDEXES
Specifies whether or not Import skips loading tables that have indexes that were set to the Index Unusable state (by either the system or the user).Defaut is N.
Syntax :SKIP_UNUSABLE_INDEXES={Y | N}
6、SQLFILE
Specifies a file into which all of the SQL DDL that Import would have executed, based on other parameters, is written.
Syntax :SQLFILE=[directory_object:]file_name
Eg:Impdp attacker/1234567 DIRECTORY=dmp_dir DUMPFILE=20121205.dmp SQLFILE=a.sql
7、STREAMS_CONFIGURATION
Specifies whether or not to import any general Streams metadata that may be present in the export dump file. Default value is Y.
8、TABLE_EXISTS_ACTION
Tells Import what to do if the table it is trying to create already exists.
Syntax :TABBLE_EXISTS_ACTION={SKIP | APPEND | TRUNCATE | FRPLACE }
9、TRANSFORM
Enables you to alter object creation DDL for specific objects, as well as for all applicable objects being loaded.
Syntax :TRANSFORM=transform_name:value[:object_type]
The possible option of transform_name are:
- SEGMENT_ATTRIBUTES(default Y), physical attributes, storage attributes, tablespaces, and logging
- STORAGE(default Y), If the value is specified as y, the storage clauses are included.
- OID(default Y).
- PCTSPACE(Integer values are required), It represents the percentage multiplier used to alter extent allocations and the size of data files.
Eg:impdp attacker/123456 directory=dmp_dir dumpfile=20121205.dmp Transform=segment_attributes:n:table
10、TRANSPORT_DATAFILES
Specifies a list of datafiles to be imported into the target database by a transportable-mode import. The files must already have been copied from the source database system. Syntax ::TRANSPORT_DATAFILE=datafile_name
Eg:impdp system/manager DIRECTORY=dmp_dir DUMPFILE=20121205.dmp TRANSPORT_DATAFILES=’/u02/oradata/tps01.dbf