Shulou(Shulou.com)05/31 Report--

Editor to share with you how to use impdp and expdp in the database. I hope you will get something after reading this article. Let's discuss it together.

1.Data Pump Export expdp

Example:

Sql > create directory dpdata1 as'/ u02bind dpdata1'

Sql > grant read, write on directory dpdata1 to ananda

$expdp ananda/abc123 tables=CASES directory=DPDATA1 dumpfile=expCASES.dmp job_name=CASES_EXPORT

$expdp ananda/abc123 tables=CASES directory=DPDATA1

Dumpfile=expCASES_%U.dmp parallel=4 job_name=Cases_Export

Include/exclude example:

Include=table: "in ('DB','TS')"

Or include=table: "like'% E%'"

Or include=function,package,procedure,table: "= 'EMP'"

Or exclude=SEQUENCE,TABLE: "IN ('EMP','DEPT')"

2.Data Pump Import impdp

1) obtain the data source exp.dmp from expdp

2) copy one schema from one database to another.

3) copy all objects in one schema to another schema in the same database.

Example:

1) the data source of impdp is the DMP file exported by expdp.

Impdp ananda/abc123 directory=dpdata1 dumpfile=expCASES.dmp job_name=cases_import

2) copy one schema from one database to another.

-- database_link established by 1.newwork_link for the target database

(user test requires grant exp_full_database to TEST;)

Create public database link TOLINK

Connect to TEST identified by oracle

Using'(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP) (HOST = 10.0.20.199) (PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = orcl)

)

)'

-- 2.impdp executes only low-to high-version imp on the target database server

Impdp network_link=TOLINK schemas=test remap_schema=test:link2

3) copy all objects in one schema to another schema in the same database.

-1. Create a database link to connect to yourself:

Create public database link system_self connect to system identified by "system" using 'orcl'

The database link has been created.

-- 2. Copy hr schema to test schema:

Impdp system/system network_link=system_self schemas=hr remap_schema=hr:test

First, create a logical directory. This command does not create a real directory in the operating system, but is best created by administrators such as system.

Create directory dpdata1 as'd:\ test\ dump'

Place the exported dmp file in the above directory

Second, check the administrator directory (also check whether the operating system exists, because Oracle does not care if the directory exists, if it does not exist, it will make an error)

Select * from dba_directories

Third, give scott users the right to operate in a specified directory, preferably by administrators such as system.

Grant read,write on directory dpdata1 to scott

Grant imp_full_database to scott

IV. Export data

1) by user guide

Expdp scott/tiger@orcl schemas=scott dumpfile=expdp.dmp DIRECTORY=dpdata1

Expdp system/oracle@uscm schemas=uaes_woorich dumpfile=expdp.dmp DIRECTORY=dpdata1

Expdp system/oracle@ucs schemas=sys8d dumpfile=sys8d.dmp directory=ORACLE_OCM_CONFIG_DIR

2) parallel process parallel

Expdp scott/tiger@orcl directory=dpdata1 dumpfile=scott3.dmp parallel=40 job_name=scott3

3) Guide by table name

Expdp scott/tiger@orcl TABLES=emp,dept dumpfile=expdp.dmp DIRECTORY=dpdata1

4) Guide according to query conditions

Expdp scott/tiger@orcl directory=dpdata1 dumpfile=expdp.dmp Tables=emp query='WHERE deptno=20'

5) according to the table space guide

Expdp system/manager DIRECTORY=dpdata1 DUMPFILE=tablespace.dmp TABLESPACES=temp,example

6) Import the entire database

Expdp system/manager DIRECTORY=dpdata1 DUMPFILE=full.dmp FULL=y

5. Restore data

Impdp user/pwd@dbname directory=dpdata1 dumpfile=a.dmp remap_schema=fromusername:tousername full=y

1) Import to the specified user

Impdp scott/tiger DIRECTORY=dpdata1 DUMPFILE=expdp.dmp SCHEMAS=scott

2) change the owner of the table

Impdp system/manager DIRECTORY=dpdata1 DUMPFILE=expdp.dmp TABLES=scott.dept REMAP_SCHEMA=scott:system

3) Import tablespace

Impdp system/manager DIRECTORY=dpdata1 DUMPFILE=tablespace.dmp TABLESPACES=example

4) Import database

Impdp system/manager DIRECTORY=dump_dir DUMPFILE=full.dmp FULL=y

5) additional data

