tablespace
is the name of the tablespace to be altered.
ADD DATAFILE
adds the datafile specified by filespec to the tablespace. See the syntax description of filespec. You can add a datafile while the tablespace is online or offline. Be sure that the datafile is not already in use by another database.
AUTOEXTEND
enables or disables the autoextending of the size of the datafile in the tablespace.
OFF disable autoextend if it is turned on. NEXT and MAXSIZE are set to zero. Values for NEXT and MAXSIZE must be respecified in further ALTER TABLESPACE AUTOEXTEND commands.
ON enable autoextend.
NEXT the size in bytes of the next increment of disk space to be automatically allocated to the datafile when more extents are required. You can also use K or M to specify this size in kilobytes or megabytes. The default is one data block.
MAXSIZE maximum disk space allowed for automatic extension of the datafile.
UNLIMITED set no limit on allocating disk space to the datafile.
RENAME DATAFILE
renames one or more of the tablespace's datafiles. Take the tablespace offline before renaming the datafile. Each 'filename' must fully specify a datafile using the conventions for filenames on your operating system.
This clause only associates the tablespace with the new file rather than the old one. This clause does not actually change the name of the operating system file. You must change the name of the file through your operating system.
COALESCE
for each datafile in the tablespace, coalesce all contiguous free extents into larger contiguous extents.
COALESCE cannot be specified with any other command option.
DEFAULT STORAGE
specifies the new default storage parameters for objects subsequently created in the tablespace. See the STORAGE clause.
ONLINE
OFFLINE
takes the tablespace offline and prevents further access to its segments.
TEMPORARY performs a checkpoint for all online datafiles in the tablespace but does not ensure that all files can be written. Any offline files may require media recovery before you bring the tablespace back online.
The default is NORMAL.
Suggestion: Before taking a tablespace offline for a long time, you may want to alter any users who have been assigned the tablespace as either a default or temporary tablespace. When the tablespace is offline, these users cannot allocate space for objects or sort areas in the tablespace. You can reassign users new default and temporary tablespaces with the ALTER USER command.
BEGIN BACKUP
signifies that an online backup is to be performed on the datafiles that comprise this tablespace. This option does not prevent users from accessing the tablespace. You must use this option before beginning an online backup. You cannot use this option on a read-only tablespace.
While the backup is in progress, you cannot:
END BACKUP
signifies that an online backup of the tablespace is complete. Use this option as soon as possible after completing an online backup. You cannot use this option on a read-only tablespace.
READ ONLY
signifies that no further write operations are allowed on the tablespace.
READ WRITE
signifies that write operations are allowed on a previously read only tablespace.
PERMANENT
specifies that the tablespace is to be converted from a temporary to a permanent one. A permanent tablespace is one wherein permanent database objects can be stored. This is the default when a tablespace is created.
TEMPORARY
specifies that the tablespace is to be converted from a permanent to a temporary one. A temporary tablespace is one wherein no permanent database objects can be stored.
Before taking a tablespace offline for a long time, you may want to alter any users who have been assigned the tablespace as either a default or temporary tablespace. When the tablespace is offline, these users cannot allocate space for objects or sort areas in the tablespace. You can reassign users new default and temporary tablespaces with the ALTER USER command.
Once a tablespace is read-only, you can copy its files to read-only media. You must then rename the datafiles in the control file to point to the new location by using the SQL command ALTER DATABASE RENAME.
If you forget to indicate the end of an online tablespace backup, and an instance failure or SHUTDOWN ABORT occurs, Oracle assumes that media recovery (possibly requiring archived redo log) is necessary at the next instance start up. To restart the database without media recovery, see Oracle7 Server Administrator's Guide.
Example I
The following statement signals to the database that a backup is about to begin:
ALTER TABLESPACE accounting
BEGIN BACKUP
Example II
The following statement signals to the database that the backup is finished:
ALTER TABLESPACE accounting
END BACKUP
Example III
This example moves and renames a datafile associated with the ACCOUNTING tablespace from 'DISKA:PAY1.DAT' to 'DISKB:RECEIVE1.DAT':
ALTER TABLESPACE accounting OFFLINE NORMAL
ALTER TABLESPACE accounting RENAME DATAFILE 'diska:pay1.dbf' TO 'diskb:receive1.dbf'
ALTER TABLESPACE accounting ONLINE
Example IV
The following statement adds a datafile to the tablespace; when more space is needed new extents of size 10 kilobytes will be added up to a maximum of 100 kilobytes:
ALTER TABLESPACE accounting ADD DATAFILE 'disk3:pay3.dbf' AUTOEXTEND ON NEXT 10 K MAXSIZE 100 K