Re: ORA-01654 Fix?

From: <bdbafh_at_gmail.com>
Date: 22 Dec 2005 15:31:29 -0800
Message-ID: <1135294289.251448.321390@f14g2000cwb.googlegroups.com>

thanks for the info.
IIRC, locally managed tablespaces weren't available in 8.1.5 (though I never used 8.1.5).
As this is standard edition, online reorganization of tables and indexes is not supported.
Here is an article discussing move/rebuild of segments: http://www.dbazine.com/oracle/or-articles/hordila1

I seem to have misplaced my otn credentials. The 8.1.7 docs are no longer available online on otn. This is a link into the administrator's guide - "Moving a Table to a New Segment or Tablespace"
http://www.oracle.com/pls/db92/db92.to_URL?remark=drilldown&urlname=http:%2F%2Fdownload-west.oracle.com%2Fdocs%2Fcd%2FB10501_01%2Fserver.920%2Fa96521%2Ftables.htm%236359

This is an offline operation - as indexes will be marked as unusable until they are rebuilt.

1. schedule a maintenance window
2. prepare your re-org scripts
3. backup the database
4. create the new tablespaces (nologging)
5. execute the script to alter table <table_name> move tablespace <tablespace_name> nologging
6. execute the script to rebuild the indexes (nologging) alter index <index_name> rebuild tablespace <tablespace_name> nologging
7. alter the tablespace, tables and indexes to logging
8. gather stats via dbms_utility.analyze_table (defaults to cascade=>true)
9. backup the database again
10. UAT

btw - you might want to alter the storage parameters for the tables and indexes prior to the rebuild - such as to set pctincrease=0, initial_extent = next extent.
"How to Stop Defragmenting and Start Living" http://www.oracle.com/technology/deploy/availability/pdf/defrag.pdf

hth.

-bdbafh Received on Thu Dec 22 2005 - 17:31:29 CST