Oracle数据库中常见有LOB字段，通过以下SQL可以查到LOB字段属于数据库的哪张表

Posted 2023-10-05 by wukaiqiang; tagged with none

SQL> select *

   from (select owner,
                segment_name || '~' || partition_name segment_name,
                segment_type,
                bytes / 1024 / 1024 / 1024 size_G
           from dba_segments
          ORDER BY BLOCKS desc)
  where rownum < 11;

OWNER SEGMENT_NAME SEGMENT_TYPE SIZE_G

HWICHDB3 SYS_LOB0001459936C00001$$~ LOBSEGMENT 199.016601
HWICHDB3 C_CLIENT_VIP_MONTH~ TABLE 89.6533203
HWICHDB3 BW_INV_MAKE_APPLYITEM~ TABLE 63.1503906
HWICHDB3 EB_STOREPDT_ITEM~ TABLE 44.2568359
HWICHDB3 C_VOUCHERS_STORE~ TABLE 43.8935546
HWICHDB3 IDX_VOUCHERS_STORE_01~ INDEX 41.4667968
HWICHDB3 IDX_FA_STORAGE_FTP_91~ INDEX 36.8662109
HWICHDB3 IDX_FA_STORAGE_FTP_10~ INDEX 34.40625
HWICHDB3 CARDMAIN_MONTH~ TABLE 30.9667968
HWICHDB3 IDX_FA_STORAGE_FTP_90~ INDEX 30.3808593

10 rows selected

SQL> select e.owner, l.table_name, l.segment_name

    from dba_extents e, dba_lobs l
   where e.owner = l.owner
     and e.segment_name = l.segment_name
     and e.segment_type = 'LOBSEGMENT'
     and l.segment_name like 'SYS_LOB0001459936C00001$$';

OWNER TABLE_NAME SEGMENT_NAME

HWICHDB3 M_RETAIL_R_GETSTORAGE_C_LOG SYS_LOB0001459936C00001$$
HWICHDB3 M_RETAIL_R_GETSTORAGE_C_LOG SYS_LOB0001459936C00001$$
HWICHDB3 M_RETAIL_R_GETSTORAGE_C_LOG SYS_LOB0001459936C00001$$
HWICHDB3 M_RETAIL_R_GETSTORAGE_C_LOG SYS_LOB0001459936C00001$$
HWICHDB3 M_RETAIL_R_GETSTORAGE_C_LOG SYS_LOB0001459936C00001$$
HWICHDB3 M_RETAIL_R_GETSTORAGE_C_LOG SYS_LOB0001459936C00001$$
HWICHDB3 M_RETAIL_R_GETSTORAGE_C_LOG SYS_LOB0001459936C00001$$

select owner, table_name, column_name, segment_name, index_name
2 from dba_lobs
3 where table_name = 'M_RETAIL_R_GETSTORAGE_C_LOG';

OWNER TABLE_NAME COLUMN_NAME SEGMENT_NAME INDEX_NAME

HWICHDB3 M_RETAIL_R_GETSTORAGE_C_LOG TEXT SYS_LOB0001459936C00001$$ SYS_IL0001459936C00001$$
————————————————
版权声明：本文为CSDN博主「灰帽DBA」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/Evils798/article/details/115358704