Name

ALTER TABLESPACE

Synopsis

ALTER TABLESPACE tablespace_name
   {
[ADD DATAFILE filename [SIZE
integer[K | M]] [REUSE]
[autoextend_clause] ]
[RENAME DATAFILE 'filename1' TO
'filename2']
   [DEFAULT STORAGE (
      [INITIAL integer[K | M] ]
      [NEXT integer[K | M] ]
      [MAXEXTENTS {integer | UNLIMITED} ]
      [PCTINCREASE integer]
      [FREELISTS integer]
      [FREELIST GROUPS integer] ]
      ) ]
   [ONLINE]
   [OFFLINE [NORMAL | TEMPORARY | IMMEDIATE]]
   [COALESCE]
   [MINIMUM EXTENT integer[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.

Keywords

ADD DATAFILE

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.

autoextend_clause

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.

RENAME

Changes the name of a datafile as stored in the data dictionary. Note that the tablespace must be offline before this command is issued.

DEFAULT STORAGE

Changes the physical characteristics for objects subsequently created in this tablespace, as follows:

NEXT

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.

MAXEXTENTS

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.

PCTINCREASE

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.

FREELISTS

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.

FREELIST GROUPS

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.

ONLINE

Brings a tablespace online.

OFFLINE

Takes a tablespace offline as follows:

NORMAL

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.

TEMPORARY

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.

IMMEDIATE

Does not perform a checkpoint and requires media recovery on the tablespace before bringing it back online.

COALESCE

Forces Oracle to coalesce all contiguous free extents into one large free extent.

MINIMUM EXTENT

Forces every extent created to be the size specified or a multiple of the size specified. This reduces fragmentation.

READ ONLY

Specifies that the tablespace is read only. That is, no more writes are allowed to the tablespace.

READ WRITE

Specifies that the tablespace is available for updates and writes.

PERMANENT

The default value. Allows permanent objects to be created in the tablespace.

TEMPORARY

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.

LOGGING

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.

NOLOGGING

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.

BEGIN BACKUP

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.

END BACKUP

Signals Oracle that the tablespace backup is complete, thereby restoring log file behavior to normal.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset