patternMinor
LOB Segment is listed in DBA_SEGMENTS, but has not corresponding row in DBA_LOBS
Viewed 0 times
butdba_lobssegmenthasloblistedrowdba_segmentscorrespondingnot
Problem
I am trying to find the column (or table) that belongs to a rather big LOB segment:
returns:
However when I try to find the corresponding table:
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
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:
To find the table the orphaned LOB column belongs to:
To remove the column and LOB segment:
Based on: Orphaned Lobs after marking LOB column unused (Doc ID 461651.1)
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 selectedBased 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 selectedContext
StackExchange Database Administrators Q#157472, answer score: 6
Revisions (0)
No revisions yet.