Determine MAX DATAFILES value

there is a view called v$parameters under sys, try select the value from there.

This view gives the DB FILES parameter, I'm looking for MAX DATAFILES.
This is the physical number of datafiles that can be stored in the controlfile. DB FILES is the logical number of datafiles.

This information is stored in: V$CONTROLFILE_RECORD_SECTION. This view displays information about the controlfile record sections.

Gosse Adema

This works for Oracle 8 only ...

which version of Oracle are you looking for ?

This maximum value of MAXDATAFILES cannot exceed the Oracle7 system limit or any operating system limit. If you are not sure how to set this parameter, use a high number to avoid unnecessary limitation. The default value is operating system-specific.

This value is not stored in ORACLE v7.3.4.

# of database files|  system |  1022 or value of DB_FILES in INIT.ORA, or limited by value of MAXDATAFILES in CREATE DATABASE. Less on some operating systems.  

Regards,
 
  rwarsh

Adjusted points to 150

We have version 7.3.4
The value must be somewhere in Oracle; how does oracle know when I execute "backup controlfile to trace" ?
I'm intending to use this value for the following check: Compare the value for DB_FILES (logical) against the the MAXDATAFILES value and send a warning if < 3.

Thanks for your response.

  You can issue 'backup controlfile to trace' command, than read file and extract MAXDATAFILES value.

 rwarsh

but wfvdijk's Q says any other way without using "backup controlfile to trace".

How come you missed reading the Q this time rwarsh :-)

Regards,
Sudhi.

See sudhi's comment.

It doesn't make any sense to check the value for maxdatafiles more than once in your database. This value, given at controlfile creation, is used to reserve disk-space in the controlfile for datafile entries. This is a (very) static value because you usualy don't recreate your controlfile every day.

The value for db_files is used to reserve memory in the instance. It's the number of records for X$KCFIO (v$filestat). The value for db_files may exceed the value for maxdatafiles so selecting the number of records will not work.

It's not possible to query the X$- or V$-tables for the value of maxdatavalues in oracle 7. It was possible in 6 (do a downgrade) and it's possible in 8 (do an upgrade).

If you want to create a query to calculate the used% of the controlfile I suggest the following:

1. Create a bacup controlfile to trace (this one again).

2. Change the value for db_files to this value (or use maxdatafiles -5)

3. Create the query using db_files.

If you realy want to use the maxdatafiles create a function which:

1. Creates a backup controlfile to trace.

2. Uses utl-file to read the file.

3. Removes the tracefiles.

Tip:

Always create the controlfile with the maximum (or enough) value for maxdatafiles and set db_files to twice the number of datafiles (even more or equal to maxdatafiles in a 7*24 environments). This will leave enough room for creating additional files withouth stopping your database to increase value for db_files or recreating the controlfile.

I'll agree with your answer and give you the points on March 1st unless there's somebody else with a better answer.

I've been looking through most of the X$ and V$ tables and I believe there is no simple query answer. I'd like to know the answer but I think there isn't.

I'll double the current points (150 points at grade A) to the person who knows the answer to this question:

Select maxdatafiles from ...

(Oracle 7, without using  backup controlfile to trace or UTL-file)

Gosse Adema

 Gosse, please open your question. I do know the answer (maxdatafiles from select)

 regards
   rwarsh

Adjusted points to 200

An additional 100 points will be added after answering the question.

Wilfred

 Sorry for the delay, I had a really busy day today.

To identify MAXDATAFILES use below select:

select count(*) from x$kcffi;    

Regards,

  rwarsh

rwarsh, when i tried that query against an Personal Oracle 8 i got an error message that the view does not exist. Is it specific to a specific version ?

You have to connect as internal (SYSDBA). It works in ORACLE 7.3.4.

But doesn't work on Oracle 8. I just checked it da.

Adema mentioned, you can use  V$CONTROLFILE_RECORD_SECTION in v. 8.

Rwarsh,

Do I have to enable the parallel server option for this fixed table?

Gosse


SQL> set linesize 1000
SQL> set pagesize 1000
SQL> select * from x$kcffi;
select * from x$kcffi
              *
FOUT in regel 1:
ORA-00942: table or view does not exist

SQL> select *
  2    from v$fixed_table
  3   where name like 'X$KCF%';

NAME                           OBJECT_ID TYPE  TABLE_NUM
------------------------------ --------- ----- ---------
X$KCFIO                        4,295E+09 TABLE        78

SQL> select * from v$option;

PARAMETER                                                        VALUE
---------------------------------------------------------------- -------------
procedural                                                       TRUE
distributed                                                      TRUE
replication                                                      TRUE
parallel query                                                   FALSE
Parallel Server                                                  FALSE
Spatial Data                                                     FALSE

6 rijen zijn geselecteerd.

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle7 Server Release 7.3.4.2.0 - Production
PL/SQL Release 2.3.4.2.0 - Production
CORE Version 3.5.4.0.0 - Production
TNS for IBM/AIX RISC System/6000: Version 2.3.4.0.0 - Production
NLSRTL Version 3.2.4.0.0 - Production

Please, use this select:

select count(*) from x$kcfio;

Regards,
 rwarsh

This is the view for the number of I/O's on the datafiles. The number of records in this view is equal to db_files (even when db_files is larger than maxdatafiles).

Do you have a list of options which are installed for the x$kcffi-view?

  This select is ORACLE's recommended way to extract MAXDATAFILES.

 rwarsh

select count(*) from x$kcffi;

Is for Oracle 6 and not for Oracle 7.

Gosse

OK, let's summarize our answers:
- In Oracle 8 you can use V$CONTROLFILE_RECORD_SECTION.
- In Oracle 7 you can determine db_files with the view x$kcfio.
- View x$kcffi (which gave MAX_DATAFILES) is an obsolete view, this is an Oracle 6 view.

The 200 point question is still not answered.

Wilfred

What do you mean, "not answered"? You received exactly what you requested:

"I'll double the current points (150 points at grade A) to the person who knows the answer to this question:

Select maxdatafiles from ...

(Oracle 7, without using  backup controlfile to trace or UTL-file) "

- select count(*) from x$kcfio.

It is not db_files. It is your MAXDATAFILES count. You don't have to beleive me, just create sample databases with different MAXDATAFILE numbers and check.

rwarsh

x$kcfio is reflecting the number of DB_FILES, not MAX DATAFILES. I did the following:

MAX datafiles = 128
DB_FILES = 64
Oracle 7.3.4 on AIX

> select count(*) from x$kcfio = 64
> change db_files to 65
> restart
> select count(*) from x$kcfio = 65
> change db_files to 255 (!)
> restart
> select count(*) from x$kcfio = 255
> changed MAX DATAFILES in controlfile to 130
> restart
> select count(*) from x$kcfio = 255

As you can see, your answer is not correct.

Wilfred

see comments