SELECT table_name, column_name, segment_name, a.bytes
FROM dba_segments a JOIN dba_lobs b
USING (owner, segment_name)
WHERE b.table_name = 'NITI_TEST_LOB';
TABLE_NAME COLUMN_NAME SEGMENT_NAME BYTES
------------ -------------- ---------------------------- ----------
NITI_TEST_LOB IMAGE SYS_LOB0000148988C00003$$ 50,331,648
SELECT NVL((SUM(DBMS_LOB.GETLENGTH(image))),0) AS BYTES
FROM niti_test_lob;
BYTES
----------
16,706,000
Use following command to manually shrink the LOB segment to free up the space immediately:
ALTER TABLE niti_test_lob MODIFY LOB (image) (SHRINK SPACE);
Table altered.
SELECT table_name, column_name, segment_name, a.bytes
FROM dba_segments a JOIN dba_lobs b
USING (owner, segment_name)
WHERE b.table_name = 'NITI_TEST_LOB';
TABLE_NAME COLUMN_NAME SEGMENT_NAME BYTES
------------ -------------- ---------------------------- ----------
NITI_TEST_LOB IMAGE SYS_LOB0000148988C00003$$ 65,536
SELECT NVL((SUM(DBMS_LOB.GETLENGTH(image))),0) AS BYTES
FROM niti_test_lob;
BYTES
----------
0
The alter table <table_name> shrink space statement was introduced in Oracle 10g R1.
The ability to extend the SHRINK SPACE command to LOBs was introduced in Oracle 10g R2
No comments:
Post a Comment