oracle表空间数据迁移另一个表空间,通过oracle10g exp/imp在不同表空间间迁移数据...

写这个文章，主要是以前对于quota on语法掌握不牢固，圣人有言，温故而知新，故写此文;

大体分为如下几步：

1,利用exp对zxy用户进行逻辑数据导出

[oracle@capitek3 ~]$ exp userid=zxy/system file=~/export_zxy.dmp statistics=none

Export: Release 10.2.0.1.0 - Production on Wed Mar 17 08:00:20 2010

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

Export done in US7ASCII character set and AL16UTF16 NCHAR character set

server uses WE8ISO8859P1 character set (possible charset conversion)

. exporting pre-schema procedural objects and actions

. exporting foreign function library names for user ZXY

. exporting PUBLIC type synonyms

. exporting private type synonyms

. exporting object type definitions for user ZXY

About to export ZXY's objects ...

. exporting database links

. exporting sequence numbers

. exporting cluster definitions

. about to export ZXY's tables via Conventional Path ...

. . exporting table                             MM          0 rows exported

. . exporting table                             T1    2097152 rows exported

. exporting synonyms

. exporting views

. exporting stored procedures

. exporting operators

. exporting referential integrity constraints

. exporting triggers

. exporting indextypes

. exporting bitmap, functional and extensible indexes

. exporting posttables actions

. exporting materialized views

. exporting snapshot logs

. exporting job queues

. exporting refresh groups and children

. exporting dimensions

. exporting post-schema procedural objects and actions

. exporting statistics

Export terminated successfully without warnings.

2,运行imp，准备把第1步导出的数据迁移到另一个表空间part1,在在此之前，请进行如下准备工作，不然imp会把数据导入原来的表空间哟

可分下面几小步:

a,以sysdba进入数据库，alter user zxy  quota 0 on zxy;--quota表明是对表空间的配额权限，on所跟的zxy为表空间(也就是要迁移用户所对应的原来表空间)；大家发现没，把quota 设置成0,这样zxy就对表空间zxy没有操作权限了哟

3,开始进行把zxy用户(对应表空间zxy)迁移到part 用户(对应表空间为part),输入如下命令

[oracle@capitek3 ~]$ imp userid=zxy/system fromuser=zxy touser=part1 file=export_zxy.dmp log=imp_sex.log --切记：imp用户必须要用system用户或具有dba角色的用户

Import: Release 10.2.0.1.0 - Production on Wed Mar 17 08:05:40 2010

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path

import done in US7ASCII character set and AL16UTF16 NCHAR character set

import server uses WE8ISO8859P1 character set (possible charset conversion)

IMP-00007: must be a DBA to import objects to another user's account

IMP-00000: Import terminated unsuccessfully

[oracle@capitek3 ~]$ imp userid=system/system fromuser=zxy touser=part1 file=export_zxy.dmp log=imp_sex.log  --记得添加log选项，这样一旦出错，便于你分析问题

Import: Release 10.2.0.1.0 - Production on Wed Mar 17 08:06:02 2010

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path

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

import done in US7ASCII character set and AL16UTF16 NCHAR character set

import server uses WE8ISO8859P1 character set (possible charset conversion)

. importing ZXY's objects into PART1

. . importing table                           "MM"          0 rows imported

. . importing table                           "T1"    2097152 rows imported

Import terminated successfully without warnings.

[oracle@capitek3 ~]$ sqlplus part1/system  --到迁移目标用户进行查询工作成果

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Mar 17 08:06:25 2010

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

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID

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

N1                             TABLE

MM                             TABLE

T1                             TABLE

SQL> select count(*) from t1;

COUNT(*)

----------

2097152

SQL>

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