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

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:\ORACLE\ORADATA\INNETDB\USERINDEX01.DBF          USERINDEX      10240    1310720
        20 D:\ORACLE\ORADATA\INNETDB\USERINDEX02.DBF          USERINDEX      10240    1310720
        21 D:\ORACLE\ORADATA\INNETDB\USERINDEX03.DBF          USERINDEX      10240    1310720
        22 D:\ORACLE\ORADATA\INNETDB\USERINDEX04.DBF          USERINDEX      10240    1310720
        23 D:\ORACLE\ORADATA\INNETDB\USERINDEX05.DBF          USERINDEX      10240    1310720
        39 D:\ORACLE\ORADATA\INNETDB\USERINDEX06.DBF          USERINDEX        6500    832000
        40 D:\ORACLE\ORADATA\INNETDB\USERINDEX07.DBF          USERINDEX        6500    832000
        41 D:\ORACLE\ORADATA\INNETDB\USERINDEX08.DBF          USERINDEX        6500    832000
        42 D:\ORACLE\ORADATA\INNETDB\USERINDEX09.DBF          USERINDEX        6300    806400
        43 D:\ORACLE\ORADATA\INNETDB\USERINDEX10.DBF          USERINDEX        6300    806400
        62 D:\ORACLE\ORADATA\INNETDB\USERINDEX11.DBF          USERINDEX        1400    179200
        63 D:\ORACLE\ORADATA\INNETDB\USERINDEX12.DBF          USERINDEX        1400    179200
 

2.创建新的索引表空间，添加索引数据文件
 CREATE SMALLFILE TABLESPACE "INDEXTBS" DATAFILE 'D:\ORACLE\ORADATA\INNETDB\INDEXTBS\INDEX001.DBF'
SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE 2000M LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
 
ALTER TABLESPACE "INDEXTBS" ADD DATAFILE 'D:\ORACLE\ORADATA\INNETDB\INDEXTBS\INDEX003.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;