| 用了10g以上的版本後 | |||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 最常用的工具就是awr 及addm | |||||||||||||||||
| 以前常用的script都丟掉了 | |||||||||||||||||
| 只是最近又幹起了Programmer的工作 | |||||||||||||||||
| 你就不太可能像以前幹dba一樣 | |||||||||||||||||
| 知道詳細的資料庫連接資料 | |||||||||||||||||
| 碰到都是人家已經在工具UI已經弄妥的設定 | |||||||||||||||||
| 你只能直接查internal view來解決問題 | |||||||||||||||||
| 昨天要refresh整個schema,然後重匯資料 | |||||||||||||||||
| 結果有lock,無法清空 | |||||||||||||||||
| 臨時間又懶得去翻找以前的東西 | |||||||||||||||||
| 感謝Internet | |||||||||||||||||
| 資料隨手可得阿 | |||||||||||||||||
| 以前的老dba都雪藏秘珍這種東西啦~~ | |||||||||||||||||
| 所以....這些人現在都活不下去了 | |||||||||||||||||
| 昨天對我有幫助的是下列這段語法 | |||||||||||||||||
| select | |||||||||||||||||
| c.owner, | |||||||||||||||||
| c.object_name, | |||||||||||||||||
| c.object_type, | |||||||||||||||||
| b.sid, | |||||||||||||||||
| b.serial#, | |||||||||||||||||
| b.status, | |||||||||||||||||
| b.osuser, | |||||||||||||||||
| b.machine | |||||||||||||||||
| from | |||||||||||||||||
| v$locked_object a , | |||||||||||||||||
| v$session b, | |||||||||||||||||
| dba_objects c | |||||||||||||||||
| where | |||||||||||||||||
| b.sid = a.session_id | |||||||||||||||||
| and | |||||||||||||||||
| a.object_id = c.object_id; | |||||||||||||||||
| 砍掉該session,就能繼續做事了 | |||||||||||||||||
| 出處 : | |||||||||||||||||
| http://www.dba-oracle.com/t_find_oracle_locked_objects.htm RAC:
alter system kill session 'sid,serial#,@inst_id'; |
| ###2021/05/25 找到lock chain#### |
|---|
| 這適用於有個session 卡住了後續其他的session |
| 砍掉它之後,系統就能跑了 |
| 主要是查v$wait_chains |
| 這東西不得了 |
| 怎麼以前沒看過 |
| col ses format a15 |
|---|
| set lines 180 |
| WITH blocked AS |
| (SELECT * FROM |
| (SELECT instance, sid, sess_serial#, blocker_instance, |
| blocker_sid, blocker_sess_serial#, level lv, |
| num_waiters, blocker_chain_id |
| FROM v$wait_chains |
| CONNECT BY PRIOR sid = blocker_sid |
| AND PRIOR sess_serial# = blocker_sess_serial# |
| AND PRIOR INSTANCE = blocker_instance |
| START WITH blocker_is_valid = 'FALSE' |
| ) |
| WHERE num_waiters >0 OR blocker_sid IS NOT NULL |
| ) |
| SELECT instance, |
| LPAD(' ', 2*(lv-1)) ||b.sid ses, b.sess_serial#, |
| b.blocker_instance, b.blocker_sid, b.blocker_sess_serial# |
| FROM blocked b |
 |
| hang analysis也可以抓wait chain |
|---|
| SQL> oradebug setmypid |
SQL> oradebug -g all hanganalyze 10 這是8720.trc 的內容  |
| 指令alter system kill session '33,1361'; lock wait 就解了 不過底下這傢伙 |
| 寫得更精采 |
| 讓我不得不佩服 |
| 這些人到底去哪裡找到的資料??? |
The Backdoor Entry to Oracle Database  |
無法用hanganalyze 12 來解決
有時間倒是可以研究一下各 level 要看什麼東西
session無法登入的情況
以前是真的碰過一次
當時要是知道這方法
也許就能查明原因了
| ### 2021/06/01#### |
|---|
| 今天碰到一個狀況 |
| 居然連select_catalog_role的權限都不給 |
| 真的是....莫名其妙 |
| 只好去找一個一個對應的fixed table的名稱 |
| grant select on sys.gv_$session to steven; |
| grant select on sys.gv_$locked_object to steven; |
| grant select on sys.v_$wait_chains to steven; |
| --Wait Chain--- |
|---|
| col ses format a15 |
| set lines 180 |
| WITH blocked AS |
| (SELECT * FROM |
| (SELECT instance, sid, sess_serial#, blocker_instance, |
| blocker_sid, blocker_sess_serial#, level lv, |
| num_waiters, blocker_chain_id |
| FROM sys.v_$wait_chains |
| CONNECT BY PRIOR sid = blocker_sid |
| AND PRIOR sess_serial# = blocker_sess_serial# |
| AND PRIOR INSTANCE = blocker_instance |
| START WITH blocker_is_valid = 'FALSE' |
| ) |
| WHERE num_waiters >0 OR blocker_sid IS NOT NULL |
| ) |
| SELECT instance, |
| LPAD(' ', 2*(lv-1)) ||b.sid ses, b.sess_serial#, |
| b.blocker_instance, b.blocker_sid, b.blocker_sess_serial# |
| FROM blocked b; |
| --Lock Objects --- |
| select * from sys.v_$wait_chains ; |
| select |
| b.inst_id, |
| c.object_name, |
| c.object_type, |
| b.sid, |
| b.serial#, |
| b.status, |
| b.osuser, |
| b.machine |
| from |
| sys.gv_$locked_object a , |
| sys.gv_$session b, |
| user_objects c |
| where |
| b.sid = a.session_id |
| and |
| a.object_id = c.object_id; |