Oracle by Example: Move Indexes from one tablespace to another

========================================
General
========================================
The tablespace which is storing indexes has grown very big.
The Linux file system is nearly 100% full.
Resize the datafile options does not yield enough space.
The best option would be to move all Indexes, partitioned and non-partitioned to another tablespace.

========================================
Flow of Events
========================================

The flow of events:

- Try to free some space by RESIZE DATAFILE

- Create new temporary Tablespace

- Move all non-partitioned indexes to new temporary Tablespace.

- Move all partitioned indexes to new temporary Tablespace.

- Drop the old tablespace with datafiles.

- Move all indexes back to the tablespace with original name (This was required, because other scripts used hard codded tablespace name)

========================================
Initial situation
========================================
Initial situation

my_user@my_server:~/workarea>% df -hP

Filesystem                      Size Used Avail Use% Mounted on

/dev/mapper/Volume00-LogVol07   16G  4.8G   11G  31% /software/oracle

/dev/vx/dsk/OraDg1/db1          79G   75G  4.3G  95% /oracle_db/db1

/dev/vx/dsk/OraDg2/Ora_Exp     100G   35G   66G  35% /backup/ora_exp

/dev/vx/dsk/OraDg2/Ora_Online  159G   41G  118G  26% /backup/ora_online

/dev/vx/dsk/OraDg3/db2         199G   2.3G 196G   2% /oracle_db/db2

TABLESPACE_NAME      DBA_FREE_SPACE_MB USED_SPACE_MB MAX_SPACE_MB   FREE_PCT ADD_
-------------------- ----------------- ------------- ------------ ---------- ----
DWH_INDEX                         5987            13         6000        100 N
SYSAUX                             512           488         1000         51 N
IGT_TABLE                        11966         18034        30000         40 N
SYSTEM                             632           368         1000         63 N
IGT_INDEX                        44308          2460        46768         95 N
DWH_TABLE                        11963            37        12000        100 N

OWNER               TABLESPACE_NAME       SEGMENT_NAME                    USED_MB
------------------- ---------------------- ------------------------ -------------
DEU_O2QQQ_SPARX     IGT_INDEX             SSU_PSMS_IMSI_IDX                   586
DEU_O2QQQ_SPARX     IGT_INDEX             SFI_CUSTOMER_PROFILE_PK             576
DEU_O2QQQ_SPARX     IGT_INDEX             SSU_PSMS_MSISDN_IDX                 479
DEU_O2DEU_SHARB     IGT_INDEX             COUNTERS_HISTORY_IDX                 72
DEU_O2QQQ_SPARX     IGT_INDEX             SFI_CUSTOMER_OPTIONS_PK              24
DEU_O2QQQ_SPARX     IGT_INDEX             GWCH_TS_LAST_MODIFIED_IDX            24
DEU_O2DEU_SHARB     IGT_INDEX             CH_LAST_UPDATE_TIME_IDX              19
DEU_O2DEU_SHARB     IGT_INDEX             COUNTERS_PROCESS_ID_IDX              12
DEU_O2QQQ_MOCOQ     IGT_INDEX             CDR_PK                                7
DEU_O2DEU_SHARB     IGT_INDEX             VIS_EVENT_PK                          5

SELECT FILE_NAME, ROUND(bytes/1024/1024) AS MB 
  FROM DBA_DATA_FILES WHERE tablespace_name = 'IGT_INDEX';

FILE_NAME                                                   MB
--------------------------------------------------- ----------
/oracle_db/db1/db_igt/ora_igt_index_01.dbf               14000
/oracle_db/db1/db_igt/ora_igt_index_02.dbf               20928


The Linux Storage at mount point  /oracle_db/db1 is 95% full
The IGT_INDEX is occupying 47Gb
But actual segments on IGT_INDEX  occupy only about 2-3Gb

========================================
Code
========================================
First Option: RESIZE DATAFILE.

SQL> ALTER DATABASE DATAFILE '/oracle_db/db1/db_igt/ora_igt_index_01.dbf' RESIZE 11000M;

Database altered.

SQL> ALTER DATABASE DATAFILE '/oracle_db/db1/db_igt/ora_igt_index_01.dbf' RESIZE 10000M;
ALTER DATABASE DATAFILE '/oracle_db/db1/db_igt/ora_igt_index_01.dbf' RESIZE 10000M
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value


