Oracle9i SQL Reference Release 2 (9.2) Part Number A96540-02 |
|
|
View PDF |
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.
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)CREATE
TEMPORARY
TABLESPACE
(see CREATE TEMPORARY TABLESPACE)ALTER
TABLESPACE
(see ALTER TABLESPACE)You must have the privileges necessary to issue one of the statements listed in the preceding section.
file_specification::=
datafile_tempfile_spec::=
redo_log_file_spec::=
autoextend_clause::=
maxsize_clause::=
This section describes the semantics of file_specification
. For additional information, refer to the SQL statement in which you specify a datafile, tempfile, or redo log file.
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 creates a unique name for the file and saves it in the directory specified by either 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
for log files).
See Also:
Oracle9i Database Administrator's Guide for more information on Oracle-managed files, "Specifying a Datafile: Example", and "Specifying a Log File: Example" |
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.
Specify the size of the file in bytes. Use K
or M
to specify the size in kilobytes or megabytes.
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.See Also:
Oracle9i 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. You must specify REUSE
if you specify a filename
that already exists.
SIZE
) or retains the original size.You cannot specify REUSE
unless you have specified filename
.
See Also:
"Adding a Datafile: Example" and "Adding a Log File: Example" |
Use the autoextend_clause
to enable or disable the automatic extension of a new datafile or tempfile. If you omit this clause:
SIZE
specified, Oracle creates a file with AUTOEXTEND
disabled.Specify ON
to enable autoextend.
Specify OFF
to turn off autoextend if is turned on.
Note: When you turn off autoextend, the values of |
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. Use K
or M
to specify this size in kilobytes or megabytes. The default is the size of one data block.
Use the MAXSIZE
clause to specify the maximum disk space allowed for automatic extension of the datafile.
Use the UNLIMITED
clause if you do not want to limit the disk space that Oracle can allocate to the datafile or tempfile.
You cannot specify this clause as part of datafile_tempfile_spec
in a CREATE
CONTROLFILE
statement or in an ALTER
DATABASE
CREATE
DATAFILE
clause.
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.
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.
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
'.
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 'diskc:stock4.dat
'. If the filename does not exist, then Oracle simply ignores the REUSE
keyword.