Number of database files

Hi,

I want to check (Oracle 8.1.7.4 and higher) if the current number of data files is nearing the number specified by the db_files parameter. To achieve this I have devised the following select:

select 'Number of datafiles ('||a.records_used||') nears db_files parameter ('||b.value||') : '

       ||(b.value-a.records_used) message from V$CONTROLFILE_RECORD_SECTION a,

     v$parameter b

where a.type = 'DATAFILE'
  and b.name = 'db_files'
  and b.value - a.records_used <= 10

To my understanding increasing the db_files parameter will automatically increase the maxdatafiles value in the control file. The purpose of above check is to anticipate when to increase db_files and plan a database bounce and avoid ORA-00059 : maximum number of DB_FILES exceeded errors.

thanks in advance.

Regards,

Ivan Received on Sun Mar 09 2008 - 06:45:52 CDT