Sasikumar, December 11, 2024 - 6:26 am UTC
Thank you Connor.
If I want to perform large table defragmentation, which is best way to do without impact to online Production database? Please advise
December 11, 2024 - 7:50 am UTC
"alter table move online" in the quietest time on your database.
And make sure you actually *need* to defragment
Andrew, December 12, 2024 - 8:41 pm UTC
ALTER TABLE t M OVE ONLINE
will also require ALTER INDEX t_ix REBUILD.
Consider, also,
ALTER TABLE t ENABLE ROW MOVEMENT;
ALTER TABLE t SHRINK SPACE COMPACT;
ALTER TABLE t SHRINK SPACE;
Also, query the ADVISOR views for segment shrink/compress suggestions
dba_advisor_log
dba_advisor_findings
dba_advisor_objects
December 16, 2024 - 5:11 pm UTC
See Rajesh's comment below - if you disagree you'll have to explain why the rebuild is necessary!
move online don't required index rebuild
Rajeshwaran Jeyabal, December 13, 2024 - 1:40 pm UTC
demo@ATP19C> create table t as select * from dba_objects;
Table created.
demo@ATP19C> create unique index t_idx1 on t(object_id);
Index created.
demo@ATP19C> create index t_idx2 on t(owner,object_name);
Index created.
demo@ATP19C>
demo@ATP19C> col index_name for a15
demo@ATP19C> select index_name, status
2 from user_indexes
3 where table_name ='T';
INDEX_NAME STATUS
--------------- --------
T_IDX1 VALID
T_IDX2 VALID
demo@ATP19C> alter table t move online;
Table altered.
demo@ATP19C> select index_name, status
2 from user_indexes
3 where table_name ='T';
INDEX_NAME STATUS
--------------- --------
T_IDX1 VALID
T_IDX2 VALID
demo@ATP19C>
December 16, 2024 - 5:11 pm UTC
Indeed