ORACLE MOVE 表空间

shenzhw

浏览: 63949 次
性别:
来自: 福州

最近访客更多访客>>

double_h

wangpengchao

可乐冰红茶茶

549885855

博主相关

博客

微博

相册

留言

关于我

文章分类

全部博客 (41)

社区版块

存档分类

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');

分享到：