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
Check if your using CONNECT BY clause in any of your query which might be causing and end less loop.
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.
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
Re: error in using dbms_utility.compile_schema
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);
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.
Rohit S Nirkhe,Oracle/Apps DBA
thank you jmodic.
i managed with script.
Click Here to Expand Forum to Full Width