SQL> ALTER DATABASE DATAFILE '/oracle_db/db1/db_igt/ora_igt_index_02.dbf' RESIZE 19000M;
ALTER DATABASE DATAFILE '/oracle_db/db1/db_igt/ora_igt_index_02.dbf' RESIZE 19000M
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value

Second Option: Move all indexes from IGT_INDEX to new Tablespace

ALTER SESSION SET DDL_LOCK_TIMEOUT=600;

CREATE TABLESPACE IGT_INDEX2 DATAFILE '/oracle_db/db1/db_igt/ora_igt_index_02.dbf' SIZE 1000M AUTOEXTEND ON MAXSIZE 4000M;

SET LINESIZE 120
SET PAGESIZE 0
SET HEADING OFF
SET VERIFY OFF
SET FEEDBACK OFF

spool move_indexes_tbs.sql
SELECT 'ALTER INDEX '||OWNER||'.'||INDEX_NAME||' REBUILD TABLESPACE IGT_INDEX2;' FROM DBA_INDEXES 
WHERE tablespace_name = 'IGT_INDEX'
  AND index_name NOT IN (SELECT index_name FROM DBA_PART_INDEXES);
spool off
SET FEEDBACK ON

Sample contents of move_indexes_tbs.sql
ALTER INDEX DEU_O2DEU_SHARB.MBS_OPCODES_PK REBUILD TABLESPACE IGT_INDEX2;
ALTER INDEX DEU_O2DEU_SHARB.MTM_OPCODES_PK REBUILD TABLESPACE IGT_INDEX2;
ALTER INDEX DEU_O2DEU_SHARB.SSU_MOCO_SUB_LAST_LU_IDX REBUILD TABLESPACE IGT_INDEX2;
ALTER INDEX DEU_O2DEU_SHARB.PROVACT_PK REBUILD TABLESPACE IGT_INDEX2;
ALTER INDEX DEU_O2DEU_SHARB.PROV_ACCOUNT_SERVICES_PK REBUILD TABLESPACE IGT_INDEX2;
ALTER INDEX DEU_O2DEU_SHARB.USERS_PK REBUILD TABLESPACE IGT_INDEX2;
ALTER INDEX DEU_O2DEU_SHARB.ICA_MT_MESSAGE_TYPE_PK REBUILD TABLESPACE IGT_INDEX2;
ALTER INDEX DEU_O2DEU_SHARB.ICA_MT_APP_MSG_NAME_UK REBUILD TABLESPACE IGT_INDEX2;

@move_indexes_tbs.sql

To move partitioned indexes:

SET LINESIZE 120

SET PAGESIZE 0

SET HEADING OFF

SET VERIFY OFF
SET FEEDBACK OFF

spool move_part_indexes_tbs.sql

SELECT 'ALTER INDEX '||OWNER||'.'||SEGMENT_NAME ||' REBUILD PARTITION '||PARTITION_NAME||' TABLESPACE IGT_INDEX2;' 

FROM  DBA_SEGMENTS 
WHERE SEGMENT_TYPE LIKE '%INDEX%' 
  AND TABLESPACE_NAME = 'IGT_INDEX'
  AND PARTITION_NAME IS NOT NULL;

spool off;

SET FEEDBACK ON

Sample contents of move_part_indexes_tbs.sql

ALTER INDEX DEU_O2QQQ_SPARX.GWCH_TS_LAST_MODIFIED_IDX REBUILD PARTITION P_3 TABLESPACE IGT_INDEX2;

ALTER INDEX DEU_O2QQQ_SPARX.SCO_START_DATE_IDX REBUILD PARTITION SYS_P68 TABLESPACE IGT_INDEX2;

ALTER INDEX DEU_O2QQQ_SPARX.SCO_START_DATE_IDX REBUILD PARTITION SYS_P67 TABLESPACE IGT_INDEX2;

ALTER INDEX DEU_O2QQQ_SPARX.SCO_START_DATE_IDX REBUILD PARTITION SYS_P66 TABLESPACE IGT_INDEX2;

ALTER INDEX DEU_O2QQQ_SPARX.SCO_START_DATE_IDX REBUILD PARTITION SYS_P65 TABLESPACE IGT_INDEX2;

ALTER INDEX DEU_O2QQQ_SPARX.SCO_START_DATE_IDX REBUILD PARTITION SYS_P64 TABLESPACE IGT_INDEX2;

