ORA-01653: unable to extend table
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: ORA-01653: unable to extend table

  1. #1
    Join Date
    Aug 2001
    Posts
    56

    ORA-01653: unable to extend table

    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.
    Anuj
    India

  2. #2
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    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
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  3. #3
    Join Date
    Dec 2002
    Location
    India
    Posts
    34
    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

  4. #4
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    Yes resumable parameter was introduced in 9iRel1...
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width