关于繁忙的系统不要轻易对数据库对象进行ddl操作，避免产生大量的library cache lock

数据库对象在进行重新编译时候，需要library cache lock

SELECT * FROM T1;

CREATE OR REPLACE procedure RO
AS
ID1 VARCHAR2(100);
ID2 VARCHAR2(100);
ID3 NUMBER:=10;
BEGIN
  FOR ID4 IN 1..ID3 LOOP
    DELETE FROM T1 WHERE ROWNUM<ID4;
    COMMIT;
    END LOOP;
    END;


SQL> select OBJECT_NAME,OBJECT_TYPE,STATUS from user_objects where object_name='RO';
OBJECT_NAM OBJECT_TYPE         STATUS
---------- ------------------- -------
RO         PROCEDURE           VALID


###############对存储过程依赖对象进行ddl重新定义#######################
1 重命名
 ALTER TABLE T1 RENAME TO T4;


SQL> /
OBJECT_NAM OBJECT_TYPE         STATUS
---------- ------------------- -------
RO         PROCEDURE           INVALID


SQL>  exec ro();
begin ro(); end;
ORA-06550: 第 1 行, 第 7 列: 
PLS-00905: 对象 SYSTEM.RO 无效
ORA-06550: 第 1 行, 第 7 列: 
PL/SQL: Statement ignored


ALTER TABLE T4 RENAME TO T1;


SQL> select OBJECT_NAME,OBJECT_TYPE,STATUS from user_objects where object_name='RO';
OBJECT_NAM OBJECT_TYPE         STATUS
---------- ------------------- -------
RO         PROCEDURE           INVALID


SQL> exec ro();
PL/SQL procedure successfully completed


SQL> /
OBJECT_NAM OBJECT_TYPE         STATUS
---------- ------------------- -------
RO         PROCEDURE           VALID






2 添加列
alter table t1 add id1 varchar2(100);
SQL> select OBJECT_NAME,OBJECT_TYPE,STATUS from user_objects where object_name='RO';
OBJECT_NAM OBJECT_TYPE         STATUS
---------- ------------------- -------
RO         PROCEDURE           INVALID


SQL> exec ro();
PL/SQL procedure successfully completed
SQL> select OBJECT_NAME,OBJECT_TYPE,STATUS from user_objects where object_name='RO';
OBJECT_NAM OBJECT_TYPE         STATUS
---------- ------------------- -------
RO         PROCEDURE           VALID




3 删除列
 
alter table t1 drop column  id1 ;
SQL> select OBJECT_NAME,OBJECT_TYPE,STATUS from user_objects where object_name='RO';
OBJECT_NAM OBJECT_TYPE         STATUS
---------- ------------------- -------
RO         PROCEDURE           INVALID


SQL> exec ro();
PL/SQL procedure successfully completed
SQL> select OBJECT_NAME,OBJECT_TYPE,STATUS from user_objects where object_name='RO';
OBJECT_NAM OBJECT_TYPE         STATUS
---------- ------------------- -------
RO         PROCEDURE           VALID


总结：对于无效对象，对象重新被调用时将对对象进行重新编译。
SQL> COL OWNER FOR A6
SQL> COL NAME FOOR A10REFERENCED_OWNER
SQL> COL TYPE FOR A10
SQL> COL REFERENCED_OWNER FRO A10
SQL> COL REFERENCED_NAME FOR A10
SQL> SELECT *FROM DBA_DEPENDENCIES T1 WHERE T1.referenced_NAME='T1'


OWNER  NAME                           TYPE       REFERENCED_OWNER               REFERENCED REFERENCED_TYPE    
------ ------------------------------ ---------- ------------------------------ ---------- ------------------ 
PUBLIC A                              SYNONYM    SYS                            T1         TABLE                                                                                  
SYS    A                              SYNONYM    SYS                            T1         TABLE                                                                                            

SYSTEM RO                             PROCEDURE  SYSTEM                         T1         TABLE 

关于其他相关的分享，请大家加qq群208206654 技术分享，共同进步，共同学习。