Recompiling invalid objects with a batch file
Ubiquitous
objects", or at least compile a specific object? I know the capacity
exists in TOAD but I have no idea what's going on behind the scenes...
Thanks!
DA Morgan
No reason to make it difficult on yourself:
SQL> @?/rdbms/admin/utlrp
http://www.psoug.org/reference/files.html
or if you prefer:
UTL_RECOMP
http://www.psoug.org/reference/utl_recomp.html
which allows you to compile in parallel or serial fashion.
or if you prefer:
DBMS_UTILITY.COMPILE_SCHEMA
http://www.psoug.org/reference/dbms_utility.html
No need to reinvent the wheel.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damo...@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Ubiquitous
wrote:
>Ubiquitous wrote:
>> Is there a way to write a sqlplus batch file to "recompile all invalid
>> objects", or at least compile a specific object? I know the capacity
>> exists in TOAD but I have no idea what's going on behind the scenes...
>
>No reason to make it difficult on yourself:
>
>SQL> @?/rdbms/admin/utlrp
>http://www.psoug.org/reference/files.html
>
>or if you prefer:
>
>UTL_RECOMP
>http://www.psoug.org/reference/utl_recomp.html
>which allows you to compile in parallel or serial fashion.
>
>or if you prefer:
>
>DBMS_UTILITY.COMPILE_SCHEMA
>http://www.psoug.org/reference/dbms_utility.html
>
>No need to reinvent the wheel.
Thank you, but this appears to be for ORACLE 11.
Is there a similar version for ORACLE 8.1.7?
Thanks again!
gazzag
> Thank you, but this appears to be for ORACLE 11.
> Is there a similar version for ORACLE 8.1.7?
>
> Thanks again!
The first suggestion Daniel supplied you with will work in 8.1.7.4
SQL> @?/rdbms/admin/utlrp
HTH
-g
DA Morgan
The listings in the Library, where it indicates 11 show that it has
been tested and works in 11g. That is not an indication that the
functionality did not exist back in earlier releases.
For example there is an entry for TABLES. I've been told tables
existed in 8i and 8.0, and 7.3, etc.
Ubiquitous
>>>> Is there a way to write a sqlplus batch file to "recompile all invalid
>>>> objects", or at least compile a specific object? I know the capacity
>>>> exists in TOAD but I have no idea what's going on behind the scenes...
>>>
>>> No reason to make it difficult on yourself:
>>>
>>> SQL> @?/rdbms/admin/utlrp
>>> http://www.psoug.org/reference/files.html
>>>
>>> or if you prefer:
>>>
>>> UTL_RECOMP
>>> http://www.psoug.org/reference/utl_recomp.html
>>> which allows you to compile in parallel or serial fashion.
>>>
>>> or if you prefer:
>>>
>>> DBMS_UTILITY.COMPILE_SCHEMA
>>> http://www.psoug.org/reference/dbms_utility.html
>>>
>>> No need to reinvent the wheel.
>>
>> Thank you, but this appears to be for ORACLE 11.
>> Is there a similar version for ORACLE 8.1.7?
>
>The listings in the Library, where it indicates 11 show that it has
>been tested and works in 11g. That is not an indication that the
>functionality did not exist back in earlier releases.
>
>For example there is an entry for TABLES. I've been told tables
>existed in 8i and 8.0, and 7.3, etc.
I asked because I was unable to find them, but it might be because I don't
have rights on those objects here.
gazzag
>
> I asked because I was unable to find them, but it might be because I don't
> have rights on those objects here.
The syntax you're after is:
ALTER <object_type> <object_name> RECOMPILE;
So, in answer to your OP, you could refine the following script:
SELECT 'ALTER '||object_type||' '||object_name||' COMPILE;'
FROM user_objects
WHERE status != 'VALID';
Spool the output to a file. This will become your recompile script.
HTH
-g
gazzag
Sorry, in my previous post "ALTER <object_type> <object_name>
RECOMPILE;" should be "ALTER <object_type> <object_name> COMPILE;"
COMPILE *not* RECOMPILE.
Apologies.
Shakespeare
"gazzag" <gar...@jamms.org> schreef in bericht
news:f32a36ab-08f8-4dd4...@d45g2000hsc.googlegroups.com...
But you would have to run this script serveral times when objects have
dependencies......
Shakespeare
gazzag
> But you would have to run this script serveral times when objects have
> dependencies......
>
> Shakespeare
Indeed. I am merely showing the horse where the water is. I have no
intention of leading it, or forcing it to drink :)
Gerard H. Pille
>
> Sorry, in my previous post "ALTER <object_type> <object_name>
> RECOMPILE;" should be "ALTER <object_type> <object_name> COMPILE;"
>
> COMPILE *not* RECOMPILE.
>
> Apologies.
>
Plus you should cater for package bodies, where the syntax is
alter package amehoela compile body;
santosh...@gmail.com
BEGIN
FOR cur_rec IN (SELECT object_name,
object_type,
DECODE(object_type, 'PACKAGE', 2,
'PACKAGE BODY', 3, 1)
AS recompile_order
FROM user_objects
WHERE object_type IN ('PACKAGE', 'PACKAGE BODY',
'FUNCTION', 'PROCEDURE')
AND status != 'VALID'
ORDER BY 3)
LOOP
BEGIN
IF cur_rec.object_type = 'PACKAGE' THEN
EXECUTE IMMEDIATE 'ALTER ' || cur_rec.object_type || ' ' ||
cur_rec.object_name || ' COMPILE';
ELSIF cur_rec.object_type = 'PACKAGE BODY' THEN
EXECUTE IMMEDIATE 'ALTER PACKAGE '
|| cur_rec.object_name || ' COMPILE BODY';
ELSIF cur_rec.object_type = 'FUNCTION'
OR cur_rec.object_type = 'PROCEDURE' THEN
EXECUTE IMMEDIATE 'ALTER ' || cur_rec.object_type || ' '
|| cur_rec.object_name || ' COMPILE';
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line(cur_rec.object_type || ' : '
|| cur_rec.object_name);
END;
END LOOP;
END ;
/
On Jun 2, 11:09 am, Ubiquitous <web...@polaris.net> wrote:
> In article <1196834485.490...@bubbleator.drizzle.com>, damor...@psoug.org
> Thanks again!- Hide quoted text -
>
> - Show quoted text -
DA Morgan
Why are you reinventing the wheel?
Use the built in UTL_RECOMP package.
http://www.psoug.org/reference/utl_recomp.html
as previously suggested.
The posted code, for example, won't recompile a type, type body, or view.