ITPub博客

首页 > 数据库 > Oracle > [转移]ORACLE MOVE 表空间

[转移]ORACLE MOVE 表空间

Oracle 作者:lotusinmay 时间:2014-01-07 09:58:34 0 删除 编辑

move一个表到另外一个表空间时,索引不会跟着一起move,而且会失效(LOB类型例外)。做表空间转移之前,被转移表的用户必须在目的表空间上有使用表空间的权限,否则会报错:ORA-01950: no privileges on tablespace...
表move分为
1.普通表move
2.分区表move
3.LONG
4.LOB大字段类型move
5.索引的move通过rebuild来实现
一、move普通表、索引
1、基本语法:
a、alter table table_name t move tablespace xxx;
b、alter index index_name rebuild tablespace xxx;
move过的普通表,在不用到失效的索引的操作语句中,语句执行正常,但如果操作的语句用到了索引(主键当做唯一索引),则此时报告用到的索引失效,语句执行失败,其他如外键,非空约束,缺省值等不会失效。
2、重新创建主键或索引基本语法为:
a、alter index index_name rebuild;
b、alter index pk_name rebuild;
3、move索引用rebuild语法:
a、alter index index_name rebuild tablespace tbs_name;
b、alter index pk_name rebuild tablespace tbs_name;
二、move分区表及索引
和普通表一样,分区表索引会失效,区别的仅仅是语法而已。
1、分区基本语法
注:如果是单级分区,则使用关键字PARTITION,如果是多级分区,则使用SUBPARTITION替代PARTITION。
如果分区或分区索引比较大,可以使用并行move或rebuild,PARALLEL (DEGREE 2);
如:
ALTER TABLE PART_ALARM move SUBPARTITION p_01 TABLESPACE usersPARALLEL (DEGREE 2);
--全局索引
ALTER INDEX GX1_ PART_ALARM REBUILD tablespace usersPARALLEL (DEGREE 2);
--分区索引
ALTER INDEX LX1_ PART_ALARM REBUILD SUBPARTITION p_01 TABLESPACE users1PARALLEL (DEGREE 2);
ALTER INDEX LX1_ PART_ALARM REBUILD SUBPARTITION p_02 TABLESPACE users2PARALLEL (DEGREE 2);
………………
ALTER INDEX LX1_ PART_ALARM REBUILD SUBPARTITION p_0n TABLESPACE usersnPARALLEL (DEGREE 2);
2、移动表的某个分区
ALTER TABLE tab_name move PARTITION partition_name TABLESPACE tbs_name;
3、重建全局索引
ALTER INDEX global_index REBUILD;

ALTER INDEX global_index REBUILD tablespace tbs_name;
4、重建局部索引
ALTER TABLE tab_name MODIFY PARTITION partition_name REBUILD UNUSABLE LOCAL INDEXES;

ALTER INDEX local_index_name REBUILD PARTITION partition_name TABLESPACE tbs_name;

提示:
USER_PART_TABLES
USER_IND_PARTITIONS
USER_IND_SUBPARTITIONS
USER_LOB_PARTITIONS
USER_LOB_SUBPARTITIONS
USER_PART_INDEXES
USER_PART_LOBS
user_segments
三、move LONG类型
可以使用DBMS_REDEFINITION包可以提供一些方便,不过没用过。
long类型不能通过MOVE来传输。特别提示,尽量不要用LONG类型,特难管理。
1、LONG不能使用insert into ... select ...等带select的模式。

create table t123 (id int,en long);

insert into t123(id,en) select * from t123;
报告错误,可以用pl/sql来帮助解决,如:
declare
cursor cur_t123 is select * from t123;
use_t123 cur_t123%rowtype;
begin

open cur_t123;
loop
fetch cur_t123 into use_t123;
exit when cur_t123%notfound;

insert into t123(id,en) values (use_t123.id,use_t123.en);

end loop;
close cur_t123;

