Re: Best bet for table defragmentation

On 8/17/07, Khan, Muhammad S <Muhammad.Khan_at_ca.com> wrote:
>
> Its Oracle 9.2.0.7 on Solaris, we have a transaction table with
> approximately 68 million rows. It is consisting of about 100GB space in the
> tablespace and it is assumed that it is highly fragmented. We definitely
> need some space at that tablespace and for that we were discussing the
> methods for defragmenting it and get some space back. One suggested method
> was export/import but that would take atleast 6 hours while another one was
> moving the table to another tablespace and rebuild the indexes which would
> not require any downtime.
>

I've done this manually on 9.2.0.6 with a similarly sized table using Materialized Views.
Total downtime was as long as it takes to stop/start SAP twice.

Since you are on 9.2.0.7 you could use DBMS_REDEFINITION to accomplish the same thing.
(it was buggy on 9.2.0.6)

This is assuming of course that you do as other replies have suggested and verify that space
will actually be saved. In our case we re-acquired about 200G of space.

Another thing to keep in mind is that the table can be loaded in sorted order according to
most frequently used range scans.

-- 
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist

--
http://www.freelists.org/webpage/oracle-l