Displaying eHealth database space usage information using sqlplus

Products

• CA eHealth

Issue/Introduction

Introduction:

This SQL statement is used to report eHealth database space usage information. For each Oracle tablespace/datafile, it will report the total disk space allocated, used disk space, free disk space and the percent free, number of free fragments, size of largest free fragment and the status of autoextend.

The largest fragment represents the largest contiguous chunk of free space. When an object tries to allocate the next extent, it first looks for this largest fragment, if the size of the largest fragment is less than that of the next extent, the datafile space is extended if the autoextend for the datafile is on.

If the autoextend for the datafile is off and enough free fragments could not be found for next extent, Oracle reports an error that it cannot extend the object with x number of space in the Oracle alert log file.

 

 

Environment:  

eHealth 6.2.2 and 6.3.0 above

 

Answer: 

Below is the SQL that can be used to report ehealth database space usage information. You should be connected to sqlplus as the $NH_USER (example: sqlplus $NH_USER/$NH_USER).

 SELECT b.file_name "DataFile",
 b.tablespace_name "TableSpace",
 b.bytes/1048576 "TotalAlloc(M)",
 round((b.bytes - sum(nvl(f.bytes,0)))/1048576,2) "Used(M)",
 round(sum(nvl(f.bytes,0))/1048576,2) "Free(M)",
 count(f.bytes) "NumFrag",
 round(max(nvl(f.bytes,0))/1048576,2) "MaxFrag(M)",
 round((sum(nvl(f.bytes,0))/(b.bytes))*100,2) "%Free",
 b.autoextensible "AautoExt"
 FROM dba_free_space f, dba_data_files b
 WHERE f.file_id(+) = b.file_id
 GROUP BY b.tablespace_name, b.file_name, b.bytes,b.autoextensible
 ORDER BY b.tablespace_name;

Environment

Release: LHDEVC99000-6.3-eHealth-Live Health Upgrade Elements
Component: