Oracle编译无效对象

问题描述:使用存储过程的方式对oracle数据库的无效对象，如视图或者同义词进行定期的编译，让他变成一个有效的对象，加上定时任务可以实现自动化的处理。同时在数据库内部创建一个记录表，用来记录被编译过的对象

/*重新编译无效视图，无效同义词存储过程
--1.创建无效对象监控表invalid_object_record，创建无效对象保存表preserve_object_record
create table invalid_object_record
(
  id            number generated by default as identity,
  OWNER         VARCHAR2(128),
  OBJECT_NAME   VARCHAR2(128),
  OBJECT_TYPE   VARCHAR2(23),
  STATUS        VARCHAR2(7)
);
COMMENT ON TABLE invalid_object_record IS 'This is the invalid object monitoring table';

create table preserve_object_record
(
  OWNER         VARCHAR2(128),
  OBJECT_NAME   VARCHAR2(128)
);
COMMENT ON TABLE invalid_object_record IS 'This is an invalid object save table';

--2.创建使用用户，分配权限
create user steven identified by 123456;
grant resource,connect,create session to steven;
--grant select on dba_users to steven;
grant select on dba_objects to steven;
alter user steven quota unlimited on USERS;

--3.写入记录保存表
insert into preserve_object_record values('SCOTT','V_TEST');
commit;

--4.在steven用户创建存储过程
*/

CREATE OR REPLACE PROCEDURE PROC_INVALID_OBJECT
as 
    t_OWNER VARCHAR2(128); 
    t_object_name VARCHAR2(128);
    t_status VARCHAR2(7);
    t_object_type  VARCHAR2(23);
    t_sql varchar2(200);

    select 
    CURSOR c1 IS select OWNER,OBJECT_NAME,OBJECT_TYPE,STATUS from DBA_OBJECTS where 
    (OWNER,OBJECT_NAME) not in (select OWNER,OBJECT_NAME  FROM preserve_object_record) 
    AND OWNER not IN (
    'SYS','SYSTEM','XS$NULL','OJVMSYS','LBACSYS','OUTLN','SYS$UMF','DBSNMP','APPQOSSYS','DBSFWUSER','GGSYS','ANONYMOUS','CTXSYS','DVF','DVSYS','GSMADMIN_INTERNAL','MDSYS','OLAPSYS','XDB',
'WMSYS','GSMCATUSER','MDDATA','REMOTE_SCHEDULER_AGENT','SYSBACKUP','GSMUSER','GSMROOTUSER','SYSRAC','SI_INFORMTN_SCHEMA','AUDSYS','DIP','ORDPLUGINS','ORDDATA','SYSKM',
'ORACLE_OCM','ORDSYS','SYSDG','PUBLIC') 
    AND STATUS = 'INVALID'
    AND OBJECT_TYPE in ('VIEW','SYNONYM');

    
begin
    --执行之前初始化
    EXECUTE IMMEDIATE 'TRUNCATE TABLE invalid_object_record';

    --循环处理无效对象
    FOR x IN c1 LOOP
    begin
      t_OWNER := x.OWNER;
      t_object_name := x.OBJECT_NAME;
      t_sql :='select count(*) from "'||t_OWNER||'"'||'.'||'"'||t_object_name||'" where a = b'; 
      Execute immediate t_sql;  
      EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLCODE || ' - ' || SQLERRM);
    END;
    END LOOP;
    
    --记录未被成功处理到表中的信息 
    insert into invalid_object_record(OWNER,OBJECT_NAME,OBJECT_TYPE,STATUS) select OWNER,OBJECT_NAME,OBJECT_TYPE,STATUS from DBA_OBJECTS where 
    (OWNER,OBJECT_NAME) not in (select OWNER,OBJECT_NAME  FROM preserve_object_record) 
    AND OWNER not IN (select username from dba_users where ORACLE_MAINTAINED = 'Y')
    AND OWNER != 'PUBLIC' 
    AND STATUS = 'INVALID'
    AND OBJECT_TYPE in ('VIEW','SYNONYM');
    commit;
END;
/