ALTER TABLESPACE
ALTER TABLESPACEtablespace_name
{ [ADD DATAFILEfilename
[SIZEinteger
[K | M]] [REUSE] [autoextend_clause
] ] [RENAME DATAFILE 'filename1
' TO 'filename2
'] [DEFAULT STORAGE ( [INITIAL integer[K | M] ] [NEXTinteger
[K | M] ] [MAXEXTENTS {integer
| UNLIMITED} ] [PCTINCREASEinteger
] [FREELISTSinteger
] [FREELIST GROUPSinteger
] ] ) ] [ONLINE] [OFFLINE [NORMAL | TEMPORARY | IMMEDIATE]] [COALESCE] [MINIMUM EXTENTinteger
[K | M] ] [READ [ONLY | WRITE] [PERMANENT] [TEMPORARY] [LOGGING] [NOLOGGING] [BEGIN BACKUP] [END BACKUP] }
Changes an existing tablespace (tablespace_name) by adding or changing datafiles, changing storage parameters, taking the tablespace offline or putting it online, or starting and stopping backups.
Adds a new operating system datafile to the existing tablespace. SIZE information is required unless the file already exists; in this case, the REUSE keyword must be specified.
Specifies whether a given datafile is able to be automatically extended when it runs out of free space. The syntax is shown in the later autoextend_clause section.
Changes the name of a datafile as stored in the data dictionary. Note that the tablespace must be offline before this command is issued.
Changes the physical characteristics for objects subsequently created in this tablespace, as follows:
Specifies the size of the next extent in bytes, kilobytes, or megabytes. If the value is not a multiple of the database blocksize, it will be rounded up to a multiple of the database blocksize.
Specifies the maximum number of extents that may be allocated for this index. The default will vary according to the database blocksize. Specify UNLIMITED for unlimited expansion.
Specifies the percentage by which each extent will grow over the previous extent. The default is 50, which means that each extent will be one-and-one-half times larger than the previous extent. Beginning with Oracle8, the PCTINCREASE parameter cannot be specified, and defaults to a value of 0.
Specifies the number of free lists contained in each freelist group in this index. The default is 1 and the maximum depends on the database blocksize.
Specifies the number of groups of free lists for this index. The default is 1. This parameter should be used only with the Parallel Server option running in parallel mode.
Brings a tablespace online.
Takes a tablespace offline as follows:
The default; performs a checkpoint for all datafiles in this tablespace and ensures that each can be written. Note that this is the only mode allowed if the database is running in noarchivelog mode.
Performs a checkpoint for all datafiles in this tablespace, but does not ensure that each can be written, and therefore may require media recovery for offline files when bringing the tablespace back online.
Does not perform a checkpoint and requires media recovery on the tablespace before bringing it back online.
Forces Oracle to coalesce all contiguous free extents into one large free extent.
Forces every extent created to be the size specified or a multiple of the size specified. This reduces fragmentation.
Specifies that the tablespace is read only. That is, no more writes are allowed to the tablespace.
Specifies that the tablespace is available for updates and writes.
The default value. Allows permanent objects to be created in the tablespace.
Specifies that no permanent objects can be stored in the tablespace. Oracle then finds a more efficient access method when using the tablespace for temporary segments.
Specifies the default value of LOGGING for objects created in the tablespace; that is, redo log records will be written. This default value can be overridden for each object when the object is created.
Specifies the default value of NOLOGGING for objects created in the tablespace. This default value can be overridden for each object at the time the object is created. A value of NOLOGGING indicates that certain operations to the object are not logged to the redo log. This is a performance gain, but leaves the object vulnerable in the event that the tablespace has to be recovered.
Signals Oracle that the tablespace is being backed up, thereby changing log file behavior to accumulate all transactions to this tablespace. Note that this command does not actually perform a backup; it signals Oracle that the backup is about to begin.
Signals Oracle that the tablespace backup is complete, thereby restoring log file behavior to normal.