9iR2 Datafile Queries

Found that I had some 9iR2 tablespaces which the space was not being evenly distributed across all of the datafiles. Some datafiles had over 50% space utilized and others had < 20% utilized. Seems to be related to the use of uniform extents. However, I haven't proved it yet. Strange how the data is not evenly distributed across the datafiles. Used 9i OEM to catch it. Didn't have any SQL but found the following. This is for those that prefer not to use the point and click GUI approach.

select substr(a.file_name,1,50) as file_name,
substr(a.file_id,1,4) as file_id,
a.bytes as allocated,
nvl(b.free,0) as free,
a.bytes-nvl(b.free,0) as used
from dba_data_files a,
( select file_id, sum(bytes) free
from dba_free_space
group by file_id ) b
where a.file_id = b.file_id (+)
and a.tablespace_name = 'TABLESPACENAME'
order by 1;

Here is a query to show the contents of a particular datafile. The input is the datafile path and name:

REM ------------------------------------------------------------------------
REM REQUIREMENTS:
REM SELECT on DBA_EXTENTS and DBA_DATA_FILES
REM ------------------------------------------------------------------------
REM AUTHOR:
REM Cary Millsap, Oracle Corporation
REM Copyright (c) 1991 by Oracle Corporation
REM ------------------------------------------------------------------------
REM PURPOSE:
REM The following script prints a record of each ORACLE database
REM segment (table, index, cluster, rollback segment, temporary
REM segment or cache segment) physically stored in a datafile.
REM This information is useful to determine the contents of a
REM database file identified as particularly hot in a SQL*DBA
REM monitor of ORACLE file I/O.
REM ------------------------------------------------------------------------
REM DISCLAIMER:
REM This script is provided for educational purposes only. It is NOT
REM supported by Oracle World Wide Technical Support.
REM The script has been tested and appears to work as intended.
REM You should always run new scripts on a test instance initially.
REM
REM ------------------------------------------------------------------------
REM Main text of script follows:
set verify off
define fname = &&1

ttitle left 'Database Segments in File &fname' skip 2

col stype format a12 justify c heading 'Segment Type'
col sname format a66 justify c heading 'Segment Name'

break on stype skip 1

select
segment_type stype,
owner'.'segment_name sname
from
dba_extents ext,
dba_data_files fil
where
fil.file_name = '&fname'
and
ext.file_id = fil.file_id
order by
segment_type,
segment_name
/

undef fname
set verify on