Recompiling Invalid Objects

STEP 1: Use the below query to find INVALID Objects



SELECT OWNER, OBJECT_NAME, OBJECT_TYPE, STATUS FROM DBA_OBJECTS WHERE STATUS='INVALID';
Export the result of the above query to xls.

STEP 2: Identify the Object Type of each invalid object.

STEP 3: If the object type is synonym

Use the below query for generating a query to create or replace invalid synonyms

SELECT  'CREATE OR REPLACE PUBLIC SYNONYM ' ||OBJECT_NAME ||' FOR '
||OBJECT_NAME||';' FROM DBA_OBJECTS where status='INVALID' AND OBJECT_TYPE='SYNONYM';

Use the below query for generating a query to grant privileges to synonyms



SELECT 'GRANT ALL ON ' ||OBJECT_NAME ||' TO PUBLIC||;' FROM DBA_OBJECTS where status='INVALID' AND OBJECT_TYPE='SYNONYM';

STEP 4: Copy and paste the result set of both the above queries in separate file


Now execute the first result set

CREATE OR REPLACE PUBLIC SYNONYM SYNONYM_NAME1 FOR OBJECT_NAME1;

CREATE OR REPLACE PUBLIC SYNONYM SYNONYM_NAME2 FOR OBJECT_NAME2;


CREATE OR REPLACE PUBLIC SYNONYM SYNONYM_NAME3 FOR OBJECT_NAME3;

CREATE OR REPLACE PUBLIC SYNONYM SYNONYM_NAME4 FOR OBJECT_NAME4;

CREATE OR REPLACE PUBLIC SYNONYM SYNONYM_NAME5 FOR OBJECT_NAME5;
CREATE OR REPLACE PUBLIC SYNONYM SYNONYM_NAME6 FOR OBJECT_NAME6;

CREATE OR REPLACE PUBLIC succeeded.
CREATE OR REPLACE PUBLIC succeeded.
CREATE OR REPLACE PUBLIC succeeded.
CREATE OR REPLACE PUBLIC succeeded.
CREATE OR REPLACE PUBLIC succeeded.
CREATE OR REPLACE PUBLIC succeeded.

Now execute the second result set

GRANT ALL ON SYNONYM_NAME1 TO PUBLIC;
GRANT ALL ON SYNONYM_NAME2 TO PUBLIC;
GRANT ALL ON SYNONYM_NAME3 TO PUBLIC;
GRANT ALL ON SYNONYM_NAME4 TO PUBLIC;
GRANT ALL ON SYNONYM_NAME5 TO PUBLIC;
GRANT ALL ON SYNONYM_NAME6 TO PUBLIC;

GRANT ALL succeeded.
GRANT ALL succeeded.
GRANT ALL succeeded.
GRANT ALL succeeded.
GRANT ALL succeeded.
GRANT ALL succeeded.

Use the below query to check whether the objects has been recompiled and the status is VALID

SELECT OWNER, OBJECT_NAME, OBJECT_TYPE, STATUS FROM DBA_OBJECTS
WHERE STATUS='VALID' AND OBJECT_TYPE='SYNONYM' AND OBJECT_NAME IN ('OBJECT_NAME1','OBJECT_NAME2','OBJECT_NAME3','OBJECT_NAME4',
'OBJECT_NAME5';

STEP 5: If the object type is not synonym


Recompile other objects as the show below