A temporary tablespace contains data that persists only for the duration of the session.
Temporary tablespaces can improve the concurrency of multiple sort operations, reduce their overhead, and avoid Oracle Database space management operations. A temporary tablespace cannot contain permanent objects and therefore no need to back up. Ensure that you create your temporary tablespaces as locally managed instead of dictionary managed.
Q: Why we need Temp tablespace?
For example, If you join two large tables, and Oracle cannot do the sort in memory (see SORT_AREA_SIZE initialisation parameter), space will be allocated in a temporary tablespace for doing the sort operation. Other SQL operations that might require disk sorting are: CREATE INDEX, ANALYZE, Select DISTINCT, ORDER BY, GROUP BY, UNION, INTERSECT, MINUS, Sort-Merge joins, etc.
Assign temporary tablespce to a user:
SQL> CREATE USER prd DEFAULT TABLESPACE data TEMPORARY TABLESPACE temp;
SQL> ALTER USER prd TEMPORARY TABLESPACE temp;
TEMPORARY TABLESPACE Creation:
CREATE TEMPORARY TABLESPACE temp
TEMPFILE '/oradata/mytemp_01.tmp' SIZE 20M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M;
For best performance, the UNIFORM SIZE must be a multiple of the SORT_AREA_SIZE parameter.
TEMPFILES:
DROP: ALTER DATABASE TEMPFILE '/oradata/temp02.dbf' DROP INCLUDING DATAFILES;
If you remove all tempfiles from temp tablespace, you may encounter error: ORA-25153: Temporary Tablespace is Empty. Then u have to add a TEMPFILE:
ADD: ALTER TABLESPACE temp ADD TEMPFILE '/oradata/temp03.dbf' SIZE 100M;
MONITORING:
Tempfile: V$TEMPFILE, DBA_TEMP_FILES.
Who occupies the spaceiews: v$sort_usage.
Temporary segments: v$sort_segment
DBA_FREE_SPACE doesn't record free space for temp tablespaces. Use V$TEMP_SPACE_HEADER:
SQL> select TABLESPACE_NAME, BYTES_USED, BYTES_FREE from V$TEMP_SPACE_HEADER;
DEFAULT TEMP TABLESPACE:
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;
Q: Find out the default temp tablespace for a database?
SQL> SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';
The following restrictions apply to default temporary tablespaces:
The Default Temporary Tablespace must be of type TEMPORARY
The DEFAULT TEMPORARY TABLESPACE cannot be taken off-line
The DEFAULT TEMPORARY TABLESPACE cannot be dropped until you create another one.
All new users that are not explicitly assigned a TEMPORARY TABLESPACE, will get the Default Temporary Tablespace as its TEMPORARY TABLESPACE.
DROP Default Temporary Tablespace:
SQL> DROP TABLESPACE temp;
drop tablespace temp
*
ERROR at line 1:
ORA-12906: cannot drop default temporary tablespace
In cases where the temporary tablespace you want to resize (using the drop/recreate method) is the default temporary tablespace for the database, you have several more steps to perform.
Step-1:
The first step you need to perform is create another temporary tablespace (lets call it TEMP2).
SQL> CREATE TEMPORARY TABLESPACE temp2
TEMPFILE '/u02/oradata/TESTDB/temp2_01.dbf' SIZE 5M REUSE
AUTOEXTEND ON NEXT 1M MAXSIZE unlimited
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
Step-2:
Next step is making TEMP2 the default temporary tablespace for the database.
SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;
Step-3:
Drop / recreate the TEMP tablespace to the size you want.
SQL> DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;
SQL> CREATE TEMPORARY TABLESPACE temp
TEMPFILE '/u02/oradata/TESTDB/temp01.dbf' SIZE 500M REUSE
AUTOEXTEND ON NEXT 100M MAXSIZE unlimited
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
Step-4:
Finally, make the newly created TEMP tablespace your default temporary tablespace for the database and drop the TEMP2 tablespace.
SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;
SQL> DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;
Problem-1: I had a temporary tablepsace TEMP and there no space left on that tablespace.
Solution:
Create a new tablespace:
CREATE TEMPORARY TABLESPACE temp01
TEMPFILE '/dump/TEMP_FILE/temp01.dbf' SIZE 500M REUSE
AUTOEXTEND ON NEXT 100M MAXSIZE unlimited
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
Making the new tablespace default:
SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp01;
Remove all the files other than the default file:
SQL> select file_name from dba_temp_files;
FILE_NAME
-----------------------------------------------
/oracle/temp_file/temp02_02.dbf
/oracle/temp_file/temp2_01.dbf
/dump/TEMP_FILE/temp01.dbf
SQL> ALTER DATABASE TEMPFILE '/oracle/temp_file/temp2_01.dbf' DROP INCLUDING DATAFILES;
SQL> ALTER DATABASE TEMPFILE '/oracle/temp_file/temp02_02.dbf' DROP INCLUDING DATAFILES;
ALTER DATABASE TEMPFILE '/oracle/temp_file/temp02_02.dbf' DROP INCLUDING DATAFILES
*
ERROR at line 1:
ORA-25152: TEMPFILE cannot be dropped at this time
Reason- Some users are working with this temp file, so it is not deleting.
bash-3.00$ rm temp02_02.dbf
SQL> shutdown immediate;
SQL> startup
