Check the list of object present in Oracle datafile

List the object present in Oracle Datafiles

SELECT start_block, end_block, 
ROUND(((end_block-start_block) * (SELECT VALUE FROM v$parameter WHERE NAME = 'db_block_size'))/(1024*1024),2) MB_SIZE, 
owner, segment_name, partition_name, segment_type 
FROM (SELECT file_id, block_id START_BLOCK, block_id + blocks - 1 end_block, owner, segment_name, partition_name, segment_type 
FROM dba_extents 
WHERE tablespace_name = '&TSNAME' 
UNION ALL SELECT file_id, block_id, (block_id + blocks - 1) end_block, 
'free' owner, NULL segment_name, NULL partition_name, NULL segment_type 
FROM dba_free_space 
WHERE tablespace_name = '&TSNAME' 
ORDER BY 1,2) 
WHERE file_id = &FILEID 
ORDER BY end_block ASC;
This entry was posted in Oracle on by .

About SandeepSingh DBA

Hi, I am working in IT industry with having more than 10 year of experience, worked as an Oracle DBA with a Company and handling different databases like Oracle, SQL Server , DB2 etc Worked as a Development and Database Administrator.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.