Managing Temporary Tablespaces in Oracle database 11gR2
Posted by Mir Sayeed Hassan on February 10th, 2018
Managing Temporary Tablespaces in Oracle database 11gR2
The temporary tablespace is basically used for sorting the large tables in database & database should contain 1 temp tbs in it, this temp tbs are created while creation of the database itself, Therefore if it requires you can create multiple temp tbs in the database.
sys@TESTDB> create temporary tablespace temp2 tempfile '/u01/app/oracle/oradata/prim/temp2.dbf' size 10m; Tablespace created.
Or
sys@TESTDB> create temporary tablespace temp3 tempfile '/u01/app/oracle/oradata/prim/temp3.dbf' size 100m extent management local uniform size 10m; Tablespace created.
Note:
Extent management clause is optional for temp tbs because all temp tbs are created with locally managed extents of a uniform size by default and AUTOALLOCATE clause is not allowed for temp tbs in Oracle database.
Increasing o decreasing the size of a Temporary Tablespace
sys@TESTDB> alter database tempfile '/u01/app/oracle/oradata/prim/temp02.dbf' resize 200m; Database altered.
Drops a temporary file and deletes the OS file from disk
sys@TESTDB> alter database tempfile '/u01/app/oracle/oradata/prim/temp02.dbf' drop including datafiles; Database altered.
-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 on
Tablespace Groups
sys@TESTDB> create temporary tablespace temp04 tempfile '/u01/app/oracle/oradata/prim/temp04.dbf' size 50m tablespace group group1; Tablespace created.
sys@TESTDB> alter tablespace temp tablespace group group1; Tablespace altered.
sys@TESTDB> desc dba_tablespace_groups; Name Null? Type ----------------------------------------- -------- ---------------------------- GROUP_NAME NOT NULL VARCHAR2(30) TABLESPACE_NAME NOT NULL VARCHAR2(30)
sys@TESTDB> select * from dba_tablespace_groups; GROUP_NAME TABLESPACE_NAME ------------------------------ -------------------------- GROUP1 TEMP GROUP1 TEMP04
Assigning a Tablespace Group as the Default Temporary Tablespace
Syntax: ALTER DATABASE DEFAULT TEMPORARY TABLESPACE group2;
To view information about Temporary Tablespaces and Tempfiles
SQL> select * from dba_temp_files; SQL> select * from v$tempfile;
To view information about free space in tempfiles
SQL> select * from V$TEMP_SPACE_HEADER;