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: Table Rebuild Options

Re: Table Rebuild Options

From: Pete's <empete2000_at_yahoo.com>
Date: 9 Apr 2003 12:39:19 -0700
Message-ID: <6724a51f.0304091139.600e91e1@posting.google.com>


Your other option is to perform CTAS(Create Table As), drop the foreign keys, rename the current table to an _old or something, drop the _old's indexes, then rename the new table to the production name and add the contraints back in. With CTAS, you'd be able to hint it such that it order's the rows via the primary key or how ever you would like. You would set the storage parameters such that you would fill the data blocks almost to their entirety and then re-setting the storage options for new rows coming in.

HTH,
Pete's

"Joseph Wilson" <me_at_home.now> wrote in message news:<BGMka.4645$2x2.2159084_at_dca1-nnrp1.news.algx.net>...
> I have a table that uses a lob column that was setup incorrectly(32k chunk
> size, 50 PCTVERSION for rows with an average length of 400). The table has
> been running this way for quite some time and has grown significantly. This
> is within an oracle 8.0.4 database and I don't believe there is any way to
> rebuild the lob segment(if there is, please inform me). So based on that
> assumption, I am going to rebuild the table with more optimal lob settings.
> Unfortunately, this table has a primary key that is referenced as a foreign
> key within many tables. This table also has some columns that are part of
> some indexes.
>
> Now my question, is the simplest way to rebuild this table in 8.0.4 to just
> disable the constraints, drop the indexes, move the data out via export or
> just to another table, then drop the table, create the new table with the
> same name and same settings, then put the data back and enable constrants
> and create indexes? I am prepared to go this route, but wanted to ask and
> make sure I didn't have any other options before proceding. Thanks for any
> help you can provide.
>
> Joe
Received on Wed Apr 09 2003 - 14:39:19 CDT

Original text of this message

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