Oracle/PLSQL: ALTER TABLESPACE statement

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.

The syntax for the ALTER TABLESPACE statement 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 }

  } ;

tablespace_name is the name of the tablespace to remove from the Oracle database.

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 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 ]

}

ALTER TABLESPACE Statement - Example

Rename a DATAFILE - Example

Let's take a 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.

Add a DATAFILE - Example

Let's take a look at an ALTER TABLESPACE statement that addes 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.

Drop a DATAFILE - Example

Let's take a 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.

Add a TEMPFILE - Example

Let's take a look at an ALTER TABLESPACE statement that addes 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.

Drop a TEMPFILE - Example

Let's take a 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.