主题:如何Oracle编译失效的包体
作者:基毛飞上天
情况说明:在吗?可以帮忙重新编译一个包吗?我们这边编译不了!
处理思维:
(1)包头失效还是包体失效?
(2)是否存在阻碍的进程影响重新编译?
(3)如果存在阻碍,与应用人员沟通,是否可以尝试杀Process以及会话
1. 查看包头和包体是否失效?
从以下SQL语句输出得知,包头没有失效,包体失效了
点击(此处)折叠或打开
- SQL> set lines 250
- SQL> set pages 1000000
- SQL> set pages 10000
- SQL> select * from dba_objects where owner in ('ZJDB') and object_name = 'PKG_TEST';
-
- OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
- ------------------------------ ------------------------------------------------------------------------------------ ---------- -------------- -------------------
- CREATED LAST_DDL_TIME TIMESTAMP STATUS T G S NAMESPACE EDITION_NAME
- ------------------- ------------------- ------------------- ------- - - - ---------- ------------------------------
- ZJDB PKG_TEST 451406 PACKAGE
- 2018-01-16 05:42:35 2018-04-14 10:21:22 2018-04-22:17:50:30 VALID N N N 1
-
- ZJDB PKG_TEST 451443 PACKAGE BODY
- 2018-01-16 05:44:41 2018-04-14 10:21:25 2018-04-25:10:14:04 INVALID N N N 2
2. 重新编译包体
检查有什么阻碍包体的编译
点击(此处)折叠或打开
- SQL> alter package ZJDB.PKG_TEST compile body;
3. 查看DDL锁的结构
点击(此处)折叠或打开
- SQL> desc dba_ddl_locks
- Name Null? Type
- ------------------ ------- ----------------
- SESSION_ID NUMBER
- OWNER VARCHAR2(30)
- NAME VARCHAR2(30)
- TYPE VARCHAR2(40)
- MODE_HELD VARCHAR2(9)
- MODE_REQUESTED VARCHAR2(9)
4. 查看DDL锁的独占 -- Exclusive
点击(此处)折叠或打开
- SQL> select * from dba_ddl_locks where name='PKG_TEST';
- SESSION_ID OWNER NAME TYPE MODE_HELD MODE_REQU
- ---------- --------------- -------------------- -------------------- --------- ---------
- 1719 ZJDB PKG_TEST