ORACLE移表空间操作说明.doc

ORACLE移表空间操作说明移动一个表到另一个表空间move一个表到另外一个表空间时,索引不会跟着一起move，而且会失效。（LOB类型例外）表move，我们分为：*普通表move*分区表move*LONG,LOB大字段类型move来进行测试和说明。索引的move，我们通过rebuild来实现（1）move普通表、索引基本语法：alter table tab_name move tablespace tbs_name;move过的普通表，在不用到失效的索引的操作语句中，语句执行正常，但如果操作的语句用到了索引（主键当做唯一索引），则此时报告用到的索引失效，语句执行失败，其他如外键，非空约束，缺省值等不会失效。我们需要重新创建主键或索引，基本语法为：alter index index_name rebuild;alter index pk_name rebuild;如果我们需要move索引，则使用rebuild语法：alter index index_name rebuild tablespace tbs_name;alter index pk_name rebuild tablespace tbs_name;提示：查询表所具有的索引，可以使用user_indexes视图（索引和主键都在这个视图里可找到）。（2）move分区表及索引和普通表一样，索引会失效，区别的仅仅是语法而已。分区基本语法：特别提醒注意，如果是单级分区，则使用关键字PARTITION，如果是多级分区，则使用SUBPARTITION替代PARTITION。如果分区或分区索引比较大，可以使用并行move或rebuild，PARALLEL (DEGREE 2);如：ALTER TABLE PART_ALARMTEXTDATA move SUBPARTITION ALARMTEXTDATA_050910_ATD01 TABLESPACE users PARALLEL (DEGREE 2);ALTER INDEX GLOBAL_ALARMTEXTDATA REBUILD tablespace users PARALLEL (DEGREE 2);ALTER INDEX LOCAL_ALARMTEXTDATA REBUILD SUBPARTITION ALARMTEXTDATA_050910_ATD01 TABLESPACE users PARALLEL (DEGREE 2);移动表的某个分区：=ALTER TABLE tab_name move PARTITION partition_name TABLESPACE tbs_name;重建全局索引：=ALTER INDEX global_index REBUILD;或ALTER INDEX global_index REBUILD tablespace tbs_name;重建局部索引：=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_TABLESUSER_IND_PARTITIONSUSER_IND_SUBPARTITIONSUSER_LOB_PARTITIONSUSER_LOB_SUBPARTITIONSUSER_PART_INDEXESUSER_PART_LOBS可查询分区相关内容，同时，分区对象，也是segment，所以也可在dba_segments里查的到。（3）move LONG,LOB类型据说DBMS_REDEFINITION包可以提供一些方便，没用过。a、LONG类型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;beginopen cur_t123;loopfetch 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;/对有LONG类型字段的表的转移，可以使用：create新表的方法。* create一个新的表，存储在需要转移的表空间。* 创建新的索引（使用tablespace 子句指定新的表空间）。* 把数据转移过来方法一：用COPY的方法：copy from bigboar/bigboarbigboar_sid insert t123(id,en) using select id,en from t123;方法二：PL/SQL（如上）方法三：直接就把LONG转换成CLOB类型create table t321(id int,en clob) tablespace users; insert into t321(id,en) select id,to_lob(en) from t123; 方法四：exp/impexp bigboar/bigboar file=a.dat tables=t123imp bigboar/bigboar file=a.dat full=y IGNORE =y* drop掉旧表。* rename 新表为旧表表名。b、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); 下面我们来看一个例子。 Connected to Oracle8i Enterprise Edition Release 8.1.7.0.0 Connected as scottSQL desc test_blob;Name Type Nullable Default Comments - - - - - FILE_NAME VARCHAR2(25) Y FILE_BIN BLOB Y FILE_BIN2 BLOB Y SQL SQL select t.segment_name, t.segment_type, t.tablespace_name 2 from sys.user_segments t 3 where t.segment_name like SYS_% 4 /SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME- - -SYS_IL0000025842C00003$ LOBINDEX SYSTEMSYS_IL0000025842C00002$ LOBINDEX SYSTEMSYS_LOB0000025842C00002$ LOBSEGMENT SYSTEMSYS_LOB0000025842C00003$ LOBSEGMENT SYSTEM6 rows selectedSQL alter table test_blob move tablespace tools;Table alteredSQL SQL select t.segment_name, t.segment_type, t.tablespace_name 2 from sys.user_segments t 3 where t.segment_name like SYS_% 4 /SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME- - -SYS_IL0000025842C00003$ LOBINDEX SYSTEMSYS_IL0000025842C00002$ LOBINDEX SYSTEMSYS_LOB0000025842C00002$ LOBSEGMENT SYSTEMSYS_LOB0000025842C00003$ LOBSEGMENT SYSTEM6 rows selectedSQL select t.segment_name, t.segment_type, t.tablespace_name 2 from sys.user_segments t 3 where t.segment_name like TEST_BLOB;SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME- - -TEST_BLOB TABLE TOOLSSQL alter table test_blob move tablespace SYSTEM;Table alteredSQL ALTER TABLE test_blob MOVE 2 TABLESPACE tools 3 LOB (FILE_BIN,FILE_BIN2) STORE AS 4 (TABLESPACE tools);Table alteredSQL select t.segment_name, t.segment_type, t.tablespace_name 2 from sys.user_segments t 3 where t.segment_name like SYS_%;SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME- - -SYS_IL0000025842C00003$ LOBINDEX TOOLSSYS_IL0000025842C00002$ LOBINDEX TOOLSSYS_LOB0000025842C00002$ LOBSEGMENT TOOLSSYS_LOB0000025842C00003$ LOBSEGMENT TOOLS6 rows selected实际工作操作示例：1、说明：把DATA_KHLS_A03表空间上的表移动到IBSSTEST表空间上，删除DATA_KHLS_A03，释放裸设备文件，重新再建一个比原先小的DATA_KHLS_A03表空间，再把表移回DATA_KHLS_A03。步骤：（1） 检查表空间表字段有没有分区、包含LOB字段和LONG字段等 a、检查表空间有什么内容（包括表、索引、分区等）select segment_name,segment_type from dba_segments where tablespace_name= DATA_KHLS_A03;b、列表字段desc 表名;（2）移动表和索引alter table IBSS.TB_CM_MSPARAM_HIST move tablespace IBSSTEST;alter table IBSS.TB_CM_MSPRESENT_HIST move tablespace IBSSTEST;alter table IBSS.TB_CM_MSSRVLTD_HIST move tablespace IBSSTEST;alter table IBSS.TB_CM_MSITEM_HIST move tablespace IBSSTEST;alter table IBSS.TB_CM_MSOBJECT_HIST move tablespace IBSSTEST;alter table IBSS.TB_CM_MSOBJGRP_HIST move tablespace IBSSTEST;alter table IBSS.TB_CM_SERVACCT_HIST move tablespace IBSSTEST;alter table IBSS.TB_CM_BANKACCT_HIST move tablespace IBSSTEST;alter table IBSS.TB_CM_ACCT_HIST move tablespace IBSSTEST;alter table IBSS.TB_CM_MSINFO_HIST move tablespace IBSSTEST;alter table IBSS.TB_CM_MSITEM_ATTR_HIST move tablespace IBSSTEST;alter index IBSS.IX_CM_MSINFO_HIST_CUST rebuild tablespace IBSSTEST;alter index IBSS.IX_CM_MSINFO_HIST_MSINFO rebuild tablespace IBSSTEST;alter index IBSS.IX_CM_MSINFO_HIST_DISC rebuild tablespace IBSSTEST;alter index IBSS.IX_CM_MSITEM_HIST_MSINFO rebuild tablespace IBSSTEST;alter index IBSS.IX_CM_MSOBJECT_HIST_OBJID rebuild tablespace IBSSTEST;alter index IBSS.IX_CM_MSOBJECT_HIST_MSINFO rebuild tablespace IBSSTEST;alter index IBSS.IX_CM_MSOBJECT_HIST_MSOBJGRP rebuild tablespace IBSSTEST;alter index IBSS.IX_CM_MSPRESENT_HIST_MSINFO rebuild tablespace IBSSTEST;（3）删除表空间DATA_KHLS_A03用Oracle Enterprise Manager Console工具或命令删除表空间drop tablespace DATA_KHLS_A03 including contents;（4）重建表空间DATA_KHLS_A03用Oracle Enterprise Manager Console工具或命令重建表空间CREATE TABLESPACE DATA_KHLS_A03 LOGGING DATAFILE /dev/vgdata17/rlvol18 SIZE 4999M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;（5）重新移动表和索引到原空间。alter table IBSS.TB_CM_MSPARAM_HIST move tablespace DATA_KHLS_A03;alter table IBSS.TB_CM_MSPRESENT_HIST move tablespace DATA_KHLS_A03;alter table IBSS.TB_CM_MSSRVLTD_HIST move tablespace DATA_KHLS_A03;alter table IBSS.TB_CM_MSITEM_HIST move tablespace DATA_KHLS_A03;alter table IBSS.TB_CM_MSOBJECT_HIST move tablespace DATA_KHLS_A03;alter table IBSS.TB_CM_MSOBJGRP_HIST move tablespace DATA_KHLS_A03;alter table IBSS.TB_CM_SERVACCT_HIST move tablespace DATA_KHLS_A03;alter table IBSS.TB_CM_BANKACCT_HIST move tablespace DATA_KHLS_A03;alter table IBSS.TB_CM_ACCT_HIST move tablespace DATA_KHLS_A03;alter table IBSS.TB_CM_MSINFO_HIST move tablespace DATA_KHLS_A03;alter table IBSS.TB_CM_MSITEM_ATTR_HIST move tablespace DATA_KHLS_A03;alter index IBSS.IX_CM_MSINFO_HIST_CUST rebuild tablespace DATA_KHLS_A03;alter index IBSS.IX_CM_MSINFO_HIST_MSINFO rebuild tablespace DATA_KHLS_A03;alter index IBSS.IX_CM_MSINFO_HIST_DISC rebuild tablespace DATA_KHLS_A03;alter index IBSS.IX_CM_MSITEM_HIST_MSINFO rebuild tablespace DATA_KHLS_A03;alter index IBSS.IX_CM_MSOBJECT_HIST_OBJID rebuild tablespace DATA_KHLS_A03;alter index IBSS.IX_CM_MSOBJECT_HIST_MSINFO rebuild tablespace DATA_KHLS_A03;alter index IBSS.IX_CM_MSOBJECT_HIST_MSOBJGRP rebuild tablespace DATA_KHLS_A03;alter index IBSS.IX_CM_MSPRESENT_HIST_MSINFO rebuild tablespace DATA_KHLS_A03;（6）检查原先表和索引是否正确，检查表空间索引有没有无效。2、把DATA_JKGL_A01表空间上的表移动到IBSSTEST表空间上，删除DATA_JKGL_A01，释放裸设备文件，重新再建一个比原先小的DATA_JKGL_A01表空间，再把表移回DATA_JKGL_A01。步骤：（1）检查表空间表字段有没有分区、包含LOB字段和LONG字段等 a、检查表空间有什么内容（包括表、索引、分区等）select segment_name,segment_type from dba_segments where tablespace_name=DATA_JKGL_A01;b、列表字段desc 表名;（2）移动表、LOB字段和索引alter table IBSS.TB_IN_TRANNEL_HIS move tablespace IBSSTEST;ALTER TABLE IBSS.TB_IN_TRANNEL_HIS move TABLESPACE IBSSTEST LOB (TARGET_DATA,RECV_DATA) STORE AS (TABLESPACE IBSSTEST);alter table IBSS.JKDD move tablespace IBSSTEST;alter table IBSS.JKYW move tablespace IBSSTEST;alter table IBSS.TB_IN_TASK_EXT_HIS move tablespace IBSSTEST;alter table IBSS.TB_IN_TASK_EXT_HIS move tablespace IBSSTEST LOB (VDATA) STORE AS (TABLESPACE IBSSTEST);alter table IBSS.TB_IN_TASK_HIS move tablespace IBSSTEST;alter table IBSS.TB_IN_TASK_LOG_HIS move tablespace IBSSTEST;alter table IBSS.TB_IM_WAIT_SND_SM move tablespace IBSSTEST;alter table IBSS.TB_IN_CHECK_RESULT_HIS move tablespace IBSSTEST;alter table IBSS.TB_IN_RAW_DATA_HIS move tablespace IBSSTEST;alter table IBSS.TB_IN_RAW_DATA_HIS move tablespace IBSSTEST LOB (SOURCE_DATA) STORE AS (TABLESPACE IBSSTEST);alter table IBSS.TB_IN_SEND_QUE_HIS move tablespace IBSSTEST;（3）删除表空间DATA_JKGL_A01用Oracle Enterprise Manager Console工具或命令删除表空间drop tablespace DATA_JKGL_A01 including contents;（4）重建表空间DATA_JKGL_A01用Oracle Enterprise Manager Console工具或命令重建表空间CREATE TABLESPACE DATA_JKGL_A01 LOGGING DATAFILE /dev/vgdata17/rlvol18 SIZE 4999M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;（5）重新移动表、LOB字段和索引到原空间。alter table IBSS.TB_IN_TRANNEL_HIS move tablespace DATA_JKGL_A01;ALTER TABLE IBSS.TB_IN_TRANNEL_HIS move TABLESPACE DATA_JKGL_A01 LOB (TARGET_DATA,RECV_DATA) STORE AS (TABLESPACE DATA_JKGL_A01);alter table IBSS.JKDD move tablespace DATA_JKGL_A01;alter table IBSS.JKYW move tablespace DATA_JKGL_A01;alter table IBSS.TB_IN_TASK_EXT_HIS move t