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;