set pages 500;
set lines 200;
set trims on;
set heading off;
spool /tmp/1.sql;
select 'alter 'object_type' 'object_name';
compile' from user_objects where status='INVALID';
spool off;
@/tmp/1.sql;
下面我们来介绍一下例外的情况:
当原始的表被drop掉后,依据它创建的同义词就变得不能访问了。
原始的表重建以后,同义词仍然不可以访问。
但在user_objects里面的status状态是'VALID',却不是'INVALID'。
这需要我们把它们找出来,运行查询同义词表结构的语句:
spool /tmp/1.sql;
select 'desc 'synonym_name';' from user_synonyms;
spool off;
@/tmp/1.sql;
出错的同义词, 一定要先彻底删除掉,再重建创建同义词。
drop synonym &synonym_name;
create synonym &synonym_name for &owner.&table_name;
drop view EXU9SYNU;
create view EXU9SYNU as
SELECT "SYNNAM","SYNNAM2","SYNTAB",
"TABOWN","TABNODE","PUBLIC___FCKpd___6quot;,"SYNOWN",
"SYNOWNID","TABOWNID","SYNOBJNO","SYNTIME"
FROM sys.exu9syn
-- WHERE synownid = UID; (原来的视图创建方法)
WHERE 0=1;
grant select on sys.EXU9SYNU to public;