ORACLE: view the objects contained in the data file. oracle: view Data Objects

ORACLE: view the objects contained in the data file. oracle: view Data Objects

In the previous article on ORACLE tablespace object viewing, I introduced how to query the database objects in a tablespace. Can we check whether a data file contains those database objects? As shown below

SELECT  E.SEGMENT_TYPE          AS SEGMENT_TYPE

      , E.SEGMENT_NAME          AS SEGMENT_NAME

      , F.FILE_NAME             AS FILE_NAME

      , SUM(E.BYTES)/1024/1024  AS SEGMENT_SIZE

FROM DBA_EXTENTS E

INNER JOIN DBA_DATA_FILES F ON E.FILE_ID= F.FILE_ID

WHERE F.FILE_ID =109

GROUP BY  E.SEGMENT_TYPE,E.SEGMENT_NAME,F.FILE_NAME

ORDER BY 4 DESC;

 

SELECT  E.SEGMENT_TYPE          AS SEGMENT_TYPE

      , E.SEGMENT_NAME          AS SEGMENT_NAME

      , F.FILE_NAME             AS FILE_NAME

      , SUM(E.BYTES)/1024/1024  AS SEGMENT_SIZE

FROM DBA_EXTENTS E

INNER JOIN DBA_DATA_FILES F ON E.FILE_ID= F.FILE_ID

WHERE F.FILE_NAME =&FILE_NAME

GROUP BY  E.SEGMENT_TYPE,E.SEGMENT_NAME,F.FILE_NAME

ORDER BY 4 DESC;

The incorrect syntax is as follows. the HEADER_FILE of DBA_SEGMENTS indicates the ID of the file containing the segment header, mainly because it only contains the file where the segment object's segment header is located.

SELECT ds.OWNER                  AS OWNER

      ,df.FILE_NAME              AS FILE_NAME

      ,ds.SEGMENT_NAME           AS SEGMENT_NAME

      ,ds.SEGMENT_TYPE           AS SEGMENT_TYPE

      ,SUM(ds.BYTES)/1024/1024   AS SEGMENT_SIZE

FROM DBA_SEGMENTS ds 

LEFT JOIN DBA_DATA_FILES df ON ds.HEADER_FILE = df.FILE_ID

WHERE ds.TABLESPACE_NAME=&TABLESPACE_NAME

  AND df.FILE_ID=&FILE_ID

GROUP BY OWNER,FILE_NAME,SEGMENT_NAME,SEGMENT_TYPE

ORDER BY 4;