How about the other MAX* create database settings?
Richard, March 24, 2006 - 12:34 pm UTC
Hi Tom.
I can see from the V$CONTROLFILE_RECORD_SECTION view how to get MAXDATAFILES, MAXLOGFILES, and MAXLOGHISTORY. However, I haven't been able to identify where MAXLOGMEMBERS and MAXINSTANCES are stored. Can these values used to initially create the database be determined through a SQL query as easily as the other three?
March 24, 2006 - 3:51 pm UTC
maxinstances THREAD INSTANCE NAME MAPPING
not sure where/if maxlogmembers is externally visible.
Michel Cadot, March 24, 2006 - 5:27 pm UTC
SQL> Select '--> MAXDATAFILES ' || records_total
2 from v$controlfile_record_section where type = 'DATAFILE';
--> MAXDATAFILES 256
SQL> Select '--> MAXLOGHISTORY ' || records_total
2 from v$controlfile_record_section where type = 'LOG HISTORY';
--> MAXLOGHISTORY 584
SQL> Select '--> MAXINSTANCES ' || records_total
2 from v$controlfile_record_section where type = 'DATABASE';
--> MAXINSTANCES 1
SQL> Select '--> MAXLOGFILES ' || records_total
2 from v$controlfile_record_section where type = 'REDO LOG';
--> MAXLOGFILES 255
SQL> Select '--> MAXLOGMEMBERS ' || dimlm from x$kccdi;
--> MAXLOGMEMBERS 5
db_files
aliyar, July 16, 2010 - 9:51 pm UTC
Dear Tom ,
Database : 10g
in our Production database maxdatafiles and db_files are having same value say 200 ..
1 ) if increase only db_files, will maxdatafiles from controlfile will dynamically increase its values... or do we need to first re-create controlfile with new value and then chnage value for db_files
2 ) db_files = total number of datafiles can exist .. that means including temp files also or only datafiles considered here
appreciate your help
Thanks
aliyar
July 19, 2010 - 1:51 pm UTC
you need to recreate controlfiles to exceed maxdatafiles
db_files is about datafiles, not tempfiles
MAXDATAFILES expands automatically
Harel Safra, July 20, 2010 - 12:50 am UTC
"you need to recreate controlfiles to exceed maxdatafiles"
That's not true since 8i (
http://download.oracle.com/docs/cd/A87860_01/doc/index.htm ):
MAXDATAFILES integer
Specify the initial sizing of the datafiles section of the control file at CREATE DATABASE or CREATE CONTROLFILE time. An attempt to add a file whose number is greater than MAXDATAFILES, but less than or equal to DB_FILES, causes the Oracle control file to expand automatically so that the datafiles section can accommodate more files.
July 23, 2010 - 6:51 am UTC
too many things to remember sometimes, thanks much!