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 '09 at 02:51 PM in Default

KillerDBA gravatar image

1.5k 8 9 10

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

3 answers: sort voted first

This query:

  Sum(ue.bytes) / 1024.0 AS stg,
   user_extents ue
  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 '09 at 05:13 PM

KillerDBA gravatar image

1.5k 8 9 10

(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 '09 at 05:44 PM

dmann gravatar image

437 1 1 4

(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 '10 at 07:46 PM

HillbillyToad gravatar image

1k 2

(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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



Answers and Comments

SQL Server Central

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



asked: Dec 03 '09 at 02:51 PM

Seen: 1444 times

Last Updated: Dec 03 '09 at 02:51 PM