Oracle / PLSQL: ALTER TABLESPACE statement

This Oracle tutorial explains how to use the Oracle ALTER TABLESPACE statement with syntax and examples.

Description

The ALTER TABLESPACE statement is used to modify a tablespace or one of its data files or temp files. A tablespace is used to allocate space in the Oracle database where schema objects are stored.

Syntax

The syntax for the ALTER TABLESPACE statement in Oracle/PLSQL is:

ALTER TABLESPACE tablespace_name
  { DEFAULT
     [ { COMPRESS | NOCOMPRESS } ] storage_clause
  | MINIMUM EXTENT integer [ K | M | G | T | P | E ]
  | RESIZE integer [ K | M | G | T | P | E ]
  | COALESCE
  | RENAME TO new_tablespace_name
  | { BEGIN | END } BACKUP
  | { ADD { DATAFILE | TEMPFILE }
       [ file_specification
          [, file_specification ]
       ]
    | DROP {DATAFILE | TEMPFILE } { 'filename' | file_number }
    | RENAME DATAFILE 'filename' [, 'filename' ] TO 'filename' [, 'filename' ]
    | { DATAFILE | TEMPFILE } { ONLINE | OFFLINE }
    }
  | { logging_clause | [ NO ] FORCE LOGGING }
  | TABLESPACE GROUP { tablespace_group_name | '' }
  | { ONLINE
    | OFFLINE [ NORMAL | TEMPORARY | IMMEDIATE ]
    }
    | READ { ONLY | WRITE }
    | { PERMANENT | TEMPORARY }
  | AUTOEXTEND
     { OFF
     | ON [ NEXT integer [ K | M | G | T | P | E ] ]
        [ MAXSIZE { UNLIMITED | integer [ K | M | G | T | P | E ] } ]
     }
  | FLASHBACK { ON | OFF }
  | RETENTION { GUARANTEE | NOGUARANTEE }
  } ;

Parameters or Arguments

tablespace_name

The name of the tablespace to remove from the Oracle database.

storage_clause

The syntax for the the storage_clause is:

STORAGE
   ({ INITIAL integer [ K | M | G | T | P | E ]
    | NEXT integer [ K | M | G | T | P | E ]
    | MINEXTENTS integer
    | MAXEXTENTS { integer | UNLIMITED }
    | PCTINCREASE integer
    | FREELISTS integer
    | FREELIST GROUPS integer
    | OPTIMAL [ integer [ K | M | G | T | P | E ] | NULL ]
    | BUFFER_POOL { KEEP | RECYCLE | DEFAULT }
    }
       [ INITIAL integer [ K | M | G | T | P | E ]
       | NEXT integer [ K | M | G | T | P | E ]
       | MINEXTENTS integer
       | MAXEXTENTS { integer | UNLIMITED }
       | PCTINCREASE integer
       | FREELISTS integer
       | FREELIST GROUPS integer
       | OPTIMAL [ integer [ K | M | G | T | P | E ] | NULL ]
       | BUFFER_POOL { KEEP | RECYCLE | DEFAULT }
       ]
   )

file_specification

The syntax for the file_specification is:

{ [ 'filename' | 'ASM_filename' ]
  [ SIZE integer [ K | M | G | T | P | E ] ]
  [ REUSE ]
  [ AUTOEXTEND
      { OFF
      | ON [ NEXT integer [ K | M | G | T | P | E ] ]
      [ MAXSIZE { UNLIMITED | integer [ K | M | G | T | P | E ] } ]
      }
  ]
| [ 'filename | ASM_filename'
| ('filename | ASM_filename'
    [, 'filename | ASM_filename' ] )
]
[ SIZE integer [ K | M | G | T | P | E ] ]
[ REUSE ]
}

Example - Rename Datafile

Let's look at an ALTER TABLESPACE statement that renames a datafile associated with a tablespace.

For example:

ALTER TABLESPACE tbs_perm_01 OFFLINE NORMAL;

ALTER TABLESPACE tbs_perm_01
  RENAME DATAFILE 'tbs_perm_01.dat'
  TO 'tbs_perm_01_new.dat';

ALTER TABLESPACE tbs_perm_01 ONLINE;

This ALTER TABLESPACE statement would take the tablespace offline, rename the datafile from tbl_perm_01.dat to tbl_perm_01_new.dat, and then bring the tablespace back online again.

Example - Add Datafile

Let's look at an ALTER TABLESPACE statement that adds a datafile to a tablespace.

For example:

ALTER TABLESPACE tbs_perm_02
 ADD DATAFILE 'tbs_perm_02.dat'
   SIZE 20M
   AUTOEXTEND ON;

This ALTER TABLESPACE statement add the datafile called tbs_perm_02.dat to the tbs_perm_02 tablespace.

Example - Drop Datafile

Let's look at an ALTER TABLESPACE statement that drops a datafile from a tablespace.

For example:

ALTER TABLESPACE tbs_perm_03
 DROP DATAFILE 'tbs_perm_03.dat';

This ALTER TABLESPACE statement drops the datafile called tbs_perm_03.dat to the tbs_perm_03 tablespace.

Example - Add Tempfile

Let's look at an ALTER TABLESPACE statement that adds a tempfile to a tablespace.

For example:

ALTER TABLESPACE tbs_temp_04
 ADD TEMPFILE 'tbs_temp_04.dat'
   SIZE 10M
   AUTOEXTEND ON;

This ALTER TABLESPACE statement add the tempfile called tbs_temp_04.dat to the tbs_temp_04 tablespace.

Example - Drop Tempfile

Let's look at an ALTER TABLESPACE statement that drops a tempfile from a tablespace.

For example:

ALTER TABLESPACE tbs_temp_05
 DROP TEMPFILE 'tbs_temp_05.dat';

This ALTER TABLESPACE statement drops the tempfile called tbs_temp_05.dat to the tbs_temp_05 tablespace.