Oracle Database Cleanup Temp table space

This article is a simple tutorial on Oracle Database Clean temp table Space Oh, if you're looking for an Oracle database to clean up the temp table space just come in and have a look.

Law One, restart the library

Library restart, the Smon process will complete temporary segment release, temp table space cleanup operations, but many times our library is not allowed down, so this method is missing a bit of application opportunities, but this method is still very useful.

A method given by law two and Metalink

Modify the temp table Space storage parameter, let the Smon process view the temporary segment, so as to achieve cleanup and temp table space.

Sql>alter tablespace temp Increase 1;

Sql>alter tablespace temp Increase 0;

Method Three, I commonly used a method, the specific contents are as follows:

1. Use the following statement A to see who is using the temporary paragraph

SELECT username,

Sid

serial#,

Sql_address,

Machine

Program

Tablespace,

Segtype,

Contents

From V$session SE,

V$sort_usage su

WHERE se.saddr=su.session_addr

2, those who are using the temporary segment of the process

Sql>alter system kill session ' sid,serial# ';

3, the temp table space back to shrink

Sql>alter tablespace TEMP Coalesce;

Method of using diagnostic events is also a method that I consider to be "killer"

1, determine the temp table space ts#

Sql>select ts#, name from sys.ts$;

ts# NAME

-----------------------

0 Sysyem

1 RBS

2 USERS

3* TEMP

4 TOOLS

5 INDX

6 Drsys

2. Perform cleanup operations

Sql>alter session Set Events ' immediate trace name drop_segments level 4 ';

Description

The temp table space is ts# to 3*, so ts#+ 1 = 4

Other:

1, the reasons for the above problems I think may be due to the large sort of the temp table space to allow the scope of space caused. may also contain other anomalous factors.

2, the view of the state of space such as temp is one of the day-to-day responsibilities of the DBA, we can through the toad, Object browser and other tools to do, you can use the following statement:

Select UPPER (f.tablespace_name) "Table space name",

D.TOT_GROOTTE_MB "Table space size (M)",

D.tot_grootte_mb-f.total_bytes "used Space (M)",

To_char (ROUND (d.tot_grootte_mb-f.total_bytes)/d.tot_grootte_mb * 100,

2),

' 990.99 ') "Use ratio",

F.total_bytes "free Space (M)",

F.max_bytes "Max Block (M)"

From (SELECT Tablespace_name,

ROUND (SUM (BYTES)/(1024 * 1024), 2) Total_bytes,

ROUND (MAX (BYTES)/(1024 * 1024), 2 max_bytes

From SYS. Dba_free_space

GROUP by Tablespace_name) F,

(SELECT DD. Tablespace_name,

ROUND (SUM (DD). BYTES)/(1024 * 1024), 2 TOT_GROOTTE_MB

From SYS. Dba_data_files DD

GROUP by DD. Tablespace_name) D

WHERE D.tablespace_name = F.tablespace_name

ORDER BY 4 DESC