Create and Alter Tablespace

Check exitsing tablespaces, their type and status -
====================================================
SQL> select tablespace_name, contents, status from dba_tablespaces;

TABLESPACE_NAME            CONTENTS  STATUS
------------------------------ --------- ---------
SYSTEM                   PERMANENT ONLINE
SYSAUX                   PERMANENT ONLINE
UNDO_TBS               UNDO     ONLINE
TEMP                   TEMPORARY ONLINE
USERS                   PERMANENT ONLINE

Creating a new tablespace with two datafile of different sizes -
================================================================
SQL> create tablespace demo_tbs
  2  datafile '/u01/app/oracle/oradata/TOYDB/df/demo_tbs_01.dbf' size 10M,
  3  '/u01/app/oracle/oradata/TOYDB/df/demo_tbs_02.dbf' size 5M;

Tablespace created.

SQL> select tablespace_name, contents, status from dba_tablespaces;

TABLESPACE_NAME            CONTENTS  STATUS
------------------------------ --------- ---------
SYSTEM                   PERMANENT ONLINE
SYSAUX                   PERMANENT ONLINE
UNDO_TBS               UNDO     ONLINE
TEMP                   TEMPORARY ONLINE
USERS                   PERMANENT ONLINE
DEMO_TBS               PERMANENT ONLINE

6 rows selected.

Renaming a tablespace -
=======================
SQL> alter tablespace demo_tbs rename to renamed_tbs;

Tablespace altered.

Renaming the datafiles -
=========================
Renaming datafiles includes two parts: rename on operating system level and renaming on database level. Rename operation on OS level needs physical rename of the datafile. For that we can use rename command on windows and mv command on linux/unix environment.

[oracle@localhost ~]$ cd $ORACLE_BASE/oradata
[oracle@localhost oradata]$ cd TOYDB/df
[oracle@localhost df]$ ls -lrt
total 938340
-rw-r-----. 1 oracle oinstall 209723392 Aug 25 02:13 users_01.dbf
-rw-r-----. 1 oracle oinstall 209723392 Aug 25 02:14 temp_01.dbf
-rw-r-----. 1 oracle oinstall 104865792 Aug 25 02:19 undo_tbs_01.dbf
-rw-r-----. 1 oracle oinstall 209723392 Aug 25 02:19 sysaux_01.dbf
-rw-r-----. 1 oracle oinstall 419438592 Aug 25 02:19 systems_01.dbf
-rw-r-----. 1 oracle oinstall  10493952 Aug 25 02:20 demo_tbs_01.dbf
-rw-r-----. 1 oracle oinstall   5251072 Aug 25 02:20 demo_tbs_02.dbf
[oracle@localhost df]$ mv demo_tbs_01.dbf renamed_tbs_01.dbf
[oracle@localhost df]$ mv demo_tbs_02.dbf renamed_tbs_02.dbf
[oracle@localhost df]$ ls -lrt
total 938340
-rw-r-----. 1 oracle oinstall 209723392 Aug 25 02:13 users_01.dbf
-rw-r-----. 1 oracle oinstall 209723392 Aug 25 02:14 temp_01.dbf
-rw-r-----. 1 oracle oinstall 104865792 Aug 25 02:19 undo_tbs_01.dbf
-rw-r-----. 1 oracle oinstall 209723392 Aug 25 02:19 sysaux_01.dbf
-rw-r-----. 1 oracle oinstall 419438592 Aug 25 02:19 systems_01.dbf
-rw-r-----. 1 oracle oinstall  10493952 Aug 25 02:20 renamed_tbs_01.dbf
-rw-r-----. 1 oracle oinstall   5251072 Aug 25 02:20 renamed_tbs_02.dbf
[oracle@localhost df]$ exit


Renaming the datadile on database level includes alter database command. We need to fire this once for each file. Please note that even though the datafiles are now renamed on OS level, at database level we still hold older names of these datafiles.

SQL> select file_name, tablespace_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME
------------------------------
/u01/app/oracle/oradata/TOYDB/df/systems_01.dbf
SYSTEM

/u01/app/oracle/oradata/TOYDB/df/sysaux_01.dbf
SYSAUX

/u01/app/oracle/oradata/TOYDB/df/undo_tbs_01.dbf
UNDO_TBS


FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME
------------------------------
/u01/app/oracle/oradata/TOYDB/df/users_01.dbf
USERS

/u01/app/oracle/oradata/TOYDB/df/demo_tbs_01.dbf
RENAMED_TBS

/u01/app/oracle/oradata/TOYDB/df/demo_tbs_02.dbf
RENAMED_TBS


6 rows selected.



After rename done on OS level we have to rename those datafiles on database levels else at runtime whenever any object belonging to these renamed datafiles
is referred, an error will be thrown. To rename datafile take tablespace offline.



SQL> alter database rename file '/u01/app/oracle/oradata/TOYDB/df/demo_tbs_01.dbf' to '/u01/app/oracle/oradata/TOYDB/df/renamed_tbs_01.dbf';

Database altered.

SQL> alter database rename file '/u01/app/oracle/oradata/TOYDB/df/demo_tbs_02.dbf' to '/u01/app/oracle/oradata/TOYDB/df/renamed_tbs_02.dbf';

Database altered.

SQL> select file_name, tablespace_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME
------------------------------
/u01/app/oracle/oradata/TOYDB/df/systems_01.dbf
SYSTEM

/u01/app/oracle/oradata/TOYDB/df/sysaux_01.dbf
SYSAUX

/u01/app/oracle/oradata/TOYDB/df/undo_tbs_01.dbf
UNDO_TBS


FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME
------------------------------
/u01/app/oracle/oradata/TOYDB/df/users_01.dbf
USERS

/u01/app/oracle/oradata/TOYDB/df/renamed_tbs_01.dbf
RENAMED_TBS

/u01/app/oracle/oradata/TOYDB/df/renamed_tbs_02.dbf
RENAMED_TBS


6 rows selected.


Taking tablespace online -
==========================

SQL> alter tablespace renamed_tbs online;

Tablespace altered.

SQL> select tablespace_name, contents, status from dba_tablespaces;

TABLESPACE_NAME            CONTENTS  STATUS
------------------------------ --------- ---------
SYSTEM                   PERMANENT ONLINE
SYSAUX                   PERMANENT ONLINE
UNDO_TBS               UNDO     ONLINE
TEMP                   TEMPORARY ONLINE
USERS                   PERMANENT ONLINE
RENAMED_TBS               PERMANENT ONLINE

6 rows selected.