When we import data now, we ofter get Ora-01652. And I checked the free space for those tablespaces, there are plenty of free space there. Some of the up to 3 to 4 gig. Another reason I thought it's fragmented it because everytime after import data, we delete the data and wait until next import. The delete won't release the space.
Thanks for the response. Tell me more what you think!
* First export the whole user-schema with COMPRESS=Y
* Then build an INDEXFILE with the imp-command
* This file is a DDL-FILE you can see now the initital/next extent of the tables.
* Then try to group the tables size in SMALL MEDIUM LARGE XLARGE
* For every sizecategory you specify a storage
* You could build for each size-group an own Tablespace.
* Edit the INDEXFILE and adopt the NEXT and INITIAL ( should be the same ) and the TABLESPACE-CLAUSE and PCTINCREASE should always be 0, depending on the size-category
* DROP all tables
* CREATE them thru the INDEXFILE
* import your objects with IGNORE=Y
So there should be new Tablespaces which all have Objects with same storage depending on size-category.
A Reorganization should for the existings object never more be neccessary, even with Oracle 7
Originally posted by lgaorcl But why do we get ora-01652 so often?
Because you don't have enough contiguous space. This could be due to your INITIAL or NEXT values being too large. If you're terribly concerned about this error and you don't want to add the needed space, you should drop the tables before you import, coalesce the free space, and import.