Oracle clean up and rebuild temporary table space

Normally, Oracle automatically frees up temporary segment A when it completes a sort operation that uses the temp table space, such as a SELECT statement, create INDEX, and so on. But some of us will encounter the temporary segment has not been released, the temp table space is almost full situation, even if we restart the database still does not solve the problem. This issue in the forum is also often asked by netizens, the following I sum up, give several processing methods.

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