DB version: 11.2.0.4
OS : Oracle Linux 6.4
By the word "Defrag" , i meant Items 1 & 2 mentioned in red below.
Lets say I have a table which is 20GB in size and I DELETE 15GB worth of records from this table and Commited it. The space consumed by the table segment will still remain 20GB. It will be reflected in USER_SEGMENTS view.
I have nearly 100 tables like above, which after the purging (DELETE +COMMIT ) , I need to
1. Release free extents in table segment back to datafile
2. Reset HWM of the table's segment for better performance.
To acheive the above two objectives, I am considering 2 options ; SHRINK command or MOVE command.
After deleting records from a huge table, I have issued the following move command. I can see that It has freed up the space (reflected in USER_SEGMENTS view) . It has possibly reset the HWM as well, but I don't know how to verify that. I will have to rebuild indexes after this.
alter table <TABLE_NAME> move ;
If MOVE is simple as above, then why did oracle come up with SHRINK COMPACT and SHRINK commands from 10g onwards ?
In general, which option is better to defrag : SHRINK or MOVE ?