imp表空间的两种情况

imp导入表空间时候，导入的结果有两种，这两种结果取决于导入环境。
一种是有有表空间，无数据表，另一种是表空间与数据表都没有。以下是这两种
情况的导入后的结果对比。

- --查看表空间：
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

---删除表空间：
SQL> drop tablespace myspace including contents;
Tablespace 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
6 rows selected.
#表空间已经删除：

--尝试查看表yourtest：
SQL> select * from yourtest;
select * from yourtest
              *
ERROR at line 1:
ORA-00942: table or view does not exist
#已经删除。

--通过imp导入表与表空间：
[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:48: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 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,tablespace_name 
  2  from dba_tables
  3  where table_name in ('YOURTEST','T1');


TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
YOURTEST                       USERS
T1                             USERS

明显看出：
1、只删除数据表，导入表空间后，数据表依然导入到原来的表空间与用户上；
2、删除表空间后，导入表空间后，数据表会导入到原来的用户，但是存放的表空间是users表空间。

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

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