Oracle Temp tablespace switch, oracletemp

Oracle Temp tablespace switch, oracletemp

I. Functions of TEMP tablespace

Temporary tablespace is mainly used to provide temporary computing space when the database performs sorting, index management, access view, and other operations. After the operation is completed, the system automatically cleans up the space. When sort is required in oracle, when sort_area_size in PGA is not enough, the data will be sorted in the temporary tablespace. If any exception occurs, it will also be placed into the temporary tablespace. Normally, after the Select statement, create index, and other sorting operations using the TEMP tablespace are completed, Oracle will automatically release the temporary segment. Note that the release only marks the free space and can be reused. The occupied disk space is not released. Therefore, the Temp tablespace may become larger and larger.

Sorting is resource-consuming. When the Temp tablespace is full, the key is to optimize your statements and minimize the number of orders.

Summary:

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

Ii. oracle temp tablespace switchover

2.1 query the TEMP tablespace usage:

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;

Default tablespace of the database:

SQL> SELECT PROPERTY_NAME, PROPERTY_VALUE
2 FROM DATABASE_PROPERTIES
3 WHERE PROPERTY_NAME = 'default _ TEMP_TABLESPACE ';

2.2 create a Temp2 tablespace

Create temporary tablespace temp1 tempfile 'd: \ APP \ ADMINISTRATOR \ ORADATA \ SDXJ \ TEMP2.dbf 'size 20 M autoextend on next 1 M maxsize unlimited
Tablespace group''
Extent management local uniform size 1 M;

2.3 modify the default tablespace of the database to Temp1

SQL> alter database default temporary tablespace temp1;
The database has been changed.

2.4 Delete the original tablespace

Exit the session and delete the original tablespace.

Drop tablespace temp including contents and datafiles;

Deleted successfully.

Iii. Common queries for Temp tablespaces

3.1. 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;

3.2 search for SQL statements that consume resources
/* Formatted on 21:58:17 (QP5 v5.163.1008.3004 )*/
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

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

4.4 perform shrink on the temporary tablespace (11g new function)
-- Shrink the temp tablespace to 20 mb
Alter tablespace temp1 shrink space keep 20 M;
-- Automatically reduces the temporary file size of the tablespace to the minimum possible size.
Alter tablespace temp1 shrink tempfile '.../temp01.dbf ';

Summary: The Temp tablespace maintenance is now complete.

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.