|René Nyffenegger's collection of things on the web|
René Nyffenegger on Oracle - Most wanted - Feedback
- Follow @renenyffenegger
List of datafiles in a database
select name from v$datafile
Changing the characteristics
Setting the size
The following statements sets the size of the datafile foo.dbf to 1 gigabyte.
alter database datafile '/db/adpdb/foo.dbf' resize 1024M
Setting the maximum size
It makes only sense to set a maximum size for a datafile if autoextend is on. Hence, the statement to set the maximum size requires to specify autoextend on.
alter database datafile '/u01/db/df/ts_data01.dbf' autoextend on maxsize 20M
Find Information about datafile headers in v$datafile_header.
Datafile count limits
The maximum number of database files can be set with the init parameter db_files.
Regardless of the setting of this paramter, the maximum number of database files in a smallfile tablespace is 1022. A bigfile tablespace can contain only one database file.
Adding a datafile is a structural change to the database that requires a backup.
A datafile is added with a alter tablespace add datafile command.
See adding datafiles to primary servers if you want to add a datafile to a primary server in a standby environment.
Renaming a datafile is a structural change to the database that requires a backup.
Datafiles were not designed to be dropped. (See also Metalink note 111316.1) This is true even though there is alter database datafile offline drop.
However, if the datafile is the only datafile in its tablespace, it can be removed together with the tablespace:
DROP TABLESPACE ts_data INCLUDING CONTENTS;
With Oracle 10g Release 2, it's finally possible to drop a datafile under certain conditions.
alter tablespace ts_something drop datafile '/path/to/datafile.dbf'
Changing a datafile's content
Whenever an SQL statement makes any changes to a datafile, those changes are recorded in the redo log.
Online and offline datafiles
A datafile can either be online or offline.
A datafile that contains a block whose SCN is more recent than the SCN of its header is called a fuzzy datafile.
The dba_data_files displays all datafiles along with their respective relevant information.
ASM simplifies the optimal layout of datafiles.
Thanks to Larry Daggett who pointed out a typo on this page.