Core Issue
To avoid running out of space, it is important to track the utilized size for each data file on each Oracle database.
Resolution
To query the Service Agent (SA) 2.0 Master Provisioning Data Store (MPDS) for the percentage of each data file utilized in Oracle databases, perform these steps:
- Login to both Oracle databases through SQLPlus (enter sqlplus) as the "system" user.
- Run this command:
select t.file_name, sum(f.bytes), t.bytes, (sum(f.bytes)/t.bytes)*100 from dba_free_space f, dba_data_files t where t.file_id = f.file_id group by t.file_name, f.file_id, t.bytes;
This is an example of the command output:
FILE_NAME
--------------------------------------------------------------------------------
SUM(F.BYTES) BYTES (SUM(F.BYTES)/T.BYTES)*100
------------ ---------- --------------------------
/db002/oradata/database1/tools01.dbf
10420224 10485760 99.375
/db002/oradata/database1/undotbs.dbf
384499712 419430400 91.671875
/db002/oradata/database1/users01.dbf
104726528 104857600 99.875
Note: This output represents the current free space for each data file.