Oracle Temp Table Space

Temporal table Space Concepts
Temporary tablespaces are used to manage database sorting operations and temporary objects such as temporary tables, intermediate sort results, and so on, when the sort is needed in Oracle, and when the sort_area_size size in the PGA is insufficient, the data is placed in a temporary table space for sorting. Like some operations in the database: CREATE INDEX, ANALYZE, SELECT DISTINCT, ORDER by, GROUP by, UNION all, INTERSECT, minus, Sort-merge JOINS, HASH JOIN The temporary table space may be used. When the operation is complete, the temporary object in the temporary tablespace is automatically cleaned up and the temporary segment is automatically freed. The release here is simply marked as free, reusable, and the actual disk space consumed is not actually released. This is also why temporary table spaces can sometimes grow.
Staging table space stores large-scale sorting operations (small-scale sorting operations are done directly in RAM, large-scale sorting requires disk sort), and intermediate results for hashing operations. It differs from the permanent table space in that it consists of a temporary data file (temporary files), Instead of a permanent data file (datafiles). Temporary tablespace does not store persistent types of objects, so it does not and does not need to be backed up. Additionally, the operation of the temporary data file does not produce a redo log, but an undo log is generated.
When you create a temporary tablespace or temporary tablespace to add temporary data files, even though the temporary data files are large, the add process is fairly fast. This is because Oracle's temporary data file is a special kind of data file: A sparse file (Sparse files) that, when created by a temporary tablespace file, only writes the file header and the last block of information (only writes The file). Its space is deferred. This is why you create temporary table spaces or add data files to a temporary tablespace.
In addition, the temporal tablespace is nologging mode and it does not save persistent type objects, so even if the database is corrupted, doing recovery does not need to restore temporary tablespace.
Temporary tablespace information

To view the temporary tablespace for an instance
SQL1:
Sql> SELECT property_name, property_value from database_properties WHERE property_name= ' Default_temp_tablespace '
SQL2:
SELECT USERNAME, temporary_tablespace from Dba_users;

To view temporal tablespace information:

Sql> SELECT bytes,blocks, User_bytes, User_blocks, blocks-user_blocks as system_used from Dba_temp_files;

In these four columns, BYTES, BLOCKS shows how many byte sizes are in the temporary file, and how many blocks of data are included. The user_bytes,user_blocks is the number of bytes and data blocks available. Therefore, we can know that a part of the temporary file is occupied by the system, presumably can be understood as the file header information, this part size is 128 blocks.

Managing temporary table Spaces

Create a temporary table space
CREATE temporary tablespace TMP tempfile '/u01/gsp/oradata/tmp01.dbf ' SIZE 8G autoextend OFF;

Add data files
When the temporary table space is too small, you need to extend the temporary tablespace (add data file, increase data file, set file Auto-expand), sometimes you need to distribute temporary data files to different disk partitions, improve IO performance, and also need to delete, add temporary tablespace data files.

Sql> ALTER tablespace TEMP 2 ADD tempfile '/u04/gsp/oradata/temp02.dbf ' 3 SIZE 4G 4 autoextend on 5 NEXT 128M 6 MAXSIZE 6G;

sql> ALTER tablespace tmpadd tempfile '/u03/eps/oradata/temp02.dbf ' SIZE 64G autoextend OFF;

Delete a data file
For example, if I want to delete a file under a temporary table space, we have two ways to delete the data file for the temporary tablespace.
Method 1:
sql> ALTER tablespace TEMP DROP tempfile '/u01/app/oracle/oradata/gsp/temp02.dbf ';
Note: This deletion of the temporary table space will remove the corresponding physical file.
Method 2:
sql> ALTER DATABASE tempfile '/u01/app/oracle/oradata/gsp/temp02.dbf ' DROP including datafiles;
Note: When you delete a temporary data file for a temporary tablespace, you do not need to specify the including datafiles option to actually delete the physical file.

Sizing files
For example, the temporary data file needs to be resized from 1G to 2G
sql> ALTER DATABASE tempfile '/u01/app/oracle/oradata/gsp/temp02.dbf ' RESIZE 2G;

File Offline online
sql> ALTER DATABASE TEMPFILE2 '/u01/app/oracle/oradata/gsp/temp02.dbf ' OFFLINE;

sql> ALTER DATABASE TEMPFILE2 '/u01/app/oracle/oradata/gsp/temp02.dbf ' ONLINE;

The default temporary tablespace does not go offline, otherwise it will error, as shown below
sql> ALTER tablespace TEMP OFFLINE;
ALTER tablespace TEMP OFFLINE
*
ERROR at line 1:
Ora-03217:invalid option for alter of temporary tablespace

Set File Auto-expansion
sql> ALTER DATABASE tempfile '/u01/app/oracle/oradata/gsp/temp03.dbf ' autoextend on NEXT 100M MAXSIZE UNLIMITED;

