导入数据到不同表空间的问题！（unlimited tablespace 权限在作怪）

本文链接：https://blog.youkuaiyun.com/zq9017197/article/details/6903332

本文介绍如何在Oracle数据库中创建用户及表空间，并演示了通过EXP和IMP命令迁移数据的过程，特别关注不同表空间间的迁移及UNLIMITED TABLESPACE权限的影响。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

可以看见有一个usr用户，有一个t表，是放在users表空间的，里面有705行记录。

SQL> conn /as sysdba
已连接。
SQL> create user usr identified by usr default tablespace users;

用户已创建。

SQL> select USERNAME,DEFAULT_TABLESPACE from dba_users where username='USR';

USERNAME                       DEFAULT_TABLESPACE
------------------------------ ------------------------------
USR                            USERS

SQL> grant connect,resource to usr;

授权成功。

SQL> create table usr.t as select * from user_tables;

表已创建。

SQL> conn usr/usr
已连接。
SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
T                              TABLE

SQL> select TABLE_NAME,TABLESPACE_NAME from user_tables;

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
T                              USERS

SQL> select count(*) from t;

  COUNT(*)
----------
       705

现在导入usr下面的所有对象

[oracle@linux oracle]$ exp system/oracle file=usr.dmp owner=usr grants=n

Export: Release 10.2.0.4.0 - Production on 星期日 10月 23 06:26:19 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集
注: 将不导出对表/视图/序列/角色的授权

即将导出指定的用户...
. 正在导出 pre-schema 过程对象和操作
. 正在导出用户 USR 的外部函数库名
. 导出 PUBLIC 类型同义词
. 正在导出专用类型同义词
. 正在导出用户 USR 的对象类型定义
即将导出 USR 的对象...
. 正在导出数据库链接
. 正在导出序号
. 正在导出簇定义
. 即将导出 USR 的表通过常规路径...
. . 正在导出表                               T导出了         705 行
. 正在导出同义词
. 正在导出视图
. 正在导出存储过程
. 正在导出运算符
. 正在导出引用完整性约束条件
. 正在导出触发器
. 正在导出索引类型
. 正在导出位图, 功能性索引和可扩展索引
. 正在导出后期表活动
. 正在导出实体化视图
. 正在导出快照日志
. 正在导出作业队列
. 正在导出刷新组和子组
. 正在导出维
. 正在导出 post-schema 过程对象和操作
. 正在导出统计信息
成功终止导出, 没有出现警告。

现在创建一个u2用户

SQL> create tablespace tb2 datafile '/u01/app/oracle/oradata/orcl/tb2.dbf' size 10m;

表空间已创建。

SQL> create user u2 identified by u2 default tablespace tb2;

用户已创建。

SQL> grant connect,resource to u2;

授权成功。

SQL> select USERNAME,DEFAULT_TABLESPACE from dba_users where username = 'U2';

USERNAME                       DEFAULT_TABLESPACE
------------------------------ ------------------------------
U2                             TB2

导入usr的数据到u2用户下面

[oracle@linux oracle]$ imp system/oracle file=usr.dmp fromuser=usr touser=u2 grants=n

Import: Release 10.2.0.4.0 - Production on 星期日 10月 23 06:30:35 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

经由常规路径由 EXPORT:V10.02.01 创建的导出文件
已经完成 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集中的导入
. 正在将 USR 的对象导入到 U2
. . 正在导入表                             "T"导入了         705 行
成功终止导入, 没有出现警告。

现在验证一下导入的数据

SQL> conn u2/u2
已连接。
SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
T                              TABLE

SQL> select count(*) from t;

  COUNT(*)
----------
       705

SQL> select TABLE_NAME,TABLESPACE_NAME from user_tables;

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
T                              USERS

SQL> select * from session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION
UNLIMITED TABLESPACE
CREATE TABLE
CREATE CLUSTER
CREATE SEQUENCE
CREATE PROCEDURE
CREATE TRIGGER
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE

已选择10行。

说明：明明是指定了“fromuser=usr touser=u2”，照理应该导入的u2用户默认的表空间tb2啊，可是缺导入到了users表空间中，原因就是“UNLIMITED TABLESPACE”这个权限的问题。

下面我撤销这个权限再来导入一次，就可以看见能够正确导入到u2用户的tb2表空间中

SQL> conn u2/u2
已连接。
SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
T                              TABLE

SQL> drop table t purge;        --删除表，后面重新导入

表已删除。

SQL> select * from tab;

未选定行

SQL> select * from session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION
UNLIMITED TABLESPACE
CREATE TABLE
CREATE CLUSTER
CREATE SEQUENCE
CREATE PROCEDURE
CREATE TRIGGER
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE

已选择10行。

SQL> conn /as sysdba
已连接。
SQL> revoke UNLIMITED TABLESPACE from u2;          --撤销UNLIMITED TABLESPACE权限

撤销成功。

SQL> alter user u2 quota 0 on users;

用户已更改。

SQL> alter user u2 quota unlimited on tb2;

用户已更改。

SQL> conn u2/u2
已连接。
SQL> select * from session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION
CREATE TABLE
CREATE CLUSTER
CREATE SEQUENCE
CREATE PROCEDURE
CREATE TRIGGER
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE

已选择9行。

还是前面的导入命令，再执行一次导入

[oracle@linux oracle]$ imp system/oracle file=usr.dmp fromuser=usr touser=u2 grants=n

Import: Release 10.2.0.4.0 - Production on 星期日 10月 23 06:40:45 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

经由常规路径由 EXPORT:V10.02.01 创建的导出文件
已经完成 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集中的导入
. 正在将 USR 的对象导入到 U2
. . 正在导入表                             "T"导入了         705 行
成功终止导入, 没有出现警告。

这回可以看见数据导入到了想要的地方去了，tb2表空间中。

SQL> conn u2/u2
已连接。
SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
T                              TABLE

SQL> select count(*) from t;

  COUNT(*)
----------
       705

SQL> select TABLE_NAME,TABLESPACE_NAME from user_tables;

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
T                              TB2