【Oracle】关于LOBs

Master Note - RDBMS Large Objects (LOBs) (Doc ID 1268771.1)

如何评估LOBs占用空间大小？
（1）

col segment_name format a30
set pagesize 10000
select a.segment_name, a.segment_type,
sum(a.bytes)/1024/1024 Bytes_MB
from dba_segments a, user_lobs b
where a.segment_name = b.segment_name
group by a.segment_name, a.segment_type;

（2）

set pagesize 10000
col tablespace_name format a10
col TS_Name format a10
col Col format a10
col segment_type format a12
select a.tablespace_name TS_Name, b.table_name,
b.column_name Col, a.segment_type,
sum(a.bytes)/1024/1024 Bytes_MB
from dba_segments a, user_lobs b
where a.segment_name = b.segment_name
group by a.tablespace_name, b.table_name,
b.column_name, a.segment_type;

（3）

col segment_name format a30
set pagesize 10000
select a.owner, a.segment_name, a.segment_type,
sum(a.bytes)/1024/1024 Bytes_MB
from dba_segments a, all_lobs b
where a.segment_name = b.segment_name
group by a.owner, a.segment_name, a.segment_type;

参考：

--- How-To / Best Practices ---

There are good references for popular topics such as:

Note 66046.1 - Example SQL Demonstrating use of LOBs in Oracle
Note 66431.1 - LOBS - Storage, Redo and Performance Issues
Note 162345.1 - LOBS - Storage, Read-consistency and Rollback
Note 268476.1 - LOB Performance Guideline
Note 468160.1 - DB 11.1: Introduction to SecureFiles

-- Generic Diagnostics ---

Note 198160.1 - Summary Note Index for BasicFiles (LOB's/BLOB's/CLOB's/NCLOB's,BFILES) and SecureFiles
Note 846562.1 - Troubleshooting Guide (TSG) - Large Objects (LOBs)

** LOB Maintenance:
Note 1453350.1 How to Determine what storage is used in a LOBSEGMENT and should it be shrunk / reorganized?
Note 369883.1 - How to Calculate Space Used by LOB Segments in the Database
Note 464558.1 - How to export/import data with LOB type from one user/schema to another?
Note 453186.1 - How to move LOB Data to Another Tablespace when the Table also contains a LONG column
Note 130814.1 - How to move LOB Data to Another Tablespace
Note 761388.1 - How To Move Or Rebuild A Lob Partition
Note 871203.1 - How to move a LOB index to another tablespace ?
Note 802059.1 - Shrink LOB Segment On Partitioned Table
Note 1151414.1 - How to Remove Lob Indexes
Note 339851.1 - Lob Index Stored In System Tablespace

** LOB Space issues
Note 48851.1 - OERR: ORA 1691 unable to extend lob segment %s.%s by %s in tablespace %s
Note 1014040.102 - How to Diagnose and Resolve ORA-1693: max # extents (%s) reached in lob segment %s.%s
Note 802897.1 - How to Release the Temp LOB Space and Avoid Hitting ORA-1652
Note 750209.1 - Temporary LOB space not released after commit: ora-1652 being hit

** LOB Partitioning issues
Note 71106.1 - OERR: ORA-22877 invalid option specified for a HASH partition or subpartition

** LOB Performance:
Note 175825.1 - Slow Performance importing LOB columns
Note 740075.1 - 'enq HW - contention' For Busy LOB Segment
Note 837883.1 - LOB HWM CONTENTION :Using AWR Reports to Identify the Problem; Confirm and Verify the Fix
Note 268476.1 - LOB Performance Guideline
Note 66431.1 - LOBS - Storage, Redo and Performance Issues
Note 978045.1 - POOR PERFORMANCE WITH LOB INSERTS

** ORA-1555 on LOBs / LOB Corruption:
Note 846079.1 - LOBs and ORA-01555 troubleshooting
Note 293515.1 - ORA-1578 ORA-26040 in a LOB segment - Script to solve the errors
Note 1206814.1 - Logical corruption of LOB data during recovery.
Note 253131.1 - Concurrent Writes May Corrupt LOB Segment When Using Auto Segment Space Management (ORA-1555)
Note 452341.1 - ORA-01555 And Other Errors while Exporting Table With LOBs, How To Detect Lob Corruption.

** SecureFile:
Note 861344.1 - 11g Advanced Compression - How to Check Space Occupied by LOB Compression