Oracle Data Pump Import and export data

Data pump is 10g launch function, personal inverted data like with data pump.

It is easy to convert table space and schema using the remap parameter when importing, and can ignore the server and client character set problems (Exp/imp need to troubleshoot the character set).

Data pump also has inconvenient place, if the remote export import, must install the database server (client not), need to create a path directory in the database (Dba_directories), and the mainstream tool supports EXP/IMP import and export (Plsql Developer), so found that the data pump prevalence is not as high as expected.

The following is a brief introduction to the schema import and export

Export all objects under the Production Library user in schema mode and import the test library.

Note: Eamdb is the production library, EAMPRD is the production library user, the password is EAMPRD

Eamuat is the test library, Eamprduat is the test library user, the password is Eamprduat

First, Export of production libraries (in Sqlplus command-line mode).

1. Log in to the production database as SYS or the system user.

2. Create schema export path (dump_dir name replaceable) and view in Dba_directories

Create directory Dump_dir as '/xxx/xxx '; select * from Dba_directories;

3. The export path and export permissions are authorized to EAMPRD, if the system and other advanced account export, then do not.

Grant Read,write on directory Dump_dir to Eamprd;grant exp_full_database to EAMPRD;

4. Exit Sqlplus, run under Oracle System user, export the corresponding schema, and recommend the second.

EXPDP Eamprd/[email protected] directory=dump_dir DUMPFILE=EAMDB.DMPEXPDP system/[email protected] Directory=dump_dir Dumpfile=eamdb.dmp SCHEMAS=EAMPRD

Export Key parameters:

Version: High to low need to add version=xx.x export some tables tables=xxxx content=metadata_only (as long as the structure) content=data_only (as long as the data)

5. Copy the eamdb.dmp to the test library.

Second, the Test library import

1. Log in to the test library as SYS or system.

2. Create an import table space and a temporary tablespace (name, path, tablespace size, and replace it yourself).

Create tablespace tbs_eamuat datafile '/xxxx/xxxxx/eamuat. DBF ' size 10240M autoextend on next 1024M maxsize 20480m;create temporary tablespace eamuat_temp tempfile '/xxx/xxx/eamuat _temp. DBF ' size 5120M;

3. Set up users and permissions (or do not set up users), it is best to give the user DBA authority when importing, in case there is not enough permission to create some objects at import, attention needs to reclaim their permissions to the users table space.

Create user Eamprduat identified by eamprduat default tablespace tbs_eamuat temporary tablespace eamuat_temp;grant Connect , Resource,create view,create session,dba to Eamprduat;revoke unlimited tablespace from eamprduat;alter user Eamprduat quo Ta Unlimited on tbs_eamuat;

4. Create an import path (put Eamdb.dmp under this path), and authorize.

Create directory Dump_dir as '/xxxxx/xxxxx '; Grant Read,write on the directory Dump_dir to Eamprduat;

5. Import data (run under Oracle System User), note the Remap_schema parameter, please replace it yourself

IMPDP Eamprduat/[email protected] directory=dump_dir dumpfile=eamdb.dmp logfile=impdp.log REMAP_SCHEMA=EAMPRD: Eamprduat;

Import Key parameters:

Remap_schema=eamprd:eamprduat,eamxxx:xxx,xxx:xxx

Remap_tablespace=eamdev:eamxxx,eamxxx:xxx,xxx:xxx

Table_exists_action=replace (replace) append (append)

Exclude=table_statistics This parameter is to cancel the collection of statistics on the table, if the table is too many, do not cancel the word is particularly slow, then you can manually collect, or wait for Oracle to collect automatically.

Execute dbms_stats.gather_table_stats (ownname=> ' USERNAME ',tabname=> ' table_name ', cascade=>true)

If the import error: Ora-31684:object type User: "xxxxx" already exists, regardless, because the user was previously established.

You can also directly remap a non-existent user in the imported statement, automatically generated, with the same password and permissions as when exporting, but its tablespace must be created manually if you do not want to use users.

Oracle Data Pump Import and export data