Queries to monitor Temporary Tablespace

Temp Usage


  set linesize 120
  col v_size_GB format 99.9999
  col TABLESPACE format a15
  col USERNAME format a15

  SELECT t.USERNAME,
    t.tablespace,
    s.sid,
    spid OS_ID,
    s.serial#,
    ROUND(blocks*8192/1024/1024/1024 , 5) v_size_GB,
    s.logon_time,
    (s.last_call_et/60) Session_Time_Minutes
  FROM v$tempseg_usage t,
    v$session s,
    v$process p
  WHERE t.SESSION_ADDR=s.saddr
  AND p.addr=s.paddr;


Temp USED/FREE 



  SELECT   A.tablespace_name tablespace, D.mb_total,
           SUM (A.used_blocks * D.block_size)/1024/1024 mb_used,
           D.mb_total - 
           SUM (A.used_blocks * D.block_size)/1024/1024 mb_free
  FROM     v$sort_segment A,
           (
           SELECT   B.name, C.block_size,
                    SUM (C.bytes) / 1024 / 1024 mb_total
           FROM     v$tablespace B, v$tempfile C
           WHERE    B.ts#= C.ts#
           GROUP BY B.name, C.block_size
           ) D
  WHERE    A.tablespace_name = D.name
  GROUP by A.tablespace_name, D.mb_total;

Steps to recover DB (All onlinelogs lost,no valid backup)

The Method below is to help you in the case where there is you have lost all the online logs & don't have any Valid Backup :

1.  Edit the init.ora  and add :
     _allow_resetlogs_corruption=true
 
2.  Recreate control file.


3.  Recover the database
    recover database until cancel using backup controlfile;
   
4.  Open the Database with "reset logs"
     alter database open resetlogs;

5.  Immediately Take a Backup of the DB ( Exp,EXPDP,RMAN ...)


6.  Recreate the DB from the Dump.





Note : Oracle doesn't recommends the use of _ parameters unless its suggest by My Oracle Support and here we use it to get the DB back online.
Once the DB is online Take a backup and create a New one.