- 最后登录
- 2014-10-17
- 在线时间
- 6 小时
- 威望
- 0
- 金钱
- 60
- 注册时间
- 2013-9-25
- 阅读权限
- 10
- 帖子
- 12
- 精华
- 0
- 积分
- 0
- UID
- 1231
|
4#
发表于 2013-12-16 22:41:16
--查询无效对象无语
SELECT owner, object_type, object_name, status
FROM all_objects
WHERE status = 'INVALID'
ORDER BY owner;
--编译无效 SQL 脚本,然后运行语句
SELECT CASE WHEN object_type = 'SYNONYM' AND owner = 'PUBLIC' THEN
'alter ' || owner || ' ' || DECODE(object_type, 'PACKAGE BODY', 'PACKAGE', object_type) || ' ' || object_name || ' ' || DECODE(object_type, 'PACKAGE BODY', 'COMPILE BODY', 'COMPILE') || ';'
ELSE
'alter ' || DECODE(object_type, 'PACKAGE BODY', 'PACKAGE', object_type) || ' ' || owner || '.' || object_name || ' ' || DECODE(object_type, 'PACKAGE BODY', 'COMPILE BODY', 'COMPILE') || ';'
END "SQL_COMMANDS"
FROM dba_objects
WHERE object_type IN ('PACKAGE', 'PACKAGE BODY', 'VIEW', 'PROCEDURE', 'FUNCTION', 'TRIGGER', 'SYNONYM')
AND status = 'INVALID'
ORDER BY DECODE(object_type, 'TRIGGER', '99', '00'); |
|