EXPDP/IMPDP of Oracle Database backup and Restore tool

Things to be aware of when using EXPDP and IMPDP:

Exp and IMP are client tool programs that can be used either on the client or on the server side.

EXPDP and IMPDP are server-side utilities that can only be used on the Oracle server and not on the client side. (cannot be run in SQL Plus, run under cmd)

IMP is only available for exp exported files, not for EXPDP export files, IMPDP only for EXPDP exported files, not for exp export files.

EXPDP Export

One: Pre-preparation for export:

1. Create a Directory object:

CREATE DIRECTORY dump_dir as ' c:\dump ';

2. Create the appropriate directory on the operating system.

3. Read and write access to the directory to the user:

GRANT READ, WRITE on DIRECTORY Dump_dir to Scott;

Two: Exported models

1. Export the table

EXPDP scott/tiger directory=dump_dir dumpfile=tab.dmp logfile=testexpdp.log tables=dept, EMP

2. Export Scenario (user)

EXPDP scott/tiger directory=dump_dir dumpfile=schema.dmp logfile=testexpdp.log Schemas=system,scott

3. Export Table Space

EXPDP System/manager Directory=dump_dir Logfile=testexpdp.log dumpfile=tablespace.dmp

Tablespaces=user01,user02

4. Export the database

EXPDP system/manager directory=dump_dir dumpfile=full.dmp logfile=testexpdp.log FULL=Y

Three: EXPDP command line options:

1. ATTACH

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

Attach=[schema_name.] Job_name

The 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 other options except the connection string and the Attach option in 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 the object data is exported, and only the object definition is exported as Metadata_only

EXPDP Scott/tiger Directory=dump Dumpfile=a.dump

Content=metadata_only

3. 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

EXPDP Scott/tiger Directory=dump Dumpfile=a.dump

To create a directory:

CREATE

The query created those subdirectories:

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 [,....]

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

EXPDP Scott/tiger DIRECTORY=DUMP1 dumpfile=dump2:a.dmp

5. ESTIMATE

Specifies the method by which to estimate the disk space consumed by the exported table. The default value is blocks

Extimate={blocks | STATISTICS}

When set to blocks, Oracle calculates the object footprint based on recent statistics by multiplying the number of data blocks occupied by the target object by the size of the data block to estimate the amount of space the object occupies, when set to 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, the default value is N

