HiveBrain v1.2.0
Get Started
← Back to all entries
patternMinor

LOB Segment is listed in DBA_SEGMENTS, but has not corresponding row in DBA_LOBS

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
butdba_lobssegmenthasloblistedrowdba_segmentscorrespondingnot

Problem

I am trying to find the column (or table) that belongs to a rather big LOB segment:

select segment_name, segment_type, bytes 
from dba_segments 
where segment_name = 'SYS_LOB0000103936C00014$';


returns:

SEGMENT_NAME | SEGMENT_TYPE | BYTES
--------------------------+--------------+-------------
SYS_LOB0000103936C00014$$ | LOBSEGMENT | 422877069312


However when I try to find the corresponding table:

select table_name, column_name, segment_name, tablespace_name, index_name 
from dba_lobs 
where segment_name = 'SYS_LOB0000103936C00014$';


No rows are returned.

Is there any other place where the information about that LOB segment is stored?

This is an Oracle 11.2.0.4 (RAC) running RHEL 6.8

Solution

One possible reason is setting a LOB column unusable:

SQL> create table t1 (c1 number, c2 clob);

Table created.

SQL> insert into t1 values (1, 'A');

1 row created.

SQL> commit;

Commit complete.

SQL> select segment_name from dba_lobs where table_name = 'T1' and column_name = 'C2';

SEGMENT_NAME
------------------------------
SYS_LOB0000015673C00002$

SQL> select segment_name from dba_segments where segment_name = 'SYS_LOB0000015673C00002$';

SEGMENT_NAME
--------------------------------------------------------------------------------
SYS_LOB0000015673C00002$

SQL> alter table t1 set unused column c2;

Table altered.

SQL> select  table_name, column_name from dba_lobs where segment_name = 'SYS_LOB0000015673C00002$';

no rows selected

SQL> select segment_name from dba_segments where segment_name = 'SYS_LOB0000015673C00002$';

SEGMENT_NAME
--------------------------------------------------------------------------------
SYS_LOB0000015673C00002$


To find the table the orphaned LOB column belongs to:

select u.name, o.name TABLENAME, decode(bitand(c.property, 1), 1, ac.name, c.name) as column_name
from sys.obj$ o, sys.col$ c, sys.attrcol$ ac,sys.lob$ l,sys.obj$ lo,sys.obj$ io,
sys.user$ u,sys.ts$ ts
where o.owner# = u.user#
and o.obj# = c.obj#
and c.obj# = l.obj# and c.intcol# = l.intcol#
and l.lobj# = lo.obj# and l.ind# = io.obj# and l.ts# = ts.ts# and c.obj# =
ac.obj#(+)
and c.intcol# = ac.intcol#(+) and lo.name ='SYS_LOB0000015673C00002$';

NAME                           TABLENAME                      COLUMN_NAME
------------------------------ ------------------------------ --------------------------------
BP                             T1                             SYS_C00002_16120712:10:58$


To remove the column and LOB segment:

SQL> alter table t1 drop unused columns;

Table altered.

SQL> select segment_name from dba_segments where segment_name = 'SYS_LOB0000015673C00002$';

no rows selected


Based on: Orphaned Lobs after marking LOB column unused (Doc ID 461651.1)

Code Snippets

SQL> create table t1 (c1 number, c2 clob);

Table created.

SQL> insert into t1 values (1, 'A');

1 row created.

SQL> commit;

Commit complete.

SQL> select segment_name from dba_lobs where table_name = 'T1' and column_name = 'C2';

SEGMENT_NAME
------------------------------
SYS_LOB0000015673C00002$$

SQL> select segment_name from dba_segments where segment_name = 'SYS_LOB0000015673C00002$$';

SEGMENT_NAME
--------------------------------------------------------------------------------
SYS_LOB0000015673C00002$$

SQL> alter table t1 set unused column c2;

Table altered.

SQL> select  table_name, column_name from dba_lobs where segment_name = 'SYS_LOB0000015673C00002$$';

no rows selected

SQL> select segment_name from dba_segments where segment_name = 'SYS_LOB0000015673C00002$$';

SEGMENT_NAME
--------------------------------------------------------------------------------
SYS_LOB0000015673C00002$$
select u.name, o.name TABLENAME, decode(bitand(c.property, 1), 1, ac.name, c.name) as column_name
from sys.obj$ o, sys.col$ c, sys.attrcol$ ac,sys.lob$ l,sys.obj$ lo,sys.obj$ io,
sys.user$ u,sys.ts$ ts
where o.owner# = u.user#
and o.obj# = c.obj#
and c.obj# = l.obj# and c.intcol# = l.intcol#
and l.lobj# = lo.obj# and l.ind# = io.obj# and l.ts# = ts.ts# and c.obj# =
ac.obj#(+)
and c.intcol# = ac.intcol#(+) and lo.name ='SYS_LOB0000015673C00002$$';

NAME                           TABLENAME                      COLUMN_NAME
------------------------------ ------------------------------ --------------------------------
BP                             T1                             SYS_C00002_16120712:10:58$
SQL> alter table t1 drop unused columns;

Table altered.

SQL> select segment_name from dba_segments where segment_name = 'SYS_LOB0000015673C00002$$';

no rows selected

Context

StackExchange Database Administrators Q#157472, answer score: 6

Revisions (0)

No revisions yet.