Managing Table Sizes with LOB Segments in Oracle
What Are LOB Segments in Oracle?
LOB (Large Object) segments store unstructured data such as documents, images, and videos separately from standard table data. Unlike traditional column data, LOBs require special handling due to their large size and storage complexity.
Key characteristics of LOBs:
- Stored in dedicated physical segments separate from table rows
- Can grow into terabytes, requiring efficient management
- Support various types, including
BLOB, CLOB, and NCLOB
- Can be stored as BasicFiles (older format) or SecureFiles (modern, optimized storage)
Why LOB Size Management Matters
As database systems grow, uncontrolled LOB storage can lead to significant performance and cost challenges. Consider the following real-world examples:
| Scenario |
Table Size |
LOB Size |
| Document Management System |
5GB |
450GB |
| Medical Imaging Database |
2GB |
1.2TB |
SQL Query for Size Calculation
The following SQL query helps you calculate table sizes, including associated LOB segments, for better storage planning.
WITH table_size AS (
SELECT dt.owner, dt.table_name,
ROUND(SUM(ds.bytes)/1024/1024/1024, 2) AS table_size_gb
FROM dba_segments ds
JOIN dba_tables dt ON ds.segment_name = dt.table_name
WHERE ds.segment_type = 'TABLE'
GROUP BY dt.owner, dt.table_name
),
lob_size AS (
SELECT dl.owner, dl.table_name,
ROUND(SUM(ds.bytes)/1024/1024/1024, 2) AS lob_size_gb
FROM dba_lobs dl
JOIN dba_segments ds ON ds.segment_name = dl.segment_name
WHERE ds.segment_type = 'LOBSEGMENT'
GROUP BY dl.owner, dl.table_name
)
SELECT ts.owner, ts.table_name,
NVL(ts.table_size_gb,0) AS table_gb,
NVL(ls.lob_size_gb,0) AS lob_gb,
(NVL(ts.table_size_gb,0) + NVL(ls.lob_size_gb,0)) AS total_gb
FROM table_size ts
LEFT JOIN lob_size ls ON ts.owner = ls.owner AND ts.table_name = ls.table_name
WHERE ts.owner='DBDOCS_USER' -- REPLACE WITH ACTUAL SCHEMA NAME
ORDER BY total_gb DESC;
Sample Query Output (DBDOCS Schema)
Below is an example of the query output for a sample DBDOCS schema:
OWNER TABLE_NAME TABLE_GB LOB_GB TOTAL_GB
--------------------------------------------------------
DBDOCS_USER CUSTOMER_DOCS 1.5 200.2 201.7
DBDOCS_USER ORDERS 0.8 75.4 76.2
DBDOCS_USER TRANSACTIONS 2.2 120.3 122.5
The above output shows how LOB segments contribute significantly to total storage size.
Understanding Query Results
The output of the above query provides insights into how LOB data impacts storage. Key columns include:
- TABLE_GB: Storage used by the table without LOBs
- LOB_GB: Storage consumed by LOB segments
- TOTAL_GB: Combined total storage footprint
Optimization Strategies
1. Using SecureFiles for LOB Storage
SecureFiles offer compression, deduplication, and encryption for LOBs.
CREATE TABLE customer_docs (
doc_id NUMBER,
file_content BLOB
) LOB(file_content) STORE AS SECUREFILE (COMPRESS HIGH);
2. Partitioning LOB Data
Partitioning improves performance by allowing efficient access to LOB data.
CREATE TABLE sensor_data (
reading_date DATE,
measurement BLOB
) PARTITION BY RANGE (reading_date) INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
LOB(measurement) STORE AS SECUREFILE;
Common Challenges & Solutions
| Issue | Solution |
|---|
| LOB Fragmentation | Use ALTER TABLE ... MOVE LOB to reorganize |
| Slow Retrieval | Enable SecureFile caching for frequently accessed LOBs |
| Space Reclamation | Use DBMS_LOB.SHRINKSPACE to free unused space |
By implementing the optimization strategies outlined, organizations can ensure efficient Oracle database storage management, reducing costs and improving query performance.