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