- 20 Sep, 2023
- 0 Comments
- 40 Secs Read
How To Drop And Recreate Temp Tablespace In Oracle.
How To Drop And Recreate Temp Tablespace In Oracle?Â
Follow the below steps:
-
Find the existing temp tablespace details :
SQL> select tablespace_name,file_name from dba_temp_files;
2. Create another Temporary Tablespace TEMP1
CREATE TEMPORARY TABLESPACE TEMP1 TEMPFILE ‘/u01/app/oradata/PRIM/temp01′ SIZE 5G;
3. Assign new tablespace as Default temp tablespace.
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP1;
4. If any sessions are using temp space, then kill them.
SELECT b.tablespace,b.segfile#,b.segblk#,b.blocks,a.sid,a.serial#,a.username,a.osuser, a.statusFROM v$session a,v$sort_usage bWHERE a.saddr = b.session_addr; ALTER SYSTEM KILL SESSION 'SID,SERIAL#' IMMEDIATE;
How to drop temp tablespace?
DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;