question

KillerDBA avatar image
KillerDBA asked

What Do I Query to Get Space and TempSpace Information?

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?

administrationtablesstoragetablespace
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

3 Answers

·
KillerDBA avatar image
KillerDBA answered

This query:

SELECT
  Sum(ue.bytes) / 1024.0 AS stg,
  ue.segment_name,
  ue.tablespace_name       
from   
   user_extents ue
GROUP BY
  ue.segment_name, ue.tablespace_name;

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.

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

dmann avatar image
dmann answered

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.

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

HillbillyToad avatar image
HillbillyToad answered

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'
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.