lob移表空间 oracle_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系统权限。