dfmon.sql

This script can be called from the orasyscheck script. It produces a list of operating system files to Oracle tablespaces and a list of which (regular) users have which objects in which tablespaces.


REM
REM Datafile monitoring
REM

set linesize 132
set pagesize 1000

column filename format a70 heading "OS File"
column tsname format a30 heading "Tablespace"
column owner format a12 heading "Owner"
column tabname format a30 heading "Table Name"
column stat format a10 heading "Status"

prompt
prompt Tablespaces to O/S file mappings
prompt ================================
prompt
select file_name filename , tablespace_name tsname, status stat
from dba_data_files
order by tablespace_name;

prompt
prompt USER Tables by Tablespace
prompt =========================
prompt

break on OWNER skip 1

select owner owner, table_name tabname, tablespace_name tsname
from dba_tables
where owner not in ('SYS' , 'SYSTEM' , 'OLAPSYS' , 'DBSNMP' , 'SCOTT' , 
                    'CSMIG' , 'PERFSTAT' , 'OUTLN' , 'WMSYS' , 'ORDSYS' ,
                    'ORDPLUGINS' , 'MDSYS' , 'RMAN' , 'CTXSYS' , 'XDB' ,
                    'ANONYMOUS' , 'WKSYS' , 'WKPROXY' , 'ODM' , 'ODM_MTR',
                    'OEM_REP')
order by owner;

quit;