After pre-allocating the datafile, the tablespace can be created with the REUSE keyword, which causes the pre-allocated datafile to be used. For example:
CREATE BIGFILE TABLESPACE bigtbs
DATAFILE 'oracle.v10g.bigfile' REUSE;
Special Considerations for VSAM
The DEFINE command must specify the LINEAR keyword, indicating that a VSAM LDS is being created. An LDS always has a control interval size of 4K and does not contain VSAM logical record structures. DEFINE parameters such as
CONTROLINTERVALSIZE and RECORDSIZE are therefore not used. (If space is specified using RECORDS, then IDCAMS assumes that each record equates to one 4K CI.) The VSAM SHAREOPTIONS default of SHR(1,3) is recommended for all database files except when the server is configured for multiple address space (MAXAS is greater than one). In this case, if automatic file extension is desired (using the AUTOEXTEND clause of the CREATE/ALTER TABLESPACE command),
SHR(3,3) is required.
Depending on the standards of your installation, you may need to specify VOLUMES or one or more of the SMS parameters (STORAGECLASS, MANAGEMENTCLASS, and DATACLASS) in the DEFINE command.
The following is an example DEFINE command for an Oracle database file:
DEFINE CLUSTER(
- NAME(VSAM.QUALS.SYSTEM.DBF2)LINEAR
STORAGECLASS(OSCM3A) MANAGEMENTCLASS(OMCM3A) -MEGABYTES(150))
No other preparation, loading, or formatting is required before a pre-allocated file is added to the database. When you specify the new file in a SQL statement (such as ALTER DATABASE or CREATE TABLESPACE), the server will format all of the primary space of the data set. Adding a large file to the database will therefore incur a noticeable delay while formatting is done. (This is true whether files are pre-allocated or created by the server.)
Oracle Managed Files on z/OS
The Oracle Managed Files (OMF) feature of Oracle Database on z/OS simplifies database administration by eliminating the need to specify the names of database
Oracle Managed Files on z/OS
files (control, log, and tablespace files) and to delete underlying files when the owning database element is logically dropped.
When you use OMF, you can omit the single-quoted filenames in the
CREATE/ALTER DATABASE and CREATE/ALTER TABLESPACE statements, because the Oracle server generates unique names for each file. When you drop an OMF log file or a tablespace comprising OMF files, the Oracle server deletes the files. In the case of DROP TABLESPACE, you can omit the INCLUDING CONTENTS AND DATAFILES clause.
To use OMF, you must do the following:
■ Specify certain init.ora file parameters involved in name generation
■ Omit the single-quoted filenames from CREATE or ALTER SQL statements The init.ora file parameters for OMF are DB_CREATE_FILE_DEST and DB_CREATE_ONLINE_LOG_DEST_n. On z/OS, these parameters supply the left-hand portion of a data set name (high-level qualifier and possibly other qualifiers), and they must end with a period. The OMF parameters can be reset or changed without shutting down, using ALTER SYSTEM or ALTER SESSION. For z/OS-specific details on the OMF parameters, see "Oracle Initialization Parameter Considerations". For general information about OMF, refer to Oracle Database Administrator’s Guide.
Even when you specify OMF parameters, you can continue to specify explicit file names in CREATE and ALTER statements. In fact, it is necessary to do so when you want to use a preallocated file or reuse an existing file.
Oracle tablespace names can be up to 30 characters long. If you want to be able to associate an OMF-created data set with its owning tablespace, then you must use tablespace names that are distinct in the first eight characters.
The right-hand portion of an OMF-generated filename depends on the type of file and includes an encoded timestamp value for uniqueness. The complete data set name format for OMF files is shown in the following example:
control files: destOMC.Attttttt log files: destOMLnnn.Attttttt permanent tablespace files: destOMD.tsn.Attttttt datafile copy: destOMD.tsn.Attttttt temporary tablespace files: destOMT.tsn.Attttttt archive log files: destOMA.Tnnn.Attttttt datafile backup piece: destOMB.Lnnn.Attttttt datafile incremental backup piece: destOMB.Lnnn.Attttttt archive log backup piece: destOMB.Tnnn.Attttttt rman backup piece: destOMB.Lnnn.Attttttt
Oracle Managed Files on z/OS
rman autobackup piece: destOMX.xnnnnnnn.Attttttt block change tracking files: destOMR.Attttttt
flash back log files: destOMF.Attttttt In the previous example, the variables are defined as follows:
Oracle allows underscores ("_") in a tablespace name, and any that are present are changed to "@" for use in the generated data set name.
Given the 44-character limit on z/OS data set names, the above data set name formats impose a limit of 29 characters on DB_CREATE_ONLINE_LOG_DEST_n and 23 characters on DB_CREATE_FILE_DEST (assuming a tablespace name of eight characters or more).
You can use Oracle-specific and z/OS system symbols in the OMF parameters. The destination string must end with a period after any symbol substitutions have been performed.
SQL statements that exploit OMF are generally the same as their non-OMF counterparts except that single-quoted filenames are missing. REUSE is not recognized for OMF and you can omit SIZE, which defaults to 100M for all types of files.
The following is an example of a CREATE DATABASE command that uses OMF for both log files and for the SYSTEM tablespace:
CREATE DATABASE W1O9 MAXINSTANCES 1
Variable Description
dest is the destination string (_DEST) in the OMF parameter
nnn is a three-digit log group number
tsn is up to eight characters of the tablespace name
ttttttt is the encoded timestamp (which looks like a random mix of letters and numerals)
Tnnn is the letter "T" followed by a three-digit thread number Lnnn is the letter "L" followed by a three digit incremental level x is the letter "P" if the database has an SPFILE or the letter "T" if
the database does not have an SPFILE nnnnnnn is a seven-byte time stamp