Add, delete, modify, and query temporary Oracle tablespace

1. view the temporary tablespace (dba_temp_files view) (v _ $ tempfile view) selecttablespace_name, file_name, bytes10241024file_size, au

1. view temporary tablespace (dba_temp_files view) (v _ $ tempfile view) select tablespace_name, file_name, bytes/1024/1024 file_size, au

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 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