Oracle 索引迁移,释放磁盘空间

Oracle索引文件迁移步骤：

准备工作：
1）备份GBOS用户表索引：通过plsqlDevelop工具将GBOS用户表索引全部导出，以做备份。
1.查看索引表空间具有那些数据文件
select file_id,file_name,tablespace_name,bytes/1024/1024 M,blocks from dba_data_files
where TABLESPACE_NAME="USERINDEX"order by 1; FILE_ID FILE_NAME TABLESPACE M BLOCKS
--------- -------------------------------------------------- ---------- ---------- ----------
19 D:ORACLEORADATAINNETDBUSERINDEX01.DBF USERINDEX 10240 1310720
20 D:ORACLEORADATAINNETDBUSERINDEX02.DBF USERINDEX 10240 1310720
21 D:ORACLEORADATAINNETDBUSERINDEX03.DBF USERINDEX 10240 1310720
22 D:ORACLEORADATAINNETDBUSERINDEX04.DBF USERINDEX 10240 1310720
23 D:ORACLEORADATAINNETDBUSERINDEX05.DBF USERINDEX 10240 1310720
39 D:ORACLEORADATAINNETDBUSERINDEX06.DBF USERINDEX 6500 832000
40 D:ORACLEORADATAINNETDBUSERINDEX07.DBF USERINDEX 6500 832000
41 D:ORACLEORADATAINNETDBUSERINDEX08.DBF USERINDEX 6500 832000
42 D:ORACLEORADATAINNETDBUSERINDEX09.DBF USERINDEX 6300 806400
43 D:ORACLEORADATAINNETDBUSERINDEX10.DBF USERINDEX 6300 806400
62 D:ORACLEORADATAINNETDBUSERINDEX11.DBF USERINDEX 1400 179200
63 D:ORACLEORADATAINNETDBUSERINDEX12.DBF USERINDEX 1400 179200
2.创建新的索引表空间，添加索引数据文件
CREATE SMALLFILE TABLESPACE "INDEXTBS" DATAFILE "D:ORACLEORADATAINNETDBINDEXTBSINDEX001.DBF"
SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE 2000M LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

ALTER TABLESPACE "INDEXTBS" ADD DATAFILE "D:ORACLEORADATAINNETDBINDEXTBSINDEX003.DBF"
SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE 4000M ;
3.查找属于gbos用户的表索引，（除去clob类型的索引,clob索引存放在user表空间）
（分区索引存放在dba_segments表里，普通索引信息放在dba_indexes表里）
SELECT "alter index gbos."||index_name||" rebuild tablespace INDEXTBS"
FROM dba_INDEXES T where t.table_owner="GBOS"
and index_name not like "%SYS_%"
and t.tablespace_name="USERINDEX"

SELECT "alter index gbos."||index_name||" rebuild tablespace INDEXTBS"
FROM dba_INDEXES T where t.tablespace_name="USERINDEX" and t.table_owner="GBOS"普通索引迁移
alter index gbos.INDEX_T_B_FAULT_LIST_STATUS rebuild tablespace INDEXTBS;
alter index gbos.INDEX_T_B_FAULT_L_OCCUR_TIME rebuild tablespace INDEXTBS;
alter index gbos.INDEX_T_B_FAULT_L_TERMINALID rebuild tablespace INDEXTBS;
alter index gbos.IDX_CAR_INFO_COL rebuild tablespace INDEXTBS;
alter index gbos.IX_T_O_OPEN_DOOR_REC_CLCT_DATE rebuild tablespace INDEXTBS;
alter index gbos.IX_T_O_OPEN_DOOR_REC_TERM_ID rebuild tablespace INDEXTBS;
alter index gbos.IX_T_O_OPEN_DOOR_REC_TICK rebuild tablespace INDEXTBS;

