ORACLE DBA_OBJECTS視圖中OBJECT_TYPE為LOB的對象查看

在ORACLE資料庫中，DBA_OBJECTS視圖中OBJECT_TYPE為LOB的對象是什麼東西呢？其實OBJECT_TYPE為LOB就是大對象（LOB），它指那些用來存儲大量數據的資料庫欄位。下麵演示一下:

CREATE TABLE TEST

(    

    "ID"   NUMBER, 

    "NAME" NVARCHAR2(10), 

    "TEST" CLOB

) 

INSERT INTO TEST

SELECT 1000, 'kerry', 'ttdfdf' FROM DUAL;

COMMIT;

SELECT * FROM DBA_LOBS WHERE OWNER='SYSTEM' AND TABLE_NAME='TEST'

SELECT * FROM DBA_OBJECTS

WHERE OBJECT_TYPE ='LOB'

AND OWNER         ='SYSTEM'

AND OBJECT_NAME='SYS_LOB0000585336C00003$$';

如果你想知道這些LOB對象涉及那些對象，可以使用下麵SQL查看詳細信息：

SELECT O.OWNER,O.OBJECT_ID,  O.OBJECT_NAME, O.OBJECT_TYPE,

       L.TABLE_NAME, L.COLUMN_NAME

FROM DBA_OBJECTS O

INNER JOIN DBA_LOBS L ON L.SEGMENT_NAME=O.OBJECT_NAME AND O.OWNER =L.OWNER

ORDER BY 1