filespec

Purpose

Use the filespec syntax 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.

Prerequisites

A filespec can appear in the statements CREATE DATABASE, ALTER DATABASE, CREATE TABLESPACE, ALTER TABLESPACE, CREATE CONTROLFILE, CREATE LIBRARY, and CREATE TEMPORARY TABLESPACE. You must have the privileges necessary to issue one of these statements.

See Also: CREATE DATABASE ALTER DATABASE CREATE TABLESPACE ALTER TABLESPACE CREATE CONTROLFILE CREATE LIBRARY CREATE TEMPORARY TABLESPACE

Syntax

filespec::=

Text description of filespec

datafile_tempfile_spec::=

Text description of datafile_tempfile_spec

redo_log_file_spec::=

Text description of redo_log_file_spec

Keywords and Parameters

'filename'

If filespec refers to a new file:

• For a datafile or tempfile, if the DB_CREATE_FILE_DEST initialization parameter is set, then filename is optional, as are the remaining clauses of filespec.

• For a logfile, if the DB_CREATE_ONLINE_LOG_DEST_n or DB_CREATE_FILE_DEST parameter is set, then filename is optional, as are the remaining clauses of filespec.

In either case, Oracle creates an Oracle-managed file in the default file system location for the type of file being created and generates a unique filename. If you omit the SIZE clause, Oracle creates a 100 MB autoextensible file with no maximum size. If the operation containing this filespec fails, any partially created Oracle-managed files are deleted. If neither parameter is set and you omit filename, the operation fails.

If filespec refers to 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.

• 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.

• The size of a tablespace must be one block greater than the sum of the sizes of the objects contained in it.

  See Also: Oracle9i Database Administrator's Guide for information on Automatic Undo Management and undo tablespaces

REUSE

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

• If the file already exists, Oracle reuses the filename and applies the new size (if you specify SIZE) or retains the original size.

• If the file does not exist, Oracle ignores this clause and creates the file.

Restriction: You cannot specify REUSE unless you have specified filename.

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

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 filespec 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 filespec 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 filespec in the DATAFILE clause specifies a datafile named 'diskc:dbone.dat', 30 megabytes in size.

Each filespec 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 filespec 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 filespec 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 'diskc:stock1.dat', 
            'diskc:stock2.dat',
            'diskc:stock3.dat'; 

The filespecs for the datafiles specify files named 'diskc:stock1.dat', 'diskc:stock2.dat', and 'diskc:stock3.dat'. Each filespec omits the SIZE parameter, so each file must already exist.

Adding a Datafile Example

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

ALTER TABLESPACE stocks 
   ADD DATAFILE 'diskc:stock4.dat' REUSE; 

The filespec specifies a datafile named 'diskc:stock4.dat'. Since the filespec omits the SIZE parameter, the file must already exist and the REUSE clause is not significant.