How to drop and re-create TEMP Tablespace in Oracle

1. Create Temporary Tablespace Temp

CREATE Temporary Tablespace TEMP2 tempfile  ‘d:\oracle\oradata\oradata\temp01’ SIZE 1500M, ‘d:\oracle\oradata\oradata\temp02’ SIZE 1500M; 
2. Move Default Database temp tablespace
Alter database default TEMPORARY tablespace TEMP2;
3. Make sure no sessions are using your Old Temp tablespace
SQL>Select username, session_num, session_addr from v$sort_usage;

If the result set contains any rows 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 result set.

SQL> Select sid, serial#, status from v$session where serial#=session_num;
or
SQL> Select sid, serial#, status from v$session where saddr=session_addr;
Now kill the session with IMMEDIATE option or you can directly using from toad
SQL> Alter system kill ‘sid,serial#’ immediate;
4. Drop temp tablespace
SQL> drop tablespace temp including contents and datafiles;
5. Recreate Tablespace Temp
SQL> create TEMPORARY tablespace TEMP tempfile ‘D:\oracle\oradata\temp\temp01’ size 1500m;
6 Move Tablespace Temp, back to new temp tablespace
SQL> Alter database default temporary tablespace TEMP;
7. Drop temporary for tablespace temp
SQL> drop tablespace TEMP2 including contents and datafiles;
In fact there is no need to shutdown while doing these operation. If any thing happens with temp tablespace, oracle database will ignore the error, but DML and SELECT query will suffer...

How to recover or re-create temporary tablespace in 10g

In database you may discover that your temporary tablespace is deleted from OS or it might get corrupted. In order to get it back you might think about recover it. The recovery process is simply recover the temporary file from backup and roll the file forward using archived log files if you are in archivelog mode.

Another solution is simply drop the temporary tablespace and then re-create a new one and assign new one as a default tablespace to the database users.
SQL> Select File_Name, File_id, Tablespace_name from DBA_Temp_Files;

FILE_NAME                     FILE_ID TABLESPACE_NAME

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

D:\ORACLE\ORADATA\MANOJ\TEMP02.DBF 1     TEMP Make the affected temporary files offline and 
create new TEMP tablespace and assign it default temporary tablespace:

SQL> Alter database tempfile 1 offline;
SQL> Create temporary tablespace TEMP1 tempfile 'D:\ORACLE\ORADATA\MANOJ\TEMP02.DBF' size 1500M;
SQL> alter database default temporary tablespace TEMP1;

Check the users who are not pointed to default temp tablespace and assign them externally then finally drop the old tablespace.

SQL> Select temporary_tablespace, username from dba_users where temporary_tablespace<>'TEMP';

TEMPORARY_TABLESPACE       USERNAME

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

TEMP                       SH1

TEMP                       SH2

SQL>alter user SH1 temporary tablespace TEMP1;
SQL>alter user SH2 temporary tablespace TEMP1;
SQL>Drop tablespace temp;

BACKUP CURRENT CONTROLFILE creates a Snapshot Controlfile

A short post today.

Recently, there was a discussion on forums about when a Snapshot controlfile is created.  It is known that the snapshot controlfile is created to re-synchronise with the Catalog.

But in my test database environment, I don't use a Catalog.  I only use the controlfile (and backups of it).

One poser said that he ran BACKUP DATABASE followed by BACKUP CURRENT CONTROLFILE. He asked if the same snapshot controlfile was used [through the two commands].  I pointed out that since they were two *separate* BACKUP calls, Oracle would create a separate Snapshot Controlfile for the second BACKUP command.

Here is a simple demo (note : I don't use a Catalog)

First the current state of the directory ($ORACLE_HOME/dbs in my case) where the Snapshot Controlfile would be created :

[oracle@localhost dbs]$ pwd

/home/oracle/app/oracle/product/11.2.0/dbhome_2/dbs

[oracle@localhost dbs]$ date

Mon Sep 23 08:17:46 PDT 2013

[oracle@localhost dbs]$ ls -ltr|tail -3

-rw-r----- 1 oracle oracle 1536 Oct  2  2010 orapworcl

-rw-rw---- 1 oracle oracle 1544 Sep 23 08:16 hc_orcl.dat

-rw-r----- 1 oracle oracle 3584 Sep 23 08:16 spfileorcl.ora

There is no snapshot currently present.

Next, I run a BACKUP CURRENT CONTROLFILE :

[oracle@localhost dbs]$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Mon Sep 23 08:17:53 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1229390655)

RMAN> backup current controlfile;

Starting backup at 23-SEP-13

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=69 device type=DISK

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

including current control file in backup set

channel ORA_DISK_1: starting piece 1 at 23-SEP-13

channel ORA_DISK_1: finished piece 1 at 23-SEP-13

piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_05_20/o1_mf_ncnnf_TAG20130520T081800_8snhob80_.bkp tag=TAG20130520T081800 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 23-SEP-13

RMAN> quit

Recovery Manager complete.

The controlfile backup got written to the FRA.

 Is there a snapshot controlfile ?

[oracle@localhost dbs]$ ls -ltr|tail -3

-rw-rw---- 1 oracle oracle    1544 Sep 23 08:16 hc_orcl.dat

-rw-r----- 1 oracle oracle    3584 Sep 23 08:16 spfileorcl.ora

-rw-rw---- 1 oracle oracle 9748480 Sep 23 08:18 snapcf_orcl.f

[oracle@localhost dbs]$

Yes, a snapshot controlfile did get created ..!