asked on

Syntax to compile entire schema?

Hi,
Does anyone know how to compile entire schema?

Thanks,
masaimara

Hi

select 'alter '||decode(OBJECT_TYPE,'PACKAGE BODY','PACKAGE',OBJECT_TYPE)||' '||OBJECT_NAME
||' COMPILE '|| DECODE(OBJECT_TYPE,'PACKAGE BODY','BODY','')||';'||CHR(10)
FROM USER_OBJECTS
WHERE STATUS <>'VALID';

USE THE ABOVE QUERY IT WILL GENERATE SCRIPT TO COMPILE ALL THE INVALID OBJECTS. RUN THE GENERATED SCRIPT.

Hope it helps you,
Regards,
Murali.

tudor1

Hi

If you have 8i you can run
@ORACLE_HOME/rdbms/admin/utlrp.sql

This compiles all invalid objects in the database, and it is fast!

Regards
tudor1

ASKER CERTIFIED SOLUTION

sagirk

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

masaimara

ASKER

tudor1,
Whom should you be logged on as to run utlrp.sql or what kind of roles should a user be assigned to run that sql?

masaimara

masaimara

ASKER

sagirk,
I ran the dbms package but it failed to compile a view which was invalid. Is there something else I should be doing?

masaimara

tudor1

Hi masaimara

You can run the script as INTERNAL/SYSTEM or another dba.

And if a view won't compile:
Are all tables ok? Has there been a change?
If for example the view uses a table that doesn't exist anymore, it will not be compiled.

regards
tudor1

masaimara

ASKER

tudor1,
I am the schema owner but I'm not a dba. Is there any other sql which can be run by a person who is not a dba?

masaimara