Datafiles [Oracle]

List of datafiles in a database

select name from v$datafile

Changing the characteristics

alter database datafile ...

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

Autoextend

Datafile Headers

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 datafiles

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.

Standby environment

See adding datafiles to primary servers if you want to add a datafile to a primary server in a standby environment.

Renaming datafiles

Renaming a datafile is a structural change to the database that requires a backup.

Dropping datafiles

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 database cannot be opened if any of the online datafiles media recovery.

Fuzzy datafiles

A datafile that contains a block whose SCN is more recent than the SCN of its header is called a fuzzy datafile.

Thanks

Thanks to Larry Daggett who pointed out a typo on this page.


	René Nyffenegger's collection of things on the web
	René Nyffenegger on Oracle - Most wanted - Feedback - Follow @renenyffenegger