Skip Headers

Oracle® Database SQL Reference
10g Release 1 (10.1)

Part Number B10759-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Feedback

Go to previous page
Previous
Go to next page
Next
View PDF

file_specification

Purpose

Use one of the file_specification forms to specify a file as a datafile or tempfile, or to specify a group of one or more files as a redo log file group. If you are storing your files in Automatic Storage Management disk groups, then you can further specify the file as a disk group file.

A file_specification can appear in the following statements:

Prerequisites

You must have the privileges necessary to issue the statement in which the file specification appears.

Syntax


file_specification::=
Description of file_specification.gif follows
Description of the illustration file_specification.gif


datafile_tempfile_spec::=
Description of datafile_tempfile_spec.gif follows
Description of the illustration datafile_tempfile_spec.gif


redo_log_file_spec::=
Description of redo_log_file_spec.gif follows
Description of the illustration redo_log_file_spec.gif


size_clause::=
Description of size_clause.gif follows
Description of the illustration size_clause.gif


ASM_filename ::=
Description of ASM_filename.gif follows
Description of the illustration ASM_filename.gif


fully_qualified_file_name::=
Description of fully_qualified_file_name.gif follows
Description of the illustration fully_qualified_file_name.gif


numeric_file_name::=
Description of numeric_file_name.gif follows
Description of the illustration numeric_file_name.gif


incomplete_file_name::=
Description of incomplete_file_name.gif follows
Description of the illustration incomplete_file_name.gif


alias_file_name::=
Description of alias_file_name.gif follows
Description of the illustration alias_file_name.gif


autoextend_clause::=
Description of autoextend_clause.gif follows
Description of the illustration autoextend_clause.gif


maxsize_clause::=
Description of maxsize_clause.gif follows
Description of the illustration maxsize_clause.gif

Semantics

This section describes the semantics of file_specification. For additional information, refer to the SQL statement in which you specify a datafile, tempfile, redo log file, or Automatic Storage Management disk group or disk group file.


datafile_tempfile_spec

Use this clause to specify the attributes of datafiles and tempfiles if your database storage is in a file system or on raw devices or in Automatic Storage Management disk groups.


redo_log_file_spec

Use this clause to specify the attributes of redo log files if your database storage is in a file system or on raw devices or in Automatic Storage Management disk groups.


filename

Use filename for files stored in a file system or on raw devices. For a new file, filename is the name of the new file. If you are not using Oracle-managed files, then you must specify filename or the statement fails. However, if you are using Oracle-managed files, then filename is optional, as are the remaining clauses of the specification. In this case, Oracle Database creates a unique name for the file and saves it in the directory specified by either the DB_RECOVERY_FILE_DEST (for logfiles and control files), the DB_CREATE_FILE_DEST initialization parameter (for any type of file) or by the DB_CREATE_ONLINE_LOG_DEST_n initialization parameter (which takes precedence over DB_CREATE_FILE_DEST and DB_RECOVERY_FILE_DEST for log files).

For an existing file, you must specify a filename. Specify the name of either a datafile, tempfile, or a redo log file member. The filename can contain only single-byte characters from 7-bit ASCII or EBCDIC character sets. Multibyte characters are not valid.

A redo log file group can have one or more members (copies). Each filename must be fully specified according to the conventions for your operating system.


ASM_filename

Use a form of ASM_filename for files stored in Automatic Storage Management disk groups. You can create or refer to datafiles, tempfiles, and redo log files with this syntax.

All forms of ASM_filename begin with the plus sign (+) followed by the name of the disk group. You can determine the names of all Automatic Storage Management disk groups by querying the V$OSM_DISKGROUP view.


See Also:

Oracle Database Administrator's Guide for information on using Automatic Storage Management


fully_qualified_file_name

When you create a file in an Automatic Storage Management disk group, the file receives a system-generated fully qualified Automatic Storage Management filename. You can use this form only when referring to an existing Automatic Storage Management file. Therefore, if you are using this form during file creation, you must also specify REUSE.

You can determine the fully qualified names of Automatic Storage Management files by querying the dynamic performance view appropriate for the file type (for example V$DATAFILE for datafiles, V$CONTROLFILE for control files, and so on). You can also obtain the filenumber and incarnation_number portions of the fully qualified names by querying the V$OSM_FILE view.

Table 8-1 Oracle File Types and Automatic Storage Management File Type Tags

Automatic Storage Management file_type Description Automatic Storage Management file_type_tag Comments
CONTROLFILE Control files and backup control files Current

Backup


DATAFILE Datafiles and datafile copies tsname Tablespace into which the file is added
ONLINELOG Online logs group_group#
ARCHIVELOG Archive logs thread_thread#_seq_sequence#
TEMPFILE Tempfiles tsname Tablespace into which the file is added
BACKUPSET Datafile and archive log backup pieces; datafile incremental backup pieces hasspfile_timestamp hasspfile can take one of two values: s indicates that the backup set includes the spfile; n indicates that the backup set does not include the spfile.
PARAMETERFILE Persistent parameter files spfile
DAATAGUARDCONFIG Data Guard configuration file db_unique_name Data Guard tries to use the service provider name if it is set. Otherwise the tag defaults to DRCname.
FLASHBACK Flashback logs log_log#
CHANGETRACKING Block change tracking data ctf Used during incremental backups
DUMPSET Data Pump dumpset user_obj#_file# Dump set files encode the user name, the job number that created the dump set, and the file number as part of the tag.
XTRANSPORT Datafile convert tsname
AUTOBACKUP Automatic backup files hasspfile_timestamp hasspfile can take one of two values: s indicates that the backup set includes the spfile; n indicates that the backup set does not include the spfile.


