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';