Running the UTLRP.SQL Script

[Scott Buckingham]

Quick question, is it OK to run this script while the database is up and running and users are accessing it? This is an HP-UX 11.00 OS running an Oracle 8.1.7 database.

[James A. Donovan · Accepted solution]

I would say that 999 times out of 1000, it's OK to run the utlrp.sql script. All it is doing is recompiling invalid objects.

[Scott Buckingham]

Sounds good to me! It's a test instance anyway but I didn't want to create any unnecessary problems.

Thanks for the quick response!

[Yogeeraj_1]

hi,

At high load, i won't do it!

Anyway, all this script does is recompile all objects (invalid objects).

You may choose not to do it as they will fix themselves as they are executed or accessed. I never worry about some invalid objects -- you'll almost always have some somewhere.

If you really want to do it, here is a better way (in pseudo code):

create table already_tried( object_type, object_name );


create function get_invalid_object return varchar2
as
begin
select ... into ... from user_objects
where not exists ( select null from already_tried a
where a.object_type = user_objects.object_type ...)
and status = 'INVALID'
and rownum = 1;

return ...
end;


begin
delete from already_tried;
loop
compile the output of get_invalid_object;
insert into already_tried;
end loop;
exception
when no_data_found then done
end;


it works by getting AN invalid object that we haven't yet tried to compile. It compiles it (which will recursively compile all needed objects -- the first invalid object may well fix ALL invalid objects).

We remember we did that one

Go onto the next.

done.



hope this helps!

regards
Yogeeraj

[James A. Donovan]

...i grabbed this from the utlrp.sql script itself, if you haven't already looked at it...

Rem
Rem Copyright (c) Oracle Corporation 1998, 1999. All Rights Reserved.
Rem
Rem NAME
Rem utlrp.sql - UTiLity script Recompile invalid Pl/sql modules
Rem
Rem DESCRIPTION
Rem
Rem This is a fairly general script that can be used at any time to
Rem recompile all existing invalid PL/SQL modules in a database.
Rem
Rem If run as one of the last steps during migration/upgrade/downgrade
Rem (see the README notes for your current release and the Oracle
Rem Migration book), this script will validate all PL/SQL modules
Rem (procedures, functions, packages, triggers, types, views) during
Rem the migration step itself.
Rem
Rem Although invalid PL/SQL modules get automatically recompiled on use,
Rem it is useful to run this script ahead of time (e.g. as one of the last
Rem steps in your migration), since this will either eliminate or
Rem minimize subsequent latencies caused due to on-demand automatic
Rem recompilation at runtime.
Rem
Rem Oracle highly recommends running this script towards the end of
Rem of any migration/upgrade/downgrade.
Rem
Rem NOTES
Rem
Rem * Must be connected as internal to run this.
Rem
Rem * The scripts expects the following packages to have been created with
Rem VALID status.
Rem STANDARD (standard.sql)
Rem DBMS_STANDARD (dbmsstdx.sql)
Rem
Rem * There should be no other DDL on the database while running
Rem the script. Not following this recommendation may lead to
Rem deadlocks.

[Scott Buckingham]

Yes, I did look at this and this is why I asked the question. I wasn't sure what was meant when it said;

There should be no other DDL on the database while running the script. Not following this recommendation may lead to deadlocks.

[James A. Donovan]

DDL => data definition language => creation/manipulation of database objects; typically DBA level stuff

DML => data manipulation language => select/update/delete/insert of data, typically user level stuff