|
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|