重庆思庄Oracle、KingBase、PostgreSQL、Redhat认证学习论坛

标题: 产生编译同义词无效对象的语句 [打印本页]

作者: ZhangQi 时间: 2025-9-14 14:42
标题: 产生编译同义词无效对象的语句
同义词无效对象有两种，一种是公共同义词，一种是普通同义词（私有同义词），下面分别是两种同义词无效对象的重编译方式

1. 公共同义词

BEGIN
FOR obj IN (
   SELECT owner, object_name, object_type
   FROM all_objects
   WHERE status = 'INVALID'
   AND object_type = 'SYNONYM' AND owner='PUBLIC'
) LOOP
   BEGIN
      EXECUTE IMMEDIATE 'ALTER ' ||  obj.owner || ' '  ||  obj.object_type || ' ' ||  obj.object_name || ' COMPILE';
   EXCEPTION
      WHEN OTHERS THEN
         DBMS_OUTPUT.PUT_LINE('编译失败: ALTER ' || obj.object_type ||' ' || obj.owner || '.' || obj.object_name || ' COMPILE');
   END;
END LOOP;
END;
/

2. 普通同义词（私有同义词）

BEGIN
FOR obj IN (
   SELECT owner, object_name, object_type
   FROM all_objects
   WHERE status = 'INVALID'
   AND object_type = 'SYNONYM' AND owner<>'PUBLIC'
) LOOP
   BEGIN
      EXECUTE IMMEDIATE 'ALTER ' || obj.object_type ||' ' || obj.owner || '.' || obj.object_name || ' COMPILE';
   EXCEPTION
      WHEN OTHERS THEN
         DBMS_OUTPUT.PUT_LINE('编译失败: ALTER ' || obj.object_type ||' ' || obj.owner || '.' || obj.object_name || ' COMPILE');
   END;
END LOOP;
END;
/

欢迎光临重庆思庄Oracle、KingBase、PostgreSQL、Redhat认证学习论坛 (http://bbs.cqsztech.com/)