Add, delete, modify, and query temporary Oracle tablespace

1. view temporary tablespace (dba_temp_files view) (v _ $ tempfile view)
Select tablespace_name, file_name, bytes/1024/1024 file_size, autoextensible from dba_temp_files;
Select status, enabled, name, bytes/1024/1024 file_size from v _ $ tempfile; -- View by sys user

2. Reduce the size of the temporary tablespace
Alter database tempfile 'd: \ Oracle \ PRODUCT \ 10.2.0 \ ORADATA \ TELEMT \ TEMP01.DBF 'resize 100 M;

3. extended temporary tablespace:
Method 1: Increase the temporary file size:
SQL> alter database tempfile '/u01/app/oracle/oradata/orcl/temp01.dbf' resize 100 m;
Method 2: Set the temporary data file to automatic extension:
SQL> alter database tempfile '/u01/app/oracle/oradata/orcl/temp01.dbf' autoextend on next 5 m maxsize unlimited;
Method 3: Add a data file to the temporary tablespace:
SQL> alter tablespace temp add tempfile '/u01/app/oracle/oradata/orcl/temp02.dbf' size 100 m;

4. Create a temporary tablespace:
SQL> create temporary tablespace temp1 tempfile '/u01/app/oracle/oradata/orcl/temp11.dbf' size 10 M;

5. Change the default temporary tablespace of the system:
-- Query the default temporary tablespace
Select * from database_properties where property_name = 'default _ TEMP_TABLESPACE ';
-- Modify the default temporary tablespace
Alter database default temporary tablespace temp1;
All users' default temporary tablespace will be switched to the new temporary tablespace:
Select username, temporary_tablespace, default _ from dba_users;
-- Change the temporary tablespace of a user:
Alter user scott temporary tablespace temp;

6. Delete temporary tablespace
Delete a data file in a temporary tablespace:
SQL> alter database tempfile '/u01/app/oracle/oradata/orcl/temp02.dbf' drop;
Delete temporary tablespace (delete permanently ):
SQL> drop tablespace temp1 including contents and datafiles cascade constraints;

7. view the usage of the temporary tablespace (the GV _ $ TEMP_SPACE_HEADER view can only be queried under the sys user)
The GV _ $ TEMP_SPACE_HEADER view records the usage and unused sizes of temporary tablespace.
The bytes field in the dba_temp_files view records the total size of the temporary tablespace.
SELECT temp_used.tablespace_name,
Total-used as "Free ",
Total as "Total ",
Round (nvl (total-used, 0) * 100/total, 3) "Free percent"
FROM (SELECT tablespace_name, SUM (bytes_used)/1024/1024 used
From gv _ $ TEMP_SPACE_HEADER
Group by tablespace_name) temp_used,
(SELECT tablespace_name, SUM (bytes)/1024/1024 total
FROM dba_temp_files
Group by tablespace_name) temp_total
WHERE temp_used.tablespace_name = temp_total.tablespace_name

8. Search for SQL statements that compare Resource Consumption
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
 
9. Check the SQL statement used by the current temporary tablespace AND THE OCCUPIED temporary tablespace
Select sess. SID, segtype, blocks * 8/1000 "MB", SQL _text
From v $ sort_usage sort, v $ session sess, v $ SQL
Where sort. SESSION_ADDR = sess. SADDR
And SQL. ADDRESS = sess. SQL _ADDRESS
Order by blocks desc;

10. Introduction to temporary tablespace groups
1) create a temporary tablespace group:
Create temporary tablespace tempts1 tempfile '/home/oracle/temp1_02.dbf' size 2 M tablespace group group1;
Create temporary tablespace tempts2 tempfile '/home/oracle/temp2_02.dbf 'size 2 M tablespace group group2;
 
2) query temporary tablespace groups: dba_tablespace_groups View
Select * from dba_tablespace_groups;
GROUP_NAME TABLESPACE_NAME
------------------------------------------------------------
GROUP1 TEMPTS1
GROUP2 TEMPTS2

3) Move the tablespace from a temporary tablespace group to another temporary tablespace group:
Alter tablespace tempts1 tablespace group GROUP2;
Select * from dba_tablespace_groups;

GROUP_NAME TABLESPACE_NAME
------------------------------------------------------------
GROUP2 TEMPTS1
GROUP2 TEMPTS2

4) Designate a temporary tablespace group to the user
Alter user scott temporary tablespace GROUP2;

5) set temporary tablespace at the database level
Alter database <db_name> default temporary tablespace GROUP2;

6) delete a temporary tablespace group (delete all temporary tablespaces that constitute the temporary tablespace Group)
Drop tablespace tempts1 including contents and datafiles;
Select * from dba_tablespace_groups;
GROUP_NAME TABLESPACE_NAME
------------------------------------------------------------
GROUP2 TEMPTS2

Drop tablespace tempts2 including contents and datafiles;
Select * from dba_tablespace_groups;
GROUP_NAME TABLESPACE_NAME

11. Perform shrink on the temporary tablespace (11g new function)
-- Shrink the temp tablespace to 20 mb
Alter tablespace temp shrink space keep 20 M;
-- Automatically reduces the temporary file size of the tablespace to the minimum possible size.
Alter tablespace temp shrink tempfile '/u02/oracle/data/lmtemp02.dbf ';

Role of temporary tablespace
Oracle temporary tablespace is mainly used to query and store some buffer data. Temporary tablespace consumption is mainly caused by sorting the intermediate query results.
Restarting the database can release temporary tablespace. If the instance cannot be restarted, the temp tablespace will continue to grow as the problematic SQL statement is executed. Until the hard disk space is exhausted.
Someone on the Internet speculated that oracle uses the greedy algorithm to allocate disk space. If the disk space consumption reached 1 GB last time, the temporary tablespace would be 1 GB.
That is to say, the size of the temporary tablespace file is the largest in history.

The main functions of temporary tablespace:
Index create or rebuild;
Order by or group;
Distinct operation;
Union, intersect, or minus;
Sort-merge joins;
Analyze.