ALTER INDEX DEU_O2QQQ_SPARX.SCO_START_DATE_IDX REBUILD PARTITION SYS_P63 TABLESPACE IGT_INDEX2;

ALTER INDEX DEU_O2QQQ_SPARX.SCO_START_DATE_IDX REBUILD PARTITION SYS_P62 TABLESPACE IGT_INDEX2;

ALTER INDEX DEU_O2QQQ_SPARX.SCO_START_DATE_IDX REBUILD PARTITION SYS_P61 TABLESPACE IGT_INDEX2;

@move_part_indexes_tbs.sql

SELECT * FROM DBA_SEGMENTS WHERE tablespace_name = 'IGT_INDEX';

no rows selected

DROP TABLESPACE IGT_INDEX INCLUDING CONTENTS AND DATAFILES ;

SQL> SELECT file_name FROM DBA_DATA_FILES WHERE file_name LIKE '%index%';

/oracle_db/db1/db_igt/ora_ginindex_01.dbf

/oracle_db/db1/db_igt/ora_dwh_index_01.dbf

/oracle_db/db1/db_igt/ora_igt_index_03.dbf

df -hP

Filesystem            Size  Used Avail Use% Mounted on

/dev/vx/dsk/OraDg1/db1   79G   44G   35G  56% /oracle_db/db1

========================================
Revert back to the original Tablespace name
========================================

Since database maintenance scripts in the instakllation use the string 'IGT_INDEX', move back the indexes from IGT_INDEX2 to IGT_INDEX.

Now to revert back to IGT_INDEX

CREATE TABLESPACE IGT_INDEX DATAFILE '/oracle_db/db1/db_igt/ora_igt_index_01.dbf' SIZE 2000M AUTOEXTEND ON MAXSIZE 6000M;

Tablespace created.

Move Non-partitioned indexes:

SET LINESIZE 120

SET PAGESIZE 0

SET HEADING OFF

SET VERIFY OFF
SET FEEDBACK OFF

spool move_indexes_tbs.sql

SELECT 'ALTER INDEX '||OWNER||'.'||INDEX_NAME||' REBUILD TABLESPACE IGT_INDEX;' FROM DBA_INDEXES WHERE  owner LIKE '%O2%' AND index_name NOT IN (SELECT index_name FROM DBA_PART_INDEXES WHERE  owner LIKE '%O2%');

spool off

SET FEEDBACK ON
@move_indexes_tbs.sql

Now all the non partitioned indexes were moved

To move partitioned indexes:

SET LINESIZE 120

SET PAGESIZE 0

SET HEADING OFF

SET VERIFY OFF
SET FEEDBACK OFF

spool move_part_indexes_tbs.sql

SELECT 'ALTER INDEX '||OWNER||'.'||SEGMENT_NAME ||' REBUILD PARTITION '||PARTITION_NAME||' TABLESPACE IGT_INDEX;' 

FROM  DBA_SEGMENTS WHERE SEGMENT_TYPE LIKE '%INDEX%' AND TABLESPACE_NAME = 'IGT_INDEX2';

spool off;
SET FEEDBACK ON

@move_part_indexes_tbs.sql

Check that there are no left over indexes in IGT_INDEX2

SELECT * FROM DBA_SEGMENTS WHERE TABLESPACE_NAME = 'IGT_INDEX2';

no rows selected

DROP TABLESPACE IGT_INDEX2 INCLUDING CONTENTS AND DATAFILES ;

SQL> SELECT file_name FROM DBA_DATA_FILES WHERE file_name LIKE '%index%';

FILE_NAME

---------------------------------------------

/oracle_db/db1/db_igt/ora_igt_index_01.dbf

/oracle_db/db1/db_igt/ora_ginindex_01.dbf

/oracle_db/db1/db_igt/ora_dwh_index_01.dbf

my_user@my_server:~/workarea>% df -hP

Filesystem                      Size Used Avail Use% Mounted on

/dev/mapper/Volume00-LogVol07   16G  4.8G   11G  31% /software/oracle

/dev/vx/dsk/OraDg1/db1          79G   46G   34G  58% /oracle_db/db1

/dev/vx/dsk/OraDg2/Ora_Exp     100G   35G   66G  35% /backup/ora_exp

/dev/vx/dsk/OraDg2/Ora_Online  159G   41G  118G  26% /backup/ora_online

/dev/vx/dsk/OraDg3/db2         199G   2.3G 196G   2% /oracle_db/db2