You cannot change this parameter dynamically. You must restart your oracle instance in order this parameter takes effect.

Unfortunately that's a static parameter, so you can't change it without restarting the DB. See http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams049.htm#sthref205

This parameter is in the CONTROL FILE not in pfile or spfile.
You must recreate the control file.

This is the only way...for me :)

bye

You do not need to recreate control file. See below:

SQL> sho user
SYS
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Prod
PL/SQL Release 10.2.0.2.0 - Production
CORE 10.2.0.2.0 Production
TNS for 32-bit Windows: Version 10.2.0.2.0 - Production
NLSRTL Version 10.2.0.2.0 - Production

SQL> sho parameter db_files

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_files integer 200
SQL> alter system set db_files = 256 scope = spfile;

SQL> shutdown immediate
SQL> startup
SQL> sho parameter db_files

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_files integer 256

: maximum number of DB_FILES exceeded
i don't think here need to create control files.
bcoz OP is Ora-00059 err

we just need to increase this parameter
and startup our database

SQL> show parameter db_files



NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_files                             integer     6

SQL> create tablespace test

  2  datafile 'd:\test01.dbf' size 1m;

create tablespace test

*

ERROR at line 1:

ORA-00059: maximum number of DB_FILES exceeded





SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> create pfile from spfile;



File created.



SQL> --change db_files parameter in init<db>.ora file

SQL> create spfile from pfile;



File created.



SQL> startup

ORACLE instance started.



Total System Global Area  171966464 bytes

Fixed Size                   787988 bytes

Variable Size             145488364 bytes

Database Buffers           25165824 bytes

Redo Buffers                 524288 bytes

Database mounted.

Database opened.

SQL> show parameter db_files



NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_files                             integer     200

SQL>

Note : @Paul . sir if this problem occur in 24x7 database enviorment then what is solution.
plz. reply
thanx

Sorry for interrupting, but in case of a production system, you would have already set DB_FILES to a calculated value (depending on size of database, expected growth etc). Even to some extent max possible value.

Considering the fact that this parameter is governed by the MAXDATAFILES used in the database creation, if set low, you need to recreate the controlfile and restart the database ;)

Thanks

if this problem occur in 24x7 database enviorment then what is solution.

No solution. You calculate it at DB creation, or you restart the DB.

Considering the fact that this parameter is governed
by the MAXDATAFILES used in the database creation, if
set low, you need to recreate the controlfile and
restart the database ;)

You don't need to recreate the controlfile. The following is from an Oracle article :

In Oracle8i and higher, if the MAXDATAFILES limit is reached the controlfile will expand automatically.

In Oracle 8i and higher, when you issue CREATE DATABASE or CREATE CONTROLFILE statements, the MAXDATAFILES parameter specifies an initial size of the datafile portion of the control file. Later, if you add a file whose number exceeds MAXDATAFILES but is less than or equal to the value specified by the DB_FILES initialization parameter, the control file automatically expands to allow the datafile portion to accommodate more files.

We can have DB_FILES set to more than the value of MAXDATAFILES.

Thanks at all!

i dont know this feature, but i know only maxdatafiles paramater.

Bye

Hi Paul,

My statement was based on Oracle reference document of Oracle 8.1.7 only..

http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76961/ch138.htm#58406

>>
DB_FILES
Parameter type: Integer

Parameter class: Static

Default value: Operating system dependent

Range of values: Minimum: the current actual number of datafiles in the database

Maximum: the value that was specified in the MAXDATAFILES clause the last time CREATE DATABASE or CREATE CONTROLFILE was executed
>>

I actually have never faced this error as I have configured my databases with a MAX possible value on my system for MAXDATAFILES, that again based on above documentation only.

I guess Oracle document is contradicting in case of DB_FILES and MAXDATAFILES documentation.

Regards

Satish

Hi Satish,

Nowadays is a bit dangerous to rely on 8.1.6 documentation...:-)

I don't have an 8.1.7.4 on hands at the moment, but probably is was already different, and surely is different 9i, and 10g, from which comes the link I posted in my first post.

Yes Paul...

The documentation is definitely different and is changing. I just referred the 9i and 10GR1 documents that were with me and sure is different.

I have some databases running on Oracle 8.1.7 (still)... so I will stick with Oracle 8i documentation for these databases at least.

Anyways, I will keep myself updated now.

Thanks.