Re: Table defragmentation/resizing question: Is this dangerous ?

In addition to John's advice, I should point out that there are no performance issues associated with having many extents. There is no technically sound reason to reduce the number of extents to 1 or 2.

When segments have over a couple thousand extents, it can take some time to perform truncates and drops on the tables, because of the activity required to reallocate space. But number of extents doesn't have any effect on read/write performance.

In the original poster's situation, it seems needless to rebuild these tables just because they have reached MAXEXTENTS. They might as well just set MAXEXTENTS to unlimited, and keep an eye on extent growth to make sure it doesn't get out of control.

--
Jeremiah

John wrote:
>
> Just an idea, but if you set maxextents to 99, instead of 120,
> then if you get the error, change the size of the next extent to be
> very large, then change maxextents to 101, and this should buy you
> time
> until you can take the database offline and compress the table down to
> 1 or 2
> extents with the procedure you mentioned below.
>
> Just a thought,
> JohnBryson
>
> >>>>>>>>>>>>>>>>>> Original Message <<<<<<<<<<<<<<<<<<
>
> On 1/17/00, 3:28:02 PM, gdallair_at_criq.qc.ca (Guy Dallaire) wrote
> regarding Table defragmentation/resizing question: Is this dangerous
> ?:
>
> > Suddenly, I'm starting to doubt about a procedure used here:
>
> > - On a production database, table A goes to its MAX EXTENT REACHED
>
> > Resolution:
>
> > - The DBA exports the table with export (CONSISTENT=Y)
> > - The DBA drops the table (Or truncates and adjust storage)
> > - The DBA re-imports the table
>
> > Is this OK or should I arrange for ALL USERS to exit and prevent them
> > to connect during this intervention ?
>
> > If the DBA permits the users to connect whil fixing the problem, while
> > the table is dropped and while it is in the process of being reloaded,
> > I wonder what happens if users are hitting the table (inserting or
> > deleting in it).
>
> > Isn't the integrity of the data compromised ?
>
> > Thanks
Received on Tue Jan 18 2000 - 12:54:16 CST