Set Default Tablespace and Temporary Tablespace for the database

 Set Default Tablespace and Temporary Tablespace

ALTER DATABASE
(1) Required Privileges
You must have the ALTER DATABASE system privilege.
To specify the RECOVER clause, you must also have the SYSDBA system privilege.

(2) Setting the Default Type of Tablespaces:

Example

The following statement specifies that subsequently created tablespaces be created as bigfile tablespaces by default:

ALTER DATABASE SET DEFAULT BIGFILE TABLESPACE;

Changing the Default Temporary Tablespace:

Examples

The following statement makes the tbs_5 tablespace:

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE tbs_05;

Alternatively, a group of tablespaces can be defined as the default temporary tablespace by using a tablespace group. The following statement makes the tablespaces in the tablespace group tbs_group_01 (created in "Adding a Temporary Tablespace to a Tablespace Group: Example") the default temporary tablespaces of the database:

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE tbs_grp_01;