Oracle Data Pump details

Comparison Between Data Pump and conventional method: Data Pump is introduced at Oracle10g. Compared with traditional expimp, data pump can work separately, and can suspend and resume Import and Export tasks.

Comparison Between Data Pump and conventional method: Oracle introduces Data Pump at 10 Gb. Compared with traditional exp/imp, data pump can separate jobs, and can suspend and resume Import and Export tasks.

Comparison Between Data Pump and conventional method: Oracle introduces Data Pump at 10 Gb. Compared with traditional exp/imp, data pump can separate jobs, and can suspend and resume Import and Export tasks, you can restart a failed job from the failed job point and control the version of the imported and exported job)
1. expdp command:
1> common parameters:

Content: whether to export data or only metadata (table and index creation statements). Options include ALL, DATA_ONLY, and METADATA_ONLY.
Directory: directory object of dumpfile and logfile
Dumpfile/logfile
Exclude: do not export the specified object (those dependent on these objects will not be exported) eg: exclude = table: "like 'dba _ % '"
Include: exports the specified object. It is used in the same way as exclude.
Job_name: defines the name of the export job.
Attach: attach to an existing job that is already running for Job Management.
Full: The default value is n, and full = y indicates that all objects are exported.
Nologfile: = y indicates that no log file expdp is written. The default value is n.
Tables/tablespaces: Run and export the specified table/tablespace, including all objects that are dependent on this table/tablespace.
Version: restrict the objects to be exported to the database of the specified version.
2> example:
-- Create an export directory and grant permissions to related users
Create or replace directory dumpdir as '/data/backup ';
Grant all on directory dumpdir to system;
Grant export full database to system;

-- Export all databases: export all database objects
Expdp system/pwd @ ip/ORACLE_SID dumpfile = dumpdir: db _ % U. dmp logfile = dumpdir: db_exp _ % U. log job_name = exp_db full = y
-- Export the specified tablespace: export the objects of the specified tablespace
Expdp system/pwd @ ip/ORACLE_SID tablespaces = tbs dumpfile = dumpdir: tbs_01.dmp logfile = dumpdir: tbs_exp_01.log job_name = exp_tbs
-- Export a specific object: export a specified object (the table starting with function, procedure, and XT in the following example)
Expdp system/pwd @ ip/ORACLE_SID include = function include = procedure include = table: "like 'xt % '" dumpfile = dumpdir: tab_01.dmp nologfile = y job_name = exp_tab
-- Export table: export the objects of the specified table
Expdp system/pwd @ ip/ORACLE_SID tables = XTCS, XTDW dumpfile = dumpdir: tab_02.dmp nologfile = y job_name = exp_tab01

2. impdp command:
1> common parameters:

Content: whether to import data or only metadata (table and index creation statements). Options include ALL, DATA_ONLY, and METADATA_ONLY.
Directory: directory object of dumpfile and logfile
Dumpfile/logfile
Include: import the specified object. For example: exclude = table: "like 'dba _ % '"
Job_name: defines the name of the import job.
Attach: attach to an existing job that is already running for Job Management.
Full: The default value is n, and full = y indicates that all objects are exported.
Nologfile: = y indicates that no log file expdp is written. The default value is n.
Tables/tablespaces: Run and export the specified table/tablespace, including all objects that are dependent on this table/tablespace.
Table_exists_action: defines the operations performed when the table already exists. Optional:

= Skip (default value) directly skips the current object and processes the next object without loading data
= Append: writes data to the current table after append.
= Replace: Delete the table first, create the table, and load data.
= Truncate clears all data rows before writing
2> example:
-- Create an import directory and grant permissions to related users
Create or replace directory pumpdir as '/data/backup ';
Grant all on directory pumpdir to system;
Grant import full database to system;

-- Import all databases: import all database objects
Impdp system/pwd @ ip/ORACLE_SID dumpfile = pumpdir: db_01.dmp logfile = pumpdir: db_imp _ % U. log job_name = imp_db full = y
-- Import the specified tablespace: import the objects of the specified tablespace.
Impdp system/pwd @ ip/ORACLE_SID tablespaces = tbs dumpfile = pumpdir: tbs_01.dmp logfile = pumpdir: tbs_imp_01.log job_name = exp_tbs table_exists_action = truncate
-- Import a specific object: import a specified object (the table starting with function, procedure, and XT in the following example)
Impdp system/pwd @ ip/ORACLE_SID include = function include = procedure include = table: "like 'xt % '" dumpfile = pumpdir: tab_01.dmp nologfile = y job_name = imp_tab
-- Import table: import the object of the specified table
Impdp system/pwd @ ip/ORACLE_SID tables = XTCS, XTDW dumpfile = pumpdir: tab_02.dmp nologfile = y job_name = imp_tab01

Installing Oracle 12C in Linux-6-64

RHEL6.4 _ 64 install a single instance Oracle 12cR1

New Features of Oracle 12C: Paging Query

12 new features of Oracle 12C