How to get Harvest Tablespace Usage and Freespace

Products

• CA Harvest Software Change Manager - OpenMake Meister

Issue/Introduction

How do I get a report from my Oracle database on the table space usage and free space?

Environment

Harvest R12.6 and above, Oracle

Resolution

The following query works to provide the database usage and free space from the Oracle database:

// Oracle
select
   fs.tablespace_name                                  "Tablespace",
   (df.totalspace - fs.freespace)                     "Used MB",
   fs.freespace                                             "Free MB",
   df.totalspace                                            "Total MB",
   round(100 * (fs.freespace / df.totalspace)) "Pct. Free"
from
   (select
      tablespace_name,
      round(sum(bytes) / 1048576) TotalSpace
   from
      dba_data_files
   group by
      tablespace_name
   ) df,
   (select
      tablespace_name,
      round(sum(bytes) / 1048576) FreeSpace
   from
      dba_free_space
   group by
      tablespace_name
   ) fs
where
   df.tablespace_name = fs.tablespace_name;

Other Information: Here is an example of running this query and getting the results using the CA DBrowse freeware utility (The HARVEST tablespaces would be the data of interest):