end;
/
2、LONG类型字段的表的转移
1.)create新表的方法。
a.create一个新的表,存储在需要转移的表空间。
b.创建新的索引(使用tablespace子句指定新的表空间)。
c.把数据转移过来
2.)用COPY的方法
copy from bigboar/bigboar@bigboar_sid insert t123(id,en) using select id,en from t123;
3、直接就把LONG转换成CLOB类型
create table t321(id int,en clob) tablespace users;
insert into t321(id,en) select id,to_lob(en) from t123;
4、exp/imp
exp bigboar/bigboar file=a.dat tables=t123
imp bigboar/bigboar file=a.dat full=y IGNORE =y
a.drop掉旧表。
b.rename新表为旧表表名。
四、LOB类型
在建立含有lob字段的表时,oracle会自动为lob字段建立两个单独的segment,一个用来存放数据(segment_type=LOBSEGMENT),另一个用来存放索引(segment_type=LOBINDEX)。默认它们会存储在和表一起的表空间。我们对表MOVE时,LOG类型字段和该字段的索引不会跟着MOVE,必须要单独来进行MOVE,语法如下如:
alter table t321 move tablespace users;
alter table t321 move lob(en) store as (tablespace users);
大家都知道在我们建立含有lob字段的表时,oracle会自动为lob字段建立两个单独的segment,一个用来存放数据,另一个用来存放索引,并且它们都会存储在对应表指定的表空间中。但是当我们用alter table tb_name move tablespace tbs_name;来对表做空间迁移时只能移动非lob字段以外的数据,而如果我们要同时移动lob相关字段的数据,我们就必需用如下的含有特殊参数据的文句来完成:
alter table tb_name move tablespace tbs_name lob (col_lob1,col_lob2) store as(tablesapce tbs_name);
五、实际工作操作示例
1、说明
把SOURCE_TABLESPACE表空间上的表移动到DEST_TABLESPACE表空间上,删除SOURCE_TABLESPACE,释放磁盘空间,重新再建一个比原先小的SOURCE_TABLESPACE表空间,再把表移回SOURCE_TABLESPACE。
2、步骤
1.)检查表空间表字段有没有分区、包含LOB字段和LONG字段等

2.) 检查表空间有什么内容(包括表、索引、分区等)
select segment_name,segment_type from dba_segments where tablespace_name='SOURCE_TABLESPACE';

3.) 移动表和索引
alter table IBSS.TB_CM_MSPARAM_HIST move tablespace DEST_TABLESPACE;
alter table IBSS.TB_CM_SPRESENT_HIST move tablespace DEST_TABLESPACE;
alter table IBSS.TB_CM_MSSRVLTD_HIST move tablespace DEST_TABLESPACE;
alter table IBSS.TB_CM_MSITEM_HIST move tablespace DEST_TABLESPACE;
alter table IBSS.TB_CM_MSOBJECT_HIST move tablespace DEST_TABLESPACE;
alter table IBSS.TB_CM_MSOBJGRP_HIST move tablespace DEST_TABLESPACE;
alter table IBSS.TB_CM_SERVACCT_HIST move tablespace DEST_TABLESPACE;
alter table IBSS.TB_CM_BANKACCT_HIST move tablespace DEST_TABLESPACE;
alter table IBSS.TB_CM_ACCT_HIST move tablespace DEST_TABLESPACE;
alter table IBSS.TB_CM_MSINFO_HIST move tablespace DEST_TABLESPACE;
alter table IBSS.TB_CM_MSITEMR_HIST move tablespace DEST_TABLESPACE;

alter index IBSS.IX_CM_MSINFOUST rebuild tablespace DEST_TABLESPACE;
alter index IBSS.IX_CM_MSINMSINF rebuild tablespace DEST_TABLESPACE;
alter index IBSS.IX_CM_MSIN _DISC rebuild tablespace DEST_TABLESPACE;
alter index IBSS.IX_CM_MSITEMNG rebuild tablespace DEST_TABLESPACE;
alter index IBSS.IX_CM_MSOBJE ID rebuild tablespace DEST_TABLESPACE;
alter index IBSS.IX_CM_M_MSINFO rebuild tablespace DEST_TABLESPACE;
alter index IBSS.IX_CM_MSOBJET rebuild tablespace DEST_TABLESPACE;
alter index IBSS.IX_CM_MSPRINFO rebuild tablespace DEST_TABLESPACE;

4.) 删除表空间SOURCE_TABLESPACE
DROP TABLESPACE HPMDBS1 INCLUDING CONTENTS and DATAFILES;

5.) 重新创建表空间
CREATE TABLESPACE HPMDBS1 DATAFILE
  '/opt/oracle/oradata1/tbs0101.dbf' SIZE 6192M AUTOEXTEND OFF;

