不同类型的数据跨表空间迁移的解决办法

Oracle10g数据跨表空间迁移

因某些开发人员由于对oracle数据库理解的不够深入,往往在建表的时候指定了当前用户非默认的表空间,这样就导致了在exp及imp等操作时候问题很多,因此需要将这些表及相关的数据迁移回当前用户的默认表空间里.Oracle10g数据数据库提供了一个Move命令可以把这样的数据对象进行跨表空间的迁移，也可以对含有BLOB、CLOB这样的二进制大字段的表进行move。但是Move命令不支持含Long型字段的数据表,针对这种情况,我们还可以使用copy命令来进行数据迁移,后面会提到,在此不详细说明。

首先我们需要查出表空间名及相关信息
select username,default_tablespace from dba_users where default_tablespace not in ('SYSAUX','SYSTEM','USERS');
USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
YDMM YDMM
YDSK YDSK

查询不同表空间的所有者及包含的数据对象
select distinct owner from dba_extents where tablespace_name='YDMM';
OWNER
---------
YDMM
YDSK

select distinct owner from dba_extents where tablespace_name='YDSK';
OWNER
---------
YDSK

从上面我们可以发现YDMM表空间还存放了YDSK用户的数据,接下来我们就来找出YDSK用户在YDMM表空间存在的数据对象,并将YDSK用户下的所有数据对象进行迁移到它对应的默认表空间YDSK里面去
select owner,segment_name,partition_name,segment_type from dba_segments where tablespace_name='YDMM' and owner='YDSK';

一般经常碰到的segment_type主要有TABLE,INDEX,TABLE PARTITION,INDEX PARTITION,LOBSEGMENT,LOBINDEX,LOB PARTITION等,下面我们就针对这些类型的数据段进行迁移
--移动表T1到YDSK表空间里
alter table T1 move tablespace YDSK;
--移动T1表的索引idx_t1到YDSK表空间里
alter index idx_t1 rebuild tablespace YDSK;
--移动含有BLOB、CLOB的字段的表T2到YDSK表空间
alter table T2 move tablespace YDSK lob(BLOB1,BLOB2) store as (tablespace YDSK); /其中BLOB1,BLOB2是表T2中包含的blob字段,CLOB类似,
移动BLOB、CLOB的字段语法
--alter table table_name move [tablespace tbs_name] lob(lob_field1,lob_field2) store as (tablespace new_tbs_name);
--如果LOB字段在分区表中，则增加partition关键字，如
--alter table table_name move [partition partname] [tablespace tbs_name] lob(field) store as (tablespace new_tbs_name);

当然,如果存在很多数据对象,我们可以通过构建SQL脚本的方式来进行迁移
根据上面所列的数据段类型，构造数据对象跨表空间的move命令语句如下。
set heading off;
set echo off;
set feedback off;
set termout on;
spool /home/oracle/move_table.sql;
--移动表
select distinct 'alter table YDSK.'|| segment_name || ' move tablespace YDSK;' from dba_extents where segment_type='TABLE' and tablespace_name='YDMM' and owner='YDSK';
--移动索引
select distinct 'alter index YDSK.'|| segment_name || ' rebuild tablespace YDSK;' from dba_extents where segment_type='INDEX' and tablespace_name='YDMM' and owner='YDSK';
--如果有分区表则需要移动分区表和分区表索引
--移动分区表
select distinct 'alter table YDSK.'|| segment_name || ' move partition '|| partition_name || ' tablespace YDSK;' from dba_extents where segment_type='TABLE PARTITION' and tablespace_name='YDMM' and owner='YDSK';
--移动分区索引
select distinct 'alter index YDSK.'|| segment_name || ' rebuild partition '|| partition_name || ' tablespace YDSK;' from dba_extents where segment_type='INDEX PARTITION' and tablespace_name='YDMM' and owner='YDSK';

spool off;
以sys/system身份登录数据库，就可以执行执行move_table.sql来进行数据的迁移了。

