Re: Oracle MAXDATAFILES

From: ddf <oratune_at_msn.com>
Date: Tue, 7 Jul 2009 05:54:45 -0700 (PDT)
Message-ID: <ffb1176a-44fd-4333-8c33-1fc443207edc_at_k19g2000yqn.googlegroups.com>

On Jul 7, 2:46 am, Ming伯 <l..._at_tech-trans.com> wrote:
> Hi,
>
>   How to determine MAXDATAFILES in a Oracle instance?  At the same
> time, how to change MAXDATAFILES?  Thx!
>
> Ming

Since Oracle 8 the parameter to use is db_files, as it takes precedence, therefore if db_files is set to 200 and MAXDATAFILES is set to 100 and you attempt to add a datafile the action will succeed as MAXDATAFILES will expand to the limit enforced by the db_files parameter. To see how db_files is set:

SQL> show parameter db_file

NAME                                 TYPE        VALUE
------------------------------------ -----------
------------------------------
db_file_multiblock_read_count        integer     128
db_file_name_convert                 string
db_files                             integer     200

SQL> Notice db_files is set to 200; executing an 'alter database backup controlfile to trace;' creates a script to recreate the control files and in that script we see, for the same database, that MAXDATAFILES is set to 100. Since that's less than the db_files parameter setting we can add datafiles in excess of the 100 set for MAXDATAFILES because that parameter will expand to the limit db_files provides (200).

The db_files parameter is not dynamic, so to change it one needs to:

alter system set db_files=500 scope=spfile;

and then restart the database.

David Fitzjarrell Received on Tue Jul 07 2009 - 07:54:45 CDT