'조회'에 해당되는 글 2건

반응형

SELECT
  TO_CHAR(NEXT_TIME,'YY/MM/DD(DY) HH24') "NEXT_TIME"
, COUNT(DISTINCT NAME) "TOTAL_ARC_COUNT"
, ROUND(SUM(DISTINCT BLOCKS*BLOCK_SIZE)/1024/1024/1024,2) "TOTAL_SIZE_GB"
, SUM(CASE WHEN INST_ID = 1 AND THREAD# = 1 THEN 1 ELSE 0 END) "RAC1"
, SUM(CASE WHEN INST_ID = 2 AND THREAD# = 2 THEN 1 ELSE 0 END) "RAC2"
, SUM(CASE WHEN INST_ID = 3 AND THREAD# = 3 THEN 1 ELSE 0 END) "RAC3"
, SUM(CASE WHEN INST_ID = 4 AND THREAD# = 4 THEN 1 ELSE 0 END) "RAC4"
FROM GV$ARCHIVED_LOG
WHERE NEXT_TIME >= TRUNC(SYSDATE) -10
GROUP BY TO_CHAR(NEXT_TIME,'YY/MM/DD(DY) HH24')
ORDER BY 1;



select * 
  from GV$ARCHIVED_LOG;
  
  
  select * from v$recovery_file_dest;
  
  select * from V$ARCHIVED_LOG;
  
  select * from v$archive;
  
  select * from v$log;
  
  select * from v$archive_dest;
  
  select * from v$archive_processes;
 
 select dest_name, name, sequence#, b.status
  from v$archive_dest a, v$archived_log b
  where a.dest_id = b.dest_id;

반응형
블로그 이미지

Runer

IT / 일상 / 먹방 / 꿀팁 / 유틸

,
반응형

오라클(oracle) 락(lock) 확인 및 제거(kill)
============================================================================

 

-- 락걸린 테이블 확인
SELECT do.object_name, do.owner, do.object_type, do.owner,
vo.xidusn, vo.session_id, vo.locked_mode
FROM
v$locked_object vo , dba_objects do
WHERE vo.object_id = do.object_id ;

 

 

--해당테이블이 락에 걸렸는지..

SELECT A.SID, A.SERIAL#, B.TYPE, C.OBJECT_NAME
FROM V$SESSION A, V$LOCK B, DBA_OBJECTS C
WHERE A.SID=B.SID AND B.ID1=C.OBJECT_ID
AND B.TYPE='TM' AND C.OBJECT_NAME IN ('테이블명');

 

 

/* 락발생 사용자와 sql, object 조회 */

SELECT distinct x.session_id, a.serial#,
d.object_name, a.machine, a.terminal,
a.program, b.address, b.piece, b.sql_text
FROM v$locked_object x, v$session a, v$sqltext b, dba_objects d
WHERE x.session_id = a.sid and
x.object_id = d.object_id and
a.sql_address = b.address
order by b.address,b.piece;

 

 

/* 락 발생 사용자확인 */

SELECT distinct x.session_id, a.serial#,
d.object_name, a.machine, a.terminal, a.program,
a.logon_time , 'alter system kill session ''' || a.sid || ', ' || a.serial# || ''';'
FROM gv$locked_object x, gv$session a, dba_objects d
WHERE x.session_id = a.sid and x.object_id = d.object_id
order by logon_time;

 

 

/* 접속 사용자 제거 */

--alter system kill session 'session_id,serial#';
alter system kill session '26,6044';

 

 

/* 현재 접속자의 sql 분석 */

SELECT distinct a.sid, a.serial#,
a.machine, a.terminal, a.program,
b.address, b.piece, b.sql_text
FROM v$session a, v$sqltext b
WHERE a.sql_address = b.address
order by a.sid, a.serial#,b.address,b.piece;

반응형
블로그 이미지

Runer

IT / 일상 / 먹방 / 꿀팁 / 유틸

,