Files in Support of Backup and Recovery Operations - Oracle RMAN for Absolute Beginners - page 41

When you're using CREATE TABLESPACE scripts in different environments, it's useful to be able to parameterize
portions of the script. For instance, in development you may size the data files at 100MB, whereas in production the
data files may be 100GB. Use ampersand ( & ) variables to make CREATE TABLESPACE scripts more portable among
environments.
The next listing defines ampersand variables at the top of the script, and those variables determine the sizes of
data files created for the tablespaces:
define tbsp_large=5G
define tbsp_med=500M
--
create tablespace reg_data datafile '/u01/dbfile/O12C/reg_data01.dbf'
size &&tbsp_large segment space management auto;
--
create tablespace reg_index datafile '/u01/dbfile/O12C/reg_index01.dbf'
size &&tbsp_med segment space management auto;
Using ampersand variables allows you to modify the script once and have the variables reused throughout the
script. You can parameterize all aspects of the script, including data file mount points and extent sizes.
You can also pass the values of the ampersand variables in to the CREATE TABLESPACE script from the SQL*Plus
command line. This lets you avoid hard-coding a specific size in the script and instead provide the sizes at runtime. To
accomplish this, first define at the top of the script the ampersand variables to accept the values being passed in:
define tbsp_large=&1
define tbsp_med=&2
--
create tablespace reg_data datafile '/u01/dbfile/O12C/reg_data01.dbf'
size &&tbsp_large segment space management auto;
--
create tablespace reg_index datafile '/u01/dbfile/O12C/reg_index01.dbf'
size &&tbsp_med segment space management auto;
Now, you can pass variables in to the script from the SQL*Plus command line. The following example executes a
script named cretbsp.sql and passes in two values that set the ampersand variables to 5G and 500M , respectively:
SQL> @cretbsp 5G 500M