How to Create TEMPORARY tablespace and drop existing temporary tablespace in oracle 11g/12c

1. Create Temporary Tablespace Temp

create temporary tablespace temp2 tempfile '/mnt/mnt04/oradata/temp01.dbf'size 2000M; 

2. Move Default Database temp tablespace

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

3. Make sure No sessions are using your Old Temp (TEMP) tablespace

   a.  Find Session Number from V$SORT_USAGE: 
       SELECT USERNAME, SESSION_NUM, SESSION_ADDR FROM V$SORT_USAGE; 

SQL> SELECT USERNAME, SESSION_NUM, SESSION_ADDR FROM V$SORT_USAGE;

USERNAME                       SESSION_NUM        SESSION_ADDR
----------------------------------------------------------------------------- 
SYS                                       45684           0000000CE2EA7CC8

   b.  Find Session ID from V$SESSION:

       If the resultset contains any tows then your next step will be to find the SID from the V$SESSION view. You can find session id by using SESSION_NUM or SESSION_ADDR from previous resultset.

       SELECT SID, SERIAL#, STATUS FROM V$SESSION WHERE SERIAL#=SESSION_NUM;
       OR
       SELECT SID, SERIAL#, STATUS FROM V$SESSION WHERE SADDR=SESSION_ADDR; 
OR
SQL> SELECT b.tablespace,b.segfile#,b.segblk#,b.blocks,a.sid,a.serial#,
a.username,a.osuser, a.status
FROM v$session a,v$sort_usage b
WHERE a.saddr = b.session_addr;

c.  Kill Session:

Provide above inputs to following query, and kill session’s.
SQL> alter system kill session 'SID_NUMBER, SERIAL#NUMBER';
For example:
SQL> alter system kill session '633,45684';

4. Drop TEMP tablespace

drop tablespace TEMP including contents and datafiles;

5. Recreate Tablespace Temp

CREATE TEMPORARY TABLESPACE TEMP TEMPFILE  '/mnt/mnt04/oradata/temp_01.dbf' SIZE 1G AUTOEXTEND ON NEXT 1G MAXSIZE 32767M;

6. Move Tablespace Temp, back to new temp tablespace

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP;

7. Drop temporary for tablespace temp

DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;

No need to do shutdown when drop temp tablespace and the recreate it. If something happen with temp tablespaces e.g. : crash, corrupt, etc. Oracle database will ignore the error, but DML (insert,update,delete) and SELECT Query will suffer.

Cleaning Oracle SYSAUX Tablespace Usage - SYSAUX tablespace is 97.51%

Normally the SYSAUX tablespace is more or less stable so it would be smart to check what is eating the space in there. Connected as a DBA user, run the script ${ORACLE_HOME}/rdbms/admin/utlsyxsz to get the current usage of the SYSAUX tablespace and see how it will grow when you change certain parameters for which you are asked to enter values.

 
OR

select 
   * 
from 
   (select 
      owner,segment_name||'~'||partition_name segment_name,bytes/(1024*1024) meg 
   from 
      dba_segments 
      where tablespace_name = 'SYSAUX' 
   order by 
      blocks desc);

In my case, below 1  occupying most of the space :-

1. SM/AWRSM/AWR — It refers to Automatic Workload Repository.Data in this section is retained for a certain amount of time (default 8 days). Setting can be checked through DBA_HIST_WR_CONTROL.

Retrieve the oldest and latest AWR snapshot

SELECTsnap_id, begin_interval_time, end_interval_timeFROMSYS.WRM$_SNAPSHOTWHEREsnap_id = ( SELECT MIN (snap_id) FROM SYS.WRM$_SNAPSHOT)UNIONSELECTsnap_id, begin_interval_time, end_interval_timeFROMSYS.WRM$_SNAPSHOTWHEREsnap_id = ( SELECT MAX (snap_id) FROM SYS.WRM$_SNAPSHOT)/

Now use the dbms_workload_repository package to remove the AWR snapshots.

BEGINdbms_workload_repository.drop_snapshot_range(low_snap_id => 7521, high_snap_id=>7888);END;/

Speed up ‘removal’ of old AWR reports

@#$%^&*()_ removing the entries takes ages and fails on undo errors … Metalink note Doc ID: 852028.1 states that I can safely remove the AWR metadata tables and recreate them.If none of the above suits as everything is set proper then consider clean up and rebuild AWR repository to clear all the space.

SQL> connect / as sysdba 
SQL> @?/rdbms/admin/catnoawr.sql 
SQL> @?/rdbms/admin/catawrtb.sql

Reference :-WRH$_ACTIVE_SESSION_HISTORY Does Not Get Purged Based Upon the Retention Policy (Doc ID 387914.1)Suggestions if Your SYSAUX Tablespace Grows Rapidly or Too Large (Doc ID 1292724.1)