SQL Statements: DROP SEQUENCE to UPDATE, 12 of 27

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

Syntax

filespec_datafiles & filespec_tempfiles::=

filespec_redo_log_file_groups::=

Keywords and Parameters

'`filename`'

Specify 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`

Specify the size of the file in bytes. 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`

Specify REUSE to allow 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

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

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

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.

filespec