오라클 19c undo 상세 정보 html 로 확인 쿼리

OS환경 : Oracle Linux 7.6 (64bit)

 

DB 환경 : Oracle Database 19.11.0.0

 

방법 : 오라클 19c  상세 정보 undo html 로 확인 쿼리

아래 쿼리 실행

SQL>
set pagesize 200
SET markup HTML on
spool Undo_Check.html
set echo on
show parameter undo
show con_name;
show pdbs;
show con_id;
SELECT property_name, property_value
FROM database_properties
WHERE property_name = 'LOCAL_UNDO_ENABLED';
SELECT con_id, tablespace_name
FROM cdb_tablespaces
WHERE tablespace_name LIKE 'UNDO%'
ORDER BY con_id;
/* Required Space */
SELECT (UR * (UPS * DBS))/1024/1024/1024 AS "G_Bytes" FROM (SELECT value AS UR FROM v$parameter WHERE name = 'undo_retention'), (SELECT undoblks/((end_time-begin_time)*86400) AS UPS FROM v$undostat WHERE undoblks = (SELECT MAX(undoblks) FROM v$undostat)), (SELECT block_size AS DBS FROM dba_tablespaces WHERE tablespace_name = (SELECT UPPER(value) FROM v$parameter WHERE name = 'undo_tablespace'));
SELECT DISTINCT STATUS, SUM(BYTES)/1024/1024/1024 as "G_Bytes", COUNT(*) FROM DBA_UNDO_EXTENTS GROUP BY STATUS;
/* Peak Undo generation */
SELECT undoblks/((end_time-begin_time)*86400) "Peak Undo Block Generation" FROM v$undostat WHERE undoblks=(SELECT MAX(undoblks) FROM v$undostat);
/* Undo tablespace size */
SELECT FILE_ID, BYTES/1024/1024 AS "BYTES (MB)", MAXBYTES/1024/1024 AS "MAXBYTES (MB)", AUTOEXTENSIBLE FROM DBA_DATA_FILES WHERE TABLESPACE_NAME = (SELECT UPPER(value) FROM v$parameter WHERE name = 'undo_tablespace');
select sum(bytes/1024/1024) MB_FREE from dba_free_space where tablespace_name= (SELECT UPPER(value) FROM v$parameter WHERE name = 'undo_tablespace');
/* Max Query Length */
select max(maxquerylen) from v$undostat;
/* Database identification */
select NAME, PLATFORM_ID, DATABASE_ROLE from v$database;
select * from V$version where banner like 'Oracle Database%';
/* Internal AUM settings */
select nam.ksppinm NAME, val.KSPPSTVL VALUE from x$ksppi nam, x$ksppsv val where nam.indx = val.indx and (nam.ksppinm like '%undo%' or nam.ksppinm like '_rollback%' or nam.ksppinm like '_smu%' or nam.ksppinm in ('event', '_first_spare_parameter' ) ) order by 1;
/* Tuned Undo Retention */
SELECT MAX(TUNED_UNDORETENTION), MAX(MAXQUERYLEN), MAX(NOSPACEERRCNT), MAX(SSOLDERRCNT) FROM V$UNDOSTAT;
column UNXPSTEALCNT heading "# Unexpired|attempt"
column EXPSTEALCNT heading "# Expired|attempt"
column SSOLDERRCNT heading "ORA-1555|Error"
column NOSPACEERRCNT heading "Out-Of-space|Error"
column MAXQUERYLEN heading "Max Query|Length"
column ACTIVEBLKS heading "Active Blocks"
column UNEXPIREDBLKS heading "Unexpired Blocks"
column EXPIREDBLKS heading "Expired Blocks"
column TUNED_UNDORETENTION heading "Tuned Undo retention"
column UNXPBLKRELCNT heading "Unexpired|Removed for reuse"
column UNXPBLKREUCNT heading "Unexpired|Reused by Transactions"
column EXPBLKRELCNT heading "Expired| Stolen from other"
column EXPBLKREUCNT heading "Expired| Stolen from same"
column UNDOBLKS heading "Undo Blocks"
select to_char(begin_time,'MM/DD/YYYY HH24:MI') begin_time, UNXPSTEALCNT, EXPSTEALCNT , SSOLDERRCNT, NOSPACEERRCNT, ACTIVEBLKS,UNEXPIREDBLKS,EXPIREDBLKS,UNXPBLKRELCNT,UNXPBLKREUCNT,EXPBLKRELCNT,EXPBLKREUCNT,MAXQUERYLEN, TUNED_UNDORETENTION from gv$undostat order by inst_id, begin_time;
SELECT tablespace_name, retention, min_extlen FROM dba_tablespaces WHERE contents = 'UNDO';
select SEGMENT_NAME,STATUS,TABLESPACE_NAME from dba_rollback_segs;
select t.start_time, t.used_ublk, s.username, r.segment_name.r.tablespace_name from v$transaction t,v$session s, dba_rollback_segs r where t.ses_addr= s.saddr and t.xidusn=r.segment_id;
SELECT TO_CHAR (s.SID) || ',' || TO_CHAR (s.serial#) sid_serial,NVL (s.username, 'None') orauser, s.program, r.NAME undoseg,t.used_ublk * TO_NUMBER (x.VALUE) / 1024 || 'K' "Undo",t1.tablespace_name FROM SYS.v_$rollname r,SYS.v_$session s,SYS.v_$transaction t,SYS.v_$parameter x,dba_rollback_segs t1 WHERE s.taddr = t.addr AND r.usn = t.xidusn(+) AND x.NAME = 'db_block_size' AND t1.segment_id = r.usn AND t1.tablespace_name = (SELECT UPPER(value) FROM v$parameter WHERE name = 'undo_tablespace');
select dtxn.ktuxeusn, dtxn.ktuxeslt, dtxn.ktuxesqn, dtxn.ktuxesta,dtxn.ktuxesiz from x$ktuxe dtxn where dtxn.ktuxesta <> 'INACTIVE' and dtxn.ktuxecfl like '%DEAD%' order by dtxn.ktuxesiz asc;
Select count(*) from dba_outstanding_alerts;
select object_name, reason from dba_outstanding_alerts;
select CREATION_TIME,METRIC_VALUE, reason, suggested_action from DBA_ALERT_HISTORY where OBJECT_NAME = (SELECT UPPER(value) FROM v$parameter WHERE name = 'undo_tablespace');
spool off;
set markup html off;

 

 

결과 html 파일 웹브라우저로 확인

 

 

결과파일 샘플

Undo_Check.html

0.29MB

 

 

참조 :