不同表空间的导入

很多人在进行数据迁移时，希望把数据导入不同于原系统的表空间，在导入之后却往往发现，数据被导入了原表空间。

 

很多人在进行数据迁移时，希望把数据导入不同于原系统的表空间，在导入之后却往往发现，数据被导入了原表空间。
本例举例说明解决这个问题:
1.如果缺省的用户具有DBA权限
那么导入时会按照原来的位置导入数据，即导入到原表空间

$imp bjbbs/passwd file=bj_bbs.dmp fromuser=jive touser=bjbbs grants=n

Import: Release 8.1.7.4.0 - Production on Mon Sep 22 11:49:41 2003

(c) Copyright 2000 Oracle Corporation. All rights reserved.

Connected to: Oracle8i Enterprise Edition Release 8.1.7.4.0 - 64bit Production
With the Partitioning option
JServer Release 8.1.7.4.0 - 64bit Production

Export file created by EXPORT:V08.01.07 via conventional path

Warning: the objects were exported by JIVE, not by you

import done in ZHS16GBK character set and ZHS16GBK NCHAR character set
. . importing table "HS_ALBUMINBOX" 12 rows imported
. . importing table "HS_ALBUM_INFO" 47 rows imported
. . importing table "HS_CATALOG" 13 rows imported
. . importing table "HS_CATALOGAUTHORITY" 5 rows imported
. . importing table "HS_CATEGORYAUTHORITY" 0 rows imported
....
. . importing table "JIVEUSERPROP" 4 rows imported
. . importing table "JIVEWATCH" 0 rows imported
. . importing table "PLAN_TABLE" 0 rows imported
. . importing table "TMZOLDUSER" 3 rows imported
. . importing table "TMZOLDUSER2" 3 rows imported
About to enable constraints...
Import terminated successfully without warnings.

查询发现仍然导入了USER表空间

$sqlplus bjbbs/passwd

SQL*Plus: Release 8.1.7.0.0 - Production on Mon Sep 22 11:50:03 2003

(c) Copyright 2000 Oracle Corporation. All rights reserved.

Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.0 - 64bit Production
With the Partitioning option
JServer Release 8.1.7.4.0 - 64bit Production

SQL> select table_name,tablespace_name from user_tables;

TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
HS_ALBUMINBOX USERS
HS_ALBUM_INFO USERS
HS_CATALOG USERS
HS_CATALOGAUTHORITY USERS
HS_CATEGORYAUTHORITY USERS
HS_CATEGORYINFO USERS
HS_DLF_DOWNLOG USERS
...
JIVEWATCH USERS
PLAN_TABLE USERS
TMZOLDUSER USERS

TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
TMZOLDUSER2 USERS

45 rows selected.

2.回收用户unlimited tablespace权限
这样就可以导入到用户缺省表空间
SQL> create user bjbbs identified by passwd
2 default tablespace bjbbs
3 temporary tablespace temp
4 /

User created.

SQL> grant connect,resource to bjbbs;

Grant succeeded.

SQL> grant dba to bjbbs;

Grant succeeded.
如果用户已经具有sysdba角色的话就只需要执行以下3步就可以啦！
SQL> revoke unlimited tablespace from bjbbs;

Revoke succeeded.

SQL> alter user bjbbs quota 0 on users;

User altered.

SQL> alter user bjbbs quota unlimited on bjbbs;

User altered.

SQL> exit
Disconnected from Oracle8i Enterprise Edition Release 8.1.7.4.0 - 64bit Production
With the Partitioning option
JServer Release 8.1.7.4.0 - 64bit Production

3.重新导入数据
$ imp bjbbs/passwd file=bj_bbs.dmp fromuser=jive touser=bjbbs grants=n

Import: Release 8.1.7.4.0 - Production on Mon Sep 22 12:00:51 2003

(c) Copyright 2000 Oracle Corporation. All rights reserved.

Connected to: Oracle8i Enterprise Edition Release 8.1.7.4.0 - 64bit Production
With the Partitioning option
JServer Release 8.1.7.4.0 - 64bit Production

Export file created by EXPORT:V08.01.07 via conventional path

Warning: the objects were exported by JIVE, not by you

import done in ZHS16GBK character set and ZHS16GBK NCHAR character set
. . importing table "HS_ALBUMINBOX" 12 rows imported
. . importing table "HS_ALBUM_INFO" 47 rows imported
. . importing table "HS_CATALOG" 13 rows imported
. . importing table "HS_CATALOGAUTHORITY" 5 rows imported
. . importing table "HS_CATEGORYAUTHORITY" 0 rows imported
. . importing table "HS_CATEGORYINFO" 9 rows imported
. . importing table "HS_DLF_DOWNLOG" 0 rows imported
....
. . importing table "JIVEUSER" 102 rows imported
. . importing table "JIVEUSERPERM" 81 rows imported
. . importing table "JIVEUSERPROP" 4 rows imported
. . importing table "JIVEWATCH" 0 rows imported
. . importing table "PLAN_TABLE" 0 rows imported
. . importing table "TMZOLDUSER" 3 rows imported
. . importing table "TMZOLDUSER2" 3 rows imported
About to enable constraints...
Import terminated successfully without warnings.

