Using ALTER TABLESPACE statement in Oracle

This article is written about how to use the Oracle ALTER TABLESPACE statement with syntax and examples.

Description

The ALTER TABLESPACE announcement is used to modify a tablespace or one of its records archives or temp files. A tablespace is used to allocate space in the Oracle database the place 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 identify of the tablespace to cast off 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 seem at an ALTER TABLESPACE assertion 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 declaration would take the tablespace offline, rename the datafile from tbl_perm_01.dat to tbl_perm_01_new.dat, and then convey the tablespace again on-line again.

Example – Add Datafile

Let’s seem to be at an ALTER TABLESPACE announcement that provides 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 appear 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 announcement drops the datafile known as tbs_perm_03.dat to the tbs_perm_03 tablespace.

Example – Add Tempfile

Let’s seem at an ALTER TABLESPACE assertion 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 assertion add the tempfile known as tbs_temp_04.dat to the tbs_temp_04 tablespace.

Example – Drop Tempfile

Let’s seem to be at an ALTER TABLESPACE declaration that drops a tempfile from a tablespace.

For example:

ALTER TABLESPACE tbs_temp_05
 DROP TEMPFILE 'tbs_temp_05.dat';

This ALTER TABLESPACE declaration drops the tempfile referred to as tbs_temp_05.dat to the tbs_temp_05 tablespace.