I have a question about tablespaces. Recently I ran a
query that listed all the tablespaces where the next allocated
extent would not fit in the tablespace.
My question is what my course of action should be.
Should I coalesce?
Should I export drop and import?
Or should I give more space to the tablespace?
Thanks for your assistance.
-------------------------------------------------------
Arthur,
If possible, see how many free blocks are in the table. You can:
ANALYZE TABLE xxx ESTIMATE STATISTICS;
SELECT EMPTY_BLOCKS FROM USER_TABLES WHERE TABLE_NAME = 'xxx';
This will tell you how many free blocks need to be filled up before a new
extent is needed. Sometimes you allocate the INITIAL (or even NEXT) too
large. For example, if your INITIAL is 500M and your NEXT is 500M and you
have 1 record in the table, then you will have 1 used block and thousands
of empty blocks. If the NEXT (500M) will not fit, this is OK since it will
take a while for your table to need to get another extent. Depending on
your application, this could be immediately (if you are loading data) or
not in a thousand years.
If it looks like your free blocks will soon fill up, then the preferred
method is to size your NEXT extent properly relative to your other
extents. If you still need more space, then you will need to add it to
your tablespace.
Hope this helps!
-Ari Kaplan
Independent Oracle DBA Consultant
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
<-> For 280+ Oracle tips, visit my Web Page: <->
<-> <->
<-> www.arikaplan.com <->
<-> <->
<-> email: akaplan@interaccess.com <->
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
Back to Ari Kaplan's Home Page