select sid, serial#, username, module, blocking_session, sql_id, sql_exec_start, status, prev_sql_id, event, b.object_id ,(select object_name from dba_objects o where o.object_id=b.object_id) object_name, (select sql_text from v$sql q where q.sql_id=s.sql_id) sqltext from v$session s, v$locked_object b where s.sid=b.session_id; select * from dba_dml_locks; select * from dba_lock where lock_type in (‘Transaction’);
lock mode
select sid, type, lmode from v$lock where type=’TM’; select object_id, session_id, locked_mode from v$locked_object;
현재 상황 해결 ( session kill )
<일반적인 상황> 1.현재 blocking session과 waiting session을 확인한다. 2.고객의 요구에 따라 현재 blocking session의 kill, commit, rollback을 결정 <현재 문제상황 해결> 1.현재 blocking session을 확인하고 kill 2-1. 현재 blocking session만 kill > 해당 lock걸린 object의 가장 먼저 실행된 wating session이 blocking session이 된다 2-2. 현재 blocking session과 관련된 모든 wait session을 kill
blocking session 찾기
1개 select sid, serial#, status, (select sql_fulltext from v$sql where sql_id=a.prev_sql_id) sqltext, prev_exec_start from v$session a where sid in (select blocking_session from v$session ); select * from dba_writers;
session이 수행한 sql
--blocking session의 수행 sql select sid, serial#, status, (select sql_fulltext from v$sql where sql_id=a.prev_sql_id) sqltext, prev_exec_start from v$session a where sid in (select blocking_session from v$session ); --waiting session의 수행 sql select sid, serial#, username, module, blocking_session, sql_id, sql_exec_start, status, prev_sql_id, event, b.object_id ,(select object_name from dba_objects o where o.object_id=b.object_id) object_name, (select sql_text from v$sql q where q.sql_id=s.sql_id) sqltext from v$session s, v$locked_object b where blocking_session is not null and s.sid=b.session_id;
2022-03-29T21:47:29.789200+09:00 Fatal SIGBUS with si_code BUS_MCEERR_AR encountered. Exception [type: SIGBUS, Machine check - action required] [ADDR:0x57A91000F] [PC:0x1D96640, kcbso1()+6096] [flags: 0x0, count: 1] Errors in file /u01/app/oracle/diag/rdbms/test/TEST/trace/TEST_clmn_53927.trc (incident=292633): ORA-07445: exception encountered: core dump [kcbso1()+6096] [SIGBUS] [ADDR:0x57A91000F] [PC:0x1D96640] [Machine check - action required] [] Incident details in: /u01/app/oracle/diag/rdbms/test/TEST/incident/incdir_292633/TEST_clmn_53927_i292633.trc Use ADRCI or Support Workbench to package the incident. See Note 411.1 at My Oracle Support for error and packaging details. Fatal SIGBUS with si_code BUS_MCEERR_AR encountered. Fatal SIGBUS with si_code BUS_MCEERR_AR encountered.
에러 유형 선택 및 첫번째 인자값, 버전 선택 후 Look-up Error
에러에 매칭되는 문서가 있는 경우
매칭되는 문서 확인, 클릭하여 문서 확인
관련 문서 클릭하여 내용 확인
아래 DETAILS에 세부 내용 확인 가능
매칭되는 문서가 없는 경우
Alert.ora 파일의 에러 확인
2022-03-29T21:47:21.691516+09:00 Continuing execution after SIGBUS with si_code of BUS_MCEERR_AO at address 0x57a800000 Errors in file /u01/app/oracle/diag/rdbms/test/TEST/trace/TEST_mz00_260241.trc (incident=294001): ORA-07445: exception encountered: core dump [sxorchk()+118] [SIGBUS] [ADDR:0x57A800000] [PC:0x12A76F56] [Machine check - action required] [] Incident details in: /u01/app/oracle/diag/rdbms/test/TEST/incident/incdir_294001/TEST_mz00_260241_i294001.trc Use ADRCI or Support Workbench to package the incident. See Note 411.1 at My Oracle Support for error and packaging details. Fatal SIGBUS with si_code BUS_MCEERR_AR encountered. Fatal SIGBUS with si_code BUS_MCEERR_AR encountered.
에러 유형 선택 및 첫번째 인자값, 버전 선택 후 Look-up Error, 매칭되는 문서가 없음
인자 하나 더 추가하여 Look-up Error
해당 에러의 ~.trc 파일 확인
2022-03-29T21:47:21.691516+09:00 Continuing execution after SIGBUS with si_code of BUS_MCEERR_AO at address 0x57a800000 Errors in file /u01/app/oracle/diag/rdbms/test/TEST/trace/TEST_mz00_260241.trc (incident=294001): ORA-07445: exception encountered: core dump [sxorchk()+118] [SIGBUS] [ADDR:0x57A800000] [PC:0x12A76F56] [Machine check - action required] [] Incident details in: /u01/app/oracle/diag/rdbms/test/TEST/incident/incdir_294001/TEST_mz00_260241_i294001.trc Use ADRCI or Support Workbench to package the incident. See Note 411.1 at My Oracle Support for error and packaging details. Fatal SIGBUS with si_code BUS_MCEERR_AR encountered. Fatal SIGBUS with si_code BUS_MCEERR_AR encountered.
파일 내부 Stack 검색하여 맨 끝까지 복사
-- 중략 -- *** 2022-03-29T21:47:21.790663+09:00 dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x3, level=3, mask=0x0) [TOC00004] ----- Current SQL Statement for this session (sql_id=1j6tnz8fcm4c3) ----- delete /* QOSH:PURGE_OLD_STS *//*+ dynamic_sampling(4) */ from sys.wri$_optstat_histgrm_history where savtime < :1 and savtime > timestamp '1900-01-01 00:00:00 -0:0' and rownum <= :2 [TOC00004-END]
[TOC00005] ----- Call Stack Trace ----- calling call entry argument values in hex location type point (? means dubious value) -------------------- -------- -------------------- ----------------------------
