RE: RECOMPILE INVALID OBJECTS
You can use the $OH/rdbms/admin/utlrp.sql even in 7.X.
However in this last case you must edit and change
where you find type# for type without #. App has a
similar procedure.
Regards.
--- Glenn Travis [EMAIL PROTECTED] wrote:
If you've got a big schema (READ: Apps), this will
cripple you. Been there, done that, and will never
do it again in an Apps database.
I have found it always better to generate my own
recompile sql (see script at bottom of my notes).
Footnote: Read the docs for 8.1.7 as Oracle has
added another parameter to this command.
DBMS_UTILITY.COMPILE_SCHEMA (schema, FALSE);
The newer version of DBMS_UTILITY.COMPILE_SCHEMA
(schema) has an extra boolean argument which
defaults to TRUE (compile everything) for backwards
compatibility, However, due to the problem of object
dependancies being circular in the sys.dependancy$
table (causing the original problem with
sys.ORDER_OBJECT_BY_DEPENDENCY view), Many objects
are actually INVALIDATED by the default usage.
Solution:
Running DBMS_UTILITY.COMPILE_SCHEMA (schema,
FALSE) will only compile the INVALID objects and
seems to work much better.
RECOMPILE SCRIPT:
set heading off
set pagesize 0
set lines 79
set verify off
set echo off
set feedback off
spool comp_all.tmp
select
decode( OBJECT_TYPE, 'PACKAGE BODY',
'alter package ' || OWNER||'.'||OBJECT_NAME || '
compile body;',
'alter ' || OBJECT_TYPE || ' ' ||
OWNER||'.'||OBJECT_NAME || ' compile;'
)
from
dba_objects a,
sys.order_object_by_dependency b
where
A.OBJECT_ID = B.OBJECT_ID(+) and
STATUS = 'INVALID' and
OBJECT_TYPE in ( 'PACKAGE BODY', 'PACKAGE',
'FUNCTION', 'PROCEDURE',
'TRIGGER', 'VIEW' )
order by
DLEVEL DESC,
OBJECT_TYPE,
OBJECT_NAME;
spool off
@comp_all.tmp
-Original Message-
From: Mohan, Ross [mailto:[EMAIL PROTECTED]]
Sent: Monday, December 17, 2001 2:25 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: RECOMPILE INVALID OBJECTS
just out of curiousity, does this buy one
something extra over/above:
execute dbms_utility.compile_schema('user');
-Original Message-
Sent: Monday, December 17, 2001 1:58 PM
To: Multiple recipients of list ORACLE-L
start
--
set heading off
set pagesize 0
set linesize 79
set verify off
set echo off
spool recomp_all.tmp
select decode( OBJECT_TYPE, 'PACKAGE BODY',
'alter package ' || OWNER||'.'||OBJECT_NAME ||
' compile body;',
'alter ' || OBJECT_TYPE || ' ' ||
OWNER||'.'||OBJECT_NAME
|| ' compile;'
)
from dba_objects A, sys.order_object_by_dependency
B
where A.OBJECT_ID = B.OBJECT_ID(+) and
STATUS = 'INVALID' and
OBJECT_TYPE in ( 'PACKAGE BODY', 'PACKAGE',
'FUNCTION', 'PROCEDURE',
'TRIGGER', 'VIEW' )
order by DLEVEL DESC, OBJECT_TYPE, OBJECT_NAME;
spool off
@recomp_all.tmp
end
Raj
__
Rajendra Jamadagni MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't
reflect
that of ESPN Inc.
QOTD: Any clod can have facts, but having an
opinion is an art!
--
Please see the official ORACLE-L FAQ:
http://www.orafaq.com
--
Author: Mohan, Ross
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051
FAX: (858) 538-5051
San Diego, California-- Public Internet
access / Mailing Lists
To REMOVE yourself from this mailing list, send an
E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of
'ListGuru') and in
the message BODY, include a line containing: UNSUB
ORACLE-L
(or the name of mailing list you want to be
removed from). You may
also send the HELP command for other information
(like subscribing).
--
Please see the official ORACLE-L FAQ:
http://www.orafaq.com
--
Author: Glenn Travis
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX:
(858) 538-5051
San Diego, California-- Public Internet
access / Mailing Lists
To REMOVE yourself from this mailing list, send an
E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of
'ListGuru') and in
the message BODY, include a line containing: UNSUB
ORACLE-L
(or the name of mailing list you want to be removed
from). You may
also send the HELP command for other information
(like subscribing).
=
ENG. Christian Trassens
Senior DBA
[EMAIL PROTECTED]
[EMAIL PROTECTED]
Phone : +34-699240979
+34-649824704
__
Do You Yahoo!?
Check out Yahoo! Shopping and Yahoo! Auctions for all of
your
RE: RECOMPILE INVALID OBJECTS
I ran the script below :
-
select decode( OBJECT_TYPE, 'PACKAGE BODY',
'alter package ' || OWNER||'.'||OBJECT_NAME ||
' compile body;',
'alter ' || OBJECT_TYPE || ' ' ||
OWNER||'.'||OBJECT_NAME
|| ' compile;'
)
from dba_objects A, sys.order_object_by_dependency B
where A.OBJECT_ID = B.OBJECT_ID(+) and
STATUS = 'INVALID' and
OBJECT_TYPE in ( 'PACKAGE BODY', 'PACKAGE','FUNCTION', 'PROCEDURE',
'TRIGGER', 'VIEW' )
order by DLEVEL DESC, OBJECT_TYPE, OBJECT_NAME
/
---
I get the following error:
ERROR at line 8:
ORA-01436: CONNECT BY loop in user data
Whats this error ???
-rM
-Original Message-
Trassens
Sent: Tuesday, December 18, 2001 2:05 PM
To: Multiple recipients of list ORACLE-L
You can use the $OH/rdbms/admin/utlrp.sql even in 7.X.
However in this last case you must edit and change
where you find type# for type without #. App has a
similar procedure.
Regards.
--- Glenn Travis [EMAIL PROTECTED] wrote:
If you've got a big schema (READ: Apps), this will
cripple you. Been there, done that, and will never
do it again in an Apps database.
I have found it always better to generate my own
recompile sql (see script at bottom of my notes).
Footnote: Read the docs for 8.1.7 as Oracle has
added another parameter to this command.
DBMS_UTILITY.COMPILE_SCHEMA (schema, FALSE);
The newer version of DBMS_UTILITY.COMPILE_SCHEMA
(schema) has an extra boolean argument which
defaults to TRUE (compile everything) for backwards
compatibility, However, due to the problem of object
dependancies being circular in the sys.dependancy$
table (causing the original problem with
sys.ORDER_OBJECT_BY_DEPENDENCY view), Many objects
are actually INVALIDATED by the default usage.
Solution:
Running DBMS_UTILITY.COMPILE_SCHEMA (schema,
FALSE) will only compile the INVALID objects and
seems to work much better.
RECOMPILE SCRIPT:
set heading off
set pagesize 0
set lines 79
set verify off
set echo off
set feedback off
spool comp_all.tmp
select
decode( OBJECT_TYPE, 'PACKAGE BODY',
'alter package ' || OWNER||'.'||OBJECT_NAME || '
compile body;',
'alter ' || OBJECT_TYPE || ' ' ||
OWNER||'.'||OBJECT_NAME || ' compile;'
)
from
dba_objects a,
sys.order_object_by_dependency b
where
A.OBJECT_ID = B.OBJECT_ID(+) and
STATUS = 'INVALID' and
OBJECT_TYPE in ( 'PACKAGE BODY', 'PACKAGE',
'FUNCTION', 'PROCEDURE',
'TRIGGER', 'VIEW' )
order by
DLEVEL DESC,
OBJECT_TYPE,
OBJECT_NAME;
spool off
@comp_all.tmp
-Original Message-
From: Mohan, Ross [mailto:[EMAIL PROTECTED]]
Sent: Monday, December 17, 2001 2:25 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: RECOMPILE INVALID OBJECTS
just out of curiousity, does this buy one
something extra over/above:
execute dbms_utility.compile_schema('user');
-Original Message-
Sent: Monday, December 17, 2001 1:58 PM
To: Multiple recipients of list ORACLE-L
start
--
set heading off
set pagesize 0
set linesize 79
set verify off
set echo off
spool recomp_all.tmp
select decode( OBJECT_TYPE, 'PACKAGE BODY',
'alter package ' || OWNER||'.'||OBJECT_NAME ||
' compile body;',
'alter ' || OBJECT_TYPE || ' ' ||
OWNER||'.'||OBJECT_NAME
|| ' compile;'
)
from dba_objects A, sys.order_object_by_dependency
B
where A.OBJECT_ID = B.OBJECT_ID(+) and
STATUS = 'INVALID' and
OBJECT_TYPE in ( 'PACKAGE BODY', 'PACKAGE',
'FUNCTION', 'PROCEDURE',
'TRIGGER', 'VIEW' )
order by DLEVEL DESC, OBJECT_TYPE, OBJECT_NAME;
spool off
@recomp_all.tmp
end
Raj
__
Rajendra Jamadagni MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't
reflect
that of ESPN Inc.
QOTD: Any clod can have facts, but having an
opinion is an art!
--
Please see the official ORACLE-L FAQ:
http://www.orafaq.com
--
Author: Mohan, Ross
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051
FAX: (858) 538-5051
San Diego, California-- Public Internet
access / Mailing Lists
To REMOVE yourself from this mailing list, send an
E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of
'ListGuru') and in
the message BODY, include a line containing: UNSUB
ORACLE-L
(or the name of mailing list you want to be
removed from). You may
also send the HELP command for other information
(like subscribing).
--
Please see the official ORACLE-L FAQ:
http://www.orafaq.com
--
Author: Glenn
Re: RECOMPILE INVALID OBJECTS
Hi I just created last week. package: set pagesize 1000; set heading off; select 'alter '||object_type||' '||owner||'.'||object_name||' compile;' from dba_objects where status='INVALID' and object_type 'PACKAGE BODY'; package body: set pagesize 1000; set heading off; select 'alter package '||owner||'.'||object_name||' compile body;' from dba_objects where status='INVALID' and object_type ='PACKAGE BODY'; Mitchell - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, December 17, 2001 1:20 PM Group, Does anyone have some SQL handy that will re-compile INVALID objects for a given user, that they would be willing to share?? TIA Al Rusnak 804-734-8453 [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rusnak, George A. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: mitchell INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RECOMPILE INVALID OBJECTS
start -- set heading off set pagesize 0 set linesize 79 set verify off set echo off spool recomp_all.tmp select decode( OBJECT_TYPE, 'PACKAGE BODY', 'alter package ' || OWNER||'.'||OBJECT_NAME || ' compile body;', 'alter ' || OBJECT_TYPE || ' ' || OWNER||'.'||OBJECT_NAME || ' compile;' ) from dba_objects A, sys.order_object_by_dependency B where A.OBJECT_ID = B.OBJECT_ID(+) and STATUS = 'INVALID' and OBJECT_TYPE in ( 'PACKAGE BODY', 'PACKAGE', 'FUNCTION', 'PROCEDURE', 'TRIGGER', 'VIEW' ) order by DLEVEL DESC, OBJECT_TYPE, OBJECT_NAME; spool off @recomp_all.tmp end Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! *2 This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. *2
RE: RECOMPILE INVALID OBJECTS
just out of curiousity, does this buy one something extra over/above:
execute dbms_utility.compile_schema('user');
-Original Message-
Sent: Monday, December 17, 2001 1:58 PM
To: Multiple recipients of list ORACLE-L
start --
set heading off
set pagesize 0
set linesize 79
set verify off
set echo off
spool recomp_all.tmp
select decode( OBJECT_TYPE, 'PACKAGE BODY',
'alter package ' || OWNER||'.'||OBJECT_NAME || ' compile body;',
'alter ' || OBJECT_TYPE || ' ' || OWNER||'.'||OBJECT_NAME || ' compile;'
)
from dba_objects A, sys.order_object_by_dependency B
where A.OBJECT_ID = B.OBJECT_ID(+) and
STATUS = 'INVALID' and
OBJECT_TYPE in ( 'PACKAGE BODY', 'PACKAGE', 'FUNCTION', 'PROCEDURE',
'TRIGGER', 'VIEW' )
order by DLEVEL DESC, OBJECT_TYPE, OBJECT_NAME;
spool off
@recomp_all.tmp
end
Raj
__
Rajendra Jamadagni MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.
QOTD: Any clod can have facts, but having an opinion is an art!
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mohan, Ross
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re: RECOMPILE INVALID OBJECTS
I use the following and take a look at the script revaldte.sql before
I run it but you can certainly start it from the same SQL script
this is from SQL PL/SQL Annotated Archives -- Loney and Carmichael
ttitle off
set pagesize 0 feedback off verify off heading off term off echo off
spool revaldte.sql
select 'alter '||
DECODE(Object_Type,'PACKAGE BODY','PACKAGE',Object_Type)||
' '||Owner ||'.'|| Object_Name ||' compile '||
DECODE(Object_Type,'PACKAGE BODY','BODY',null)||';'
from DBA_OBJECTS
where Object_Type in
('PROCEDURE','PACKAGE','FUNCTION','TRIGGER',
'VIEW','PACKAGE BODY')
and Owner = owner
and Status != 'VALID'
order by Object_Type, Object_Name
/
spool off
--- Rusnak, George A. [EMAIL PROTECTED] wrote:
Group,
Does anyone have some SQL handy that will re-compile INVALID objects
for a
given user, that they would be willing to share??
TIA
Al Rusnak
804-734-8453
[EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Rusnak, George A.
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing
Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
__
Do You Yahoo!?
Check out Yahoo! Shopping and Yahoo! Auctions for all of
your unique holiday gifts! Buy at http://shopping.yahoo.com
or bid at http://auctions.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Rachel Carmichael
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: RECOMPILE INVALID OBJECTS
Title: RE: RECOMPILE INVALID OBJECTS from dba_objects A, sys.order_object_by_dependency B Doesn't work under 8.1.6, there is not such view as 'sys.order_object_by_dependency', however, you can replace this with an inline view of from dba_objects a, (select max(level) dlevel, object_id from public_dependency connect by object_id = prior referenced_object_id group by object_id) b Matt Adams - GE Appliances - [EMAIL PROTECTED] The thrill is gone - B. B. King If the thrill is gone, then it's time to take it back. - Meatloaf -Original Message- From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]] Sent: Monday, December 17, 2001 1:58 PM To: Multiple recipients of list ORACLE-L Subject: RE: RECOMPILE INVALID OBJECTS start -- set heading off set pagesize 0 set linesize 79 set verify off set echo off spool recomp_all.tmp select decode( OBJECT_TYPE, 'PACKAGE BODY', 'alter package ' || OWNER||'.'||OBJECT_NAME || ' compile body;', 'alter ' || OBJECT_TYPE || ' ' || OWNER||'.'||OBJECT_NAME || ' compile;' ) from dba_objects A, sys.order_object_by_dependency B where A.OBJECT_ID = B.OBJECT_ID(+) and STATUS = 'INVALID' and OBJECT_TYPE in ( 'PACKAGE BODY', 'PACKAGE', 'FUNCTION', 'PROCEDURE', 'TRIGGER', 'VIEW' ) order by DLEVEL DESC, OBJECT_TYPE, OBJECT_NAME; spool off @recomp_all.tmp end Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art!
RE: RECOMPILE INVALID OBJECTS
There was a bug related to dbms_utility.compile_schema where order by dependency was missing. It may have been fixed, but we still stick with our homegrown script. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! *1 This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. *1
RE: RECOMPILE INVALID OBJECTS
If you've got a big schema (READ: Apps), this will cripple you. Been there, done
that, and will never do it again in an Apps database.
I have found it always better to generate my own recompile sql (see script at bottom
of my notes).
Footnote: Read the docs for 8.1.7 as Oracle has added another parameter to this
command.
DBMS_UTILITY.COMPILE_SCHEMA (schema, FALSE);
The newer version of DBMS_UTILITY.COMPILE_SCHEMA (schema) has an extra boolean
argument which defaults to TRUE (compile everything) for backwards compatibility,
However, due to the problem of object dependancies being circular in the
sys.dependancy$ table (causing the original problem with
sys.ORDER_OBJECT_BY_DEPENDENCY view), Many objects are actually INVALIDATED by the
default usage.
Solution:
Running DBMS_UTILITY.COMPILE_SCHEMA (schema, FALSE) will only compile the INVALID
objects and seems to work much better.
RECOMPILE SCRIPT:
set heading off
set pagesize 0
set lines 79
set verify off
set echo off
set feedback off
spool comp_all.tmp
select
decode( OBJECT_TYPE, 'PACKAGE BODY',
'alter package ' || OWNER||'.'||OBJECT_NAME || ' compile body;',
'alter ' || OBJECT_TYPE || ' ' || OWNER||'.'||OBJECT_NAME || ' compile;'
)
from
dba_objects a,
sys.order_object_by_dependency b
where
A.OBJECT_ID = B.OBJECT_ID(+) and
STATUS = 'INVALID' and
OBJECT_TYPE in ( 'PACKAGE BODY', 'PACKAGE', 'FUNCTION', 'PROCEDURE',
'TRIGGER', 'VIEW' )
order by
DLEVEL DESC,
OBJECT_TYPE,
OBJECT_NAME;
spool off
@comp_all.tmp
-Original Message-
From: Mohan, Ross [mailto:[EMAIL PROTECTED]]
Sent: Monday, December 17, 2001 2:25 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: RECOMPILE INVALID OBJECTS
just out of curiousity, does this buy one something extra over/above:
execute dbms_utility.compile_schema('user');
-Original Message-
Sent: Monday, December 17, 2001 1:58 PM
To: Multiple recipients of list ORACLE-L
start --
set heading off
set pagesize 0
set linesize 79
set verify off
set echo off
spool recomp_all.tmp
select decode( OBJECT_TYPE, 'PACKAGE BODY',
'alter package ' || OWNER||'.'||OBJECT_NAME || ' compile body;',
'alter ' || OBJECT_TYPE || ' ' || OWNER||'.'||OBJECT_NAME
|| ' compile;'
)
from dba_objects A, sys.order_object_by_dependency B
where A.OBJECT_ID = B.OBJECT_ID(+) and
STATUS = 'INVALID' and
OBJECT_TYPE in ( 'PACKAGE BODY', 'PACKAGE', 'FUNCTION', 'PROCEDURE',
'TRIGGER', 'VIEW' )
order by DLEVEL DESC, OBJECT_TYPE, OBJECT_NAME;
spool off
@recomp_all.tmp
end
Raj
__
Rajendra JamadagniMIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect
that of ESPN Inc.
QOTD: Any clod can have facts, but having an opinion is an art!
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mohan, Ross
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Glenn Travis
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: RECOMPILE INVALID OBJECTS
thanks...that makes great sense. -Original Message- Sent: Monday, December 17, 2001 2:50 PM To: Multiple recipients of list ORACLE-L There was a bug related to dbms_utility.compile_schema where order by dependency was missing. It may have been fixed, but we still stick with our homegrown script. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohan, Ross INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RECOMPILE INVALID OBJECTS
It is certainly less code, but doesn't always catch all
the dependencies. I've had to run it multiple times
in order to recompile all objects.
Jared
Mohan, Ross
MohanR@STARS- To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
SMI.com cc:
Sent by: Subject: RE: RECOMPILE INVALID OBJECTS
[EMAIL PROTECTED]
om
12/17/01 11:25
AM
Please respond
to ORACLE-L
just out of curiousity, does this buy one something extra over/above:
execute dbms_utility.compile_schema('user');
-Original Message-
Sent: Monday, December 17, 2001 1:58 PM
To: Multiple recipients of list ORACLE-L
start --
set heading off
set pagesize 0
set linesize 79
set verify off
set echo off
spool recomp_all.tmp
select decode( OBJECT_TYPE, 'PACKAGE BODY',
'alter package ' || OWNER||'.'||OBJECT_NAME || ' compile body;',
'alter ' || OBJECT_TYPE || ' ' || OWNER||'.'||OBJECT_NAME || '
compile;'
)
from dba_objects A, sys.order_object_by_dependency B
where A.OBJECT_ID = B.OBJECT_ID(+) and
STATUS = 'INVALID' and
OBJECT_TYPE in ( 'PACKAGE BODY', 'PACKAGE', 'FUNCTION', 'PROCEDURE',
'TRIGGER', 'VIEW' )
order by DLEVEL DESC, OBJECT_TYPE, OBJECT_NAME;
spool off
@recomp_all.tmp
end
Raj
__
Rajendra Jamadagni MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN
Inc.
QOTD: Any clod can have facts, but having an opinion is an art!
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mohan, Ross
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: RECOMPILE INVALID OBJECTS
yes...and apparently there's another bug...interesting.
homemade soup is still best, I guess.
-Original Message-
Sent: Monday, December 17, 2001 4:54 PM
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
It is certainly less code, but doesn't always catch all
the dependencies. I've had to run it multiple times
in order to recompile all objects.
Jared
Mohan, Ross
MohanR@STARS- To: Multiple recipients of list
ORACLE-L [EMAIL PROTECTED]
SMI.com cc:
Sent by: Subject: RE: RECOMPILE INVALID
OBJECTS
[EMAIL PROTECTED]
om
12/17/01 11:25
AM
Please respond
to ORACLE-L
just out of curiousity, does this buy one something extra over/above:
execute dbms_utility.compile_schema('user');
-Original Message-
Sent: Monday, December 17, 2001 1:58 PM
To: Multiple recipients of list ORACLE-L
start --
set heading off
set pagesize 0
set linesize 79
set verify off
set echo off
spool recomp_all.tmp
select decode( OBJECT_TYPE, 'PACKAGE BODY',
'alter package ' || OWNER||'.'||OBJECT_NAME || ' compile body;',
'alter ' || OBJECT_TYPE || ' ' || OWNER||'.'||OBJECT_NAME || '
compile;'
)
from dba_objects A, sys.order_object_by_dependency B
where A.OBJECT_ID = B.OBJECT_ID(+) and
STATUS = 'INVALID' and
OBJECT_TYPE in ( 'PACKAGE BODY', 'PACKAGE', 'FUNCTION', 'PROCEDURE',
'TRIGGER', 'VIEW' )
order by DLEVEL DESC, OBJECT_TYPE, OBJECT_NAME;
spool off
@recomp_all.tmp
end
Raj
__
Rajendra Jamadagni MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN
Inc.
QOTD: Any clod can have facts, but having an opinion is an art!
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mohan, Ross
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mohan, Ross
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: RECOMPILE INVALID OBJECTS
I think it also only recompiles invalid objects. My thought was
dbms_utility always compiles the while schema. I also thought there was a
good recompile script in rdbms/admin?
- Ethan
-Original Message-
Sent: Monday, December 17, 2001 3:55 PM
To: Multiple recipients of list ORACLE-L
It is certainly less code, but doesn't always catch all
the dependencies. I've had to run it multiple times
in order to recompile all objects.
Jared
Mohan, Ross
MohanR@STARS- To: Multiple recipients of list
ORACLE-L [EMAIL PROTECTED]
SMI.com cc:
Sent by: Subject: RE: RECOMPILE INVALID
OBJECTS
[EMAIL PROTECTED]
om
12/17/01 11:25
AM
Please respond
to ORACLE-L
just out of curiousity, does this buy one something extra over/above:
execute dbms_utility.compile_schema('user');
-Original Message-
Sent: Monday, December 17, 2001 1:58 PM
To: Multiple recipients of list ORACLE-L
start --
set heading off
set pagesize 0
set linesize 79
set verify off
set echo off
spool recomp_all.tmp
select decode( OBJECT_TYPE, 'PACKAGE BODY',
'alter package ' || OWNER||'.'||OBJECT_NAME || ' compile body;',
'alter ' || OBJECT_TYPE || ' ' || OWNER||'.'||OBJECT_NAME || '
compile;'
)
from dba_objects A, sys.order_object_by_dependency B
where A.OBJECT_ID = B.OBJECT_ID(+) and
STATUS = 'INVALID' and
OBJECT_TYPE in ( 'PACKAGE BODY', 'PACKAGE', 'FUNCTION', 'PROCEDURE',
'TRIGGER', 'VIEW' )
order by DLEVEL DESC, OBJECT_TYPE, OBJECT_NAME;
spool off
@recomp_all.tmp
end
Raj
__
Rajendra Jamadagni MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN
Inc.
QOTD: Any clod can have facts, but having an opinion is an art!
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mohan, Ross
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Post, Ethan
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re: RECOMPILE INVALID OBJECTS
also take a look at utlrcmp which will preform a parallel recompile of invalids. Ron Thomas Hypercom, Inc [EMAIL PROTECTED] sknd100@yahoo .com To: [EMAIL PROTECTED] Sent by: cc: root@fatcity.Subject: Re: RECOMPILE INVALID OBJECTS com 12/17/01 03:56 PM Please respond to ORACLE-L Can't look at the moment, but doesn't ?/rdbms/admin/utlrp.sql do this for you? --Scott - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, December 17, 2001 12:20 PM Group, Does anyone have some SQL handy that will re-compile INVALID objects for a given user, that they would be willing to share?? TIA Al Rusnak 804-734-8453 [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rusnak, George A. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Scott Shafer INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ron Thomas INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
