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