Impdp system/manager DIRECTORY=dpdata1 DUMPFILE=expdp.dmp SCHEMAS=system TABLE_EXISTS_ACTION=append

USCM Migration:

Expdp system/oracle directory=dump dumpfile=scm0121.dmp schemas=scm,reviewdatabase,testdatabase logfile=scm0121.log

Impdp system/oracle directory=imp dumpfile=SCM0121.DMP logfile=scm0121imp.log exclude=statistics

After that, the dump parameter is described as follows:

EXPDP command line options

1. ATTACH

This option is used to establish an association between a customer session and an existing export role. The syntax is as follows

ATTACH= [schema _ name.] job_name

Schema_name is used to specify the scheme name, and job_name is used to specify the export job name. Note that if you use the ATTACH option, you cannot specify any options other than the connection string and ATTACH options on the command line, as shown in the following example:

Expdp scott/tiger ATTACH=scott.export_job

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 object data is exported, and when METADATA_ONLY, only object definitions are exported

Expdp scott/tiger DIRECTORY=dump DUMPFILE=a.dump

CONTENT=METADATA_ONLY

3. DIRECTORY

Specify the directory where the dump and log files are located

DIRECTORY=directory_object

Directory_object is used to specify the name of the directory object. It is important to note that directory objects are objects created using CREATE DIRECTORY statements, not OS directories

Expdp scott/tiger DIRECTORY=dump DUMPFILE=a.dump

Create a directory:

CREATE DIRECTORY dump as'ddump'

Which subdirectories are created by the query:

SELECT * FROM dba_directories

4. DUMPFILE

Used to specify the name of the dump file. The default name is expdat.dmp

DUMPFILE= [directory _ object:] file_name [, … .]

Directory_object is used to specify the directory object name, and file_name is used to specify the dump file name. It should be noted that if you do not specify directory_object, the export utility automatically uses the directory object specified by the DIRECTORY option

Expdp scott/tiger DIRECTORY=dump1 DUMPFILE=dump2:a.dmp

5. ESTIMATE

Specifies the division method for estimating the disk space occupied by the exported table. The default value is BLOCKS

EXTIMATE= {BLOCKS | STATISTICS}

When set to BLOCKS, oracle estimates the space occupied by the target object by multiplying the number of blocks occupied by the target object by the block size. When set to STATISTICS, the space occupied by the object is estimated based on the most recent statistics.

Expdp scott/tiger TABLES=emp ESTIMATE=STATISTICS

DIRECTORY=dump DUMPFILE=a.dump

6. EXTIMATE_ONLY

Specifies whether to estimate only the disk space occupied by the export job, with a default value of N

EXTIMATE_ONLY= {Y | N}

When set to Y, the export function only estimates the disk space occupied by the object, but does not perform the export job. When N, it not only estimates the disk space occupied by the object, but also performs the export operation.

Expdp scott/tiger ESTIMATE_ONLY=y NOLOGFILE=y

7. EXCLUDE

This option is used to specify that the object type or related objects to be excluded are released when the operation is performed

EXCLUDE=object_type [: name_clause] [, … .]

Object_type is used to specify the types of objects to be excluded, and name_clause is used to specify specific objects to be excluded. Exclude and INCLUDE cannot be used at the same time

Expdp scott/tiger DIRECTORY=dump DUMPFILE=a.dmp EXCLUDE=VIEW

EXPDP scott/tiger directory=dump dumpfile=a.dmp table= (tfocus TT) exclude=table/table_data:\ "=\'T\'\"

Can also be used for partitioned tables

Expdp scott/tiger directory=dump dumpfile=a.dmp table=t,tt exclude=table_data:\ "in\ (\'P1\',\'P2\',\'P3\')\"

8. FILESIZE

Specifies the maximum size of the exported file, which defaults to 0 (indicates that there is no limit to the file size)

9. FLASHBACK_SCN

Specify the export of table data for a specific SCN time

FLASHBACK_SCN=scn_value

Scn_value is used to identify SCN values. Flash _ SCN and FLASHBACK_TIME cannot be used at the same time

Expdp scott/tiger DIRECTORY=dump DUMPFILE=a.dmp

FLASHBACK_SCN=358523

10. FLASHBACK_TIME

Specify the export of table data at a specific point in time

FLASHBACK_TIME= "TO_TIMESTAMP (time_value)"

Expdp scott/tiger DIRECTORY=dump DUMPFILE=a.dmp FLASHBACK_TIME=

