Max extents reached

[malay boy]

Kenny,
7.2 cannot increase the extend ???.In this case then just export the table and import back but make the extend size bigger...
Well this might be a good idea because the tables have a lot of insert and deletes....

insert/delete definately will make your table defrag and only export/import will help you.

is coalesce exist in 7.2 ???? Surely 8 you can do manual coelesce...


good luck.

regards
mB

[kenny chia]

Hi mB
I get what you mean by coalesce. But Oracle 7.2 does not support Alter Tablespace coalesce

[Indira Aramandla]

Hi,

For a table that has frequent deletes and inserts, it has tobe re-organised every now and then. For this you can cron job the task to export and then import the table data say once every week.
For the first time when you export the table data, drop the table and re-create the table with good size of initial and next extents. This will prevent you table from extending into too many extents.

There are other oprions as well. But as you are usng Oracle 7.2, the export and import of table data should fix the issue of extending to many extents. Choose and convenient time when the table is is not beeing used then schedule the cron job.


IA

[Volker Borowski]

Hi Kenny,

this is bad news. So there is no way out than to reorganize. Indira listed the steps already. If you do not do this frequently, but only every now and then, be sure to assign only 100 for MAX-Extents in your reorg.

When you bounce this one, you can increase NEXT and set MAX to 110 and schedule your next reorg.
When you bounce 110 you can increase NEXT and set MAX to 115 and schedule an urgent reorg.
When you bounce 115 you can increase NEXT and set MAX to 119 and schedule a very very very urgent reorg and still have two spares left. :-)

Another thing you might consider is to use PCT_INCREASE, which will increase the NEXT size every time a new extent is allocated. But usually nobody likes this, because it requires a bit more of monitoring to avoid unexpected grow of segments.

Good luck
Volker