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表空间是由哪个表空间映射过去的。

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值