ORACLE11G and 10g data import and export

Background: Oracle data import and export imp/exp is equivalent to Oracle data restore and backup. The EXP command can export data from a remote database server to a local DMP file, and the IMP command can import the DMP file from a local to a distant database server. The following summarizes the specific import and export steps:

-------------------------------------------------------------------------------------------

#11g新特性数据库使用数据泵expdp &IMPDP Export Import Operation #

-------------------------------------------------------------------------------------------

1. What needs to be done before the export operation

[Email protected] ~]$ Sqlplus/as SYSDBA

sql> Create or replace directory Test_expdir as '/home/oracle/';

Directory created.

Sql> Grant Read,write on the directory test_expdir to user;

Grant succeeded.

-------------------------------------------------------------------------------------------

2. Export operation

1) According to the user guide

[Email protected] ~]$ EXPDP system/[email protected] dumpfile=test_expdp.dmp directory=test_expdir owner=user;

[Email protected] ~]$ IMPDP user/password directory=test_expdir dumpfile=test_expdir.dmp;

2) Parallel Process parallel

[Email protected] ~]$ EXPDP user/[email protected] Directory=test_expdir dumpfile=test_expdir.dmp parallel=40 job_name =test

3) According to the table name guide

[Email protected] ~]$ EXPDP user/[email protected] tables=emp,dept dumpfile=test_expdir.dmp directory=test_expdir;

4) Guided by query criteria

[[email protected] ~]$ EXPDP user/[email protected] directory=test_expdir dumpfile=test_expdir.dmp tables=emp query= ' WHERE deptno=20 ';

5) According to the Table space Guide

[Email protected] ~]$ EXPDP system/manager directory=test_expdir dumpfile=test_expdir.dmp tablespaces=temp,example;

6) Guide the entire database

[Email protected] ~]$ EXPDP system/manager directory=test_expdir dumpfile=full.dmp full=y;

-------------------------------------------------------------------------------------------

3. What to do before the import operation

[Email protected] ~]$ Sqlplus/as SYSDBA

sql> Create or replace directory dpdata1 as '/home/oracle ';

Directory created.

Sql> Grant Read,write on the directory dpdata1 to USER;

Grant succeeded.

-------------------------------------------------------------------------------------------

4. Import operation

1) leads to the specified user

[Email protected] ~]$ IMPDP user/password directory=dpdata1 dumpfile=test_expdir.dmp schemas=user;

2) Change the owner of the table

[Email protected] ~]$ IMPDP user/password directory=dpdata1 dumpfile=test_expdir.dmp tables=username.tablename REMAP_ Schema=user1:user2;

3) Import Table space

[Email protected] ~]$ IMPDP user/password directory=dpdata1 dumpfile=test_expdir.dmp tablespaces=example;

4) Import the database

[Email protected] ~]$ impdb user/password directory=dpdata1 dumpfile=full.dmp full=y;

5) Append Data

[Email protected] ~]$ IMPDP user/password directory=dpdata1 dumpfile=test_expdir.dmp schemas=user table_exists_action

-------------------------------------------------------------------------------------------

Database using EXP&IMP Export import operation before #10g (contains 10g) #

-------------------------------------------------------------------------------------------

Data export:

----------

1, the database test is fully exported, username user, password password, export to D:exp.dmp

[[Email protected] ~]$ exp user/[email protected] file=d:exp.dmp full=y

2, export the system user in the database and the SYS user's table

[[Email protected] ~]$ exp user/[email protected] file=d:exp.dmp owner= (System,sys)

3, export the table test1, test2 in the database

[[Email protected] ~]$ exp user/[email protected] file= d:exp.dmp tables= (TEST1,TEST2)

4. Export the field in table Table1 in the database with the age of "1"

[[Email protected] ~]$ exp user/[email protected] file=d:exp.dmp tables= (table1) query= "where age like ' 1% '"

Data import:

----------

1, import the data from the D:EXP.DMP into the TestDB database.

[[Email protected] ~]$ imp user/[email protected] File=d:exp.dmp

[[Email protected] ~]$ imp user/[email protected] full=y file=d:exp.dmp ignore=y

Note: If the import of some tables already exist, do not add ignore=y, the import will error, meaning that the table will not be imported.

2, import the table table1 from d:exp.dmp into the TestDB database.

[[Email protected] ~]$ imp user/[email protected] file=d:exp.dmp tables= (table1)

-------------------------------------------------------------------------------------------

# # #10g的备注 # #

-------------------------------------------------------------------------------------------

If you want to export files for compression, you can compress the exported DMP files using the Linux compression command. It can also be implemented by adding compress=y to the above command.

-------------------------------------------------------------------------------------------

# # #11g的备注 # #

-------------------------------------------------------------------------------------------

# # #更换表空间

Using Remap_tablespace parameters

--Export all data under the User1 user

EXPDP System/password Directory=exp_dir dumpfile=user1.dmp schemas=user1

Note: If the user data is exported with the SYS user, including the user creation and authorization section, the content is not included with the export of the user.

--The following is the import of all data under the User1 user into the Tablespace TDB1 (formerly TDB2 table space)

IMPDP User/password Directory=user_impdir dumpfile=user1.dmp remap_tablespace=tdb1:tdb2

# # #要想创建用户, you first create a tablespace:

Create tablespace TDB1 datafile '/home/tdb1. DBF ' size 100M autoextend on next 100M maxsize 30000M;

# # #现在建好了名为 ' TDB1 ' tablespace, you can create a user below:

Sql> CREATE USER TEST1 identified by TEST1 account UNLOCK DEFAULT tablespace TDB1 temporary tablespace db_temp;

The default tablespace, ' default Tablespace ', uses the table space name created above: TDB1.

Temporary tablespace ' temporary tablespace ' uses the temporary tablespace name created above: Db_temp.

# # #接着授权给新建的用户:

Sql> GRANT Connect,resource to TEST1;

GRANT Create,select,delete to TEST1;

--Means to grant Connect,resource permissions to TEST1 users

Sql> GRANT DBA to TEST1;

Authorization is successful.

--Means to grant DBA authority to TEST1 users

This article from "10784508" blog, declined reprint!

ORACLE11G and 10g data import and export