Extimate_only={y | N

When set to Y, the export only estimates the disk space occupied by the object, not the export job, which, when N, not only estimates the disk space occupied by the object, but also performs an export operation.

EXPDP Scott/tiger estimate_only=y Nologfile=y

7. EXCLUDE

This option is used to specify whether to dispose of object types or related objects when an operation is performed

Exclude=object_type[:name_clause] [,....]

The object_type is used to specify the type of object to exclude, and name_clause is used to specify the specific object to exclude. Exclude and include cannot be used at the same time

EXPDP Scott/tiger directory=dump dumpfile=a.dup Exclude=view

8. FILESIZE

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

9. Flashback_scn

Specify table data to export specific SCN moments

Flashback_scn=scn_value

The scn_value is used to identify the SCN value. FLASHBACK_SCN and Flashback_time cannot be used simultaneously

EXPDP Scott/tiger Directory=dump dumpfile=a.dmp

flashback_scn=358523

Ten. 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:35:00 ', ' dd-mm-yyyy HH24:MI:SS ')"

One. Full

Specify database schema export, default = N

Full={y | N

Is Y, the identity performs a database export.

Help

Specifies whether to display help information for the EXPDP command-line option, default = N

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

EXPDP help=y

. INCLUDE

Specify the type of objects to include in the export and related objects

INCLUDE = Object_type[:name_clause] [,...]

Job_name.

Specifies the name of the action to export, which defaults to Sys_xxx

Job_name=jobname_string

LOGFILE.

Specifies the name of the exported log file file, the default name is Export.log

Logfile=[directory_object:]file_name

The 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 appropriate option values for your directory.

EXPDP Scott/tiger directory=dump dumpfile=a.dmp logfile=a.log

Network_link.

Specifies the database chain name, which must be set if you want to export the remote database object to a dump file for a local routine.

Nologfile.

This option specifies that the export log file is prohibited from being generated and the default value is N.

PARALLEL.

Specifies the number of parallel processes that perform the export operation, with a default value of 1

Parfile.

Specify the name of the export parameter file

Parfile=[directory_path] File_name

. QUERY

Used to specify where conditions to filter the 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 a conditional restriction clause. The query option cannot be used in conjunction with options such as Connect=metadata_only,extimate_only,transport_tablespaces.

EXPDP Scott/tiger Directory=dump dumpfiel=a.dmp

Tables=emp query= ' WHERE deptno=20 '

SCHEMAS.

This scenario is used to specify the execution scheme pattern export, which is implicitly considered the current user scenario.

STATUS

Specifies the verbose status of the export process, with a default value of 0

TABLES.

Specify table Schema Export

Tables=[schema_name.] table_name[:p artition_name][,...]

Schema_name is used to specify the scheme 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 export.

Tablespaces.

Specify the table space list to export

Transport_full_check.

This option is used to specify the check method for the moved table space and the non-moving Tablespace association relationship, by default, N.

When set to Y, the export checks the full association of the table space directly, and an error message is displayed if only one tablespace is moved in the table space where the tablespace or its index resides. When set to N, the export only checks for single-ended dependencies, if the table space of the index is removed, but the table space is not moved. An error message is displayed, and if the table space of the move table is not moved, the table space in which the index is located is not displayed.

Transport_tablespaces.

Specify to perform table space mode export

VERSION

Specifies the database version of the exported object, the default value is compatible.

version={compatible | LATEST | Version_string}

When compatible, the object metadata is generated based on the initialization parameter compatible, and the object metadata is generated based on the actual version of the database when latest. Version_string is used to specify the database version string.

EXPDP Import

One: Pre-import preparation (if you are restoring the old database, this step can be omitted):

1. Create a Directory object:

CREATE DIRECTORY Dump_dir as ': \dump ';

2. Create the appropriate directory in the operating system.

3. Assign directory Read and write permissions to the user

GRANT READ, Wirte on DIRECTORY Dump_dir to Scott;

Two: Imported Models

1. Importing tables

IMPDP Scott/tiger Directory=dump_dir dumpfile=schema.dmp Schemas=scott

IMPDP system/manage directory=dump_dir dumpfile=tab.dmp tables=scott.dept,scott.emp REMAP_SCHEMA=SCOTT:SYSTEM

The first method is to import the dept and EMP tables into the Scott scenario, and the second method represents the system scenario in which the Dept and EMP tables are imported.

Note: If you want to import a table into another scenario, you must specify the Remap schema option.

If the current database is restored, the Remap_schema parameter is omitted;

In addition, if the parameter has content=data_only, the value of the self-Increment ID field will change when the table is restored, in order to prevent the ID from changing, it is necessary to drop the original table first and remove the content parameter.

2. Import scenario (user)

IMPDP Scott/tiger Directory=dump_dir dumpfile=schema.dmp Schemas=scott

IMPDP System/manager Directory=dump_dir dumpfile=schema.dmp

Schemas=scott Remap_schema=scott:system

3. Import Table Space

IMPDP System/manager Directory=dump_dir Dumpfile=tablespace.dmptablespaces=user01

4. Import the database

IMPDP System/manager Directory=dump_dir dumpfile=full.dmp full=y

Three: IMPDP command line options:

The IMPDP command-line options are much the same as the EXPDP, and are different:

1,remap_datafile

This option is used to convert the source data file name to the target data file name, which may be required when moving tablespaces between different platforms.

Remap_datafiel=source_datafie:target_datafile

2,remap_schema

This option is used to load all objects of the source scheme into the target scenario.

Remap_schema=source_schema:target_schema

3,remap_tablespace

Import all objects from the source table space into the target table space

Remap_tablespace=source_tablespace:target:tablespace

4.reuse_datafiles

This option specifies whether to overwrite existing data files when the tablespace is established. Default = N

Reuse_datafiels={y | N

5.skip_unusable_indexes

Specifies whether the import skips an index that is not available and defaults to n

6,sqlfile

Specifies that the import of the index DDL operation to be specified is written to the SQL script

Sqlfile=[directory_object:]file_name

IMPDP Scott/tiger directory=dump dumpfile=tab.dmp sqlfile=a.sql

7.streams_configuration

Specifies whether to import streaming metadata (stream matadata), with the default value Y.

8,table_exists_action

This option specifies the action to be performed by the import job when the table already exists, and the default is skip

Tabble_exists_action={skip | APPEND | TRUNCATE | Frplace}

When this option is set to skip, the import job skips the existing table to process the next object, and when set to append, the data is appended, and when truncate, the import job truncates the table and appends new data to it, and when set to replace, the import job deletes the existing table. Rebuilding table sickness append data, note that the TRUNCATE option is not applicable with cluster table and Network_link options

9.TRANSFORM

This option specifies whether to modify the DDL statement that establishes the object

Transform=transform_name:value[:object_type]

Transform_name is used to specify the conversion name, where segment_attributes is used to identify segment properties (physical properties, storage properties, tablespace, logs, and so on), storage is used to identify segment storage properties, and value is used to specify whether to include segment properties or segment storage properties A object_type used to specify the object type.

IMPDP Scott/tiger Directory=dump dumpfile=tab.dmp

Transform=segment_attributes:n:table

10.transport_datafiles

This option is used to specify the data files to be imported to the target database when moving space

Transport_datafile=datafile_name

Datafile_name used to specify data files that are copied to the target database

IMPDP System/manager Directory=dump dumpfile=tts.dmp

transport_datafiles= '/user01/data/tbs1.f '

EXPDP/IMPDP of Oracle Database backup and Restore tool