Solved

Determine MAX DATAFILES value

Posted on 2000-02-18
32
1,946 Views
Last Modified: 2012-05-05
Does anybody know how to determine the MAX DATAFILE value in a query (WITHOUT using "backup controlfile to trace").
Note: I'm looking for the MAX DATAFILE value, not the DB FILE value.
It was possible for Oracle 6.x (by using a x$ table), but I can't find this in Oracle 7
0
Comment
Question by:wfvdijk
  • 11
  • 9
  • 7
  • +2
32 Comments
 
LVL 4

Expert Comment

by:urim
ID: 2534821
there is a view called v$parameters under sys, try select the value from there.
0
 

Author Comment

by:wfvdijk
ID: 2534867
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.
0
 

Expert Comment

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

Gosse Adema
0
 

Author Comment

by:wfvdijk
ID: 2534957
This works for Oracle 8 only ...
0
 
LVL 4

Expert Comment

by:sudhi022299
ID: 2536109
which version of Oracle are you looking for ?
0
 
LVL 3

Expert Comment

by:rwarsh
ID: 2536605

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

0
 

Author Comment

by:wfvdijk
ID: 2540600
Adjusted points to 150
0
 

Author Comment

by:wfvdijk
ID: 2540601
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.
0
 
LVL 3

Expert Comment

by:rwarsh
ID: 2541286
  You can issue 'backup controlfile to trace' command, than read file and extract MAXDATAFILES value.

 rwarsh
0
 
LVL 4

Expert Comment

by:sudhi022299
ID: 2541320
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.
0
 

Author Comment

by:wfvdijk
ID: 2541520
See sudhi's comment.
0
 

Expert Comment

by:adema
ID: 2541706
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.






0
 

Author Comment

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

Expert Comment

by:adema
ID: 2541864
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
0
 
LVL 3

Expert Comment

by:rwarsh
ID: 2542900
 Gosse, please open your question. I do know the answer (maxdatafiles from select)

 regards
   rwarsh
0
 

Author Comment

by:wfvdijk
ID: 2545233
Adjusted points to 200
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:wfvdijk
ID: 2545234
An additional 100 points will be added after answering the question.

Wilfred
0
 
LVL 3

Expert Comment

by:rwarsh
ID: 2548826
 Sorry for the delay, I had a really busy day today.

To identify MAXDATAFILES use below select:

select count(*) from x$kcffi;    

Regards,

  rwarsh

0
 
LVL 4

Expert Comment

by:sudhi022299
ID: 2548842
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 ?
0
 
LVL 3

Expert Comment

by:rwarsh
ID: 2548874
You have to connect as internal (SYSDBA). It works in ORACLE 7.3.4.
0
 
LVL 4

Expert Comment

by:sudhi022299
ID: 2548897
But doesn't work on Oracle 8. I just checked it da.
0
 
LVL 3

Expert Comment

by:rwarsh
ID: 2548909
Adema mentioned, you can use  V$CONTROLFILE_RECORD_SECTION in v. 8.
0
 

Expert Comment

by:adema
ID: 2549267
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

0
 
LVL 3

Expert Comment

by:rwarsh
ID: 2551178
Please, use this select:

select count(*) from x$kcfio;

Regards,
 rwarsh
0
 

Expert Comment

by:adema
ID: 2553338
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?


0
 
LVL 3

Expert Comment

by:rwarsh
ID: 2553420
  This select is ORACLE's recommended way to extract MAXDATAFILES.

 rwarsh
0
 

Expert Comment

by:adema
ID: 2553456
select count(*) from x$kcffi;

Is for Oracle 6 and not for Oracle 7.

Gosse
0
 

Author Comment

by:wfvdijk
ID: 2553458
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
0
 
LVL 3

Expert Comment

by:rwarsh
ID: 2554470
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


0
 

Author Comment

by:wfvdijk
ID: 2554647
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
0
 

Author Comment

by:wfvdijk
ID: 2564399
see comments
0
 

Accepted Solution

by:
adema earned 200 total points
ID: 2595414
I'll agree with your answer and give you the points on March 1st unless there's somebody else with a better answer.

0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

912 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now