Mir Sayeed Hassan – Oracle Blog

First Verify the Temp TBS Exist in database

[code]SQL> select * from v$tempfile;

FILE# CREATION_CHANGE# CREATION_ TS# RFILE# STATUS ENABLED BYTES BLOCKS CREATE_BYTES BLOCK_SIZE

———- —————- ——— ———- ———- ——- ———- ———- ———- ———— ———-

NAME

1 925784 07-NOV-15 3 1 ONLINE READ WRITE 4158652416 507648 20971520 8192[/code]

/u01/app/oracle/oradata/prim/temp01.dbf

Move Default Database temp2 tablespace

[code]SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;[/code]

Make sure No sessions are using your Old Temp tablespace

– Find Session Number from V$SORT_USAGE:

[code]SQL> SELECT USERNAME, SESSION_NUM, SESSION_ADDR FROM V$SORT_USAGE;[/code]
no rows selected

If the find any session exist then get the SID from the V$SESSION view, Try to kill that session

SELECT SID, SERIAL#, STATUS FROM V$SESSION WHERE SERIAL#=SESSION_NUM;

Or

SELECT SID, SERIAL#, STATUS FROM V$SESSION WHERE SADDR=SESSION_ADDR;

– Kill Session:

Now kill the session with IMMEDIATE.

[code]SQL> ALTER SYSTEM KILL ‘SID,SERIAL#’ IMMEDIATE; [/code]

Drop the existing temp tablespace

[code]SQL> select * from v$tablespace;

TS# NAME INC BIG FLA ENC

—————– —————————— — — — —

0 SYSTEM YES NO YES

1 SYSAUX YES NO YES

2 UNDOTBS1 YES NO YES

4 USERS YES NO YES

3 TEMP NO NO YES

6 EXAMPLE YES NO YES

7 TEMP2 NO NO YES
[/code]
[code]SQL> DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;[/code]

Tablespace dropped.

Move Tablespace Temp, back to new temp tablespace

[code]SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;[/code]

Database altered.

7. Drop temporary for tablespace temp02 which is newly created

[code]SQL> DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;[/code]

Tablespace dropped.

Verify the Temp TBS

[code]SQL> select * from v$tempfile;

FILE# CREATION_CHANGE# CREATION_ TS# RFILE# STATUS ENABLED

———- —————- ——— ———- ———- ——- ———-

BYTES BLOCKS CREATE_BYTES BLOCK_SIZE

———- ———- ———— ———-

NAME

——————————————————————————-

1 2.2857E+11 24-AUG-17 3 1 ONLINE READ WRITE

104857600 12800 104857600 8192[/code]

/u01/app/oracle/oradata/prim/temp01.dbf

Hence – Temp TBS is drop/recreate created as per our requirement, Tested, Verified in our “TEST DATABASE”;

Therefore DB Shutdown is not required to perform the above operation as drop/recreate the temp tbs.

---

Dear Friends, If there is any clarification, Please revert me back – 24/7.

Personal mail id: myself@mirsayeedhassan.com

Linkedin: https://www.linkedin.com/in/mir-sayeed-hassan-0193639

Phone: +91-9986993860 (What’s up/Telegram)

Skype: mirsayeed2