ORA-01653: unable to extend table

[anuj]

importing TIR's objects into TIR
. . importing table "TB_TIR0ACO0"
IMP-00058: ORACLE error 1653 encountered
ORA-01653: unable to extend table TIR.TB_TIR0ACO0 by 256 in tablespace TS_TIR001
IMP-00028: partial import of previous table rolled back: 280588 rows rolled back


When I see Oracle Error, the action for this erro was:
Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated

well it is neseccary to add new data file, even my datafile size is just 100 MB. I have Autoextended now to 512K. will this solve my problem or anyhow I have to add new data file for the tablespace.

[adewri]

If there is enough space on the disk then enable the autoextend option. Also you can use the parameter resumable=y during import so that whenever there is space problem the imp will pause. This way you can increase the datafile size or add another datafile and continue with the import from that point.

PS: resumable=y is available in 8i(not sure about the lowest version) and above.

HTH

[ksridhar]

This info might help u..

this is from metalink..

Amar the resumable option is available in Oracle 9i version but not in Oracle 8i.. pls check.

PURPOSE
-------
This bulletin is an overview of ORA-1653 error message for tablespace dictionary managed.

SCOPE& APPLICATION
------------------
It is for users requiring further information on ORA-01653 error message.


Error: ORA-01653
Text: unable to extend table %s.%s by %s in tablespace %s
-------------------------------------------------------------------------------
Cause: Failed to allocate an extent for table segment in tablespace.
Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more
files to the tablespace indicated.


Explanation:
------------
This error does not necessarily indicate whether or not you have enough space
in the tablespace, it merely indicates that Oracle could not find a large enough area of free
contiguous space in which to fit the next extent.


Diagnostic Steps:
-----------------
1. In order to see the free space available for a particular tablespace, you must
use the view DBA_FREE_SPACE. Within this view, each record represents one
fragment of space. How the view DBA_FREE_SPACE can be used to determine
the space available in the database is described in:
[NOTE:121259.1] Using DBA_FREE_SPACE

2. The DBA_TABLES view describes the size of next extent (NEXT_EXTENT) and the
percentage increase (PCT_INCREASE) for all tables in the database.
The "next_extent" size is the size of extent that is trying to be allocated (and for
which you have the error).

When the extent is allocated :
next_extent = next_extent * (1 + (pct_increase/100))

Algorythm to allocate extent for segment is described in the Concept Guide
Chapter : Data Blocks, Extents, and Segments - How Extents Are Allocated

3. Look to see if any users have the tablespace in question as their temporary tablespace.
This can be checked by looking at DBA_USERS (TEMPORARY_TABLESPACE).

Possible solutions:
-------------------
- Manually Coalesce Adjacent Free Extents
ALTER TABLESPACE COALESCE;
The extents must be adjacent to each other for this to work.

- Add a Datafile:
ALTER TABLESPACE ADD DATAFILE ''
SIZE ;

- Resize the Datafile:
ALTER DATABASE DATAFILE '' RESIZE ;

- Enable autoextend:
ALTER DATABASE DATAFILE ?? AUTOEXTEND ON
MAXSIZE UNLIMITED;

- Defragment the Tablespace:

- Lower "next_extent" and/or "pct_increase" size:
ALTER STORAGE ( next
pctincrease );

- If the tablespace is being used as a temporary tablespace, temporary segments may
be still holding the space.

Amar the resumable option is available in Oracle 9i version but not in Oracle 8i.. pls check.

Thanks
Sridhar

[adewri]

Yes resumable parameter was introduced in 9iRel1...