根据LOB字段名查询LOB所在的表名

最新推荐文章于 2022-12-14 10:32:42 发布

灰帽DBA

最新推荐文章于 2022-12-14 10:32:42 发布

阅读量4k

点赞数

分类专栏： Oracle 日记文章标签：数据库 oracle

本文链接：https://blog.csdn.net/Evils798/article/details/115358704

版权

Oracle 日记专栏收录该内容

66 篇文章 1 订阅

订阅专栏

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

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