DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: SQL in PROC generates ORA-30036

  1. #1
    Join Date
    Sep 2000
    Location
    VA
    Posts
    343

    SQL in PROC generates ORA-30036

    I have a query, that when executed directly on SQLPLUS takes about 1.15 hours - which is agreeable given the size of the data in the tables and the sorting done. When I place this query as part of a pacakge and execute the package, just when the sorting operation is performed (I am looking at it from OEM), it errors out as "ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDO1'" - why is this ?

    Thanks.

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Does the package contain only "SELECT" statement?

    Tamil

  3. #3
    Join Date
    Sep 2000
    Location
    VA
    Posts
    343
    It does this SELECT into a for loop cursor and then does some PL/SQL calculations for each of the records and then INSERTs them one after the other into a table.

  4. #4
    Join Date
    Sep 2005
    Posts
    278
    It becoz of the INSERT statement,

    Oracle uses undo tablespaces for rollbacking any transactions, and it must have enough space for doing such.
    Last edited by tabreaz; 09-09-2005 at 02:45 PM.

  5. #5
    Join Date
    Sep 2000
    Location
    VA
    Posts
    343
    The table into which I am inserting is set o NOLOGGING Mode and I am using an 'append' hint - still no use.
    Anything else I can do apart from increasing the UNDO Tablespace ?

  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    dont you know nologging and append only works for insert select????

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