Oracle7 Server SQL Reference

ALTER TABLESPACE

Purpose

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

to add datafile(s)

to rename datafiles

to change default storage parameters

to take the tablespace online or offline

to begin or end a backup

to allow or disallow writing to a tablespace

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:

to take the tablespace online or offline

to begin or end a backup

make the tablespace read-only or read-write

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.

The tablespace must be online.

There must not be any active transactions in the entire database.

This is necessary to ensure that there is no undo information that needs to be applied to the tablespace.

The tablespace must not contain any active rollback segments.

For this reason, the SYSTEM tablespace can never be made read-only, since it contains the SYSTEM rollback segment. Additionally, because the rollback segments of a read-only tablespace are not accessible, it is recommended that you drop the rollback segments before you make a tablespace read-only.

The tablespace must not be involved in an online backup, since the end of a backup updates the header file of all datafiles in the tablespace.

The COMPATIBLE initialization parameter must be set to 7.1.0 or greater.

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 the tablespace's creation label is higher than your DBMS label, you must have READUP and WRITEUP system privileges.

If the tablespace's creation label is lower than your DBMS label, you must have WRITEDOWN system privilege.

If the tablespace's creation label and your DBMS label are not comparable, you must have READUP, WRITEUP, and WRITEDOWN system privileges.

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:

take the tablespace offline normally

shutdown the instance

begin another backup of the tablespace

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':

1. Take the tablespace offline using an ALTER TABLESPACE statement with the OFFLINE option:

	ALTER TABLESPACE accounting OFFLINE NORMAL

2. Copy the file from 'DISKA:PAY1.DAT' to 'DISKB:RECEIVE1.DAT' using your operating system's commands.

3. Rename the datafile using the ALTER TABLESPACE command with the RENAME DATAFILE clause:

	ALTER TABLESPACE accounting
 		RENAME	DATAFILE 'diska:pay1.dbf'
 		TO		'diskb:receive1.dbf'

4. Bring the tablespace back online using an ALTER TABLESPACE statement with the ONLINE option:

	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