ORA-01653: unable to extend table USR.TEST1 by 256 in tablespace TESTSP
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: ORA-01653: unable to extend table USR.TEST1 by 256 in tablespace TESTSP

Hybrid View

  1. #1
    Join Date
    Aug 2000
    Posts
    194
    Hi :

    I have a locally managed tablespace (TESTSP) created with AUTOALLOCATE clause. Initilaly it was created with 1MB datafile and then added another 1MB datafile.

    Now when I try to add records (total size less than 40KB) into the table in this tablespace I am getting the following error:

    "ORA-01653: unable to extend table USR.TEST1 by 256 in tablespace TESTSP".

    I queried the dictionary tables and it seems the table/tablespace is using only one of the datafiles (only 1024K is occupied). I have almost 1MB of free space in the tablespace.

    DBA_EXTENTS shows 16 extents with 64KB each.

    Now, my question is about the AutoAllocate clause of the Locally Managed Tablespace.

    1, Am I missing anything here?

    2, When I add more data files after the creation of the Tablespace, wouldn't it be taken into consideration?

    3, If adding a datafile, doesn't help, is the only other way to "Migrate the locally-managed tablespace to a dictionary management" and then alter the extent parameter?.

    4, could anyone of you point me to some good documentation on how the autoallocate clause works. (I went thru the oracle manuals, but I couldn't get much info on why the insert fails even though there is enough space)

    Thanks,

  2. #2
    Join Date
    Aug 2000
    Posts
    194
    correction,
    ------------------------------------------------------------------------
    I queried the dictionary tables and it seems the table/tablespace is using only "one of the datafiles" (only 1024K is occupied).
    ------------------------------------------------------------------------

    I am not sure on this, but I do know only 1MB is used. 15 extents are on the 1st datafile and the 16th on is on the newly added datafile.

  3. #3
    Join Date
    Mar 2000
    Location
    CA
    Posts
    317
    Are you sure that, the second datafile is attached to this TB?
    Thanks
    Kishore Kumar

  4. #4
    Join Date
    Aug 2000
    Posts
    194
    I am very sure.

    See the o/p of the following qry,

    select substr(file_name,1,40) "FileName", tablespace_name TS, bytes, status,
    AUTOEXTENSIBLE, maxbytes,user_bytes, user_blocks
    from dba_data_files
    where tablespace_name = 'TESTSTP';


    FileName TS BYTES STATUS AUT MAXBYTES USER_BYTES USER_BLOCKS
    ---------------------------------- ------ ------- --------- ----- -------- ---------- -----------
    /oracle/dbacct/oradata/testsp01.db TESTSP 1048576 AVAILABLE NO 0 983040 240
    /oracle/dbacct/oradata/testsp02.db TESTSP 1048576 AVAILABLE NO 0 983040 240

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