Shulou(Shulou.com)06/01 Report--

Solution to excessive temporary tablespace

There are two ways to solve the problem that the temporary table space is too large. One is to increase the size of the temporary table space, and the other is to reconstruct the temporary table space to solve the problem of excessive temporary table space.

Option 1: increase the size of the temporary tablespace

-1. Use of temporary tablespaces

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 extended size of the temporary tablespace (you can see the data file)

Select file_name

Tablespace_name

Bytes / 1024 / 1024 MB

Autoextensible

Maxbytes / 1024 / 1024 MAX_MB

From dba_temp_files

-- increase the size of 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

Plan 2: rebuild temporary tablespaces to solve the problem of excessive temporary tablespaces.

-- 0. View the current default temporary tablespace

Select *

From database_properties

Where property_name = 'DEFAULT_TEMP_TABLESPACE'

-1. Create transit temporary tablespace

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 temporary tablespaces from 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 deleting temporary tablespaces temp1 and temp2

Drop tablespace temp1 including contents and datafiles

Drop tablespace temp2 including contents and datafiles

-- 2.3 if you hang when deleting a tablespace, you can use the following statement to kill the sql statement running in the temp temporary tablespace. Most of these sql statements are 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 '584 and 23181'

Alter system kill session '196pr 64972'

Alter system kill session '262 Jing 19832'

Alter system kill session '324040273'

Alter system kill session '326 and 38967'

Alter system kill session '1266, 54596'

Or

-- restart DB

-- close App-- > disable listening-- > shutdown immediate

-- startup-- > start listening-- > Open the application after doing the following

Create temporary tablespaces and join 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

Add one member to each member of the temporary tablespace group temp temp1,temp2,temp3,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.7View temporary tablespace group temp

Select * from dba_tablespace_groups

-- 3 temporary tablespace groups still use 99.98%

3.1 add 4G space to 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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.