x

How do I determine the free space in a tablespace?

How do I determine the free space in a tablespace?

more ▼

asked Nov 10, 2009 at 08:14 AM in Default

Cee gravatar image

Cee
153 12 13 15

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

2 answers: sort voted first

Query DBA_FREE_SPACE.

Possibly more usefully, understand how space is allocated in extents by Oracle. Each Segment (table, partition, index etc.) has one or more extents for storing data. The extents exist in a tablespace. When more storage is needed by a segment, another extent is allocated, the space in that extent may still mostly be unused, but now can only be used by that segment.

DBA_FREE_SPACE will tell you about the unallocated extents in a tablespace.

Use the procedures in DBMS_SPACE to query how much unused space has been allocated to an object.

http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/schema005.htm provides a good overview

more ▼

answered Nov 10, 2009 at 11:31 AM

Andrew Mobbs gravatar image

Andrew Mobbs
1.5k 1 3

This can get tricky, if autoextend is on, then the tablespace can grow as needed, and won't really be 'full' until it hits it's quota or the device becomes full if set to 'UNLIMITED'. Toad tries to account for this if you happen to have it available...
Nov 10, 2009 at 02:06 PM HillbillyToad

The unallocated space isn't technically in the tablespace yet :-)

Good point; though modern filesystems can be dynamically resized, and some (such as NetApp filers) are also capable of autogrow, so even just looking at space left in the filesystem isn't always sufficient to determine the growth limits.
Nov 11, 2009 at 07:37 AM Andrew Mobbs
(comments are locked)
10|1200 characters needed characters left

Here's a query I use:

SELECT a.TABLESPACE_NAME, a.BYTES bytes_used, b.BYTES bytes_free, b.largest, round(((a.BYTES-b.BYTES)/a.BYTES)*100,2) percent_used from ( select TABLESPACE_NAME, sum(BYTES) BYTES from dba_data_files group by TABLESPACE_NAME ) a left outer join ( select TABLESPACE_NAME, sum(BYTES) BYTES , max(BYTES) largest from dba_free_space group by TABLESPACE_NAME ) b on a.TABLESPACE_NAME=b.TABLESPACE_NAME order by ((a.BYTES-b.BYTES)/a.BYTES) desc

more ▼

answered Nov 10, 2009 at 04:42 PM

prodlife gravatar image

prodlife
201 1

(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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x378
x30
x17
x1

asked: Nov 10, 2009 at 08:14 AM

Seen: 2576 times

Last Updated: Nov 10, 2009 at 08:14 AM