前面提到了Move命令不支持含Long型字段的数据表,那么我们该如何对这样的表进行迁移了?其实很简单,就是用一个copy命令,下面我们来看下copy命令的用法与优点:
COPY命令语法如下：
COPY FROM user/password@dbname1 TO user/password@dbname2 CREATE/APPEND/INSERT/REPLACE TABLE_NAME (COLUMN_NAME, ……) USING SELECT * from table_name;
简单地来描述一下上面语法中各子句的含义：
FROM和TO语句：分别描述从哪个数据库COPY到哪个数据库，FROM或TO指向当前数据库时，可以忽略，但不能同时忽略FROM和TO。
CREATE/APPEND/INSERT/REPLACE：COPY命令的四个选项，功能分别为创建表并插入数据/在已经存在的表的记录后面插入新数据/在空表中插入数据/删除表然后重建新表并插入新数据。
TABLE_NAME (COLUMN_NAME, ……)：复制后的表名和列名，如果列名列表省略的话，则会根据后面查询语句来确定列的名称。
USING SELECT * from table_name：将该表查询的结果按照指定方法写入到前面的表中。

COPY命令的的几个主要优点：
1、支持LONG类型：由于CREATE TABLE AS和INSERT INTO SELECT都不支持LONG类型。这就导致了对LONG类型数据的处理十分麻烦，而使用存储过程的方法相对比较麻烦，利用EXP和IMP则有很多的限制条件。相对来说使用COPY是最简单快速的方法。
2、不需要建立数据库链，只要本地的TNSNAMES.ORA中配置了远端数据库就可以直接访问。操作方便快捷。而且也支持本数据库到本数据库的COPY。
3、语法灵活，支持目标表名列名与查询语句中不相符，而且还有多种数据处理方式。

下面我们将做一个简单的例子来进行copy的具体操作。（T3是含有long型字段的表）
创建一个含有long型字段的表T3
SQL> CREATE TABLE T3 (ID NUMBER, DEFAULT_VALUE LONG);
通过create table的方式进行复制
SQL> CREATE TABLE T AS SELECT * FROM T3;
CREATE TABLE T AS SELECT * FROM T3
*
ERROR 位于第 1 行:
ORA-00997: 非法使用 LONG 数据类型

通过insert into的方式进行复制
SQL> CREATE TABLE T (ID NUMBER, DEFAULT_VALUE LONG);
SQL> INSERT INTO T SELECT ID, DATA_DEFAULT FROM T3;
INSERT INTO T SELECT ID, DATA_DEFAULT FROM T3
*
ERROR 位于第 1 行:
ORA-00997: 非法使用 LONG 数据类型
从上面看，使用常用的表复制方式的不行的，下面我们来使用copy命令进行复制
SQL> DROP TABLE T;
表已丢弃。
SQL> COPY FROM YDMM/YDMM@ORCL TO YDSK/YDSKK@ORCL CREATE T USING SELECT * FROM T3；
数组读取/结合的大小为15。（数组大小为15）
将在完成时提交。（提交的复本为 0）
最长为80。（长度为80）
表T已创建。
0行选自YDMM@ORCL。
0行被插入T。
0行已提交至T（位于YDSK@ORCL）。

其中每次读取数据数组大小有SET的ARRAYSIZE参数决定。每次提交的数据量由SET的COPYCOMMIT参数控制。COPY是否进行类型检查由SET的COPYTYPECHECK参数控制。COPY执行时LONG类型截取长度由SET的LONG参数控制。如果不希望LONG类型被截断，则保证LONG的值超过表中LONG类型的最长值。
SQL> SET ARRAYSIZE 1000
SQL> SET COPYCOMMIT 1000
SQL> SET COPYTYPECHECK OFF
SQL> SET LONG 100000
SQL> COPY FROM YDMM/YDMM@ORCL TO YDSK/YDSKK@ORCL CREATE T USING SELECT * FROM T3；
数组读取/结合的大小为1000。（数组大小为1000）
将在每1000个数组结合之后提交。（提交的复本为1000）
最长为100000。（长度为100000）
0行选自YDMM@ORCL。
0行被插入T。
0行已提交至T（位于YDSK@ORCL）。

在使用COPY命令时，尽量只对用户自己的表进行操作。如果需要对其他用户下的表执行REPLACE操作时，仅仅拥有这个表的INSERT、DELETE权限是没有任何作用的，必须拥有CREATE ANY TABLE、DROP ANY TABLE和INSERT ANY TABLE系统权限。