We help IT Professionals succeed at work.

Syntax to compile entire schema?

masaimara
masaimara asked
on
Hi,
Does anyone know how to compile entire schema?

Thanks,
masaimara
Comment
Watch Question

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.

Commented:
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
Commented:
Hi !

You can use

 EXEC DBMS_UTILITY.COMPILE_SCHEMA('SCOTT',TRUE) ;

Assuming Either u have logined as SYSTEM or SCOTT


R. K.

Author

Commented:
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

Author

Commented:
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

Commented:
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

Author

Commented:
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

Explore More ContentExplore courses, solutions, and other research materials related to this topic.