How to use Oracle 10g Data Pump

We know that,Oracle 10gUsedData PumpData Dump) technology enables DBAs or developers to define database metadata objects) and quickly move Data to another oracle database.

I. Role of data pump export to EXPDP and IMPDP)

1. implement logical backup and logical recovery.

2. move objects between database users.

3. move objects between databases

4. Implement table space migration.

Ii. Differences between data pump export and traditional export

Before 10 Gb, the traditional export and import tools respectively use the EXP tool and IMP tool. Starting from 10 Gb, the data pump export and import tools EXPDP and IMPDP were added.

EXP and IMP are client-segment tool programs that can be used either on the client or on the server.

EXPDP and IMPDP are tool programs on the server. They can only be used on the ORACLE server, but not on the client.

IMP only applies to EXP export files, not EXPDP export files; IMPDP only applies to EXPDP export files, not EXP export files.

Data Pump export includes four modes: export table, export scheme, export tablespace, and export database.

3. EXPDP command line options

1. ATTACH

This option is used to establish an association between a customer session and an existing export role. Syntax: 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: If you use the ATTACH option, you cannot specify any other options in the command line except the connection string and ATTACH options. For example: Expdp newhappy/pdmcn.com ATTACH = scott. export_job.

2. CONTENT

This option is used to specify the content to be exported. 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. If it is DATA_ONLY, only the object data is exported. If it is METADATA_ONLY, only the object definition is exported. Expdp newhappy/pdmcn.com DIRECTORY = dump DUMPFILE = a. dumpCONTENT = METADATA_ONLY.

3. DIRECTORY

DIRECTORY where the dump and log files are located: DIRECTORY = directory_object. Directory_object is used to specify the directory object name. Note that DIRECTORY objects are created using the create directory statement, rather than the OS DIRECTORY. Expdp newhappy/pdmcn.com DIRECTORY = dump DUMPFILE = a. dump

Create directory: create directory dump as 'd: dump; query created subdirectories: SELECT * FROM dba_directories;

4. DUMPFILE

Specifies 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. Note that if you do not specify directory_object, the export tool automatically uses the DIRECTORY object specified by the DIRECTORY option. Expdp newhappy/pdmcn.com DIRECTORY = dump1 DUMPFILE = dump2: a. dmp

5. ESTIMATE

Used to estimate the disk space used by the exported table. The default value is BLOCKS, EXTIMATE = {BLOCKS | STATISTICS}. When it is set to BLOCKS, oracle will estimate the space occupied by the object based on the number of data BLOCKS occupied by the target object multiplied by the data block size, when it is set to STATISTICS, the space occupied by the object is estimated based on the recent statistical value. Expdp newhappy/pdmcn.com 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. The default value is N, EXTIMATE_ONLY = {Y | N}. When set to Y, the export function only estimates the disk space occupied by the object, the export job is not executed. When N is used, not only the disk space occupied by the object is estimated, but also the export operation is performed. Expdp newhappy/pdmcn.com ESTIMATE_ONLY = y NOLOGFILE = y

7. EXCLUDE

This option is used to specify the object type or related objects to be excluded when the operation is released. EXCLUDE = object_type [: name_clause] [,…], Object_type is used to specify the object type to be excluded, and name_clause is used to specify the specific object to be excluded. EXCLUDE and INCLUDE cannot be used at the same time. Expdp newhappy/pdmcn.com DIRECTORY = dump DUMPFILE = a. dup EXCLUDE = VIEW

8. FILESIZE

Specifies the maximum size of the exported file. The default value is 0, indicating that the file size is unlimited)

9. FLASHBACK_SCN

Specify the table data at a specific SCN time point. FLASHBACK_SCN = scn_value and Scn_value are used to identify the SCN value. FLASHBACK_SCN and FLASHBACK_TIME cannot be used at the same time. Expdp newhappy/pdmcn.com DIRECTORY = dump DUMPFILE = a. dmp, FLASHBACK_SCN = 358523.

10. FLASHBACK_TIME

Specify the table data to be exported at a specific time point. FLASHBACK_TIME = "TO_TIMESTAMPtime_value)" Expdp newhappy/pdmcn.com DIRECTORY = dump DUMPFILE =. dmp FLASHBACK_TIME = "TO_TIMESTAMP '25-08-2004 14:35:00 ', 'dd-MM-YYYY HH24: MI: ss ')"

