filespec

Syntax

filespec_datafiles & filespec_tempfiles::=

filespec_redo_log_file_groups::=

Purpose

To specify a file as a datafile or tempfile

To specify a group of one or more files as a redo log file group.

Prerequisites

A filespec can appear in the following statements: "CREATE DATABASE", "ALTER DATABASE", "CREATE TABLESPACE", and "ALTER TABLESPACE", "CREATE CONTROLFILE", "CREATE LIBRARY", and "CREATE TEMPORARY TABLESPACE".

You must have the privileges necessary to issue one of these statements.

Keywords and Parameters

'filename'	is the name of either a datafile, tempfile, or a redo log file member. A '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 integer	specifies the size of the file. Use K or M to specify the size in kilobytes or megabytes. You can omit this parameter only if the file already exists. The size of a tablespace must be one block greater than the sum of the sizes of the objects contained in it.
REUSE	allows Oracle to reuse an existing file. If the file already exists, Oracle verifies that its size matches the value of the SIZE parameter (if you specify SIZE). If the file does not exist, Oracle ignores this clause and creates the file. You can omit this clause only if the file does not already exist. If you omit this clause, Oracle creates the file.
	Note: Whenever Oracle uses an existing file, the file's previous contents are lost.

Examples

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.

All of these filespecs specify a value for the SIZE parameter and omit 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 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 already exist. If a member exists, it must have a size of 50 kilobytes. If it does not exist, Oracle creates it with that size.

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 specifies files named 'diskc:stock1.dat', 'diskc:stock2.dat', and 'diskc:stock3.dat'. Since each filespec omits the SIZE parameter, each file must already exist.

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.