Move Rename File
For example, I need to rename the/u01/app/oracle/oradata/gsp/temp4.dbf file to/u01/app/oracle/oradata/gsp/temp04.dbf
1: Take temporary file for temporary tablespace offline
Sql> ALTER DATABASE tempfile 2 '/u01/app/oracle/oradata/gsp/temp4.dbf ' OFFLINE;
2: Move or rename the relevant temporary file
MV/U01/APP/ORACLE/ORADATA/GSP/TEMP4.DBF/U01/APP/ORACLE/ORADATA/GSP/TEMP04.DBF '
3: Use the script alter DATABASE RENAME FILE
sql> ALTER DATABASE RENAME FILE '/u01/app/oracle/oradata/gsp/temp4.dbf ' to '/u01/app/oracle/oradata/gsp/temp04.dbf ‘;
4: Bring temporary file for temporary tablespace online
sql> ALTER DATABASE tempfile '/u01/app/oracle/oradata/gsp/temp04.dbf ' ONLINE;
To delete a temporary table space
Sql> DROP tablespace TEMP including CONTENTS and datafiles CASCADE CONSTRAINTS;
Note: You cannot delete the default tablespace for the current user, or you will report a ORA-12906 error
Sql> DROP tablespace TMP including CONTENTS and datafiles CASCADE CONSTRAINTS; Drop tablespace TMP including CONTENTS and datafiles CASCADE CONSTRAINTS * ERROR at line 1:ora-12906:cannot drop default Temporary tablespace
If you need to delete a default temporary tablespace, you must first create a temporary tablespace, and then specify the newly created tablespace as the default tablespace, and then delete the original temporary table space

Temporary table space groups
Pro Table Space Group:
The Pro Table Space Group is a new feature introduced by Oracle 10g, which is a logical concept that does not require display creation and deletion. As long as a temporary tablespace is assigned to a group, the temporary Tablespace group is automatically created, and all temporary table spaces are removed from the temporary tablespace group automatically.
A temporary tablespace group must consist of at least one temporary tablespace and there is no explicit maximum limit.
A Temporary tablespace group contains at least one tablespace. There is no limit for a group to has a maximum number of tablespaces
If you delete all members of a temporary tablespace group, the group is also automatically deleted.
The name of a temporary tablespace cannot be the same as the name of a temporary table space group.
It shares the namespace of tablespaces, thus its name cannot is the same as that for any tablespace.
You can specify a table space group when you create a temporary tablespace, which is implicitly created.
Sql>create temporary tablespace TEMP2 tempfile '/u01/app/oracle/oradata/gsp/temp2_1.dbf ' SIZE 200M TABLESPACE GROUP Grp_temp;
To view the staging tablespace group:
Sql> SELECT * from dba_tablespace_groups
You can also specify a temporary tablespace group that has been created for a temporary table space.
sql> ALTER tablespace TEMP tablespace GROUP grp_temp;

Sql> select * from Dba_tablespace_groups;
To remove from a group:
sql> ALTER tablespace TEMP tablespace GROUP ';
You can use the name of a temporary tablespace group when you specify a temporary table space for a database or specify a temporary tablespace for a user
ALTER USER DM temporary tablespace grp_temp;

Toggle temporary table Space

1: View old temporary tablespace information
SELECT * from V$tempfile
SELECT USERNAME, temporary_tablespace from Dba_users
2: Create interim tablespace for staging
3: Add the appropriate data file
4: Toggle temporary table space.
ALTER DATABASE DEFAULT temporary tablespace TMP;
5: Delete old temporal tablespace data files
DROP tablespace TEMP including CONTENTS and datafiles;
6: If necessary, re-specify the user temp tablespace as a new temporary table space
ALTER USER ODS temporary tablespace TMP;

ALTER USER EDS temporary tablespace TMP;

ALTER USER ETL temporary tablespace TMP;

ALTER USER DM temporary tablespace TMP;

Shrinking temporary table spaces
The temporary segments used by operations such as sorting, which are marked as idle when used, indicate that they can be reused, that the space occupied is not immediately released, and that temporary table space can become very large, which frees up unused space by shrinking the temporary table space. Shrinking temporary tablespace is a new feature of Oracle 11g.
sql> ALTER tablespace TEMP SHRINK SPACE KEEP 8G;

sql> ALTER tablespace TEMP SHRINK tempfile '/u01/app/oracle/oradata/gsp/temp02.dbf '

Monitoring temporary table space
To view the usage of temporary files that correspond to temporary table spaces

SELECT Tablespace_name as Tablespace_name,
bytes_used/1024/1024/1024 as tablesapce_used,
bytes_free/1024/1024/1024 as Tablesapce_free from V$temp_space_header ORDER by 1 DESC;

Finding SQL statements that consume more temporal tablespace resources

SELECT Se.username,
Se.sid,
Su.extents,
Su.blocks * To_number (RTrim (P.value)) as Space,
Tablespace,
Segtype,
Sql_text
From V$sort_usage Su, v$parameter p, v$session se, v$sql s
WHERE p.name = ' db_block_size '
and su.session_addr = Se.saddr
and S.hash_value = Su.sqlhash
and s.address = Su.sqladdr
ORDER by Se.username, Se.sid;

Oracle Temp Table Space