MANAGE LOB OBJECTS IN ORACLE

 

Manage LOB Segment in Oracle Database

Leave a reply

Check the table having LOB segment in the Oracle Database

set pagesize 200
set lines 200
set long 999
col owner for a15
col table_name for a20
col column_name for a21
select a.owner,a.table_name,a.column_name, data_type
from dba_lobs a, dba_tab_columns b
where a.column_name=b.column_name
and a.table_name = b.table_name
and a.owner = b.owner
and b.owner not in ('SYS');

Check Space usage by LOB column

SELECT s.bytes FROM dba_segments s JOIN dba_lobs l USING (owner, segment_name)
WHERE l.table_name = '&table_name';

Check actual space used by LOB

SELECT nvl((sum(dbms_lob.getlength( &lob_column ))),0) AS bytes FROM &table_name;

Move LOB segment to another tablespace

-- Find the lob segment details
select table_name,COLUMN_NAME,SEGMENT_NAME,TABLESPACE_NAME from dba_lobs where
OWNER='HR';
-- Move to new tablespace
alter table HR.DOCUMENT move lob (TEACHER) store as SYS_LOB0000100201C00044$$ ( tablespace USERS);