Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations bkrike on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
in which view can I find the total number of my datafiles?
- Thread starter lhugh
- Start date
SantaMufasa
Technical User
LHugh,
If your want just the number of datafiles that are "database data files", you can:
SELECT COUNT(*) from dba_data_files;
or
select count(*) from v$datafile;
But, your on-line redo logfiles:
select count(*) from v$logfile;
and controlfiles:
select count(*) from v$controlfile;
Cheers,
![[santa]](/data/assets/smilies/santa.gif "[santa] [santa]")
Mufasa
(aka Dave of Sandy, Utah, USA @ 22:41 (05Dec03) GMT, 15:41 (05Dec03) Mountain Time)
If your want just the number of datafiles that are "database data files", you can:
SELECT COUNT(*) from dba_data_files;
or
select count(*) from v$datafile;
But, your on-line redo logfiles:
select count(*) from v$logfile;
and controlfiles:
select count(*) from v$controlfile;
Cheers,
Mufasa(aka Dave of Sandy, Utah, USA @ 22:41 (05Dec03) GMT, 15:41 (05Dec03) Mountain Time)
- Thread starter
- #3
SantaMufasa
Technical User
LHugh,
To see the value for MAXDATAFILES, do the following:
1) ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
2) select name p, value v from v$parameter
where name like 'user_dump%';
3) From your operating system, "cd <your_user_dump_dest>"
4) display the contents of the most recent ".trc" file
5) Among the displayed contents are lines that look like:
CREATE CONTROLFILE REUSE DATABASE "DHUNT" RESETLOGS NOARCHIVELOG
MAXLOGFILES 32
MAXLOGMEMBERS 2
MAXDATAFILES 32
MAXINSTANCES 16
MAXLOGHISTORY 1815
Notice that in the case above, MAXDATAFILES=32.
Mufasa
(aka Dave of Sandy, Utah, USA @ 23:07 (05Dec03) GMT, 16:07 (05Dec03) Mountain Time)
To see the value for MAXDATAFILES, do the following:
1) ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
2) select name p, value v from v$parameter
where name like 'user_dump%';
3) From your operating system, "cd <your_user_dump_dest>"
4) display the contents of the most recent ".trc" file
5) Among the displayed contents are lines that look like:
CREATE CONTROLFILE REUSE DATABASE "DHUNT" RESETLOGS NOARCHIVELOG
MAXLOGFILES 32
MAXLOGMEMBERS 2
MAXDATAFILES 32
MAXINSTANCES 16
MAXLOGHISTORY 1815
Notice that in the case above, MAXDATAFILES=32.
Mufasa(aka Dave of Sandy, Utah, USA @ 23:07 (05Dec03) GMT, 16:07 (05Dec03) Mountain Time)
- Thread starter
- #5
- Thread starter
- #6
I did this, but I was not sure if the datafile 100 is the maxfile number. Now I know it is. Thanks
SQL> select type, records_total from v$controlfile_record_section;
TYPE RECORDS_TOTAL
-------------------- -------------
DATABASE 1
CKPT PROGRESS 4
REDO THREAD 1
REDO LOG 50
DATAFILE 100
FILENAME 351
TABLESPACE 100
TEMPORARY FILENAME 100
RMAN CONFIGURATION 50
LOG HISTORY 226
OFFLINE RANGE 145
TYPE RECORDS_TOTAL
-------------------- -------------
ARCHIVED LOG 13
BACKUP SET 204
BACKUP PIECE 210
BACKUP DATAFILE 211
BACKUP REDOLOG 107
DATAFILE COPY 210
BACKUP CORRUPTION 185
COPY CORRUPTION 204
DELETED OBJECT 408
PROXY COPY 306
BACKUP SPFILE 226
TYPE RECORDS_TOTAL
-------------------- -------------
DATABASE INCARNATION 145
23 rows selected.
SQL> select type, records_total from v$controlfile_record_section;
TYPE RECORDS_TOTAL
-------------------- -------------
DATABASE 1
CKPT PROGRESS 4
REDO THREAD 1
REDO LOG 50
DATAFILE 100
FILENAME 351
TABLESPACE 100
TEMPORARY FILENAME 100
RMAN CONFIGURATION 50
LOG HISTORY 226
OFFLINE RANGE 145
TYPE RECORDS_TOTAL
-------------------- -------------
ARCHIVED LOG 13
BACKUP SET 204
BACKUP PIECE 210
BACKUP DATAFILE 211
BACKUP REDOLOG 107
DATAFILE COPY 210
BACKUP CORRUPTION 185
COPY CORRUPTION 204
DELETED OBJECT 408
PROXY COPY 306
BACKUP SPFILE 226
TYPE RECORDS_TOTAL
-------------------- -------------
DATABASE INCARNATION 145
23 rows selected.
- Status