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

This article mainly explains "how to solve the problem of Oracle temporary table space is too large". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn how to solve the problem that the Oracle temporary table space is too large.

Option 1: increase the size of the temporary tablespace

1. The usage of temporary tablespaces

SELECT D.TablespaceNameMagol 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) BLOCKSFROM dba_temp_filesGROUP BY tablespace_name) D, (SELECT tablespace) Round (SUM (blocks * 8192) / (1024 * 1024), 2) USED_SPACEFROM v$sort_usageGROUP BY tablespace) FWHERE 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_MBfrom 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_propertieswhere 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

two。 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 Delete 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_textfrom v$sort_usage su, v$parameter p, v$session se, v$sql swhere p.name = 'db_block_size'and su.session_addr = se.saddrand s.hash_value = su.sqlhashand s.address = su.sqladdrorder by se.username,se.sid

2.4 kill related processes

Alter system kill session '584, 23181 alter system kill session' 1969, 64972 alter system kill session '262, 19832 alter system kill session' 324, 40273 alter system kill session '326, 38967 alter alter system kill session' 1266, 54596'

Or restart DB close App > close listener > shutdown immediate startup > start listener > Open App after doing the following

2.5 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

View temporary tablespace group temp

Select * from dba_tablespace_groups

3 temporary tablespace groups still use 99.98%

Add 4G 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; at this point, I believe you have a better understanding of "how to solve the problem of excessive Oracle temporary table space". You might as well do it in practice! Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.