DBSNMP Session Using Huge Temp Tablespace Space (Doc ID 2889002.1)

Last updated on JANUARY 11, 2024

Applies to:

Symptoms

DBSNMP sessions are using huge temp space intermittently. Following is the sql statement consuming the temp tablespace

SqlId = 2qr52n47vt2aj

WITH jd AS
(SELECT end_time, input_bytes, session_key, session_recid, session_stamp, input_type, output_device_
type, status
FROM V$RMAN_BACKUP_JOB_DETAILS
WHERE output_device_type IN ('SBT_TAPE', '*')
AND status NOT LIKE 'RUNNING%'),
db_bkp_data AS
(SELECT to_char(jd.end_time, 'YYYY-MM-DD HH24:MI:SS') db_backup_end_time, ROUND((sysdate-end_time)*2
4,2) db_backup_age, jd.input_bytes db_backup_size, p.media db_backup_media,
jd.status db_backup_status, s.incremental_level db_backup_incr_level, jd.input_type db_backup_input_
type
FROM V$BACKUP_SET_DETAILS s, jd, V$BACKUP_PIECE_DETAILS p
WHERE jd.input_type IN ('DB FULL', 'DB INCR')
AND s.session_key = jd.session_key
AND s.session_recid = jd.session_recid
AND s.session_stamp = jd.session_stamp
AND p.session_key = s.session_key
AND p.session_recid = s.session_recid
AND p.session_stamp = s.session_stamp),
LAST_SUC_DB_FULL AS
(SELECT * FROM
(SELECT * FROM
(SELECT db_backup_end_time last_db_backup_date, db_backup_age last_db_backup_age, db_backup_size las
t_db_backup_size, db_backup_media last_full_media
FROM db_bkp_data
WHERE db_backup_status LIKE 'COMPLETED%'
AND db_backup_input_type = 'DB FULL'
AND db_backup_incr_level IS NULL
ORDER by db_backup_end_time DESC)
UNION ALL
SELECT NULL, NULL, NULL, NULL FROM DUAL)
WHERE ROWNUM = 1),
....
.....

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.