select substr(tabschema, 1, 8) || '.' || substr(tabname, 1, 16) tablename
, lock_name
, substr(lock_object_type, 1, 12) lock_object_type
, req_application_handle
, req_member
, substr(req_application_name, 1, 14) req_app_name
, hld_member
, hld_application_handle
, substr(hld_application_name, 1, 14) hld_app_name
, substr(req_stmt_text, 1, 32) req_stmt
, substr(hld_current_stmt_text, 1, 26) hel_cur_stmt
from sysibmadm.mon_lockwaits
;
req_application_handle 값으로 kill 한다.
--CALL SYSPROC.ADMIN_CMD('FORCE APPLICATION (124236)');
SELECT f.name, OBJECT_NAME(f.parent_object_id) TableName, COL_NAME(fc.parent_object_id,fc.parent_column_id) ColName
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
ON f.OBJECT_ID = fc.constraint_object_id
INNER JOIN sys.tables t
ON t.OBJECT_ID = fc.referenced_object_id
WHERE OBJECT_NAME (f.referenced_object_id) = '테이블명'
출처: https://hackhyun.tistory.com/250 [전산이야기]
'SQL SERVER' 카테고리의 다른 글
Extended Event Data Table 로 가져오기 (0) | 2020.10.13 |
---|---|
SQL Formatter (0) | 2020.06.05 |
sql server 2012 Recommended Trace flag (0) | 2020.02.13 |
Oracle Client 업그레이드 (0) | 2020.01.16 |
sql server default 제약조건 조회SQL (0) | 2020.01.15 |
1.undo 사용량 확인
SELECT TO_CHAR(s.sid)||',' ||TO_CHAR(s.serial#) sid_serial,
NVL(s.username, 'None') orauser,
s.program,
r.name undoseg,
ROUND(t.used_ublk * TO_NUMBER(x.value)/1024/1024, 2)||'M' "Undo"
FROM sys.v_$rollname r,
sys.v_$session s,
sys.v_$transaction t,
sys.v_$parameter x
WHERE s.taddr = t.addr
AND r.usn = t.xidusn(+)
AND x.name = 'db_block_size'
ORDER BY (t.used_ublk * TO_NUMBER(x.value/1024)) DESC
2. 현재 Rollback 진행률 확인
select OPNAME,TARGET,SOFAR,TOTALWORK,UNITS,START_TIME,TIME_REMAINING, ELAPSED_SECONDS
from v$session_longops where username='SCOTT';
--출처
'오라클' 카테고리의 다른 글
Temp TableSpace 사용량 확인 (0) | 2020.04.10 |
---|---|
lock tree 형식으로 조회 (0) | 2020.04.09 |
ORACLE shrink 작업 (0) | 2020.03.02 |
DBLink(DB Link 생성) (0) | 2019.08.22 |
oracle lock tree 확인 (0) | 2019.06.20 |