6.) 重新移动表和索引到原空间
alter table IBSS.TB_CM_MSPARAM_HIST move tablespace SOURCE_TABLESPACE;
alter table IBSS.TB_CM_SPRESENT_HIST move tablespace SOURCE_TABLESPACE;
alter table IBSS.TB_CM_MSSRVLTD_HIST move tablespace SOURCE_TABLESPACE;
alter table IBSS.TB_CM_MSITEM_HIST move tablespace SOURCE_TABLESPACE;
alter table IBSS.TB_CM_MSOBJECT_HIST move tablespace SOURCE_TABLESPACE;
alter table IBSS.TB_CM_MSOBJGRP_HIST move tablespace SOURCE_TABLESPACE;
alter table IBSS.TB_CM_SERVACCT_HIST move tablespace SOURCE_TABLESPACE;
alter table IBSS.TB_CM_BANKACCT_HIST move tablespace SOURCE_TABLESPACE;
alter table IBSS.TB_CM_ACCT_HIST move tablespace SOURCE_TABLESPACE;
alter table IBSS.TB_CM_MSINFO_HIST move tablespace SOURCE_TABLESPACE;
alter table IBSS.TB_CM_MSITEMR_HIST move tablespace SOURCE_TABLESPACE;

alter index IBSS.IX_CM_MSINFOUST rebuild tablespace SOURCE_TABLESPACE;
alter index IBSS.IX_CM_MSINMSINF rebuild tablespace SOURCE_TABLESPACE;
alter index IBSS.IX_CM_MSIN _DISC rebuild tablespace SOURCE_TABLESPACE;
alter index IBSS.IX_CM_MSITEMNG rebuild tablespace SOURCE_TABLESPACE;
alter index IBSS.IX_CM_MSOBJE ID rebuild tablespace SOURCE_TABLESPACE;
alter index IBSS.IX_CM_M_MSINFO rebuild tablespace SOURCE_TABLESPACE;
alter index IBSS.IX_CM_MSOBJET rebuild tablespace SOURCE_TABLESPACE;
alter index IBSS.IX_CM_MSPRINFO rebuild tablespace SOURCE_TABLESPACE;

7.) 检查原先表和索引是否正确,检查表空间索引有没有无效。
SELECT index_name,index_type, STATUS,partitioned, table_name
FROM User_Indexes
Where status=’UNUSABLE’;

六、常用SQL
      该方法可以用来释放被表空间占用的大量的磁盘空间,如果数据对象非常多就很麻烦。可以用以下的SQL来批量处理。
1、重建分区/普通表和索引的拼接sql语句
SELECT 'ALTER INDEX '||t1.owner||'.'||segment_name||' REBUILD '||''||
       CASE WHEN t1.segment_type = 'INDEX PARTITION' THEN 'PARTITION '||partition_name
            WHEN t1.segment_type = 'INDEX SUBPARTITION' THEN 'SUBPARTITION '||partition_name
       ELSE ''
       END
       ||' tablespace '||'&DEST_TABLESPACE'||';'
FROM dba_segments t1
WHERE tablespace_name='&SOURCE_TABLESPACE' AND segment_type IN ('INDEX','INDEX PARTITION','INDEX SUBPARTITION')
UNION
SELECT 'ALTER TABLE '||t1.owner||'.'||segment_name||' MOVE '||''||
       CASE WHEN t1.segment_type = 'TABLE PARTITION' THEN 'PARTITION '||partition_name
            WHEN t1.segment_type = 'TABLE SUBPARTITION' THEN 'SUBPARTITION '||partition_name
       ELSE ''
       END
       ||' tablespace '||'&DEST_TABLESPACE'||';'
FROM dba_segments t1
WHERE tablespace_name='&SOURCE_TABLESPACE' AND segment_type IN ('TABLE','TABLE PARTITION')
ORDER BY 1 DESC;

2、转移LOB字段SQL
SELECT 'ALTER TABLE '||t2.owner||'.'||t2.table_name||' move lob ('||t1.segment_name||')'||' STORE AS ('||' tablespace '||&end_tablespace||');'
FROM dba_segments t1,dba_lobs t2
WHERE t1.segment_name=t2.segment_name AND tablespace_name='&source_tablespace' AND segment_type IN ('LOBINDEX','LOBSEGMENT','LOB PARTITION');

 

原文作者:小申 地址:http://shenzhw.iteye.com/blog/1420780

<!-- 正文结束 -->

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

上一篇: 没有了~
下一篇: 没有了~
请登录后发表评论 登录
全部评论