SQL> select table_name,tablespace_name from user_tables;

TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
HS_ALBUMINBOX BJBBS
HS_ALBUM_INFO BJBBS
HS_CATALOG BJBBS
HS_CATALOGAUTHORITY BJBBS
....
JIVETHREAD BJBBS
JIVETHREADPROP BJBBS
JIVEUSER BJBBS
JIVEUSERPERM BJBBS
JIVEUSERPROP BJBBS
JIVEWATCH BJBBS
PLAN_TABLE BJBBS
TMZOLDUSER BJBBS

TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
TMZOLDUSER2 BJBBS

45 rows selected.
现在数据被导入到正确的用户表空间中. -----

如何已经导入完毕采用下面方式来更换表空间，同时上面的方法也存在一定对表空间规划的问题

另外如果你已经在没有运行以上命令时就已将数据给导入后还可通过alter方式来对表以及索引进行表空间规划.

1:先通过:SELECT 'ALTER INDEX '||INDEX_NAME||' REBUILD TABLESPACE tablespace_name;' FROM USER_INDEXES;语句来得到一个SQL脚本文件.

2:然后在pl/sql developer工具中执行操作即可.

3:当在执行过程中可能会遇到一些不能重新编译的情况,这时可以将这些暂不能编译的放在最后面执行即可.

更改表的所属表空间:alter table a move tablespace tbsname;补充：移动完毕后需重建索引.alte index idx_name rebuild tablespace tablespace_name;

补:在实际应用中遇到了另一种情况就是当按上面第一种方案进行数据导入以后所有的信息全在一个表空间下,这样对于表空间规划不太合理,为了进一步对表空间的规划,又在第一种方案下采用方案二对表空间进行规划,当执行方案二中得到的SQL语句执行时会报出:ORA-01950 no privileges on tablespace 'workindex'这样错误提示,这只要是由于方案一中的alter命令所造成的,可能通过:alter user workflow quota unlimited on workinedx;语句来使workflow用户在workindex表空间上也具有无限制配额即可.然后再执行方案二中得到的alter的SQL脚本!

以下是我工作过程中所使用的导出导入语句

导入语句
导入采用命令行，有利于产生log文件
imp workflow/workflow@WORKFLOW file = /setup/workflow.dmp fromuser=workflow touser=workflow show=n buffer=2048000 ignore=n commit=y grants=y full=n log=/tmp/imp_workflw.log
imp workflow/workflow@WORKFLOW file=/oracle/installfiles/workflow.dmp fromuser=workflow touser=workflow show=n buffer=2048000 ignore=n commit=y grants=y full=n log=/oracle/installfiles/imp_workflw.log
imp workflow/workflow file=/oracle/installfiles/workflow.dmp fromuser=WF9 touser=workflow show=n buffer=2048000 ignore=n commit=y grants=y full=n log=/oracle/installfiles/imp_workflw.log

导入导出Windows下使用以下为IMP/EXP导入导出命令工具在项目中一些常用的方式如下:

1:导出整个方案库
EXP username/password@database FILE=D:filename.dmp log=D:filename.log

2:导入整个方案库 --当源库中存在表或其它信息内容可加入ROWS=Y IGNORE=Y这两个参数--
IMP username/password@database file=D:filename.dmp log=D:filename.log fromuser=exp_username touser=imp_username;

3:导出方案中部分表信息
EXP username/password@database tables=(table_name1,table_name2,table_name3,...) file=D:filename.dmp log=D:filename.log

4:导入方案中部分表信息 --同上--
IMP username/password@database tables=(table_name1,table_name2,table_name3,...) ROWS=Y IGNORE=Y file=D:filename.dmp log=D:filenameimp.log fromuser=exp_username touser=imp_username

5:导出方案中表带子查询条件的数据--也就是导出某张表中满足条件的记录.(用符号将"和'转义)
EXP username/password@database TABLES=(table_name) QUERY="WHERE column_name IN ('column_value1','column_value2','column_value3',...)" file=D:filename.dmp log=D:filename.log

6:导入方案中表的部分记录增量导入
IMP username/password@database tables=(table_name) ROWS=Y IGNORE=Y file=D:filename.dmp log=D:filename_imp.log fromuser=exp_username touser=imp_username