Tuesday, March 13, 2012

Maximum number of Datafiles for an instance

I'm back after a long hiatus! Hopefully, with stuff that you will all appreciate and use, in your day to day activities. So, here goes.

Objective

To find the maximum number of datafiles for a given instance (instance = database, both are interchangeable, unless we are talking of RAC).

Options

1. Using init file:

Check the parameter db_files.

e.g: SQL> show parameter db_files


2. Using control file:

Check the parameter MAXDATAFILES after generating the controlfile trace.

eg:

CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS ARCHIVELOG
MAXLOGFILES 64
MAXLOGMEMBERS 5
MAXDATAFILES 512
MAXINSTANCES 8
MAXLOGHISTORY 14607


3. Execute the below query in sqlplus:

SQL> select records_total from v$controlfile_record_section where type='DATAFILE';

No comments: