同一个用户分属不同表空间的表，然后导入到同一个用户同一个表空间tablespace是否可以...

最新推荐文章于 2021-11-04 11:47:19 发布

clg10051

最新推荐文章于 2021-11-04 11:47:19 发布

阅读量148

点赞数

文章标签：数据库

　SQL> conn sys/system@orcl as sysdba
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as SYS

SQL> create tablespace tbs_1 datafile 'c:\tbs_1.dbf' size 1m;

Tablespace created

SQL> create tablespace tbs_2 datafile 'c:\tbs_2.dbf' size 1m;

Tablespace created

SQL> create tablespace tbs_3 datafile 'c:\tbs_3.dbf' size 1m;

Tablespace created

SQL> create user tbs_user identified by system default tablespace tbs_1 account unlock;

User created

SQL> grant resource,connect to tbs_user;

Grant succeeded

SQL> conn tbs_user/system@orcl
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as tbs_user

SQL> create table t_1(a int) tablespace tbs_1;

Table created

SQL> insert into t_1 values(1);

1 row inserted

SQL> commit;

Commit complete

SQL> create table t_2(a int) tablespace tbs_2;

Table created

SQL> insert into t_2 values(1);

1 row inserted

SQL> commit;

Commit complete

SQL> create table t_3(a int) tablespace tbs_3;

Table created

SQL> insert into t_3 values(1);

1 row inserted

SQL> commit;

Commit complete

SQL> select u.TABLE_NAME,u.TABLESPACE_NAME from user_tables u;

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
T_1                            TBS_1
T_2                            TBS_2
T_3                            TBS_3

exp userid=tbs_user/system@orcl tables=t_1,t_2,t_3 file=c:\exp_t_1_2_3.dmp

SQL> conn sys/system@orcl as sysdba
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as SYS

SQL> drop user tbs_user cascade;

User dropped

SQL> drop tablespace tbs_1 including contents and datafiles;

Tablespace dropped

SQL> drop tablespace tbs_2 including contents and datafiles;

Tablespace dropped

SQL> drop tablespace tbs_3 including contents and datafiles;

Tablespace dropped

SQL> create user tbs_user identified by system default tablespace users account unlock;

User created

SQL> grant resource,connect to tbs_user;
Grant succeeded

--导入
imp userid=tbs_user/system@orcl tables=t_1,t_2,t_3 file=c:\exp_t_1_2_3.dmp

SQL> conn tbs_user/system@orcl
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as tbs_user

SQL> select u.TABLE_NAME,u.TABLESPACE_NAME from user_tables u;

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
T_1                            USERS
T_2                            USERS
T_3                            USERS

小结：同一个用户但分属不同的表空间的dmp，在导入同一个用户时，会默认导入到users表空间

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

转载于:http://blog.itpub.net/9240380/viewspace-748685/