Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: ORA-01654 Fix?

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US