Re: Unused datafiles

From: Shayne Bell <shayne.bell_at_mitchell-energy.com>
Date: Fri, 19 Nov 1999 21:24:18 GMT
Message-ID: <01bf32ce$2e5d2010$bb28fa80@sbell>

Hi,

The following script might give you i/o percentages that are being used by different datafiles.

Later !!!

Suresh Bhat
Oracleguru
www.oracleguru.net

set doc off pause off wrap on  

REM

REM     Author      : Suresh N. Bhat
REM     Date        : 09/09/1999
REM     File Name   : datafile_io_weights.sql
REM     Usage       : On sqlplus prompt enter:
REM                         @datafile_io_weights
REM
REM     Description : Gives statistics on DataFile access io weights.
                                The TOTAL_IO of the most accessed data file
is used for
                                calculating percentages.
 

set pagesize 58 feedback off verify off termout off trimspool on  

column today        new_value today     noprint
column time         new_value time      noprint
column global_name  new_value global_name noprint
 

select replace(global_name,'.WORLD') global_name   from global_name
/  

select to_char(sysdate, 'DD-MON-YYYY') today,

         to_char(sysdate, 'HH:MI:SS AM') time   from dual;  

ttitle today center 'Database File IO Weights' -

   right 'Page ' format 990 sql.pno skip 1 -    time center &global_name skip 1 -
   datafile_io_weights.sql skip 2  

set termout on  

column total_io         format 999999999
column weight           format 999.99
column file_name        format a23
 

break on drive skip 2  

compute sum of weight on drive  

spool $HOME/rep/datafile_io_weights_&global_name..lst  

select substr(df.name, 1,3) drive,

         df.name        file_name,
         fs.phyblkrd    Block_read,
         fs.phyblkwrt   Block_written,
         fs.phyblkrd +
         fs.phyblkwrt   total_io,
         100*(fs.phyblkrd + fs.phyblkwrt)/maxio weight
  from   v$filestat fs,
         v$datafile df,
        (select   max(phyblkrd + phyblkwrt) maxio
           from   v$filestat
        )

 where df.file# = fs.file#
order by weight desc
/  

prompt
prompt

prompt.                      ********** END OF REPORT *********
 

spool off  

exit

Frederic DEBRUS <frederic.debrus_at_ces-cdr.be> wrote in article <01bf32a3$bdbb73e0$3e35a99e_at_gal159a>...
> Hello there,
>
> I have a tablespace A composed of 6 datafiles A1 to A6
> but I only use 5% of this tablespace is used so I would like to drop some
> datafiles
>
> Is there a way other then export, drop tablespace, recreate, and import
to
> do it ?
> also how to report which datafiles is used and which is not....
>
> Some guideline will be appreciated
>
> Many thanks,
> FreD
>
Received on Fri Nov 19 1999 - 15:24:18 CST