Solution to Oracle temporary tablespace being too large

Solution to Oracle temporary tablespace being too large

There are two ways to solve the problem of temporary tablespace too much in Oracle. One is to increase the size of the temporary tablespace and the other is to create a temporary tablespace.

Solution 1: increase the size of the temporary tablespace
-- 1. Usage of temporary tablespace
Select d. tablespace_name,
SPACE "SUM_SPACE (M )",
Blocks "SUM_BLOCKS ",
Used_space "USED_SPACE (M )",
Round (Nvl (used_space, 0)/SPACE * 100, 2) "USED_RATE (% )",
SPACE-used_space "FREE_SPACE (M )"
FROM (SELECT tablespace_name,
Round (SUM (bytes)/(1024*1024), 2) SPACE,
SUM (blocks) BLOCKS
FROM dba_temp_files
Group by tablespace_name) D,
(SELECT tablespace,
Round (SUM (blocks * 8192)/(1024*1024), 2) USED_SPACE
FROM v $ sort_usage
Group by tablespace) F
Where d. tablespace_name = F. tablespace (+)
And d. tablespace_name like 'temp % ';

-- View the total size and maximum size of the temporary tablespace (data files can be viewed)
Select file_name,
Tablespace_name,
Bytes/1024/1024 MB,
Autoextensible,
Maxbytes/1024/1024 MAX_MB
From dba_temp_files;

-- Increase the size of the temporary tablespace
Alter tablespace temp1 add tempfile '/data/prod/proddata/temp013.dbf' size 4G;
Alter tablespace temp2 add tempfile '/data/prod/proddata/temp024.dbf' size 4G;

-- Solution 2: recreate the temporary tablespace to solve the problem that the temporary tablespace is too large.

-- 0. view the current default temporary tablespace
Select *
From database_properties
Where property_name = 'default _ TEMP_TABLESPACE ';

-- 1. create temporary tablespace for transit
Create temporary tablespace temp3 tempfile '/data/prod/proddata/temp31.dbf' size 4G tablespace group temp;
Create temporary tablespace temp4 tempfile '/data/prod/proddata/temp41.dbf' size 4G tablespace group temp;

-- 2. Delete the temporary tablespace in the original temporary tablespace group.
-- 2.1 remove temp1 and temp2 from the default temporary tablespace group temp;
Alter tablespace temp1 tablespace group '';
Alter tablespace temp2 tablespace group '';

-- 2.2 Delete temporary tablespace temp1 and temp2
Drop tablespace temp1 including contents and datafiles;
Drop tablespace temp2 including contents and datafiles;

-- 2.3 If the hang statement is used when the tablespace is deleted, kill the SQL statement that runs in the temp temporary tablespace first. Such SQL statements are mostly sorted statements.
Select se. username,
Se. sid,
Se. serial #,
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;

-- 2.4 kill related processes
Alter system kill session '2017 81 ';
Alter system kill session '2017 72 ';
Alter system kill session '2010 32 ';
Alter system kill session '2017 73 ';
Alter system kill session '2017 67 ';
Alter system kill session '1996 96 ';
 
Or
-- Restart the database
-- Disable Application --> disable listener --> shutdown immediate
-- Startup --> Start the listener --> execute the following operations to open the application

-- 2.5 create a temporary tablespace and add it to the temporary tablespace group temp
Create temporary tablespace temp1 tempfile '/data/prod/proddata/temp11.dbf' size 4G tablespace group temp;
Create temporary tablespace temp2 tempfile '/data/prod/proddata/temp21.dbf' size 4G tablespace group temp;

-- 2.6 Add one member to the temp members of the Temporary tablespace group temp1, temp2, temp3, and temp4.
Alter tablespace temp1 add tempfile '/data/prod/proddata/temp12.dbf' size 4G;
Alter tablespace temp2 add tempfile '/data/prod/proddata/temp22.dbf' size 4G;
Alter tablespace temp3 add tempfile '/data/prod/proddata/temp32.dbf' size 4G;
Alter tablespace temp4 add tempfile '/data/prod/proddata/temp42.dbf' size 4G;

-- 2.7 view temporary tablespace group temp
Select * from dba_tablespace_groups;

-- 3 the temporary tablespace group still uses 99.98%,
-- 3.1 add 4 GB space for each temporary tablespace
Alter tablespace temp1 add tempfile '/data/prod/proddata/temp13.dbf' size 4G;
Alter tablespace temp2 add tempfile '/data/prod/proddata/temp23.dbf' size 4G;
Alter tablespace temp3 add tempfile '/data/prod/proddata/temp33.dbf' size 4G;
Alter tablespace temp4 add tempfile '/data/prod/proddata/temp43.dbf' size 4G;

Https://www.bkjia.com/topicnews.aspx? Tid = 12

This article permanently updates link: https://www.bkjia.com/Linux/2018-03/151245.htm