Oracle7 Server SQL Reference

Contents Index Home Previous Next

ALTER TABLESPACE

Purpose

To alter an existing tablespace in one of the following ways:

Prerequisites

If you have ALTER TABLESPACE system privilege, you can perform any of this command's operations. If you have MANAGE TABLESPACE system privilege, you can only perform the following operations:

Before you can make a tablespace read-only, the following conditions must be met. It may be easiest to meet these restrictions by performing this function in restricted mode, so that only users with the RESTRICTED SESSION system privilege can be logged on.

If you are using Trusted Oracle7 in DBMS MAC mode, your DBMS label must match the tablespace's creation label or you must satisfy one of the following criteria:

If you are using Trusted Oracle7 in DBMS MAC mode, to add a datafile, your operating system process label must be the equivalent of DBHIGH.

Syntax

Keywords and Parameters

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

brings the tablespace online.

OFFLINE

takes the tablespace offline and prevents further access to its segments.

NORMAL performs a checkpoint for all datafiles in the tablespace. All of these datafiles must be online. You need not perform media recovery on this tablespace before bringing it back online. You must use this option if the database is in noarchivelog mode.

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.

IMMEDIATE does not ensure that tablespace files are available and does not perform a checkpoint. You must perform media recovery on the tablespace before bringing it 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.

Usage Notes

If you are using Trusted Oracle7, datafiles that you add to a tablespace are labelled with the operating system equivalent of DBHIGH.

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

Related Topics

CREATE TABLESPACE command [*] CREATE DATABASE command [*] DROP TABLESPACE command [*] STORAGE clause [*]


Contents Index Home Previous Next