The DEFAULT TEMPORARY TABLESPACE clause of the CREATE DATABASE statement specifies that a temporary tablespace is to be created at database creation time. This tablespace is used as the default temporary tablespace for users who are not otherwise assigned a temporary tablespace.
Users can be explicitly assigned a default temporary tablespace in the CREATE USER statement. But, if no temporary tablespace is specified, they default to using the SYSTEM tablespace. It is not good practice to store temporary data in the SYSTEM tablespace. To avoid this problem, and to avoid the need to assign every user a default temporary tablespace at CREATE USER time, you can use the DEFAULT TEMPORARY TABLESPACE clause of CREATE DATABASE.
If you decide later to change the default temporary tablespace, or to create an initial one after database creation, you can do so. You do this by creating a new temporary tablespace (CREATE TEMPORARY TABLESPACE), then assign it as the temporary tablespace using the ALTER DATABASE DEFAULT TEMPORARY TABLESPACE statement. Users will automatically be switched (or assigned) to the new temporary default tablespace.
The following statement assigns a new default temporary tablespace:
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE tempts2;
The new default temporary tablespace must be an existing temporary tablespace. When using a locally managed SYSTEM tablespace, the new default temporary tablespace must also be locally managed.
You cannot drop a default temporary tablespace, but you can assign a new default temporary tablespace, then drop the former one. You are not allowed to change a default temporary tablespace to a permanent tablespace, nor can you take a default temporary tablespace offline.
Users can obtain the name of the current default temporary tablespace using the DATABASE_PROPERTIES view. The PROPERTY_NAME column contains the value "DEFAULT_TEMP_TABLESPACE" and the PROPERTY_VALUE column contains the default temporary tablespace name.
Users can be explicitly assigned a default temporary tablespace in the CREATE USER statement. But, if no temporary tablespace is specified, they default to using the SYSTEM tablespace. It is not good practice to store temporary data in the SYSTEM tablespace. To avoid this problem, and to avoid the need to assign every user a default temporary tablespace at CREATE USER time, you can use the DEFAULT TEMPORARY TABLESPACE clause of CREATE DATABASE.
If you decide later to change the default temporary tablespace, or to create an initial one after database creation, you can do so. You do this by creating a new temporary tablespace (CREATE TEMPORARY TABLESPACE), then assign it as the temporary tablespace using the ALTER DATABASE DEFAULT TEMPORARY TABLESPACE statement. Users will automatically be switched (or assigned) to the new temporary default tablespace.
The following statement assigns a new default temporary tablespace:
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE tempts2;
The new default temporary tablespace must be an existing temporary tablespace. When using a locally managed SYSTEM tablespace, the new default temporary tablespace must also be locally managed.
You cannot drop a default temporary tablespace, but you can assign a new default temporary tablespace, then drop the former one. You are not allowed to change a default temporary tablespace to a permanent tablespace, nor can you take a default temporary tablespace offline.
Users can obtain the name of the current default temporary tablespace using the DATABASE_PROPERTIES view. The PROPERTY_NAME column contains the value "DEFAULT_TEMP_TABLESPACE" and the PROPERTY_VALUE column contains the default temporary tablespace name.
Related Post:-