"TO_TIMESTAMP ('25-08-2004 14-14-35-15-00-5-8-14-14-15-14-15-15-14-14-15-15-14-15-15-14-14-15-15-14-15-15-15-14-15-14-15-15-14-14-15-15-15-14-14-15-15-15-14-15-15-14-15-15-14-15-15-14-15-15-14-14-15-15-14-15-14-15-15-14-15-14-15-15-14-14-15-15-14-14-15-15-14-14-15-15-14-14-15-15-14-14-15-15-15-14-14-15-15-14-14

11. FULL

Specifies the database schema export, which defaults to N

FULL= {Y | N}

When Y, the identity performs a database export.

12. HELP

Specifies whether to display help for EXPDP command line options, which defaults to N

When set to Y, help information for the export option is displayed.

Expdp help=y

13. INCLUDE

Specify the types of objects and related objects to be included in the export

INCLUDE = object_type [: name_clause] [, … ]

14. JOB_NAME

Specifies the name of the role to be exported. Default is SYS_XXX.

JOB_NAME=jobname_string

15. LOGFILE

Specifies the name of the exported log file. The default name is export.log.

LOGFILE= [directory _ object:] file_name

Directory_object is used to specify the directory object name, and file_name is used to specify the export log file name. If you do not specify directory_object. The export function automatically uses the corresponding option value of DIRECTORY.

Expdp scott/tiger DIRECTORY=dump DUMPFILE=a.dmp logfile=a.log

16. NETWORK_LINK

Specify the database chain name, which must be set if you want to export remote database objects to the dump file of the local routine.

17. NOLOGFILE

This option is used to specify that the generation of export log files is prohibited, and the default value is N.

18. PARALLEL

Specifies the number of parallel processes performing the export operation. The default is 1.

19. PARFILE

Specify the name of the export parameter file

PARFILE= [directory _ path] file_name

20. QUERY is very inefficient for large tables

Used to specify where conditions for filtering exported data

QUERY= [schema.] [table_name:] query_clause

Schema is used to specify the scheme name, table_name is used to specify the table name, and query_clause is used to specify the conditional restriction clause. The query option cannot be used with options such as CONNECT=METADATA_ONLY,EXTIMATE_ONLY,TRANSPORT_TABLESPACES.

Expdp scott/tiger directory=dump dumpfile=a.dmp Tables=emp query='WHERE deptno=20'

Expdp scott/tiger directory=dump dumpfile=a.dmp table== (tfocus TT) query='t: "where 1: 2" 'derives the structure of t and the data and structure of tt.

21. SCHEMAS

This scheme is used to specify the execution scheme mode export, which defaults to the current user scheme.

twenty-two。 STATUS

Specifies that the detailed status of the export process is displayed. The default value is 0.

23. TABLES

Specify table schema export

TABLES= [schema _ name.] table_name [: partition_name] [,...]

Schema_name is used to specify the scheme name, table_name is used to specify the exported table name, and partition_name is used to specify the partition name to be exported.

24. TABLESPACES

Specify that you want to export the tablespace list

25. TRANSPORT_FULL_CHECK

This option is used to specify how to check the relationship between moved and unmoved tablespaces, which defaults to N.

When set to Y, the export function checks the direct complete association of the tablespace, and an error message is displayed if only one tablespace in the tablespace or its index is moved. When set to N, the export function only checks for single-end dependencies. If the table space of the index is moved, but the table space of the table is not moved, an error message will be displayed. If the table space of the table is moved and the table space of the index is not moved, no error message will be displayed.

twenty-six。 TRANSPORT_TABLESPACES

Specify to perform tablespace schema export

twenty-seven。 VERSION

Specifies the database version of the exported object. The default value is COMPATIBLE.

VERSION= {COMPATIBLE | LATEST | version_string}

When COMPATIBLE, object metadata is generated based on the initialization parameter COMPATIBLE; when LATEST, object metadata is generated based on the actual version of the database. Version _ string is used to specify the database version string. Call EXPDP

When using the EXPDP tool, the dump file can only be stored in the OS directory corresponding to the DIRECTORY object, but can not directly specify the OS directory where the dump file is located. therefore,

When using the EXPDP tool, you must first create a DIRECTORY object. And database users need to be granted permission to use DIRECTORY objects.

After reading this article, I believe you have a certain understanding of "how to use impdp and expdp in the database". If you want to know more about it, you are welcome to follow the industry information channel. Thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.