Skip Headers

Oracle9i SQL Reference
Release 2 (9.2)

Part Number A96540-02
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 Go to next page
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.

A file_specification can appear in the following statements:

Prerequisites

You must have the privileges necessary to issue one of the statements listed in the preceding section.

Syntax

file_specification::=

Text description of clauses18.gif follows
Text description of file_specification


datafile_tempfile_spec::=

Text description of clauses20.gif follows
Text description of datafile_tempfile_spec


redo_log_file_spec::=

Text description of clauses21.gif follows
Text description of redo_log_file_spec


autoextend_clause::=

Text description of clauses22.gif follows
Text description of autoextend_clause


maxsize_clause::=

Text description of clauses23.gif follows
Text description of 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, or redo log file.

'filename'

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.

SIZE Clause

Specify the size of the file in bytes. Use K or M to specify the size in kilobytes or megabytes.

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.


Note:

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


See Also:

"Adding a Datafile: Example" and "Adding a Log File: Example"

autoextend_clause

Use the autoextend_clause to enable or disable the automatic extension of a new 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.


Note:

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. Use K or M to specify this size in kilobytes or megabytes. 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 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 'diskc:stock4.dat'. If the filename does not exist, then Oracle simply ignores the REUSE keyword.