1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

URGENT: Err Code 1653 Input truncated by 23 characters

Discussion in 'SQL PL/SQL' started by man_expo, Nov 11, 2009.

  1. man_expo

    man_expo Active Member

    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    55
    URGENT: Err Code 1653 Unable to extend table in tablespace

    I am running a PL SQL procedure which will be called through a unix script. This procedure will run some update statements on several tables. I am getting an error code -1653. The table I am trying to update has 65000 rows. I am able to successfully update 3 of its columns. On attempting to update the fourth column the error occurs. Apart from this column one more column has to be updated(Total 5 columns).

    The exact error message is 'unable to extend table ABCD.EFGH by 30 in tablespace XYZ'

    I ran the following query.

    Code (SQL):
     
    select max(bytes/1024/1024) max_Mega from dba_free_space fs
    where fs.tablespace_name='XYZ'
    Code (SQL):
     

    and got the result as MAX_MEGA = 0.1171875

    1653 I suppose is something related to table extents.
    1. Can someone tell me how can I increase the table extent. I think this can be increased by ALTER TABLESPACE or by using ORACLE ENTERPRISE MANAGER. Can anyone explain the exact procedure for both?
    2. Can there be any problem if I increase extent size to UNLIMITED? What is autoextent?
    3. How is extent size exceeded when UPDATE statement is run?
    4. Will I need the extra space allocated for table extent after COMMIT, considering that the original table has been existing and functioning quite fine for sometime.
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,346
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Re: URGENT: Err Code 1653 Unable to extend table in tablespace

    I expect you're running Oracle 10.2 or later, which means your tablespaces are locally managed; you cannot alter the next extent sizes of locally managed tablespaces after they have been created. Your best option is to increase the size of the tablespace by either extending the current datafile or adding a new one.

    Adding data to a table increases its size; when there is insufficient space to allocate the next extent for a table in a tablespace you get the error you reported. Adding space or deleting records will provide more room for new rows.
     
  3. sundaranivas

    sundaranivas Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    your answer based on increase the size or update the lot of data into database.but i want to initialize the array variable from user input using for loop.it can't achieved by me.
    anyway thanks for your answer.
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,346
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Re: URGENT: Err Code 1653 Unable to extend table in tablespace

    You have simply run out of available space in tablespace XYZ, at least the space necessary to extend this table in that tablespace. Either resize the current datafile or add another to this tablespace and try the procedure again. You seriously need to read the Concepts Guide; these questions are answered there. Simply because there was enough space in the currently allocated extents to store data doesn't mean those extents don't fill up; they do. When they do additional extents are necessary to store the data. Oracle cannot allocate a new extent in tablespace XYZ because there is not enough space to do so (you're at least 30 blocks short of having enough space). Again, you need to read the Concepts Guide.