Oracle ALTER TABLESPACE operator

The ALTER TABLESPACE operator is used to change the table space or one of its data files or temporary files. TABLESPACE is used to allocate space in an Oracle database where schema objects are stored.

Syntax for ALTER TABLESPACE operator in Oracle / PLSQL

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 | ] ] [ K | M | G | T | P | E ] ] ?
}
| FLASHBACK { ON | OFF }
| RETENTION { GUARANTEE | NOGUARANTEE }
} ;

Parameters and arguments of ALTER TABLESPACE operator

• tablespace_name – the name of the tabular space to be deleted from the Oracle database.
• storage_clause – the syntax for the file storage_clause:

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 [ 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 [ K | M | G | T | P | E ] | NULL ]
| BUFFER_POOL { KEEP | RECYCLE | DEFAULT }
]
)

• file_specification – syntax for file_specification parameter:

{ [ '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 | ] ] [ 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: Renaming a data file

Consider the ALTER TABLESPACE operator, which renames the data file associated with the table space. For example:

ALTER TABLESPACE tbs_01 OFFLINE NORMAL;

ALTER TABLESPACE tbs_01
RENAME DATAFILE 'tbs_01.dat'
TO 'tbs_01_new.dat';
ALTER TABLESPACE tbs_01 ONLINE;

This ALTER TABLESPACE operator will take the table space in OFFLINE mode, and rename the data file from tb_01.dat to tb_01_new.dat, and then bring the table space back to ONLINE.

Example: Add a data file

Let’s consider the ALTER TABLESPACE operator, which adds a data file to the table space.
For example:

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

This ALTER TABLESPACE operator adds the data file tbs_02.dat to the table space tbs_02.

Example: The TABLESPACE operator adds the data file tbs_02.dat to the table space tbs_02

Consider the ALTER TABLESPACE operator, which removes the data file from the table space. For example:

ALTER TABLESPACE tbs_03
DROP DATAFILE 'tbs_03.dat';

This ALTER TABLESPACE operator removes the data file tbs_03.dat in the table space tbs_03.

Example of adding Tempfile

Let’s consider the ALTER TABLESPACE operator, which adds a temporary file to the table space. For example:

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

This example of the ALTER TABLESPACE operator adds a temporary tbs_04.dat file to the tbs_04 table space.

Example of removing Tempfile

Let’s consider an example of the ALTER TABLESPACE operator, which removes a temporary file from the table space. For example:

ALTER TABLESPACE tbs_05
DROP TEMPFILE 'tbs_05.dat';

This example of the ALTER TABLESPACE operator will delete the temporary file tbs_05.dat in the table space tbs_05.

Alter Tablespace in Oracle