分区索引迁移
Alter index gbos.IDX_O_PERIOD_INFO_COLS rebuild partition SYS_P61 tablespace INDEXTBS;
Alter index gbos.IDX_O_PERIOD_INFO_COLS rebuild partition SYS_P62 tablespace INDEXTBS;
Alter index gbos.IDX_O_PERIOD_INFO_COLS rebuild partition SYS_P63 tablespace INDEXTBS;
Alter index gbos.IDX_O_PERIOD_INFO_COLS rebuild partition SYS_P64 tablespace INDEXTBS;
Alter index gbos.IDX_O_PERIOD_INFO_COLS rebuild partition SYS_P65 tablespace INDEXTBS;
Alter index gbos.IDX_O_PERIOD_INFO_COLS rebuild partition SYS_P66 tablespace INDEXTBS;
Alter index gbos.IDX_O_PERIOD_INFO_COLS rebuild partition SYS_P67 tablespace INDEXTBS;
Alter index gbos.IDX_O_PERIOD_INFO_COLS rebuild partition SYS_P68 tablespace INDEXTBS;
Alter index gbos.IDX_O_PERIOD_INFO_COLS rebuild partition SYS_P69 tablespace INDEXTBS;
Alter index gbos.IDX_O_PERIOD_INFO_COLS rebuild partition SYS_P70 tablespace INDEXTBS;

Alter index gbos.INDEX_T_O_PERIOD_INFO rebuild partition SYS_P41 tablespace INDEXTBS;
Alter index gbos.INDEX_T_O_PERIOD_INFO rebuild partition SYS_P42 tablespace INDEXTBS;
Alter index gbos.INDEX_T_O_PERIOD_INFO rebuild partition SYS_P43 tablespace INDEXTBS;
Alter index gbos.INDEX_T_O_PERIOD_INFO rebuild partition SYS_P44 tablespace INDEXTBS;
Alter index gbos.INDEX_T_O_PERIOD_INFO rebuild partition SYS_P45 tablespace INDEXTBS;
Alter index gbos.INDEX_T_O_PERIOD_INFO rebuild partition SYS_P46 tablespace INDEXTBS;
Alter index gbos.INDEX_T_O_PERIOD_INFO rebuild partition SYS_P47 tablespace INDEXTBS;
Alter index gbos.INDEX_T_O_PERIOD_INFO rebuild partition SYS_P48 tablespace INDEXTBS;
Alter index gbos.INDEX_T_O_PERIOD_INFO rebuild partition SYS_P49 tablespace INDEXTBS;
Alter index gbos.INDEX_T_O_PERIOD_INFO rebuild partition SYS_P50 tablespace INDEXTBS;

Alter index gbos.Ix_T_O_Period_Info_Tick rebuild partition SYS_P51 tablespace INDEXTBS;
Alter index gbos.Ix_T_O_Period_Info_Tick rebuild partition SYS_P52 tablespace INDEXTBS;
Alter index gbos.Ix_T_O_Period_Info_Tick rebuild partition SYS_P53 tablespace INDEXTBS;
Alter index gbos.Ix_T_O_Period_Info_Tick rebuild partition SYS_P54 tablespace INDEXTBS;
Alter index gbos.Ix_T_O_Period_Info_Tick rebuild partition SYS_P55 tablespace INDEXTBS;
Alter index gbos.Ix_T_O_Period_Info_Tick rebuild partition SYS_P56 tablespace INDEXTBS;
Alter index gbos.Ix_T_O_Period_Info_Tick rebuild partition SYS_P57 tablespace INDEXTBS;
Alter index gbos.Ix_T_O_Period_Info_Tick rebuild partition SYS_P58 tablespace INDEXTBS;
Alter index gbos.Ix_T_O_Period_Info_Tick rebuild partition SYS_P59 tablespace INDEXTBS;
Alter index gbos.Ix_T_O_Period_Info_Tick rebuild partition SYS_P60 tablespace INDEXTBS;

Oracle 11g R2通过透明网关连接DB2使用Oracle sql profile固定执行计划相关资讯 Oracle索引

Oracle跳跃式索引扫描测试（08月09日）
Oracle组合索引与回表（08/07/2015 18:11:53）
Oracle 索引基本原理（04/12/2015 18:03:58）

关于Oracle位图索引内部浅论（09/17/2015 19:23:59）
Oracle 索引的可见与隐藏（visible （07/18/2015 09:41:42）
Oracle索引合并coalesce操作（04/01/2015 20:21:34）

本文评论查看全部评论（0）