Solved

Determine MAX DATAFILES value

Posted on 2000-02-18
32
1,942 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
Comment Utility
there is a view called v$parameters under sys, try select the value from there.
0
 

Author Comment

by:wfvdijk
Comment Utility
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
Comment Utility
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
Comment Utility
This works for Oracle 8 only ...
0
 
LVL 4

Expert Comment

by:sudhi022299
Comment Utility
which version of Oracle are you looking for ?
0
 
LVL 3

Expert Comment

by:rwarsh
Comment Utility

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
Comment Utility
Adjusted points to 150
0
 

Author Comment

by:wfvdijk
Comment Utility
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
Comment Utility
  You can issue 'backup controlfile to trace' command, than read file and extract MAXDATAFILES value.

 rwarsh
0
 
LVL 4

Expert Comment

by:sudhi022299
Comment Utility
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
Comment Utility
See sudhi's comment.
0
 

Expert Comment

by:adema
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
 Gosse, please open your question. I do know the answer (maxdatafiles from select)

 regards
   rwarsh
0
 

Author Comment

by:wfvdijk
Comment Utility
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
Comment Utility
An additional 100 points will be added after answering the question.

Wilfred
0
 
LVL 3

Expert Comment

by:rwarsh
Comment Utility
 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
Comment Utility
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
Comment Utility
You have to connect as internal (SYSDBA). It works in ORACLE 7.3.4.
0
 
LVL 4

Expert Comment

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

Expert Comment

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

Expert Comment

by:adema
Comment Utility
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
Comment Utility
Please, use this select:

select count(*) from x$kcfio;

Regards,
 rwarsh
0
 

Expert Comment

by:adema
Comment Utility
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
Comment Utility
  This select is ORACLE's recommended way to extract MAXDATAFILES.

 rwarsh
0
 

Expert Comment

by:adema
Comment Utility
select count(*) from x$kcffi;

Is for Oracle 6 and not for Oracle 7.

Gosse
0
 

Author Comment

by:wfvdijk
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
see comments
0
 

Accepted Solution

by:
adema earned 200 total points
Comment Utility
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

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.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
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
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

763 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

13 Experts available now in Live!

Get 1:1 Help Now