oracle 数据泵 通过表空间导入导出的时候遇到的一个问题

DBA用户查询所有表空间的表结构：

SQL> desc dba_users;

Name Null? Type

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

USERNAME NOT NULL VARCHAR2(30)

USER_ID NOT NULL NUMBER

PASSWORD VARCHAR2(30)

ACCOUNT_STATUS NOT NULL VARCHAR2(32)

LOCK_DATE DATE

EXPIRY_DATE DATE

DEFAULT_TABLESPACE NOT NULL VARCHAR2(30)

TEMPORARY_TABLESPACE NOT NULL VARCHAR2(30)

CREATED NOT NULL DATE

PROFILE NOT NULL VARCHAR2(30)

INITIAL_RSRC_CONSUMER_GROUP VARCHAR2(30)

EXTERNAL_NAME VARCHAR2(4000)

PASSWORD_VERSIONS VARCHAR2(8)

EDITIONS_ENABLED VARCHAR2(1)

AUTHENTICATION_TYPE VARCHAR2(8)

SQL>select USERNAME,ACCOUNT_STATUS,CREATED,DEFAULT_TABLESPACE from dba_users;

USERNAME ACCOUNT_STATUS CREATED DEFAULT_TABLESPACE

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

SYSTEM OPEN 24-AUG-13 SYSTEM

SYS OPEN 24-AUG-13 SYSTEM

TEST OPEN 03-JAN-17 USERS

HR OPEN 21-OCT-16 USERS

SCOTT OPEN 24-AUG-13 USERS

TESTEIMP OPEN 04-JAN-17 USERS

MGMT_VIEW EXPIRED & LOCKED 24-AUG-13 SYSTEM

OUTLN EXPIRED & LOCKED 24-AUG-13 SYSTEM

DBSNMP EXPIRED & LOCKED 24-AUG-13 SYSAUX

OLAPSYS EXPIRED & LOCKED 24-AUG-13 SYSAUX

SI_INFORMTN_SCHEMA EXPIRED & LOCKED 24-AUG-13 SYSAUX

USERNAME ACCOUNT_STATUS CREATED DEFAULT_TABLESPACE

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

OWBSYS EXPIRED & LOCKED 24-AUG-13 SYSAUX

ORDPLUGINS EXPIRED & LOCKED 24-AUG-13 SYSAUX

XDB EXPIRED & LOCKED 24-AUG-13 SYSAUX

SYSMAN EXPIRED & LOCKED 24-AUG-13 SYSAUX

ANONYMOUS EXPIRED & LOCKED 24-AUG-13 SYSAUX

CTXSYS EXPIRED & LOCKED 24-AUG-13 SYSAUX

ORDDATA EXPIRED & LOCKED 24-AUG-13 SYSAUX

OWBSYS_AUDIT EXPIRED & LOCKED 24-AUG-13 SYSAUX

APEX_030200 EXPIRED & LOCKED 24-AUG-13 SYSAUX

APPQOSSYS EXPIRED & LOCKED 24-AUG-13 SYSAUX

WMSYS EXPIRED & LOCKED 24-AUG-13 SYSAUX

USERNAME ACCOUNT_STATUS CREATED DEFAULT_TABLESPACE

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

EXFSYS EXPIRED & LOCKED 24-AUG-13 SYSAUX

ORDSYS EXPIRED & LOCKED 24-AUG-13 SYSAUX

MDSYS EXPIRED & LOCKED 24-AUG-13 SYSAUX

FLOWS_FILES EXPIRED & LOCKED 24-AUG-13 SYSAUX

SPATIAL_WFS_ADMIN_USR EXPIRED & LOCKED 24-AUG-13 USERS

SPATIAL_CSW_ADMIN_USR EXPIRED & LOCKED 24-AUG-13 USERS

APEX_PUBLIC_USER EXPIRED & LOCKED 24-AUG-13 USERS

OE EXPIRED & LOCKED 21-OCT-16 USERS

DIP EXPIRED & LOCKED 24-AUG-13 USERS

SH EXPIRED & LOCKED 21-OCT-16 USERS

IX EXPIRED & LOCKED 21-OCT-16 USERS

USERNAME ACCOUNT_STATUS CREATED DEFAULT_TABLESPACE

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

MDDATA EXPIRED & LOCKED 24-AUG-13 USERS

PM EXPIRED & LOCKED 21-OCT-16 USERS

BI EXPIRED & LOCKED 21-OCT-16 USERS

XS$NULL EXPIRED & LOCKED 24-AUG-13 USERS

ORACLE_OCM EXPIRED & LOCKED 24-AUG-13 USERS

38 rows selected.

可以看出hr的默认表空间就是USER

SQL> conn hr

Enter password:

Connected.

SQL> select TABLE_NAME,TABLESPACE_NAME from user_tables;

TABLE_NAME TABLESPACE_NAME

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

COUNTRIES

JOB_HISTORY EXAMPLE

EMPLOYEES EXAMPLE

JOBS EXAMPLE

LOCATIONS EXAMPLE

REGIONS EXAMPLE

DEPARTMENTS EXAMPLE

7 rows selected.

可以发现hr里面的表却是EXAMPLE表空间的，所以映射过去，highgo的表是ts_highgo表空间的

SQL> select TABLE_NAME,TABLESPACE_NAME from user_tables;

TABLE_NAME TABLESPACE_NAME

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

COUNTRIES

DEPARTMENTS TS_HIGHGO

REGIONS TS_HIGHGO

LOCATIONS TS_HIGHGO

JOBS TS_HIGHGO

EMPLOYEES TS_HIGHGO

JOB_HISTORY TS_HIGHGO

7 rows selected.

把highgo表空间删除后

SQL> drop tablespace ts_highgo INCLUDING CONTENTS CASCADE CONSTRAINTS;

Tablespace dropped.

发现还有一个视图：

SQL> select * from tab;

TNAME TABTYPE CLUSTERID

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

EMP_DETAILS_VIEW VIEW

因为这个视图是USER里面的。。。。。。。

总结：hr自身是属于user表空间的，但是hr用户包含的表是实例（EXAMPLE）表空间的，要搞清楚，ts_highgo表空间是由哪个表空间映射过去的。