error in using dbms_utility.compile_schema
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: error in using dbms_utility.compile_schema

  1. #1
    Join Date
    Jan 2003
    Location
    india
    Posts
    175

    error in using dbms_utility.compile_schema

    i use oracle 8.1.5 on winNT.

    When i try to compile all the packages of a particular
    schema using dbms_utility.compile_schema procedure
    i get the following error (please see the screen shot).

    can anybody help to solve this?


    SQL> exec dbms_utility.compile_schema('PHARM');
    BEGIN dbms_utility.compile_schema('PHARM'); END;

    *
    ERROR at line 1:
    ORA-01436: CONNECT BY loop in user data
    ORA-06512: at "SYS.DBMS_UTILITY", line 195
    ORA-06512: at line 1

    SQL>


    with thanks,
    -Raja

  2. #2
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    Check if your using CONNECT BY clause in any of your query which might be causing and end less loop.

    regards
    anandk
    anandkl

  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    It's bug 895238, fixed in 8.1.7. You can't doo much about it, except for create your own procedure/script, that will individualy compile all objects in a given schema.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  4. #4
    Join Date
    Feb 2001
    Location
    Bombay,India
    Posts
    530

    Re: error in using dbms_utility.compile_schema

    Hi
    This is likely bug 895238. If you use the dbms_utility.compile_schema() command and receive an ORA-1436
    and the following select returns a non-zero count then you are probably seeing this bug :

    select count(*) from obj$ where type# in (28,29,30,31);

    Workaround:
    Write a plsql program to compile all objects individually that would have been compiled by dbms_utility.compile_schema(). Ie, procedures, functions, packages and package bodies.
    This bug is fixed in 8.1.7 but a request for inclusion for 8.1.5 has been submitted and approved. A future 8.1.5 patchset will include the fix as well.

    Regards
    Rohit S Nirkhe,Oracle/Apps DBA
    OCP 8i,9i
    Thanks and Regards
    Rohit S Nirkhe
    rohitsnirkhe@rediffmail.com

  5. #5
    Join Date
    Jan 2003
    Location
    india
    Posts
    175
    thank you jmodic.

    i managed with script.

    -Raja

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