使用exp/imp导出导入数据（逻辑备份恢复）

使用exp/imp导出导入数据：

---创建新的目录对象存放导出的数据：
--创建目录对象：
SQL> create directory homedir as '/home/oracle/homedir';
Directory created.

--授予目录对象给两个用户：
SQL> grant read,write on directory homedir to scott;
Grant succeeded.

SQL> grant read,write on directory homedir to suxing;
Grant succeeded.

---使用exp导出表emp：
--导出策略:exp scott/tiger tables=emp  file=emp.dmp 
[oracle@enmo homedir]$ exp scott/tiger tables=emp  file=emp.dmp 
Export: Release 11.2.0.4.0 - Production on Wed Nov 2 20:10:17 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table                            EMP         14 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
Export terminated successfully with warnings.
[oracle@enmo homedir]$ 
#导出成功。

--查看导出文件：
[oracle@enmo homedir]$ ls
emp.dmp
[oracle@enmo homedir]$ ll
total 16
-rw-r--r-- 1 oracle oinstall 16384 Nov  2 20:10 emp.dmp
[oracle@enmo homedir]$ 

---导出Scott模式schema：
--导出策略1：exp scott/tiger owner=scott file=scott.dmp
[oracle@enmo homedir]$ exp scott/tiger owner=scott file=scott.dmp
Export: Release 11.2.0.4.0 - Production on Wed Nov 2 20:13:38 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user SCOTT 
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user SCOTT 
About to export SCOTT's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export SCOTT's tables via Conventional Path ...
. . exporting table                          BONUS          0 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table                           DEPT          4 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table                            EMP         14 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table                      HISLOADER          3 rows exported
. . exporting table                       MYLOADER          7 rows exported
. . exporting table                       SALGRADE          5 rows exported
EXP-00091: Exporting questionable statistics.
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully with warnings.
[oracle@enmo homedir]$ 
#导出成功。导出schema时候，是导出用户的所有数据库对象。

--导出schema策略2：exp system/oracle owner=scott file=scotts.dmp   
[oracle@enmo homedir]$ exp system/oracle owner=scott file=scotts.dmp  
Export: Release 11.2.0.4.0 - Production on Wed Nov 2 20:16:37 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user SCOTT 
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user SCOTT 
About to export SCOTT's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export SCOTT's tables via Conventional Path ...
. . exporting table                          BONUS          0 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table                           DEPT          4 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table                            EMP         14 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table                      HISLOADER          3 rows exported
. . exporting table                       MYLOADER          7 rows exported
. . exporting table                       SALGRADE          5 rows exported
EXP-00091: Exporting questionable statistics.
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully with warnings.
[oracle@enmo homedir]$ 
#导出成功。可以看到两个策略导出的结果是一样的，也就是说导出模式时候
DBA角色的用户以及模式对应的用户都可以导出模式里所有的对象。

--查看导出的文件：
[oracle@enmo homedir]$ ll
total 572
-rw-r--r-- 1 oracle oinstall  16384 Nov  2 20:10 emp.dmp
-rw-r--r-- 1 oracle oinstall 516096 Nov  2 20:24 myspace.dmp
-rw-r--r-- 1 oracle oinstall  24576 Nov  2 20:13 scott.dmp
-rw-r--r-- 1 oracle oinstall  24576 Nov  2 20:16 scotts.dmp
[oracle@enmo homedir]$ 

---导出MySpace表空间：
--导出策略：exp system/oracle tablespaces=myspace file=myspace.dmp
[oracle@enmo homedir]$ exp system/oracle tablespaces=myspace file=myspace.dmp
Export: Release 11.2.0.4.0 - Production on Wed Nov 2 21:36:42 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
About to export selected tablespaces ...
For tablespace MYSPACE ...
. exporting cluster definitions
. exporting table definitions
. . exporting table                       YOURTEST          0 rows exported
. . exporting table                             T1        100 rows exported
. exporting referential integrity constraints
. exporting triggers
Export terminated successfully without warnings.
[oracle@enmo homedir]$ 
#导出成功。

--查看导出的文件：
[oracle@enmo homedir]$ ll
total 572
-rw-r--r-- 1 oracle oinstall  16384 Nov  2 20:10 emp.dmp
-rw-r--r-- 1 oracle oinstall 516096 Nov  2 20:24 myspace.dmp
-rw-r--r-- 1 oracle oinstall  24576 Nov  2 20:13 scott.dmp
-rw-r--r-- 1 oracle oinstall  24576 Nov  2 20:16 scotts.dmp
[oracle@enmo homedir]$ 

---还有用exp导出全库，策略：exp system/oracle full=y  file=wholedatabae.dmp

---导入表emp：
--导入策略：imp scott/tiger tables=emp file=emp.dmp 
--查看表emp数据：
SQL> select count(*) from emp;
  COUNT(*)
----------
        14
--删除表emp：        
SQL> drop table emp purge;
Table dropped.
               
---导入表emp到Scott用户：
[oracle@enmo homedir]$ imp scott/tiger tables=emp file=emp.dmp
Import: Release 11.2.0.4.0 - Production on Wed Nov 2 20:51:04 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V11.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. importing SCOTT's objects into SCOTT
. importing SCOTT's objects into SCOTT
. . importing table                          "EMP"         14 rows imported
About to enable constraints...
Import terminated successfully without warnings.
[oracle@enmo homedir]$ 
#导入完成。

--查看emp表：
SQL> select count(*) from emp;
  COUNT(*)
----------
        14
#可知导入成功。
--------------------------------------------------------------------------------------
---导入表空间myspace:
--导入策略：imp system/oracle tablespaces=myspace  file=myspace.dmp full=y

--查看表空间：
SQL> select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
EXAMPLE
MYSPACE
7 rows selected.

--myspace表空间所拥有的表：
SQL> select table_name from dba_tables 
  2   where tablespace_name='MYSPACE';
TABLE_NAME
------------------------------
YOURTEST
T1

--删除这两个表：
SQL> drop table t1 purge;
Table dropped.

SQL> drop table yourtest purge;
Table dropped.

--再次查看：
SQL> select table_name from dba_tables
  2  where tablespace_name='MYSPACE';
no rows selected

-- 看表空间：
SQL>  select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
EXAMPLE
MYSPACE
7 rows selected.

--导入表空间：
--策略1：imp system/oracle tablespaces=myspace  file=myspace.dmp full=y

[oracle@enmo homedir]$ imp system/oracle tablespaces=myspace  file=myspace.dmp full=y
Import: Release 11.2.0.4.0 - Production on Wed Nov 2 21:42:15 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V11.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. importing SYSTEM's objects into SYSTEM
. importing SUXING's objects into SUXING
. . importing table                     "YOURTEST"          0 rows imported
. importing SCOTT's objects into SCOTT
. . importing table                           "T1"        100 rows imported
Import terminated successfully without warnings.
[oracle@enmo homedir]$ 
#导入完成。

--查看空间的数据表：
SQL> select table_name from dba_tables
  2  where tablespace_name='MYSPACE';
TABLE_NAME
------------------------------
YOURTEST
T1
恢复完成。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31392094/viewspace-2127555/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31392094/viewspace-2127555/