This content has been marked as final. Show 4 replies
Hi;1 person found this helpful
Oracle Recommended Patches For Tablespace Monitoring using the Tablespace Space Used % metric [ID 849498.1]
How Can Temporary Segment Usage Be Monitored Over Time? [ID 364417.1]
Is there information in the view: select * from v$sort_usage; ?
Or check on regular base:
You could also check the long operations
SELECT s.sid "SID", s.username "User", s.program "Program", u.tablespace "Tablespace", u.contents "Contents", u.extents "Extents", u.blocks * 8 / 1024 "Used Space in MB", q.sql_text "SQL TEXT", a.object "Object", k.bytes / 1024 / 1024 "Temp File Size" FROM v$session s, v$sort_usage u, v$access a, dba_temp_files k, v$sql q WHERE s.saddr = u.session_addr AND s.sql_address = q.address AND s.sid = a.sid AND u.tablespace = k.tablespace_name;
SELECT sl.sid, sl.serial#, SYSDATE, TO_CHAR (sl.start_time, 'DD-MON-YYYY:HH24:MI:SS') start_time, ROUND ( (sl.elapsed_seconds / 60), 2) min_elapsed, ROUND ( (sl.time_remaining / 60), 2) min_remaining, sl.opname, sl.MESSAGE FROM v$session_longops sl, v$session s WHERE s.sid = sl.sid AND s.serial# = sl.serial# AND sl.opname like 'Sort%' ORDER BY sl.start_time DESC, sl.time_remaining ASC;
Use V$TEMPSEG_USAGE to monitor usage of the temp tablespace.
Note that as the usage grows, extents are allocated to the temporary segment. They do NOT shrink (or deallocate) when usage falls. The Temporary segment always remains at the highest achieved size. So the 75GB "usage" for the tablespace that you see would have been the peak usage. Usage would only shrink when you shutdown and restart the instance (or if you create and designate a new temporary tablespace and drop the existing one).
Hemant K Chitale
Hi , I havent restarted or deleted the temp tablespace, but at the moment the usage is very minimal almost 0.01% . from dba_tablespace_usage_metrics.
Im following the ID 364417 metalink , which is given by above user, will let you guys know if i find something interesting