How to Monitor LOB Storage in Oracle: A Simple SQL Query

Database administrators often need to monitor storage usage, and one of the most common culprits for large data growth is Large Objects (LOBs). LOBs can include anything from documents and images to video files stored directly in the database.

Knowing which programs are generating the most LOB data is a critical first step in managing your database's storage footprint. The following SQL query provides a simple and effective way to do just that, presenting the results in both megabytes (MB) and gigabytes (GB) for easy analysis.

The Query

Here is the SQL query you can run against your Oracle database:

-- This query calculates the total size of LOBs per program,
-- showing the results in both Megabytes (MB) and Gigabytes (GB).
select
  program_name,
  -- Calculate size in Megabytes (MB)
  round(sum(dbms_lob.getlength(FILE_DATA)) / 1024 / 1024, 0) as "Size(MB)",
  -- Calculate size in Gigabytes (GB)
  round(sum(dbms_lob.getlength(FILE_DATA)) / 1024 / 1024 / 1024, 2) as "Size(GB)"
from
  APPS.fnd_LOBS
where
  expiration_date is NULL
group by
  program_name
order by
  "Size(MB)" desc;

Breakdown of the Query

• select program_name, ...: This selects the name of the program that owns the LOB.

• dbms_lob.getlength(FILE_DATA): This is the core of the calculation. The dbms_lob.getlength function retrieves the size of the FILE_DATA LOB column in bytes.

• round(sum(...) / 1024 / 1024, 0) as "Size(MB)": We take the sum of the byte lengths for each program, then divide by 1024 twice to convert bytes to megabytes. The round function ensures the number is a clean integer, and the as clause provides a clear column header.

• round(sum(...) / 1024 / 1024 / 1024, 2) as "Size(GB)": Similar to the megabyte calculation, this line divides the total bytes by 1024 three times to get the size in gigabytes, rounded to two decimal places.

• from APPS.fnd_LOBS: The query retrieves data from the fnd_LOBS table, which is a common table in Oracle E-Business Suite for storing LOB data.

• where expiration_date is NULL: This is an important filter. It ensures that only LOBs that have not yet expired are included in the size calculation, giving you a picture of your current, active storage.

• group by program_name: This groups all rows belonging to the same program_name together so that the sum function can calculate a total for each one.

• order by "Size(MB)" desc: Finally, the results are sorted in descending order based on the total size in megabytes. This brings the programs consuming the most storage to the top of your list, allowing you to quickly identify your biggest storage users.

Conclusion

Running this simple query can provide valuable insights into your database's LOB storage, helping you proactively manage resources and identify areas for potential cleanup or optimization.