numeric_file_name

A numeric Automatic Storage Management filename is similar to a fully qualified filename except that it uses only the unique filenumber.incarnation_number string. You can use this form only to refer to an existing file. Therefore, if you are using this form during file creation, you must also specify REUSE.


incomplete_file_name

Incomplete Automatic Storage Management filenames are used during file creation only. If you specify the disk group name alone, Automatic Storage Management uses the appropriate default template for the file type. For example, if you are creating a datafile in a CREATE TABLESPACE statement, Automatic Storage Management uses the default DATAFILE template to create an Automatic Storage Management datafile. If you specify the disk group name with a template, Automatic Storage Management uses the specified template to create the file. In both cases, Automatic Storage Management also creates a fully qualified filename.


template_name

A template is a named collection of attributes. You can create templates and apply them to files in a disk group. You can determine the names of all Automatic Storage Management template names by querying the V$OSM_TEMPLATE data dictionary view. Please refer to diskgroup_template_clauses for instructions on creating Automatic Storage Management templates.

You can specify template only during file creation. It appears in the incomplete and alias name forms of the ASM_filename diagram:


alias_file_name

An alias is a user-friendly name for an Automatic Storage Management file. You can use alias filenames during file creation or reference. You can specify a template with an alias, but only during file creation. To determine the alias names for Automatic Storage Management files, query the V$OSM_ALIAS data dictionary view.

If you are specifying an alias during file creation, please refer to diskgroup_directory_clauses and diskgroup_alias_clauses for instructions on specifying the full alias name.


SIZE Clause

Specify the size of the file in bytes. Use K, M, G, or T to specify the size in kilobytes, megabytes, gigabytes, or terabytes.


REUSE

Specify REUSE to allow Oracle to reuse an existing file. You must specify REUSE if you specify a filename that already exists.


Restriction on the REUSE Clause

You cannot specify REUSE unless you have specified filename.

Whenever Oracle uses an existing file, the previous contents of the file are lost.


autoextend_clause

The autoextend_clause is valid for datafiles and tempfiles but not for redo log files. Use this clause to enable or disable the automatic extension of a new or existing datafile or tempfile. If you omit this clause:


ON

Specify ON to enable autoextend.


OFF

Specify OFF to turn off autoextend if is turned on. When you turn off autoextend, the values of NEXT and MAXSIZE are set to zero. If you turn autoextend back on in a subsequent statement, you must reset these values.


NEXT

Use the NEXT clause to specify the size in bytes of the next increment of disk space to be allocated automatically when more extents are required. The default is the size of one data block.


MAXSIZE

Use the MAXSIZE clause to specify the maximum disk space allowed for automatic extension of the datafile.


UNLIMITED

Use the UNLIMITED clause if you do not want to limit the disk space that Oracle can allocate to the datafile or tempfile.


Restriction on the autoextend_clause

You cannot specify this clause as part of the datafile_tempfile_spec in a CREATE CONTROLFILE statement or in an ALTER DATABASE CREATE DATAFILE clause.

Examples


Specifying a Log File: Example

The following statement creates a database named payable that has two redo log file groups, each with two members, and one datafile:

CREATE DATABASE payable 
   LOGFILE GROUP 1 ('diska:log1.log', 'diskb:log1.log') SIZE 50K, 
           GROUP 2 ('diska:log2.log', 'diskb:log2.log') SIZE 50K 
   DATAFILE 'diskc:dbone.dat' SIZE 30M; 

The first file specification in the LOGFILE clause specifies a redo log file group with the GROUP value 1. This group has members named 'diska:log1.log' and 'diskb:log1.log', each 50 kilobytes in size.

The second file specification in the LOGFILE clause specifies a redo log file group with the GROUP value 2. This group has members named 'diska:log2.log' and 'diskb:log2.log', also 50 kilobytes in size.

The file specification in the DATAFILE clause specifies a datafile named 'diskc:dbone.dat', 30 megabytes in size.

Each file specification specifies a value for the SIZE parameter and omits the REUSE clause, so none of these files can already exist. Oracle must create them.


Adding a Log File: Example

The following statement adds another redo log file group with two members to the payable database:

ALTER DATABASE payable 
   ADD LOGFILE GROUP 3 ('diska:log3.log', 'diskb:log3.log') 
   SIZE 50K REUSE; 

The file specification in the ADD LOGFILE clause specifies a new redo log file group with the GROUP value 3. This new group has members named 'diska:log3.log' and 'diskb:log3.log', each 50 kilobytes in size. Because the file specification specifies the REUSE clause, each member can (but need not) already exist.


Specifying a Datafile: Example

The following statement creates a tablespace named stocks that has three datafiles:

CREATE TABLESPACE stocks 
   DATAFILE 'stock1.dat' SIZE 10M, 
            'stock2.dat' SIZE 10M,
            'stock3.dat' SIZE 10M; 

The file specifications for the datafiles specify files named 'diskc:stock1.dat', 'diskc:stock2.dat', and 'diskc:stock3.dat'.


Adding a Datafile: Example

The following statement alters the stocks tablespace and adds a new datafile:

ALTER TABLESPACE stocks 
   ADD DATAFILE 'stock4.dat' SIZE 10M REUSE; 

The file specification specifies a datafile named 'stock4.dat'. If the filename does not exist, then Oracle simply ignores the REUSE keyword.


Using a Fully Qualified Automatic Storage Management Datafile Name: Example

When using Automatic Storage Management, he following syntax shows how to use the fully_qualified_file_name clause to bring online a datafile in a hypothetical database, testdb:

ALTER DATABASE testdb 
   DATAFILE '+dgroup_01/testdb/datafile/system.261.1' ONLINE;