Re: Best way to reduce number of extents?

one pretty efficient way is to use export/import with the COMPRESS=Y flag on the export. That compresses all the extents into 1 large extent. However, this does require unloading and reloading the data. Depending on the size of the table, another alternative, provided you have sufficient free space, would be to create a temporary table with proper extent sizings, insert all the data from the real table into the temporary table, rename the original table to a fake name, rename the temporary table to the original table name, and drop the phone (original) table. Using the SQL*Plus AUTOCOMMIT feature should help minimize the chance of overflowing your rollback segments. The downside to this is that is most likely will cause free space fragmentation in the tablespace, and may require you to increase the tablespace size to accommodate 2 copies of the table. The upside is that you have minimal downtime, just long enough to rename the tables. If the table is highly active, you will need to find a way to make sure you get consistent data copied across.

HTH,
Roy

In article <81gf1f$3b3$1_at_newsmaster01.magnet.at>,   "Bernd Maierhofer" <BerndM_at_dato.at> wrote:
> I wondered what would be the best approach for reducing the number of
> extents in an O8 database. Can this be done wothout unloading the
data? Can
> this be automated? Like making a stored procedure which unloads the
data,
> reconfigures the table, loads the data?
>
> Thank you for your advice!
>
> --
> dato Denkwerkzeuge - Bernd Maierhofer
> Corneliusgasse 4/5
> A-1060 Wien , Austria
> support_at_dato.at
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Nov 24 1999 - 11:16:51 CST