Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 








Oracle tablespace script

Oracle Database Tips by Donald Burleson


This next Oracle tablespace query can be very helpful when you are trying to understand what tablespaces you have in your database, and what datafiles are associated with those tablespaces. In this query we use DBA_DATA_FILES to extract each tablespace name, each datafile is listed with the tablespace, and the size of the datafile is also listed.

We have also joined in DBA_FREE_SPACE to determine how much free space we have left in our tablespace. This gives us some idea about future growth. Note that we have also included some SQL*Plus commands to make the report look much nicer.

SQL>BREAK ON tablespace_name SKIP 2
SQL>COMPUTE SUM OF allocated_bytes, free_bytes ON tablespace_name
SQL>COLUMN allocated_bytes FORMAT 9,999,999,999
SQL>COLUMN free_bytes FORMAT 9,999,999,999
SQL>SELECT a.tablespace_name, a.file_name, a.bytes allocated_bytes,
2 b.free_bytes
3 FROM dba_data_files a,
4 (SELECT file_id, SUM(bytes) free_bytes
5 FROM dba_free_space b GROUP BY file_id) b
6 WHERE a.file_id=b.file_id
7 ORDER BY a.tablespace_name;
-------------------- ------------------------- --------------- --------------
TBS_LOCALS           /u01/app/oradata/devdb/de      20,971,520     20,774,912
********************                           --------------- --------------
sum                                                 20,971,520     20,774,912
TBS_TOURISTS         /u01/app/oradata/devdb/de      20,971,520      8,257,536
********************                           --------------- --------------
sum                                                199,229,440    176,291,840
USERS                /u01/app/oradata/devdb/de     112,721,920        655,360
********************                           --------------- --------------
sum                                                112,721,920        655,360


Queries like this make great tablespace monitoring queries, though I?d probably further restrict the rows that are returned to those where the tablespace is has only five or ten percent free space available. The point that we want you to get here is that there are a number of different ways of looking at this tablespace data and querying the data dictionary, but you have to dig into the data dictionary and learn it before you will be able to find such queries.  Here is another example script to show space usage within tablespaces:

set lines 132
set pages 105
set pause off
set echo off
set feedb on

column "TOTAL ALLOC (MB)"      format 9,999,990.00
column "TOTAL PHYS ALLOC (MB)" format 9,999,990.00
column "USED (MB)"             format 9,999,990.00
column "FREE (MB)"             format 9,999,990.00
column "% USED"                format 990.00

   a.bytes_alloc/(1024*1024) "TOTAL ALLOC (MB)",
   a.physical_bytes/(1024*1024) "TOTAL PHYS ALLOC (MB)",
   nvl(b.tot_used,0)/(1024*1024) "USED (MB)",
   (nvl(b.tot_used,0)/a.bytes_alloc)*100 "% USED"
      sum(bytes) physical_bytes,
      sum(decode(autoextensible,'NO',bytes,'YES',maxbytes)) bytes_alloc
    group by
      tablespace_name ) a,
      sum(bytes) tot_used
    group by
      tablespace_name ) b
   a.tablespace_name = b.tablespace_name (+)
   a.tablespace_name not in
   (select distinct
   a.tablespace_name not like 'UNDO%'
order by 1

For a complete set of Oracle tablespace scripts, try the Oracle Script collection.  It's over 600 ready-to-run Oracle dictionary scripts.

col "Tablespace"          for a22
col "Used MB"             for 9,999,999.99
col "Free MB"             for 9,999,999.99
col "Total MB"            for 9,999,999.99
col extendable_free_space for 9,999,999.99
col "Pct. Free"           for 999,999.99
col "maxspace"            for 999,999.99
set linesize 112

select df.tablespace_name "Tablespace",
totalusedspace "Used MB",
(df.totalspace - tu.totalusedspace) "Free MB",
df.totalspace "Total MB",
round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace),2)
"Pct. Free"
(select tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from dba_data_files
group by tablespace_name) df,
(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
from dba_segments
group by tablespace_name) tu
where df.tablespace_name = tu.tablespace_name;

select df.tablespace_name "Tablespace",
nvl(totalusedspace,0) "Used MB",
(df.totalspace - nvl(tu.totalusedspace,0)) "Free MB",
df.totalspace "Total MB",
round(100 * ( (df.totalspace - nvl(tu.totalusedspace,0))/ df.totalspace),2)
"Pct. Free",
nvl(fs.free_space,0) extendable_free_space
, round(maxspace,2) maxspace
(select tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
, sum(maxbytes)/1024/1024 maxspace
from dba_data_files
group by tablespace_name) df,
(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
from dba_segments
group by tablespace_name) tu
select tablespace_name, round(sum(bytes)/1024/1024 ,2) as free_space
       from dba_free_space
       group by tablespace_name
) fs
where df.tablespace_name = tu.tablespace_name(+)
AND df.tablespace_name = fs.tablespace_name(+)
ORDER BY "Pct. Free";

If you like Oracle tuning, you may enjoy the new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.



Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.