Database version: 11.2.0.4
Operating system: 6.4
I have 4 non-partitioned tables which are of following sizes . Each table's index count is mentioned below.
Table1 - 750GB ---> 40 Indexes
Table2 - 500GB ---> 7 Indexes
Table3 - 500GB ---> 5 Indexes
Table4 - 350GB ---> 7 Indexes
These tables are highly fragmented. For eg., Table1's post-defrag space is only 500GB.
We have Clone of this production DB (Hitachi SAN Clone) .
We tested shrinking with the following command for all the above tables and it took 3 hours to complete.
alter table <tableName> shrink space;
If I want indexes to be shrunk as well, I will have to use CASCADE clause. But when I used the following command , it took 5 hours to complete for all the tables.
alter table <tableName> shrink space cascade;
But, I have been granted only 4 hours to complete this activity and SHRINK cannot use parallelism. So, I am thinking of the following approach:
Step1. alter table <tableName> shrink space; (without CASCADE)
Step2. Rebuild index manually in parallel using ALTER INDEX index_name REBUILD;
This approach should free up the space in Index ie. defrag the index . Right ?