2006-04-28

Invalid public synonyms on Oracle 10g

Don't know why but sometimes, some public synonyms on the dba_xxxx views become invalid...
I write a little script to regenerate them:

/******************************************
2006-04-18 - V1.0 - Creation of the script
(c) INFOR-Conseils
oracle _at_ infor-conseils.be
Regenerate invalid public synonyms
******************************************/
prompt system@&&database
conn system@&&database

set pages 0
set feed off
set linesize 1000
set trimsp on
spool _your_disk_\recrpubsyn.sql
select 'CREATE OR REPLACE PUBLIC SYNONYM 'object_name' for 'object_name';' from dba_objects where status = 'INVALID'and object_type = 'SYNONYM' and owner = 'PUBLIC';
spool off
@@_your_disk_\recrpubsyn.sql
host _the_remove_command_ _your_disk_\recrpubsyn.sql

exit;



_your_disk_ must be replaced by a path in your OS
e.g d:\ on Windows or /home/oracle on Linux/Unix.
The _the_remove_command_ must be replaced by del on Windows and by rm on Linux/Unix

Aucun commentaire: