I have 14 database but only one of them is causing this error. Anyway I can run a gather stats command on a table to get this sorted?
Oracle 11gRAC 11.2.0.4, linux 6
Anyone know which tables
I've attached the query that DBSNMP is running to cause this, it's based on RMAN tables
thanks
I think there was a similar issue in previous release but not sure if this would even solve it
High temp space consumption by DBSNMP (Doc ID 1389377.1)
https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=569714749957873
sql code below
SELECT DISTINCT j.session_key ,
j.session_recid ,
j.session_stamp ,
j.command_id ,
j.STATUS ,
j.input_type ,
j.output_device_type ,
TO_CHAR(j.start_time, 'YYYY-MM-DD HH24:MI:SS') AS start_time ,
TO_CHAR(j.end_time, 'YYYY-MM-DD HH24:MI:SS') AS end_time ,
j.time_taken_display ,
j.input_bytes ,
j.output_bytes ,
sp.media ,
sp.keep_until ,
sp.keep ,
sp.keep_options ,
j.compression_ratio ,
DECODE(sp.incremental_level,-1, NULL, sp.incremental_level) incremental_level,
j.elapsed_seconds ,
NVL(sp.tag, 'NO_TAG_'
||
TO_CHAR(systimestamp,'ddmmyyyyhh24missFF')) tag,
sp.CONTAINER ,
sp.compressed ,
sp.encrypted ,
j.input_bytes_display ,
j.output_bytes_display ,
j.output_bytes_per_sec ,
j.output_bytes_per_sec_display
FROM v$rman_backup_job_details j
LEFT JOIN
(
SELECT *
FROM
(
SELECT d.*,
COUNT(tag) over (partition BY tag) tag_count_final
FROM
(
SELECT t.* ,
COUNT(tag) over (partition BY tag) tag_count
FROM
(
SELECT DISTINCT session_recid ,
session_stamp ,
incremental_level ,
TO_CHAR(keep_until,'YYYY-MM-DD HH24:MI:SS') keep_until,
keep ,
keep_options ,
tag ,
media ,
compressed ,
ENCRYPTED ,
CONTAINER
FROM
(
SELECT s.session_recid ,
s.session_stamp ,
NVL(s.incremental_level, -1) incremental_level, -- change null to -1 to enable the comparisions for distinct.
keep_until ,
s.keep ,
NVL(s.keep_options, ' ') keep_options ,
p.tag ,
NVL(p.media,' ') media ,
p.compressed ,
p.ENCRYPTED ,
p.bs_key ,
p.handle ,
NVL(
CASE
WHEN p.device_type != 'DISK'
THEN NVL2(p.handle, NVL2(SUBSTR(p.handle, 1, INSTR(p.handle, '/', -1, 1) - 1), SUBSTR(p.handle, 1, INSTR(p.handle,
'/', -1, 1) - 1), p.MEDIA), p.MEDIA)
ELSE p.MEDIA
END, ' ') AS CONTAINER
FROM v$backup_set_details s,
v$backup_piece_details p
WHERE s.session_recid = p.session_recid
AND s.bs_key = p.bs_key
AND p.session_stamp = s.session_stamp
)
)
t -- This will get the distinct data per session from the piece and set details.
)
d -- This will add the tag_count column at the end of data fetched in "t"
WHERE d.tag_count <= 1
OR
(
d.tag_count > 1
AND d.incremental_level != -1
) --Filter out the rows with null incremental levels only if there are other rows corresponding to same tag.
)
WHERE tag_count_final <=1
)
sp -- Filter out all the duplicate tag rows.
ON sp.session_recid = j.session_recid
AND sp.session_stamp = j.session_stamp
ORDER BY NVL(end_time, start_time) DESC