ORA-01653: unable to extend table xxx by xxx

I can not  - file size is max allowed by OS size.
ok - lets rephrase question - how to compress file so it will have all empty blocks contiguous?

select tablespace_name, sum(bytes)/1024/1024 from dba_free_space group by tablespace_name;

What does that show for your tablespace?

alter table abc move tablespace def;

where abc is the name of the table and def is a tablepsace that is large enlugh to hold the table. If you do that with all your tables/indexes in the ts and then move them back, you'll have reclaimed space. If you know some tables with large delete activity, try just that one and it may be enough.

The immediate issue is the ORA-01653 and the second issue is hte amount of free space (and still needing to extend).

Why don't you add another data file to get rid of the ORA-01653.

As for the free space, MathiasMagnusson's suggestion sounds good, if you can afford the downtime.  I suggest you startup the database in restricted mode OR insure the applicaiton using the tables/indexes in the tablepspace are not running.

Bertram Moshier
Oracle Certified Professional 8i and 9i DBA

http://www.bmoshier.net/bertram

I NEED ONLINE SOLUTION - DATABASE CAN NOT BE OFFLINE FOR LONG TIME - AS FOR MOVE TABLESPACE AND EXPORT/IMPORT ACTIVITIES

ANOTHER QUESTION:
COULD THIS SITUATION PREVENTED FROM THE BEGINING?

1. Verify that the next extent size for the table is indeed 100k.  If you allocated as

storage (initial 100k next 100k pctincrease 50)  or some other pctincrease other than 0

then your next extent grows to 150 then 225 etc.  If your table has many extents the next extent might be larger than you expect.

2. Yes this could have been prevented.  Use Locally managed tablespaces with uniform extent sizes.  If you are on a pre-8i version of Oracle you don't have LMT.  Let me know and I can summarize an Oracle white paper on how to simulate LMT and put a link to the paper.

extent size is ok - there is no pctincrease specified - it is 0

I found that tablespace is "managed in the dictionary"
What will I gain switching it to managed localy - could you give me a link (if I dont find it sooner)

I cut this from Metalink.  I prefer the uniform extent size option.  That way, you can make your extent size 1m, then every allocation in the tablespace is 1m so every free chunk is a multiple of 1m.


A Locally Managed Tablespace is a tablespace that manages its own extents  maintaining a bitmap in each datafile to keep track of the free or used status  of blocks in that datafile. Each bit in the bitmap corresponds to a block or  a group of blocks. When the extents are allocated or freed for reuse, Oracle  changes the bitmap values to show the new status of the blocks. These changes  do not generate rollback information because they do not update tables in the  data dictionary (except for tablespace quota information), unlike the default  method of Dictionary - Managed Tablespaces.  

** Reduced recursive space management  
** Reduced contention on data dictionary tables  
** No rollback generated  
** No coalescing required

Advantages


 - Because locally managed tablespaces do not record free space in data    dictionary, it reduces contention on these tables.  
 - Local management of extents automatically tracks adjacent free space,    eliminating the need to coalesce free extents.
 - Avoids recursive space management operations, which can occur in    dictionary-managed tablespaces if consuming or releasing space in an    extent results in another operation that consumes or releases space in    a rollback segment or data dictionary table.  
 - Sizes of extents that are managed locally can be determined automatically    by the system. Alternatively, all extents can have the same size in a    locally managed tablespace.  
 - Changes to the extent bitmaps do not generate rollback information    because they do not update tables in the data dictionary (except for    special cases such as tablespace quota information).  
 - Reduced fragmentation
 

how this impact performance?

LMT (Locally Managed Tablespaces) improve performance (for one because to allocate/deallocate an extent the system no longer has to touch the data dictionary).

BTW, Oracle stated direction is to only support LMT in the future.

LMT are the only way to go in Oracle 9i!

Bertram Moshier
Oracle Certified Professional 8i and 9i DBA

http://www.bmoshier.net/bertram

- Defragment the Tablespace:  

how?

First:
Run this query (from Metalink) to be sure that the tablespace needs to be defragmented (Use SQL Plus)

select substr(ts.name, 1,10) TableSpace,
to_char(f.file#,990) "file #",
tf.blocks blocks,
sum(f.length) free,
to_char(count(*),9990) frags,
max(f.length) bigst,
to_char(min(f.length),999990) smllst,
round(avg(f.length)) avg,
to_char(sum(decode(sign(f.length-5), -1, f.length,0)),99990) dead
from sys.fet$ f, sys.file$ tf, sys.ts$ ts
where ts.ts# = f.ts#
and ts.ts# = tf.ts#
group by ts.name, f.file#, tf.blocks;

Before you defrag try coalescing free space in the Tablespace
(The extents must be adjacent to each other for this to work) Use sql plus

ALTER tablespace XYZ  COALESCE;

To defrag:
(1)export using owner mode, compress = Y.
(2)drop the tablespace.
(3)create the tablespace.
(4)import  

another easy one to aschieve this with downtime

1. Add a new data file
2. create a new_table with the same structure as the original one
3. put the table into read-only mode
4. export the data to the new table
5. replace the odl table new new one
6. get rid of unsued table space if necessary