mysql undo表空间大小_【DB笔试面试184】在Oracle中，如何监控TEMP和UNDO表空间并将耗费TEMP和UNDO空间非常大的SQL语句记录？...

CREATE TABLE XT_TMP_TS_LHR

( INST_ID                NUMBER,

SID                    NUMBER,

SERIAL#                NUMBER,

USERNAME               VARCHAR2(30),

STATUS                 VARCHAR2(8),

ACTION                 VARCHAR2(64),

MACHINE                VARCHAR2(64),

MODULE                 VARCHAR2(64),

OSUSER                 VARCHAR2(30),

TERMINAL               VARCHAR2(30),

PROGRAM                VARCHAR2(48),

SQL_ID                 VARCHAR2(13),

TABLESPACE             VARCHAR2(31),

SIZE_M                 NUMBER,

TEMP_TS_SIZE_M         NUMBER,

C_USED_PERCENT         NUMBER,

SEGTYPE                VARCHAR2(9),

SQL_TEXT               VARCHAR2(4000),

IN_DATE      DATE

)

NOLOGGING;

CREATE OR REPLACE PROCEDURE P_TMP_TS_LHR AS

BEGIN

INSERT INTO XT_TMP_TS_LHR

SELECT V.INST_ID,

V.SID,

V.SERIAL#,

V.USERNAME,

V.STATUS,

V.ACTION,

V.MACHINE,

V.MODULE,

V.OSUSER,

V.TERMINAL,

V.PROGRAM,

V.SQL_ID,

SU.TABLESPACE,

(SU.BLOCKS *

TO_NUMBER((SELECT RTRIM(VALUE)

FROM V$PARAMETER P

WHERE P.NAME = 'db_block_size'))) / 1024 / 1024 AS SIZE_M,

(SELECT ROUND(SUM(BYTES) / (1024 * 1024), 3) FROM V$TEMPFILE) TEMP_TABLESPACE_SIZE_M,

ROUND((SU.BLOCKS *

TO_NUMBER((SELECT RTRIM(VALUE)

FROM V$PARAMETER P

WHERE P.NAME = 'db_block_size'))) * 100 /

(SELECT SUM(BYTES)

FROM V$TEMPFILE),

3) C_USED_PERCENT,

SU.SEGTYPE,

(SELECT A.SQL_TEXT

FROM GV$SQLAREA A

WHERE A.SQL_ID = NVL(V.SQL_ID, SU.SQL_ID)

AND A.INST_ID = V.INST_ID

AND ROWNUM = 1) SQL_TEXT,

SYSDATE

FROM GV$SORT_USAGE SU, --GV$TEMPSEG_USAGE

GV$SESSION    V

WHERE SU.SESSION_ADDR = V.SADDR

AND SU.INST_ID = V.INST_ID;

COMMIT;

END P_TMP_TS_LHR;

/