Oracle index migration, releasing disk space

Oracle index file migration steps:
 
Preparations:
1) Back up GBOS User table indexes: Use plsqlDevelop to export all GBOS User table indexes for backup.
 

1. view the data files in the index tablespace
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 a new index tablespace and add an index data file.
Create smallfile tablespace "INDEXTBS" DATAFILE 'd: \ ORACLE \ ORADATA \ INNETDB \ INDEXTBS \ INDEX001.DBF'
SIZE 100 m autoextend on next 100 m maxsize 2000 m logging extent management local segment space management auto;
 
Alter tablespace "INDEXTBS" add datafile 'd: \ ORACLE \ ORADATA \ INNETDB \ INDEXTBS \ INDEX003.DBF'
SIZE 100 m autoextend on next 100 m maxsize 4000 M;
 

3. Search for table indexes belonging to gbos users. (except for clob indexes, clob indexes are stored in the user tablespace)
(Partition indexes are stored in the dba_segments table, and common index information is stored in the dba_indexes table)
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'

Normal index migration
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;
 
Partition index migration
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_bptablespace 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;

1 2 Next Page