|
I noticed dmann wrote a question, "Finding SQL Statements hitting TEMP Space limit...", and mentioned that he was using cron to schedule a periodic query that showed use of TEMP space. On SQL Server, I generally use DMO to get database and table size information but I haven't the faintest idea how to do that in Oracle. What tables would be involved and what might a couple of useful queries be? Things I might want to know would be tablespace sizes, allocations within them by user and table. Would someone provide suggestions or samples?
(comments are locked)
|
|
This query:
is a start, I think. THe user_extents table contains storage information. There's more to it than this, I think, so other answers with more info would be welcome. But I thought I'd post this so that other people who like to experiment (as I do) would have a starting point.
(comments are locked)
|
|
Regarding Temp Space usage - most of the Temp Space queries I have seen center around v$sort_usage which has info on sorts that are currently in progress. I will dig around for a good example.
(comments are locked)
|
|
Try this...
Select d.tablespace_name "Tablespace",
sum(f.bytes_free) + sum(f.bytes_used) "Total Size",
nvl(sum(p.bytes_used), 0) "Used",
nvl(sum(f.bytes_used), 0) "Used HWM",
((sum(f.bytes_free) + sum(f.bytes_used)) - nvl(sum(p.bytes_used), 0)) "Free",
s.initial_extent "Uniform Extent Size",
sum(f.bytes_free) / s.initial_extent "# Pieces Free"
from SYS.V_$TEMP_SPACE_HEADER f, DBA_TEMP_FILES d, SYS.V_$TEMP_EXTENT_POOL p, dba_tablespaces s
where f.tablespace_name(+) = d.tablespace_name
and s.tablespace_name = d.tablespace_name
and f.file_id(+) = d.file_id
and p.file_id(+) = d.file_id
and d.tablespace_name = :x
group by d.tablespace_name, s.initial_extent
:x(VARCHAR[4],IN/OUT)='TEMP'
(comments are locked)
|

