Normally, we don't create additional temporary tablespace, because there's already a default one. For some reason, we need to create one for assigning the temporary tablespace to some users.
In this post, let me show you some quick and easy examples that create a temporary tablespace.
Single TempFile
It contains only one tempfile when creating the temporary tablespace.
SQL> create temporary tablespace temp1 tempfile '/u01/app/oracle/oradata/ORCL/temp1_01.dbf' size 10m autoextend on next 10m maxsize unlimited;
Tablespace created.
Multiple TempFiles
We repeated the file specification delimiting by commas when creating the temporary tablespace.
SQL> create temporary tablespace temp1 tempfile '/u01/app/oracle/oradata/ORCL/temp1_01.dbf' size 10m autoextend on next 10m maxsize unlimited, '/u01/app/oracle/oradata/ORCL/temp1_02.dbf' size 10m autoextend on next 10m maxsize unlimited, '/u01/app/oracle/oradata/ORCL/temp1_03.dbf' size 10m autoextend on next 10m maxsize unlimited;
Tablespace created.
In this case, we initially use 3 tempfiles for the tablespace creation.
Reuse TempFile
If the file is existing in the file system physically, you can use REUSE to take over the file.
SQL> create temporary tablespace temp1 tempfile '/u01/app/oracle/oradata/ORCL/temp1_01.dbf' size 10m reuse autoextend on next 10m maxsize unlimited;
Tablespace created.
To better manage a tablespace, you may want to know how big a tablespace can grow.