x

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?

more ▼

asked Dec 03, 2009 at 02:51 PM in Default

avatar image

KillerDBA
1.5k 9 11 14

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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.

more ▼

answered Dec 03, 2009 at 05:13 PM

avatar image

KillerDBA
1.5k 9 11 14

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Dec 04, 2009 at 05:44 PM

avatar image

dmann
437 2 3 8

(comments are locked)
10|1200 characters needed characters left

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'
more ▼

answered May 09, 2010 at 07:46 PM

avatar image

HillbillyToad
1k 2 4

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x131
x63
x38
x17

asked: Dec 03, 2009 at 02:51 PM

Seen: 2059 times

Last Updated: Dec 03, 2009 at 02:51 PM

Copyright 2016 Redgate Software. Privacy Policy