Oracle Data Pump Detailed ____oracle

Oracle Data Pump detailed

The Oracle database 10g uses data pump technology that enables DBAs or developers to quickly move database metadata (object definitions) and data to another Oracle databases.
the role of data Pump export import (EXPDP and IMPDP):
1, the implementation of logical backup and logical recovery.
2. Move objects between database users.
3. Moving objects between databases
4, realize the table space move.

Second, the data pump export import and traditional export import difference:
Prior to 10g, traditional export and import using exp tools and IMP tools, starting with 10g, increased data pump export import Tools EXPDP and IMPDP.

Exp and Imp are customer segment utility programs that can be used either on the client or on the server side.
EXPDP and IMPDP are service-side tools that they can use only on the Oracle server and cannot be used on the client side
IMP applies only to the EXP export file, not to the EXPDP export file, IMPDP only to the EXPDP export file, not to the EXP export file.
Data pump export includes export table, export scheme, export table space, export database 4 ways.
ThreeEXPDP Command line Options
1. ATTACH
This option is used to establish an association between a client session and an existing export action. The syntax is as follows
Attach=[schema_name.] Job_name
Schema_name is used to specify the scheme name, job_name to specify the export job name. Note that if you use the Attach option, you cannot specify any other options except for the connection string and the Attach option on the command line, as shown in the following example:
EXPDP newhappy/pdmcn.com 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 the content is set to all, the object definition and all its data are exported. When data_only, only object data is exported, when metadata_only, only the object definition is exported
EXPDP newhappy/pdmcn.com Directory=dump Dumpfile=a.dump
Content=metadata_only
3. DIRECTORY
Specify the directory where dump files and log files are located
Directory=directory_object
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 newhappy/pdmcn.com Directory=dump Dumpfile=a.dump
Create a directory:
CREATE DIRECTORY dump as ' d:dump ';
The query created those subdirectories:
SELECT * from Dba_directories;
4. DumpFile
Use 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, file_name to specify the dump file name. Note that if you do not specify Directory_object, the export tool automatically uses directory objects specified by the directory option
EXPDP newhappy/pdmcn.com DIRECTORY=DUMP1 dumpfile=dump2:a.dmp
5. Estimate
Specifies the disk space partition method used to estimate which table is being exported. The default value is blocks
Extimate={blocks | STATISTICS}
When set to blocks, Oracle uses the number of blocks of data used by the target object multiplied by the size of the data block to estimate the space occupied by the object, and when set to statistics, estimate the object footprint according to the most recent statistic
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, and the default value is N
Extimate_only={y | N
When set to Y, the export effect estimates only the disk space occupied by the object, but does not perform an export job, when n, not only estimating the disk space occupied by the object, but also performing an export operation.
EXPDP newhappy/pdmcn.com estimate_only=y Nologfile=y
7. EXCLUDE
This option specifies the release of the object type or related object to exclude when performing an action
Exclude=object_type[:name_clause] [,...]
Object_type is used to specify the type of object to exclude, name_clause to specify the specific object to exclude. Exclude and include cannot be used concurrently
EXPDP newhappy/pdmcn.com directory=dump dumpfile=a.dup Exclude=view
8. FILESIZE

Specifies the maximum size of the exported file, which defaults to 0 (indicates file size is not limited)
9. Flashback_scn
Specify table data to export a specific SCN time
Flashback_scn=scn_value
Scn_value is 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
Flashback_time
Specify the export of table data for a specific point in time
Flashback_time= "To_timestamp (time_value)"
EXPDP newhappy/pdmcn.com 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 to N
Full={y | N
When Y, the identity performs the database export.
Help
Specifies whether to display help information for the EXPDP command-line option, which defaults to n
When set to Y, help information for the export option is displayed.
EXPDP help=y
INCLUDE
Specify the types of objects and related objects to include when exporting
INCLUDE = Object_type[:name_clause] [,...]
Job_name
Specifies the name of the function to export, default to Sys_xxx
Job_name=jobname_string
LOGFILE
Specifies the name of the export log file file, with the default name Export.log
Logfile=[directory_object:]file_name
Directory_object is used to specify the directory object name, file_name to specify the export log filename. If you do not specify Directory_object. The export action automatically uses the appropriate option values for your directory.
EXPDP newhappy/pdmcn.com directory=dump dumpfile=a.dmp logfile=a.log
Network_link
Specifies the database chain name, and you must set this option if you want to export the remote database object to a dump file in a local routine.
Nologfile
This option specifies that the export log file is prevented 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 exported parameter file
Parfile=[directory_path] File_name
QUERY
Used to specify where conditions for filtering exported data
Query=[schema.] [TABLE_NAME:] Query_clause
Schema is used to specify the scenario name, table_name to specify the table name, and query_clause to specify the conditional restriction clause. The query option cannot be used in conjunction with options such as Connect=metadata_only,extimate_only,transport_tablespaces.
EXPDP newhappy/pdmcn.com Directory=dump dumpfiel=a.dmp
Tables=emp query= ' WHERE deptno=20 '
SCHEMAS.
This scenario is used to specify the execution scenario schema export, which defaults to the current user scenario.
STATUS
Specifies the detailed status of the export action 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 scenario name, table_name to specify the exported table name, partition_name to specify the partition name to export.
Tablespaces
Specify the list of table spaces to export
Transport_full_check
This option specifies how to check the relationship between the moved tablespace and the unbound table space, by default N.
When set to Y, the export action checks the full association of the table space directly, and displays an error message if the tablespace or the tablespace in which the index is located has only one tablespace moved. When set to N, the export only checks for single-ended dependencies, and if you move the tablespace of the index, but the table space is not moved, an error message is displayed, and if the table space in which the move table is located, the table space for the index is not moved.
Transport_tablespaces
Specifies that the table space mode export is performed
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 according to the initialization parameter compatible, and when latest, object metadata is generated from the actual version of the database. Version_string is used to specify the database version string. Call EXPDP
When using the EXPDP tool, its dump file can only be stored in the corresponding OS directory of the directory object, not directly to the OS directory where the dump file resides. Therefore, when you use the EXPDP tool, you must first establish a directory object. And you need to grant database users permission to use directory objects.
CREATE DIRECTORY dump dir as ' d:dump ';
GRANT READ, Wirte on DIRECTORY dump_dir to Newhappy;
Iv. Examples

1. Export table
EXPDP newhappy/pdmcn.com Directory=dump_dir dumpfile=tab.dmp tables=dept,emp
2. Export scheme
EXPDP newhappy/pdmcn.com Directory=dump_dir dumpfile=schema.dmp
Schemas=system,scott
3. Export Table Space
EXPDP newhappy/pdmcn.com Directory=dump_dir dumpfile=tablespace.dmp
Tablespaces=user01,user02
4. Export Database
EXPDP newhappy/pdmcn.com directory=dump_dir dumpfile=full.dmp full=y PS: I built a few Oracle QQ group, Welcome to join the database enthusiasts. Oracle Expert QQ1 Group: 60632593 Oracle Expert QQ2 Group: 60618621 Oracle Expert QQ3 Group: 23145225

==========================end=======================