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;

 

 

TABLESPACE_NAME      FILE_NAME                 ALLOCATED_BYTES     FREE_BYTES

-------------------- ------------------------- --------------- --------------

TBS_LOCALS           /u01/app/oradata/devdb/de      20,971,520     20,774,912

                     vdb/devdb_tbs_locals_01.d

                     bf

 

********************                           --------------- --------------

sum                                                 20,971,520     20,774,912

 

 

TBS_TOURISTS         /u01/app/oradata/devdb/de      20,971,520      8,257,536

                     vdb/devdb_tbs_tourists_01

                     .dbf

 

********************                           --------------- --------------

sum                                                199,229,440    176,291,840

 

 

USERS                /u01/app/oradata/devdb/de     112,721,920        655,360

                     vdb/users01.dbf

 

********************                           --------------- --------------

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

select
   a.tablespace_name,
   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"
from
   (select
      tablespace_name,
      sum(bytes) physical_bytes,
      sum(decode(autoextensible,'NO',bytes,'YES',maxbytes)) bytes_alloc
    from
      dba_data_files
    group by
      tablespace_name ) a,
   (select
      tablespace_name,
      sum(bytes) tot_used
    from
      dba_segments
    group by
      tablespace_name ) b
where
   a.tablespace_name = b.tablespace_name (+)
and
   a.tablespace_name not in
   (select distinct
       tablespace_name
    from
       dba_temp_files)
and
   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"
from
(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
from
(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";