Oracle® Database SQL Reference 10g Release 2 (10.2) Part Number B14200-02 |
|
|
View PDF |
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:
CREATE
CONTROLFILE
(see CREATE CONTROLFILE)
CREATE
DATABASE
(see CREATE DATABASE)
ALTER
DATABASE
(see ALTER DATABASE)
CREATE
TABLESPACE
(see CREATE TABLESPACE)
ALTER
TABLESPACE
(see ALTER TABLESPACE)
ALTER
DISKGROUP
(see ALTER DISKGROUP)
Prerequisites
You must have the privileges necessary to issue the statement in which the file specification appears.
Syntax
file_specification::=
ASM_filename ::=
autoextend_clause::=
maxsize_clause::=
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. The filename
can specify either a new file or an existing file. For a new file:
If you are not using Oracle-managed files, then you must specify both filename
and the SIZE
clause or the statement fails. When you specify a filename without a size, Oracle attempts to reuse an existing file and returns an error if the file does not exist.
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, 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.
The way the database interprets filename
also depends on whether you specify it with the SIZE
and REUSE
clauses.
If you specify filename
only, or with the REUSE
clause but without the SIZE
clause, then the file must already exist.
If you specify filename
with SIZE
but without REUSE
, the file must be a new file.
If you specify filename
with both SIZE
and REUSE
, then the file can be either new or existing. If the file exists, it is reused with the new size. If it does not exist, the database ignores the REUSE
keyword and creates a new file of the specified size.
See Also:
Oracle Database Administrator's Guide for more information on Oracle-managed files, "Specifying a Datafile: Example", and "Specifying a Log File: Example"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$ASM_DISKGROUP
view.
See Also:
Oracle Database Administrator's Guide for information on using Automatic Storage Managementfully_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
.
db_name
is the value of the DB_UNIQUE_NAME
initialization parameter. This name is equivalent to the name of the database on which the file resides, but the parameter distinguishes between primary and standby databases, if both exist.
file_type
and file_type_tag
indicate the type of database file. The table that follows lists all of the file types and their corresponding Automatic Storage Management tags.
filenumber
and incarnation_number
are system-generated identifiers to guarantee uniqueness.
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$ASM_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 |
---|---|---|---|
|
Control files and backup control files |
Current Backup |
— |
|
Datafiles and datafile copies |
|
Tablespace into which the file is added |
|
Online logs |
|
— |
|
Archive logs |
|
— |
|
Tempfiles |
|
Tablespace into which the file is added |
|
Datafile and archive log backup pieces; datafile incremental backup pieces |
|
|
|
Persistent parameter files |
|
— |
|
Data Guard configuration file |
|
Data Guard tries to use the service provider name if it is set. Otherwise the tag defaults to |
|
Flashback logs |
|
— |
|
Block change tracking data |
|
Used during incremental backups |
|
Data Pump dumpset |
|
Dump set files encode the user name, the job number that created the dump set, and the file number as part of the tag. |
|
Datafile convert |
|
— |
|
Automatic backup files |
|
|
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$ASM_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:
If you specify template
immediately after the disk group name, then Automatic Storage Management uses the specified template to create the file, and gives the file a fully qualified filename.
If you specify template after specifying an alias, then Automatic Storage Management uses the specified template to create the file, gives the file a fully qualified filename, and also creates the alias so that you can subsequently use it to refer to the file. If the alias you specify refers to an existing file, then Automatic Storage Management ignores the template specification unless you also specify REUSE
.
See Also:
diskgroup_template_clauses for information about the default templatesalias_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$ASM_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.
Specify the size of the file in bytes. Use K
, M
, G
, or T
to specify the size in kilobytes, megabytes, gigabytes, or terabytes.
For undo tablespaces, you must specify the SIZE
clause for each datafile. For other tablespaces, you can omit this parameter if the file already exists, or if you are creating an Oracle-managed file.
If you omit this clause when creating an Oracle-managed file, then Oracle creates a 100M file.
The size of a tablespace must be one block greater than the sum of the sizes of the objects contained in it.
See Also:
Oracle Database Administrator's Guide for information on automatic undo management and undo tablespaces and "Adding a Log File: Example"Specify REUSE
to allow Oracle to reuse an existing file.
If the file already exists, then Oracle reuses the filename and applies the new size (if you specify SIZE
) or retains the original size.
If the file does not exist, then Oracle ignores this clause and creates the file.
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.
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:
For Oracle-managed files:
If you specify SIZE
, then Oracle Database creates a file of the specified size with AUTOEXTEND
disabled.
If you do not specify SIZE
, then the database creates a 100M file with AUTOEXTEND
enabled. When autoextension is required, the database extends the file by its original size or 100MB, whichever is smaller. You can override this default behavior by specifying the NEXT
clause.
For user-managed files, with or without SIZE
specified, Oracle creates a file with AUTOEXTEND
disabled.
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, the 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;