How to Redo an oracle temporary table space

Scenario: Temporary table space Full, unable to release

The solution is summarized as follows:

--1 Create transit temporary table space

Create temporary tablespace temp02

Tempfile '/oradata/crpt_temp_tra.dbf '

Size 20G reuse autoextend on next 1M;

--2 Modify user temporary table space for transit temporary table space

Alter user Crmreport temporary tablespace temp02

--3 deletes the user's original temporary tablespace tbs_crpt_temp

Drop tablespace tbs_crpt_temp including contents and datafiles;

--4 rebuild the user's original temporary tablespace tbs_crpt_temp

Create temporary tablespace tbs_crpt_temp

Tempfile '/oradata/crpt_temp.dbf '

Size 30G reuse autoextend on next 1M;

--5 Reset the user's temporary table space to the new original temporary table space

Alter user Crmreport temporary tablespace tbs_crpt_temp

--6 Delete Transit temporary table space

Drop tablespace temp02 including contents and datafiles;

Attention:

Before you delete the temp temporary tablespace, kill the SQL statement that runs in the temp temporary table space, which is more of a sorted statement, and the query method is as follows:

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;

--After the query comes out, kill the session of these SQL statements:

Alter system kill session ' sid,serial# ';

This column more highlights: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/