主页 / dba / 问题 / 157472
Accepted
a_horse_with_no_name
Asked: 2016-12-08 02:56:43 +0800 CST

LOB 段在 DBA_SEGMENTS 中列出，但在 DBA_LOBS 中没有对应的行

  • 772

我试图找到属于一个相当大的 LOB 段的列（或表）：

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

返回：

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

但是，当我尝试找到相应的表时：

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

不返回任何行。

是否还有其他地方存储有关该 LOB 段的信息？

这是运行 RHEL 6.8 的 Oracle 11.2.0.4 (RAC)

oracle

1 个回答

  1. Best Answer

    一个可能的原因是将 LOB 列设置为不可用：

    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$$

    要查找孤立 LOB 列所属的表：

    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$

    要删除列和 LOB 段：

    SQL> alter table t1 drop unused columns;

Table altered.

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

no rows selected

    基于：标记 LOB 列未使用后的孤立 Lobs（文档 ID 461651.1）

    • 6

相关问题