Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2004
    Posts
    205

    Unanswered: compilation issue ?

    Hi,

    when I compiled my Oracle PL/SQL file, it gives the following error :

    ERROR at line 1:
    ORA-00604: error occurred at recursive SQL level 1
    ORA-01653: unable to extend table SYS.IDL_UB1$ by 16 in tablespace SYSTEM
    ORA-27059: skgfrsz: could not reduce file size
    Linux Error: 22: Invalid argument
    Additional information: 1

    I could find the issue from the below document :

    http://www.orafaq.net/maillist/oracl...05/14/1263.htm

    Is there anyway to resove this issue ?

    Please advice.

    Thanks,
    Sam

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    PHP Code:
    Select round(sum(bytes)/1024/10242Megabytes,
            
    sum(extentsextents
    from  sys
    .dba_segments 
    Where segment_type in 
    ('TABLE''TABLE PARTITION''TABLE SUBPARTITION'
    and 
    segment_name='IDL_UB1$'
    I only have about 16 Meg worth of data in my table.
    I imagine yours is pretty high.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Mar 2004
    Posts
    205
    Duck,

    Thanks for your reply. Mine is :

    MEGABYTES EXTENTS
    ---------- ----------
    83.26 667

    So, now how to solve this issue, any solution. Please advice.

    Thanks,
    Sam

  4. #4
    Join Date
    Mar 2004
    Posts
    205
    Any update on this post ?

  5. #5
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Just try to allocate more space to the datafile of the tablespace SYSTEM. You do not have any extents, so I guess you are not running this tablespace SYSTEM with the AUTO EXTEND option on, which could also fix the problem (allthough not recommended!).

  6. #6
    Join Date
    Mar 2004
    Posts
    205
    I did autoextend, but still I get the below error :

    1 create or replace procedure proc1
    2 is
    3 begin
    4 null
    5* end
    SQL> create or replace procedure proc1
    2 is
    3 begin
    4 null;
    5 end;
    6 /
    create or replace procedure proc1
    *
    ERROR at line 1:
    ORA-00604: error occurred at recursive SQL level 1
    ORA-01653: unable to extend table SYS.IDL_UB1$ by 16 in tablespace SYSTEM

  7. #7
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    By chance do you have maxextents set in the SYSTEM tablespace and
    SYS.IDL_UB1$ has now reached that limit ???

    sql> select max_extents from dba_tablespaces where tablespace_name = 'SYSTEM';

    sql> SELECT SUM(BYTES) "Size", count(*) "Extents" FROM DBA_EXTENTS WHERE SEGMENT_NAME = 'IDL_UB1$'

    Gregg

  8. #8
    Join Date
    Jul 2003
    Posts
    2,296
    following what gregg said, I believe you need to increase the max extents at the table level.
    PHP Code:
      1select max_extents from dba_tables where table_name 'IDL_UB1$'
    > /

    MAX_EXTENTS
    -----------
     
    2147483645 
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  9. #9
    Join Date
    Mar 2004
    Posts
    205
    Hi,

    Below is the output from my Oracle DB. What can I do, to solve this issue ?


    SQL> select max_extents from dba_tablespaces where tablespace_name = 'SYSTEM';

    MAX_EXTENTS
    -----------
    2147483645

    SQL> SELECT SUM(BYTES) "Size", count(*) "Extents" FROM DBA_EXTENTS WHERE SEGMENT_NAME = 'IDL_UB1$'\
    2

    SQL> SELECT SUM(BYTES) "Size", count(*) "Extents" FROM DBA_EXTENTS WHERE SEGMENT_NAME = 'IDL_UB1$'
    2 ;

    Size Extents
    ---------- ----------
    87302144 667

    SQL>

  10. #10
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    You have roughly an 84meg table with 667 extents...

    a) Look at dba_tables to ensure the max extents on the table are not
    set at 667 (as Duck said)

    b) Is it possible that the disk drive the system tablespace is located on
    is full and the system tablespace cannot extent (You stated above that you had autoextend on the system tablespace ... How much do you have
    set for the NEXT extent of the tablespace data file ??? Does the disk drive where the datafile have THAT MUCH space available ???)

    HTH
    Gregg

Posting Permissions

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