ALTER TABLESPACE

Purpose

Use the ALTER TABLESPACE statement to alter an existing tablespace or one or more of its datafiles or tempfiles.

See Also: CREATE TABLESPACE for information on creating a tablespace

Prerequisites

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

• Take the tablespace online or offline

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

• The tablespace must be online.

• The tablespace must not contain any active rollback segments. For this reason, the SYSTEM tablespace can never be made read only, because it contains the SYSTEM rollback segment. Additionally, because the rollback segments of a read-only tablespace are not accessible, Oracle recommends that you drop the rollback segments before you make a tablespace read only.

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

Performing this function in restricted mode may help you meet these restrictions, because only users with RESTRICTED SESSION system privilege can be logged on.

Syntax

datafile / tempfile_clauses::=

filespec: See filespec.

autoextend_clause::=

maxsize_clause::=

storage_clause: See storage_clause.

Keywords and Parameters

tablespace

Specify the name of the tablespace to be altered.

Note: For locally managed temporary tablespaces, the only clause you can specify in this statement in the ADD clause.

datafile / tempfile_clauses

The datafile and tempfile clauses let you add or modify a datafile or tempfile.

ADD DATAFILE | TEMPFILE	Specify ADD to add to the tablespace a datafile or tempfile specified by filespec. You can add a datafile or tempfile to a locally managed tablespace that is online or to a dictionary managed tablespace that is online or offline. Be sure the file is not in use by another database. See Also: filespec
	Note: For locally managed temporary tablespaces, this is the only clause you can specify at any time.
RENAME DATAFILE	Specify RENAME DATAFILE to rename 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 merely 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.

autoextend_clause

The autoextend_clause lets you enable or disable the automatic extending of the size of the datafile in the tablespace.

OFF	Specify OFF to 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 statements.
ON	Specify ON to enable autoextend.
NEXT integer	Specify the size in bytes of the next increment of disk space to be allocated automatically to the datafile when more extents are required. Use K or M to specify this size in kilobytes or megabytes. The default is one data block.
maxsize_clause	The maxsize_clause lets you specify maximum disk space allowed for automatic extension of the datafile.
	UNLIMITED	Specify UNLIMITED to set no limit on allocating disk space to the datafile.

DEFAULT storage_clause

DEFAULT storage_clause lets you specify the new default storage parameters for objects subsequently created in the tablespace. For a dictionary-managed temporary table, Oracle considers only the NEXT parameter of the storage_clause.

Restriction: You cannot specify this clause for a locally managed tablespace.

See Also: storage_clause

MINIMUM EXTENT

The MINIMUM EXTENT clause lets you control free space fragmentation in the tablespace by ensuring that every used or free extent size in a tablespace is at least as large as, and is a multiple of, integer. This clause is not relevant for a dictionary-managed temporary tablespace.

Restriction: You cannot specify this clause for a locally managed tablespace.

See Also: Oracle8i Administrator's Guide for more information about using MINIMUM EXTENT to control space fragmentation

ONLINE | OFFLINE

Specify ONLINE to bring the tablespace online.

Specify OFFLINE to take the tablespace offline and prevents further access to its segments.

Suggestion: Before taking a tablespace offline for a long time, you may want to alter the tablespace allocation of 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.

See Also: ALTER USER

BEGIN BACKUP

Specify BEGIN BACKUP to indicate that an open backup is to be performed on the datafiles that make up this tablespace. This clause does not prevent users from accessing the tablespace. You must use this clause before beginning an open backup.

Restrictions: You cannot specify this clause for a read-only tablespace or for a temporary locally managed tablespace.

Note: While the backup is in progress, you cannot take the tablespace offline normally, shut down the instance, or begin another backup of the tablespace.

END BACKUP

Specify END BACKUP to indicate that an open backup of the tablespace is complete. Use this clause as soon as possible after completing an open backup. You cannot use this clause on a read-only tablespace.

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.

See Also: Oracle8i Administrator's Guide for information on restarting the database without media recovery

READ ONLY | READ WRITE

Specify READ ONLY to place the tablespace in transition read-only mode. In this state, existing transactions can complete (commit or roll back), but no further write operations (DML) are allowed to the tablespace except for rollback of existing transactions that previously modified blocks in the tablespace.

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 statement ALTER DATABASE ... RENAME.

See Also: Oracle8i Concepts for more information on read-only tablespaces ALTER DATABASE

Specify READ WRITE to indicate that write operations are allowed on a previously read-only tablespace.

PERMANENT | TEMPORARY

Specify PERMANENT to indicate that the tablespace is to be converted from a temporary to a permanent one. A permanent tablespace is one in which permanent database objects can be stored. This is the default when a tablespace is created.

Specify TEMPORARY to indicate specifies that the tablespace is to be converted from a permanent to a temporary one. A temporary tablespace is one in which no permanent database objects can be stored. Objects in a temporary tablespace persist only for the duration of the session.

COALESCE

For each datafile in the tablespace, this clause coalesces all contiguous free extents into larger contiguous extents.

LOGGING | NOLOGGING

Specify LOGGING if you want logging of all tables, indexes, and partitions within the tablespace. The tablespace-level logging attribute can be overridden by logging specifications at the table, index, and partition levels.

When an existing tablespace logging attribute is changed by an ALTER TABLESPACE statement, all tables, indexes, and partitions created after the statement will have the new default logging attribute (which you can still subsequently override). The logging attributes of existing objects are not changed.

Only the following operations support NOLOGGING mode:

• DML: direct-load INSERT (serial or parallel); Direct Loader (SQL*Loader)

• DDL: CREATE TABLE ... AS SELECT, CREATE INDEX, ALTER INDEX ... REBUILD, ALTER INDEX ... REBUILD PARTITION, ALTER INDEX ... SPLIT PARTITION, ALTER TABLE ... SPLIT PARTITION, ALTER TABLE ... MOVE PARTITION.

In NOLOGGING mode, data is modified with minimal logging (to mark new extents invalid and to record dictionary changes). When applied during media recovery, the extent invalidation records mark a range of blocks as logically corrupt, because the redo data is not logged. Therefore, if you cannot afford to lose the object, it is important to take a backup after the NOLOGGING operation.

Examples

Backup Examples

The following statement signals to the database that a backup is about to begin:

ALTER TABLESPACE accounting 
    BEGIN BACKUP; 

The following statement signals to the database that the backup is finished:

ALTER TABLESPACE accounting 
    END BACKUP; 

Moving and Renaming Example

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

   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 statement 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 clause:

   ALTER TABLESPACE accounting ONLINE; 
   

Adding a Datafile Example

The following statement adds a datafile to the tablespace and changes the default logging attribute to NOLOGGING. When more space is needed, new extents of size 10 kilobytes will be added up to a maximum of 100 kilobytes:

ALTER TABLESPACE accounting NOLOGGING
    ADD DATAFILE 'disk3:pay3.dbf'
    SIZE 50K
    AUTOEXTEND ON
    NEXT 10K
    MAXSIZE 100K;

Altering a tablespace logging attribute has no affect on the logging attributes of the existing schema objects within the tablespace. The tablespace-level logging attribute can be overridden by logging specifications at the table, index, and partition levels.

Changing Extent Allocation Example

The following statement changes the allocation of every extent of tabspace_st to a multiple of 128K:

ALTER TABLESPACE tabspace_st MINIMUM EXTENT 128K;