Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2003
    Posts
    21

    Unanswered: unable to extend table

    Hi there;
    I am definitely a newbie in ORACLE matters therefore my novice posting.
    The point is a Have a two file system-tablespace one of which has absoutely ran out of space and just as it did I began having load troubles the typical "ORA-01653: unable to extend table X by 311072 in tablespace TX", hopefully it's not due to this because within the same tablespace I have 2 other tables which are having no problem at loads.

    any Idea of how to solve the problem?

  2. #2
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    I hope you are not using the SYSTEM tablespace to load your data ...
    If so, you are asking for a LOT of problems....

    The problem is that 1 table has many rows and the table itself is setup to extend as needed (next_extent size from user_tables). The physical files that make up the tablespace also can extent (if they have been setup with autoextending on) ... Either the physical files have extened to their max limit or they have filled the drive ... If they are NOT set for autoextending, then the problem is the table needs to extend and there is not room in the tablespace for this to occur ...

    --- is the tablespace setup to autoextend ???
    sql> select file_name,AUTOEXTENSIBLE from dba_data_files;

    HTH
    Gregg

  3. #3
    Join Date
    Oct 2003
    Posts
    21
    No I'm not using the SYSTEM tablespace,
    all datafiles comprising the tablespace I m using use the whole of my file system therefore I cannot set them AUTOEXENT
    such tablespace still has room enough to store uploading rows however
    Im getting such error .

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    first try
    ALTER TABLESPACE TX COALESCE;
    and then try to reload your data.
    if this still fails, then do
    ALTER TABLE X STORAGE( NEXT 32M);
    and then try to reload your data again, again.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    SELECT FILE_NAME,BYTES,MAXBYTES,USER_BYTES FROM DBA_DATA_FILES WHERE TABLESPACE_NAME = 'xxxxx';

    SELECT SUM(BYTES) FROM DBA_EXTENTS WHERE TABLESPACE_NAME='xxxx';

    SELECT INITIAL_EXTENT,NEXT_EXTENT,MAX_EXTENTS,TABLESPACE_ NAME
    FROM USER_TABLES WHERE TABLE_NAME = 'xxxxx';

    Fill in the blanks ... and post results ...


    hth
    Gregg

  6. #6
    Join Date
    Oct 2003
    Posts
    21
    I did it and it worked far beyond the way I expected
    Thanks a lot you guys

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I did it and it worked far beyond the way I expected
    Define/clarify exactly what "it" is.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  8. #8
    Join Date
    Oct 2003
    Posts
    21
    I did this
    ALTER TABLE X STORAGE( NEXT 32M);

Posting Permissions

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