11. FULL

Specifies the database mode for export. The default value is N, FULL = {Y | N}. If the value is Y, the database is exported.

12. HELP

Specifies whether to display the help information of the EXPDP command line option. The default value is N. When it is set to Y, the help information of the export option is displayed. Expdp help = y

13. INCLUDE

Specify the object type and related objects to be included during export, INCLUDE = object_type [: name_clause] [,… ]

14. JOB_NAME

Name of the role to be exported. The default value 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 log export file name. If directory_object is not specified, the corresponding option value of DIRECTORY is automatically used for export.

Expdp newhappy/pdmcn.com DIRECTORY = dump DUMPFILE = a. dmp logfile = a. log

16. NETWORK_LINK

Specifies the database chain name. to export a remote database object to a dump file of a local routine, you must set this option.

17. NOLOGFILE

This option is used to specify whether to generate exported log files. The default value is N.

18. PARALLEL

Number of parallel processes that execute the export operation. The default value is 1.

19. PARFILE

Specify the name of the exported parameter file

PARFILE = [directory_path] file_name

20. QUERY

Where condition used to filter and export data

QUERY = [schema.] [table_name:] query_clause

Schema is used to specify the solution name, table_name is used to specify the table name, And query_clause is used to specify the condition limit clause. The QUERY option cannot be used with options such as CONNECT = METADATA_ONLY, EXTIMATE_ONLY, and TRANSPORT_TABLESPACES.

21. SCHEMAS

This scheme is used to specify the execution scheme mode for export. The default is the current user scheme.

22. STATUS

Specifies the detailed status of the exported process. The default value is 0.

23. TABLES

Export in specified table mode

TABLES = [schema_name.] table_name [: partition_name] [,…]

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

24. TABLESPACES

Table space list to export

25. TRANSPORT_FULL_CHECK

This option is used to check the association between the removed tablespace and the unmoved tablespace. The default value is N.

When it is set to Y, the export function checks the complete association of the space directly. If only one tablespace in the tablespace or its index is moved, an error message is displayed. When it is set to N, the export function only checks single-ended dependencies. If the index's tablespace is moved but the table's tablespace is not moved, the error message is displayed. If the table's tablespace is moved, if the tablespace where the index is located is not removed, no error message is displayed.

26. TRANSPORT_TABLESPACES

Specify the execution tablespace mode for export

27. VERSION

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

VERSION = {COMPATIBLE | LATEST | version_string}

For COMPATIBLE, object metadata is generated based on the initialization parameter COMPATIBLE. For LATEST, object metadata is generated based on the actual database version. Version_string is used to specify the database version string. Call EXPDP

When the EXPDP tool is used, its dump file can only be stored in the OS directory corresponding to the DIRECTORY object, rather than directly specifying the OS DIRECTORY where the dump file is located. Therefore, you must first create a DIRECTORY object when using the EXPDP tool. The database user must be granted the permission to use the DIRECTORY object.

 
 

  
  
CREATE DIRECTORY dump dir AS ‘D:DUMP’；  
  
  
GRANT READ, WIRTE ON DIRECTORY dump_dir TO newhappy; 
 
 

Iv. Example

1. Export a table

 
 

  
  
Expdp newhappy/pdmcn.com   DIRECTORY=dump_dir DUMPFILE=tab.dmp TABLES=dept,emp 
 
 

2. Export Solution

 
 

  
  
Expdp newhappy/pdmcn.com  DIRECTORY=dump_dir DUMPFILE=schema.dmp  
  
  
SCHEMAS=system,scott 
 
 

3. Export tablespace

 
 

  
  
Expdp newhappy/pdmcn.com  DIRECTORY=dump_dir DUMPFILE=tablespace.dmp  
  
  
TABLESPACES=user01,user02 
 
 

4. Export the database

 
 

  
  
Expdp newhappy/pdmcn.com  DIRECTORY=dump_dir DUMPFILE=full.dmp FULL=Y 
 
 

This article introduces how to use Oracle 10g Data Pump. I hope this introduction will help you gain some benefits!