Oracle Data Pump details
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