Create and modify temporary tablespace of Oralce

Create and modify temporary tablespace of Oralce

A temporary tablespace is a disk space used to store the temporary data generated when you execute the order by lamp statement for sorting or summarizing. By default, all users use temp as the default temporary tablespace. However, other temporary tablespace can be used as the default temporary tablespace, which must be specified when the user is created.

When creating a temporary tablespace, you must use the temprary keyword. The temporary tablespace corresponds to a temporary file, which is specified by the tempfile keyword. That is to say, data files are no longer used in the temporary tablespace, but temporary files are used.

As follows:
SQL> create temporary tablespace mytemp tempfile 'f: \ Oracledata \ mytemp. dbf' size 5 m autoextend on next 2 m maxsize 20 m;

The tablespace has been created.
In this case, you can find the mytemp. dbf file in the oracledata directory of drive F.
SQL> select tablespace_name, status from dba_tablespaces;

TABLESPACE_NAME STATUS
---------------------------------------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
TEMPGROUP ONLINE
TEMPGROUP02 ONLINE
MYBIGSPACE ONLINE
BLOCKSPACE ONLINE
ONLINE
TESTSPACE ONLINE
TEST ONLINE

MYTEMP ONLINE

12 rows have been selected.

In this case, we can find the MYTEMP tablespace in our tablespace.

Modify temporary tablespace
After creating a temporary tablespace, you can modify the tablespace. Because temporary files do not store permanent data and temporary data generated during operations such as knowledge storage sorting, the data stored in the temporary files is deleted by the system after the User Creation operation is complete, therefore, you do not need to adjust the temporary tablespace. However, when there are a large number of users and complicated operations, the temporary tablespace may be insufficient. In this case, the data administrator can add temporary files to increase the temporary tablespace.
To add or remove temporary files, use the add tempfile clause.
To modify the size of a temporary file, you can use the resize keyword.
You can also change the Temporary File status to offline or online.

SQL> select file #, status, name from v $ tempfile;

FILE # STATUS NAME
Bytes -----------------------------------------------------------------------------------------------
1 online d: \ ORACLE \ ORADATA \ ORACLE12C \ TEMP01.DBF
2 online d: \ ORACLE \ ORADATA \ ORACLE12C \ PDBSEED \ PDBSEED_TEMP012015-01-03_05-20-33-PM.DBF
3 online d: \ ORACLE \ ORADATA \ ORACLE12C \ PDBORCL \ PDBORCL_TEMP012015-01-03_05-44-35-PM.DBF
4 online f: \ ORACLEDATA \ MYTEMP. DBF
6 online f: \ ORACLEDATA \ TEMPGROUP01.DBF
7 online f: \ ORACLEDATA \ TEMPGROUP02.DBF

You have selected 6 rows.

SQL> alter tablespace mytemp add tempfile 'f: \ oracledata \ mytemp2.dbf' size 10 m;

The tablespace has been changed.

SQL> select file #, status, name from v $ tempfile;

FILE # STATUS NAME
Bytes ----------------------------------------------------------------------------------------------
1 online d: \ ORACLE \ ORADATA \ ORACLE12C \ TEMP01.DBF
2 online d: \ ORACLE \ ORADATA \ ORACLE12C \ PDBSEED \ PDBSEED_TEMP012015-01-03_05-20-33-PM.DBF
3 online d: \ ORACLE \ ORADATA \ ORACLE12C \ PDBORCL \ PDBORCL_TEMP012015-01-03_05-44-35-PM.DBF
4 online f: \ ORACLEDATA \ MYTEMP. DBF
5 online f: \ ORACLEDATA \ MYTEMP2.DBF
6 online f: \ ORACLEDATA \ TEMPGROUP01.DBF
7 online f: \ ORACLEDATA \ TEMPGROUP02.DBF

As shown above, a mytemp2.dbf file is generated under the oracledata folder of drive F.

The above articles are from the learning records.

How to handle Undo tablespace failures

Oracle Undo tablespace reconstruction and restoration

Oracle 11g installation manual on RedHat Linux 5.8 _ x64 Platform

Installing Oracle 12C in Linux-6-64

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian