An alternative would be to use DBMS_REDEFINITION to recreate tables in a new tablespace. The downtime to switch to the new table is minimal.
A Caveat here:I haven't actually used DBMS_REDEFINITION for this. I have done it manually using materialized views on prebuilt tables, which I believe is the mechanism underlying DBMS_REDEFINITION.
DBMS_REDEFINITION was buggy on the Oracle version I had to do this on, hence the manual process. Great learning experience. :)
There is one requirement for doing this: Your table must either have a primary key, or it must have column(s) upon which you can temorarily build a PK. Some systems (such as SAP) do not use PK in the database, but they can be easily added and dropped.
Jared
Our production database is of version 9.2 but still using DMT because it is upgraded from 8174. And we want to move the table to a LMT tablespace, althougth we can use some oracle package to convert DMT to LMT, but it's not perfect as TOM pointed. We can reorg these tables while the database is online, but the performance will be adversely effected.
So is it possible to create a logical standy based on the production database, and then reorg the logical standby, after the reorg is done, then switch the role of the standby to primary database? As far as I know, the logical standy use sql appying instead of redo applying, so the transaction should be able to applied to logical standby while the reorg is under going.
------------------------------ Sneak preview the all-new Yahoo.com<http://us.rd.yahoo.com/evt=40762/*http://www.yahoo.com/preview>. It's not radically different. Just radically better.
-- Jared Still Certifiable Oracle DBA and Part Time Perl Evangelist