| Compile sechema Error [message #37868] |
Mon, 04 March 2002 10:12  |
jie
Messages: 10
Registered: March 2002
|
Junior Member |
|
|
When I ran the the compile_schema procedure, I got the following error on some of my servers. Some not. What is the problem? I have more than one schema on the same database that have the same stored procedures. Thanks
SQL> execute dbms_utility.compile_schema('RETAILEASE');
*
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
|
|
|
|
| Re: Compile sechema Error [message #37870 is a reply to message #37868] |
Mon, 04 March 2002 10:51  |
Grant
Messages: 578
Registered: January 2002
|
Senior Member |
|
|
Make sure there are no invalid objects in the SYS schema.
sqlplus internal
SQL> select object_name,object_type from dba_objects where status<>'VALID' and owner='SYS';
If there are invalid objects compile them individually. Use the ALTER command to compile the invalid object.
SQL> alter view VIEWNAME compile;
SQL> alter procedure PROCNAME compile;
SQL> alter procedure PROCNAME compile body;
and so on...
Then try it again. If this isn't the problem you may have hit a bug. Check the Oracle site (MetaLink). You can also get around the problem with dynamic sql. Here is a script that will do it for you. It doesn't have all objects but you can add ass needed.
rem Use this to dynamically alter user objects that are invalid.
rem Connect as SYS.
rem Created by Grant Howell
ACCEPT sowner prompt 'Enter Schema: '
set pagesize 10000
set echo off
set serveroutput off
set feedback off
set head off
set verify off
set linesize 80
spool compinv.sql
select 'alter view ' || owner || '.' || object_name || ' compile;'
from dba_objects where object_type='VIEW'
and status='INVALID' and owner=upper('&sowner')
/
select 'alter procedure ' || owner || '.' || object_name || ' compile;'
from dba_objects where object_type='PROCEDURE'
and status='INVALID' and owner=upper('&sowner')
/
select 'alter package ' || owner || '.' || object_name || ' compile;'
from dba_objects where object_type='PACKAGE'
and status='INVALID' and owner=upper('&sowner')
/
select 'alter package ' || owner || '.' || object_name || ' compile body;'
from dba_objects where object_type='PACKAGE BODY'
and status='INVALID' and owner=upper('&sowner')
/
select 'alter trigger ' || owner || '.' || object_name || ' compile body;'
from dba_objects where object_type='TRIGGER'
and status='INVALID' and owner=upper('&sowner')
/
spool off
set serveroutput on
set feedback on
set head on
set verify on
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
