Using Oracle ASM File Names in SQL Statements

You can specify Oracle ASM file names in the file specification clause of your SQL statements. If you are creating a file for the first time, then use the creation form of an Oracle ASM file name. If the Oracle ASM file exists, then you must use the reference form of the file name, and if you are trying to re-create the file, you must add the REUSE keyword so that the space is reused for the new file. This usage might occur when, for example, trying to re-create a control file, as shown in "Creating Control Files in Oracle ASM".

If a reference form is used with the REUSE keyword and the file does not exist, an error results.

Example 5-2 is an example of specifying an Oracle ASM file name in a SQL statement. In this case, it is used in the file creation form.

The tablespace mytblspace is created and comprises one data file of size 200 MB contained in the disk group data. The data file is set to auto-extensible with an unlimited maximum size. You can use an AUTOEXTEND clause to override this default.

For more examples of the use of Oracle ASM file name in SQL statements, see Creating Database Files in Oracle ASM Using SQL*Plus.

Example 5-2 Using an Oracle ASM File name in a SQL statement

CREATE TABLESPACE mytblspace DATAFILE '+data' SIZE 200M AUTOEXTEND ON;