Hemant K Chitale wrote:Heck, his manager probably ordered him to do it - because that's the only tool in the manager's toolkit from when he was a DBA at Oracle 5.0.
1. Is it really 5.7TB in size ?
2. How are you managing or propose to manage the outage when MOVing the table ?
3. You do know that you will also have to REBUILD the indexes on the table ?
4. What sound reason do you have for attempting to rebuild the table ?
5. Does your manager approve of this ?
Hemant K Chitale
Also do not forget to rebuild unusable indexes after you move partition.
select de.* from dba_extents de, (select file_id, max(block_id) block_id from dba_extents where tablespace_name= 'LMT' group by file_id) t where tablespace_name= 'LMT' and de.file_id = t.file_id and de.block_id = t.block_id / ALTER TABLE <t_owner>.<t_name> MOVE PARTITION <p_name> TABLESPACE LMT PARALLEL;
select 'alter INDEX ' || index_OWNER || '.' || INDEX_NAME || ' REBUILD PARTITION ' || PARTITION_NAME || ' TABLESPACE ' || TABLESPACE_NAME || ' NOLOGGING PARALLEL;' from all_ind_partitions where status='UNUSABLE' order by partition_name DESC, index_name