ALTER TABLESPACE

Purpose

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

See Also: Oracle9i Database Administrator's Guide and 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

alter_tablespace::=

Text description of alter_tablespace

datafile_tempfile_clauses::=

Text description of datafile_tempfile_clauses

filespec: See filespec.

autoextend_clause::=

Text description of autoextend_clause

maxsize_clause::=

Text description of maxsize_clause

storage_clause: See storage_clause.

Keywords and Parameters

tablespace

Specify the name of the tablespace to be altered.

Restrictions:

• If tablespace is an undo tablespace, the only other clauses you can specify in this statement are ADD DATAFILE, RENAME DATAFILE, DATAFILE ... ONLINE | OFFLINE, and BEGIN | END BACKUP.

• For locally managed temporary tablespaces the only clause you can specify in this statement is the ADD clause.

  See Also: Oracle9i Database Administrator's Guide for information on Automatic Undo Management and undo tablespaces

datafile_tempfile_clauses

The tablespace file clauses let you add or modify a datafile or tempfile.

ADD DATAFILE | TEMPFILE Clause

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.

Restriction: For locally managed temporary tablespaces, this is the only clause you can specify at any time.

Note: On some operating systems, Oracle does not allocate space for the tempfile until the tempfile blocks are actually accessed. This delay in space allocation results in faster creation and resizing of tempfiles, but it requires that sufficient disk space is available when the tempfiles are later used. Please refer to your operating system documentation to determine whether Oracle allocates tempfile space in this way on your system.

See Also: filespec

RENAME DATAFILE Clause

Specify RENAME DATAFILE to rename one or more of the tablespace's datafiles. The database must be open, and you must take the tablespace offline before renaming it. 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.

DATAFILE | TEMPFILE ONLINE | OFFLINE

Use this clause to take all datafiles or tempfiles in the tablespace offline or put them online. This clause has no effect on the ONLINE/OFFLINE status of the tablespace.

The database must be mounted. If tablespace is SYSTEM, or an undo tablespace, or the default temporary tablespace, the database must not be open.

autoextend_clause

Note: This clause is valid only when you are adding a new datafile or tempfile. To enable or disable AUTOEXTEND for an existing datafile, use the ALTER DATABASE DATAFILE clause.

Use the autoextend_clause to enable or disable the automatic extension of a new datafile or tempfile. If you do not specify this clause, these files are not automatically extended.

ON

Specify ON to enable autoextend.

OFF

Specify OFF to turn off autoextend if is turned on.

Note: When you turn off autoextend, the values of NEXT and MAXSIZE are set to zero. If you turn autoextend back on in a subsequent statement, you must reset these values.

NEXT

Use the NEXT clause to specify the size in bytes of the next increment of disk space to be allocated automatically when more extents are required. Use K or M to specify this size in kilobytes or megabytes. The default is the size of one data block.

MAXSIZE

Use the MAXSIZE clause to specify the maximum disk space allowed for automatic extension of the datafile.

UNLIMITED

Use the UNLIMITED clause if you do not want to limit the disk space that Oracle can allocate to the datafile or tempfile.

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 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: Oracle9i Database Administrator's Guide for more information about using MINIMUM EXTENT to control space fragmentation

ONLINE

Specify ONLINE to bring the tablespace online.

OFFLINE

Specify OFFLINE to take the tablespace offline and prevent further access to its segments. When you take a tablespace offline, all of its datafiles are also offline.

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 ALTER USER for more information on allocating tablespace quota to users.

Restriction: You cannot take a temporary tablespace offline.

NORMAL

Specify NORMAL to flush all blocks in all datafiles in the tablespace out of the SGA. You need not perform media recovery on this tablespace before bringing it back online. This is the default.

TEMPORARY

If you specify TEMPORARY, Oracle 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

If you specify IMMEDIATE, Oracle 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.

Note: The FOR RECOVER setting for ALTER TABLESPACE ... OFFLINE has been deprecated. The syntax is supported for backward compatibility. However, users are encouraged to use the transportable tablespaces feature for tablespace recovery.

See Also: Oracle9i User-Managed Backup and Recovery Guide for information on using transportable tablespaces to perform media recovery

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 online backup of the tablespace is complete. Use this clause as soon as possible after completing an online backup. Otherwise, if 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.

Restriction: You cannot use this clause on a read-only tablespace.

See Also: Oracle9i Database Administrator's Guide for information on restarting the database without media recovery ALTER DATABASE END BACKUP Clause for information on taking individual datafiles (or all datafiles in the tablespace) out of online ("hot") backup mode.

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: Oracle9i Database 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.

Restriction: If tablespace was not created with a standard block size, you cannot change it from permanent to temporary.

COALESCE

For each datafile in the tablespace, this clause combines 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-path 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 back it up 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. When more space is needed, new extents of size 10 kilobytes will be added up to a maximum of 100 kilobytes:

ALTER TABLESPACE tab_space2 
    ADD DATAFILE 'diska:tabspace_file3.dat'
    SIZE 50K
    AUTOEXTEND ON
    NEXT 10K
    MAXSIZE 100K;

Changing Logging Attribute Example

The following example changes the default logging attribute of a tablespace to NOLOGGING:

ALTER TABLESPACE tab_space2 NOLOGGING;

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;

Adding an Oracle-managed Datafile Example

The following example adds an Oracle-managed datafile to the omf_ts1 tablespace (see "Oracle-managed Files Examples" for the creation of this tablespace). The new datafile is 100M and is autoextensible with unlimited maximum size:

ALTER TABLESPACE omf_ts1 ADD DATAFILE;