Oracle: display tablespace information with sizes

root's picture

Recently I had to extract some tablespace information along with their sizes. I won't bla bla about it too much; here it goes:

Firstly, some formatting to have a nice view (it depends of your screen size so you may have to modify the values according to your needs):

SQL>
set lines 230
set echo off
set term off
set trimspool on
set verif off
set feed off
set pagesize 100

column filename format a20
column tablespace format a15
column status format a10 trunc
column autoextend format a10

To get all information about all tablespaces:

SQL> select * from dba_data_files;

To get only certain columns from all tablespaces (in our example the columns: file_name, tablespace_name, bytes, autoextensible, increment_by, maxbytes):
Note: "/1024/1024" is calculating the value in MB and round is rounding up after comma.

SQL> select
	[color=darkblue]file_name[/color] "Filename",
	[color=darkblue]tablespace_name[/color] "Tablespace",
	round([color=darkblue]bytes[/color]/1024/1024 ,2) "Current Size (MB)",
	[color=darkblue]autoextensible[/color] "Autoextend",
	round([color=darkblue]increment_by[/color]*8192/1024/1024 ,2) "Autoextend Size (MB)",
	round([color=darkblue]maxbytes[/color]/1024/1024 ,2) "Max Size (MB)"
from dba_data_files
order by TABLESPACE_NAME;

To get only certain columns from target tablespace:

SQL> select
	[color=darkblue]file_name[/color] "Filename",
	[color=darkblue]tablespace_name[/color] "Tablespace",
	round([color=darkblue]bytes[/color]/1024/1024 ,2) "Current Size (MB)",
	[color=darkblue]autoextensible[/color] "Autoextend",
	round([color=darkblue]increment_by[/color]*8192/1024/1024 ,2) "Autoextend Size (MB)",
	round([color=darkblue]maxbytes[/color]/1024/1024 ,2) "Max Size (MB)"
from dba_data_files
[color=green]where tablespace_name = 'SYSTEM'[/color];

Output for the last command but it is similar to others:

SQL> [color=green]select
        file_name "Filename",
        tablespace_name "Tablespace",
        round(bytes/1024/1024 ,2) "Current Size (MB)",
        autoextensible "Autoextend",
        round(increment_by*8192/1024/1024 ,2) "Autoextend Size (MB)",
        round(maxbytes/1024/1024 ,2) "Max Size (MB)"
from dba_data_files
where tablespace_name = 'SYSTEM';[/color]  2    3    4    5    6    7    8    9  

Filename             Tablespace      Current Size (MB) Autoextend Autoextend Size (MB) Max Size (MB)
-------------------- --------------- ----------------- ---------- -------------------- -------------
/data/system11.dbf   SYSTEM                        250 YES                         100      32767.98
/data/system10.dbf   SYSTEM                        270 YES                         100      32767.98
/data/system01.dbf   SYSTEM                        750 YES                         100      32767.98
/data/system02.dbf   SYSTEM                        750 YES                         100      32767.98
/data/system03.dbf   SYSTEM                        750 YES                         100      32767.98
/data/system04.dbf   SYSTEM                        750 YES                         100      32767.98
/data/system05.dbf   SYSTEM                        750 YES                         100      32767.98
/data/system06.dbf   SYSTEM                        750 YES                         100      32767.98
/data/system07.dbf   SYSTEM                        750 YES                         100      32767.98
/data/system08.dbf   SYSTEM                        750 YES                         100      32767.98
/data/system09.dbf   SYSTEM                        250 YES                         100      32767.98

The following will show you the total size and percentage usage of each tablespace, order by percentage:

select T1.TABLESPACE_NAME,
       round(T1.BYTES/1024/1024 ,2) "Used Size (MB)",
       round(T2.BYTES/1024/1024 ,2) "Free Size (MB)",
       round(T2.largest/1024/1024 ,2) "Largest Datafile (MB)",
       round(((T1.BYTES-T2.BYTES)/T1.BYTES)*100,2) "Used %"
  from
  (
   select TABLESPACE_NAME,
   sum(BYTES) BYTES
   from dba_data_files
   group by TABLESPACE_NAME
  )
   T1,
  (
   select TABLESPACE_NAME,
   sum(BYTES) BYTES ,
   max(BYTES) largest
   from dba_free_space
   group by TABLESPACE_NAME
  )
   T2
   where T1.TABLESPACE_NAME=T2.TABLESPACE_NAME
   order by ((T1.BYTES-T2.BYTES)/T1.BYTES) desc;

If you do not want to convert the bytes into MB or whatever, use the following. Otherwise you will get the high values as power of 10 (eg. 3.4360E+10):

column maxbytes format 9,999,999,999,999
column bytes format 9,999,999,999,999
column user_bytes format 9,999,999,999,999

Thou shalt not steal!

If you want to use this information on your own website, please remember: by doing copy/paste entirely it is always stealing and you should be ashamed of yourself! Have at least the decency to create your own text and comments and run the commands on your own servers and provide your output, not what I did!

Or at least link back to this website.

Recent content