BEGIN pkg_test.ap_get_baTran(200,20180515); END;
-
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(to_char('7b4dpss4k3hc5'),NULL));
-
-
PLAN_TABLE_OUTPUT
-
--------------------------------------------------
-
SQL_ID 7b4dpss4k3hc5, child number 0
-
BEGIN pkg_test.ap_get_baTran(200,20180515); END;
-
NOTE: cannot fetch plan for SQL_ID: 7b4dpss4k3hc5, CHILD_NUMBER: 0
-
Please verify value of SQL_ID and CHILD_NUMBER;
-
It could also be that the plan is no longer in cursor cache (check v$sql_plan)
7. 殺會話
經與應用人員溝通,可以盡情地殺會話,以及PROCESS(這個需要本地殺)
-
alter system kill session '12481,8711';
8. 再次查看是否有獨占編譯包體的鎖
檢查出1719 ZJDB PKG_TEST Body Exclusive None,還是存在獨占DDL鎖的進程。按照以上的方式,殺1719的會話,是不對的!因為這個會話就是SYS用戶正在執行重新編譯的語句。不可能殺掉重新編譯的語句嘛,所以看下還能從什么方面下手!
-
SQL> select * from dba_ddl_locks where name='PKG_REPORT';
-
-
SESSION_ID OWNER NAME TYPE MODE_HELD MODE_REQU
-
---------- --------------- ---------------- ------------------------- --------- ---------
-
9710 ZJDB PKG_TEST Table/Procedure/Type Null None
-
948 ZJDB PKG_TEST Table/Procedure/Type Null None
-
10155 ZJDB PKG_TEST Table/Procedure/Type Null None
-
6814 ZJDB PKG_TEST Table/Procedure/Type Null None
-
2851 ZJDB PKG_TEST Table/Procedure/Type Null None
-
21183 ZJDB PKG_TEST Table/Procedure/Type Null None
-
14698 ZJDB PKG_TEST Table/Procedure/Type Null None
-
16963 ZJDB PKG_TEST Table/Procedure/Type Null None
-
23503 ZJDB PKG_TEST Table/Procedure/Type Null None
-
2650 ZJDB PKG_TEST Table/Procedure/Type Null None
-
20671 ZJDB PKG_TEST Table/Procedure/Type Null None
-
1719 ZJDB PKG_TEST Body Exclusive None
-
9710 ZJDB PKG_TEST Body Null None
-
20671 ZJDB PKG_TEST Body Null None
-
6814 ZJDB PKG_TEST Body Null None
-
16963 ZJDB PKG_TEST Body Null None
-
21183 ZJDB PKG_TEST Body Null None
-
948 ZJDB PKG_TEST Body Null None
-
2851 ZJDB PKG_TEST Body Null None
-
10155 ZJDB PKG_TEST Body Null None
-
23503 ZJDB PKG_TEST Body Null None
-
2650 ZJDB PKG_TEST Body Null None
-
14698 ZJDB PKG_TEST Body Null None
-
23 rows selected.
9. 找出ZJDB.PKG_TEST對應ddl鎖的相關信息
找出ZJDB.PKG_TEST對應的DDL鎖的SID與會話SID符合的會話,機器名字和用戶名字以及狀態
-
SQL> select b.saddr, b.sid, b.serial#,b.process,b.status,b.username, b.MACHINE from v$session b where b.sidin
-
( Select b.sid From dba_ddl_locks a, v$session b Where a.session_id = b.SID and a.name = 'PKG_TEST' and owner='ZJDB')
-
order by b.username desc;
-
-
SADDR SID SERIAL# PROCESS STATUS USERNAME MACHINE
-
---------------- ---------- ---------- ---------- -------- --------- ---------
-
070000A02BB0E7E0 1719 30765 37358470 ACTIVE SYS RACZJ-DB1
-
070000A010BFF958 10155 46885 33227250 ACTIVE ZJDB RACZJ-DB1
-
070000A00CB32110 9710 9641 27525876 ACTIVE ZJDB RACZJ-DB1
-
070000A023BC4438 2032 9479 31195338 ACTIVE ZJDB RACZJ-DB1
-
070000A0061E8210 23503 30787 55313168 ACTIVE ZJDB RACZJ-DB1
-
070000A0279C9920 948 44781 13631706 ACTIVE ZJDB RACZJ-DB1
-
070000A02B9B4FC0 893 27923 54263886 ACTIVE ZJDB RACZJ-DB1
-
070000A02BCA68C8 2650 48113 29033334 ACTIVE ZJDB RACZJ-DB1
-
070000A00DE3DE58 21183 59867 43516348 ACTIVE ZJDB RACZJ-DB1
-
070000A015A40440 18410 50345 3998656 ACTIVE ZJDB RACZJ-DB1
-
070000A009727FB0 16963 33555 66847560 ACTIVE ZJDB RACZJ-DB1
-
070000A023D20C00 2851 17117 10224286 ACTIVE ZJDB RACZJ-DB1
-
070000A02FB62590 1776 28699 46334786 ACTIVE ZJDB RACZJ-DB1
-
070000A015E08488 20671 38781 53150014 ACTIVE ZJDB RACZJ-DB1
-
070000A011386AF0 14698 60901 37421588 ACTIVE ZJDB RACZJ-DB1
-
070000A005CC69A8 20416 37977 30147116 ACTIVE ZJDB RACZJ-DB1
-
070000A001EE7AC0 21745 60937 8192734 ACTIVE ZJDB RACZJ-DB1
-
17 rows selected.
10. 操作系統層面殺process
--以sid=10155為例子,殺系統的process,這個殺process的操作,一般是在他們的主機殺process!
-
SQL> select sid, SERIAL#,process,status from v$session where sid=10155;
-
-
SID SERIAL# PROCESS STATUS
-
---------- ---------- ------------------------ --------
-
10155 30765 37358425 ACTIVE
-
-
$ ps -ef|grep 37358425
-
$ kill -9 37358425 --操作系統層面殺會話
-
$ ps -ef|grep 37358452
12. 數據庫層面殺會話
-
alter system kill session '10155,46885';
-
alter system kill session '9710,9641';
-
alter system kill session '2032,9479';
-
alter system kill session '23503,30787';
-
alter system kill session '948,44781';
-
alter system kill session '893,27923';
-
alter system kill session '2650,48113';
-
alter system kill session '21183,59867';
-
alter system kill session '18410,50345';
-
alter system kill session '16963,33555';
-
alter system kill session '2851,17117';
-
alter system kill session '1776,28699';
-
alter system kill session '20671,38781';
-
alter system kill session '14698,60901';
-
alter system kill session '20416,37977';
-
alter system kill session '21745,60937';
13. 查看創建包體的命令是否成功
-
SQL> alter package ZJDB.PKG_TEST compile body;
-
Package body altered.
14. 相關鏈接
http://www.itpub.net/forum.php?mod=viewthread&tid=1761963
https://blog.csdn.net/u011146687/article/details/72808565
聲明:本網站發布的內容(圖片、視頻和文字)以用戶投稿、用戶轉載內容為主,如果涉及侵權請盡快告知,我們將會在第一時間刪除。文章觀點不代表本網站立場,如需處理請聯系客服。電話:028-86922220;郵箱:631063699@qq.com。內容未經允許不得轉載,或轉載時需注明來源:
創新互聯