-- 21.1DataPump tool DataPump introduces the datapump (Data Pump) tool from oracledatabase10g, which provides a server-based data export and import user program. All datapump are used as a server process, and data no longer must be processed by a client program. DataPump tool for exporting and importing Oracle Data
-- 21.1 Data Pump tool Data Pump introduced the data pump tool from oracledatabase 10 Gb, which provides a server-based Data export and import user program. All data pump is used as a server process, and data no longer must be processed by a client program. Data Pump tool for exporting and importing Oracle Data
-- 21.1 Data Pump Tool
Data Pump
The data pump (data pump) tool was introduced from oracledatabase 10 Gb, which provides a server-based data export and import user program.
All data pump is used as a server process, and data no longer must be processed by a client program. Data Pump tool Export and Import implementation
Data transmission between Oracle databases.
The Data Pump tool contains Data Pump Export and Data Pump Import. The command line client program used is expdp and impdp.
Note]
Before 10 Gb, the traditional export and import tools respectively use the exp tool and imp tool. Since oracle database 10 Gb, not only the original
The exp and imp tools also provide the data pump export import tools expdp and impdp.
Any features available in traditional export and import applications are available in data pump Starting from 11g.
When using expdp and impdp tools, pay attention to the following points:
1 exp and imp are client tool programs that can be used either on the client or on the server.
2 expdp and impdp are server tool programs, which can only be used on the oracle server and cannot be used by the client.
3 imp only applies to exp exported files, not expdp exported files; impdp only applies to files exported from expdp
Is not applicable to export exp files. The data pump export and import files are similar to the traditional import/export
The file exported by the application is not compatible.
[Pump features]
Compared with the original export and import programs, oracle's data pump tool has the following features:
1. In an export or import job, you can control the number of parallel threads used for this job.
2. You can export and import data on the network without using a dump file set.
3. If the job fails or stops, you can restart a data pump job. You can also resume the Export and Import job.
4. A client program can be used to connect to or quit a running job.
5. The space estimation capability does not need to be exported.
6. You can specify the database version of the exported import object. Allows you to control the versions of exported import objects to make them compatible with earlier versions of the database.
[Pump data dictionary]
Data Dictionary description
Dba_datapump_jobs: displays information about the data pump job running. You can also use the user_datapump_jobs variable.
Dba_datapump_sessions provides session-level information for Data Pump jobs
Datapump_paths provides a series of valid object types that can be associated with the include or exclude parameters of export or impdp.
Dba_directories provides a series of defined Directories
-- 21.2 preparations for using datapump
1. configure it in the bin directory of the Environment Variable squadron. By default, the corresponding environment variables are automatically configured when the oracle database is installed,
For example, D:/oracle/product/10.2.0/db_1/BIN
2. Check whether the expdp.exeand impdp.exe files exist in the binfolder of the Oracle installation folder.
3. Create an external directory.
Data pump requires that you create a directory for the data files and log files to be created and read.
Used external directory. When creating a directory object in oracle, you can use create directory
Statement.
[Instance]
1. Check whether the bin directory exists in the advanced environment variable-pathpath.
2. Check whether the expdp.exe1_impdp.exe file exists.
3. Create a directory
C:/> sqlplus/nolog
SQL> conn sys/sys as sysdba
SQL> create directory mypump as 'd:/app/temp ';
SQL> grant read, write on directory mypump to scott;
SQL> select * from dba_directories; query all directories
-- 21.3 data pump export option
Parameter description
Help displays online help for export. The default value is n.
Compress specifies the data to be compressed. Optional values include all, data_only, metadata_only, and none.
The content to be filtered and exported. Optional values include all, data_only, and metadata_only.
Directory specifies the target directory for log files and dump file Sets
Dumpfile specifies the name and directory for the dump file
Encryption Level output by encryption. Optional values: all, data_only, encrypted_columns_only, etadata_only, and none
Exclude exclusion of exported objects and Data
Flashback_sch is the system change number that the database flashes back during the export process.
Flashback_time is the timestamp used to flash back during Database Export.
Include specifies the criteria for exporting objects and Data
Logfile: the name of the exported log and the name of the optional Directory.
Parfile specifies the parameter file name
Query filters rows from a table during export
Reuse_dmupfiles overwrite existing dump files
Status: displays the detailed status of the data pump job.
Attach connects a client session to a currently running data pumpexport job.
Transportable exports metadata only in Table mode
Full notifies data pump in full mode to export all data and metadata.
Schemas name the mode to be exported in a schemas mode Export
Tables lists the tables and partitions that will be exported in one table mode.
Tablespaces: list the tablespaces to Be Exported
Transport_tablespaces
Specify a transportable tablespace mode for export
Transport_full_check
Whether to verify that the tablespace being exported is a self-contained set
Command list in expdp Interaction Mode
Parameter description
Add_file: Add a dump file to the dump set.
Exit_client exits the Client Session and keeps the job running
Kill_job separation and deletion of jobs
Paraliel changes the number of worker processes of the user data pump export job
Start_job start and resume the current job
Status: displays the job status of data pump export.
Whether reuse_dmpfiles overwrites existing dump files. When set to y, the existing dump file will be overwritten;
When the default value n is used, an error occurs if the dump file already exists.
Stop_job closes the executed job in sequence and exits the client. Stop_job = immediate will immediately shut down the Data Pump job
[Instance]
1. Go to the Directory D:/> cd D:/oracle/product/10.2.0/db_1/BIN.
2. expdp help = y lists all parameters.
3. In Interactive Mode
/BIN> expdp system/system
Wait a moment, press ctrl c to enter the export Mode
Export> stop_job
/BIN>
-- 21.4 data export
Export Mode
Mode parameter description
Full export the entire database exp_full_database
Schema schemas has the exp_full_database role for exporting data and metadata in one or more user modes. You can export data in any mode,
Otherwise, you can only export your own mode.
Table tables exports a group of specific tables with the exp_full_database role. You can export tables in any mode.
Tablespace tablespaces export data of one or more tablespaces exp_full_database
Transportable transport_tablespaces
Tablespace exports the metadata of objects in the tablespace exp_full_database
[Instance]
1. Enter the bin directory.
2. Table mode Export
Expdp scott/scott_2009 directory = mypump dumpfile = expdptab. dmp tables = dept, emp
(Select * from dba_tablespaces; alter tablespace testspace online ;)
3. Export in full database mode
Expdp system/system directory = mypump dumpfile = expdp. dmp full = y
4. Export in schema mode
Expdp system/system directory = mypump dumpfile = expdp. dmp schemas = scott nologfile = y
5. Export tablespace data
Expdp system/system directory = mypump dumpfile = expdpspace. dmp tablespaces = users
6. Export movable tablespace
1. Set the user tablespace to read-only, and then execute metadata export for the movable tablespace.
SQL> alter tablespace users read only;
BIN> expdp system/system directory = mypump dumpfile = exptrans. dmp transport_tablespaces = users;
SQL> alter tablespace users online;
-- 21.5 use export parameters
1. Use the exclude Parameter
You can specify the exclude parameter in the expdp command to exclude objects from data pump export. If an object is excluded
All objects related to it.
The format of exclude is as follows:
Exclude = object_type [: name_clause] [,...]
Object_type can be any oracle object type, including permissions, indexes, and tables. name_clause is used to limit the returned values.
2. Use the exclude Parameter
If you use include in data pump export, you can export only the objects that meet the requirements. All other objects are excluded. Use include
The parameter format is as follows:
Include = object_type [: name_clause] [,...]
3. Use query parameters
For objects that meet the exclude and include criteria, all rows of the object will be exported. In this case, you can use the query parameter to limit the returned rows,
The format of the query parameter is as follows:
Query = [schema.] [table_name:] query_clause
Schema specifies the user name or user mode name to which the table belongs;
Table_name specifies the table name; query_clause is used to specify the conditions.
[Instance]
1 exclude parameter. Note that the table name must be in uppercase.
Expdp system/system directory = mypump dumpfile = expdpexclude. dmp tablespaces = users exclude = table: "in ('emp')" exclude = table: "in ('dept ')"
2. The include parameter. Note that the table name must be in uppercase. Export index information of users tablespace
Expdp system/system directory = mypump dumpfile = expdpinclude. dmp tablespaces = users include = index
3 query parameters
Expdp system/system directory = mypump dumpfile = expdpquery. dmp tables = scott. dept query =/"where deptno = 10 /"
-- 21.6 Data Pump import option
Keyword description (default)
------------------------------------------------------------------------------
ATTACH connects to an existing job, for example, ATTACH [= job name].
CONTENT specifies the data to be loaded. The valid keyword is:
(ALL), DATA_ONLY and METADATA_ONLY.
DIRECTORY is the DIRECTORY object used for dumping files, log files, and SQL files.
DUMPFILE: the list of dump files to be imported from (expdat. dmp,
For example, DUMPFILE = scott1.dmp, scott2.dmp, dmpdir: scott3.dmp.
ENCRYPTION_PASSWORD is the key word used to access the encrypted column data.
This parameter is invalid for network import jobs.
ESTIMATE calculates the estimated job value. The valid keyword is:
(BLOCKS) and STATISTICS.
EXCLUDE is used to EXCLUDE specific object types, such as EXCLUDE = TABLE: EMP.
FLASHBACK_SCN is used to set the session snapshot back to the SCN in the previous state.
FLASHBACK_TIME is used to obtain the SCN time closest to the specified time.
FULL import all objects from the source (Y ).
HELP displays the HELP message (N ).
INCLUDE includes specific object types, such as INCLUDE = TABLE_DATA.
The name of the import job to be created in JOB_NAME.
LOGFILE log File Name (import. log ).
The name of the remote database to which NETWORK_LINK is linked.
NOLOGFILE does not write log files.
PARALLEL changes the number of active workers of the current job.
PARFILE specifies the parameter file.
QUERY is the predicate clause used to import a subset of a table.
REMAP_DATAFILE redefines data file references in all DDL statements.
REMAP_SCHEMA loads objects in one scheme to another.
REMAP_TABLESPACE remaps a tablespace object to another tablespace.
REUSE_DATAFILES if the tablespace already exists, initialize it (N ).
List of SCHEMAS import schemes.
SKIP_UNUSABLE_INDEXES skips indexes that are set to useless indexes.
SQLFILE writes all SQL DDL statements to the specified file.
When the default value (0) shows the new STATUS when available,
The frequency (in seconds) job status to be monitored.
STREAMS_CONFIGURATION enable stream metadata Loading
The operation performed when the TABLE_EXISTS_ACTION import object already exists.
Valid keywords: (SKIP), APPEND, REPLACE, and TRUNCATE.
TABLES identifies the list of TABLES to be imported.
TABLESPACES identifies the list of TABLESPACES to be imported.
TRANSFORM must be applied to the metadata conversion of applicable objects.
Valid conversion keywords: SEGMENT_ATTRIBUTES, STORAGE
OID and PCTSPACE.
TRANSPORT_DATAFILES: list of data files imported in transmission mode.
TRANSPORT_FULL_CHECK verifies the storage segments of all tables (N ).
The list of tablespaces from which TRANSPORT_TABLESPACES will load metadata.
Valid only for the NETWORK_LINK mode import operation.
The VERSION of the object to be exported. The valid keyword is:
(COMPATIBLE), LATEST or any valid database version.
Valid only for NETWORK_LINK and SQLFILE.
The following commands are valid in interactive mode.
Note: abbreviations are allowed.
Command description (default)
------------------------------------------------------------------------------
CONTINUE_CLIENT returns to record mode. If the job is idle, the job is restarted.
EXIT_CLIENT exits the Client Session and keeps the job running.
HELP summarizes interactive commands.
KILL_JOB separates and deletes jobs.
PARALLEL changes the number of active workers of the current job.
PARALLEL = .
START_JOB start/restore the current job.
START_JOB = SKIP_CURRENT will be skipped before the job starts
Any operation performed when the job is stopped.
When the default value (0) shows the new STATUS when available,
The frequency (in seconds) job status to be monitored.
STATUS [= interval]
STOP_JOB closes the executed job in sequence and exits the client.
STOP_JOB = IMMEDIATE will be closed immediately
Data pump operation.
[Instance]
Run impdp help = y.
1. Table mode Import
Impdp scott/scott_2009 directory = mypump dumpfile = expdptab. dmp tables = dept, emp table_exists_action = replace
2. Full-Database Import
Impdp system/system directory = mypump dumpfile = expdp. dmp full = y
3. Import in schema mode
Impdp system/system directory = mypump dumpfile = expdp. dmp schemas = scott nologfile = y
4. tablespace Data Import
Impdp system/system directory = mypump dumpfile = expdpspace. dmp tablespaces = users
5. removable tablespace Import
1. Set the user tablespace to read-only, and then execute metadata export for the movable tablespace.
SQL> alter tablespace users read only;
BIN> impdp system/system directory = mypump dumpfile = exptrans. dmp transport_tablespaces = users;
SQL> alter tablespace users online;
