Drop And Recreate Temp Tablespace In Oracle : During this article we’ll see how to Drop And Recreate temp tablespace In Oracle Database.
Note :-
While doing this activity, existing temporary tablespace may have existing live sessions, due to same oracle won’t let us to drop existing temporary tablespace. Resulting, we need to kill existing session before dropping temporary tablespace.
Check Temporary tablespaces and tempfiles :
SQL>
SQL> set lines 400 pages 400
SQL> col FILE_NAME for a60
SQL> select FILE_NAME, TABLESPACE_NAME, AUTOEXTENSIBLE, BYTES/1024/1024 "Size in MB" from dba_temp_files;
FILE_NAME TABLESPACE_NAME AUT Size in MB
------------------------------------------------------------ ------------------------------ --- ----------
/u02/app/oracle/oradata/ORCL/temp01.dbf TEMP YES 100
SQL>
Create Temp tablespace :
SQL>
SQL> create temporary tablespace temp_new tempfile '/u02/app/oracle/oradata/ORCL/temp_new.dbf' size 100m autoextend on;
Tablespace created.
SQL>
SQL> select FILE_NAME, TABLESPACE_NAME, AUTOEXTENSIBLE, BYTES/1024/1024 "Size in MB" from dba_temp_files;
FILE_NAME TABLESPACE_NAME AUT Size in MB
------------------------------------------------------------ ------------------------------ --- ----------
/u02/app/oracle/oradata/ORCL/temp01.dbf TEMP YES 100
/u02/app/oracle/oradata/ORCL/temp_new.dbf TEMP_NEW YES 100
SQL>
Add and Resize Temp tablespace :
SQL>
SQL> select FILE_NAME, TABLESPACE_NAME, AUTOEXTENSIBLE, BYTES/1024/1024 "Size in MB" from dba_temp_files;
FILE_NAME TABLESPACE_NAME AUT Size in MB
------------------------------------------------------------ ------------------------------ --- ----------
/u02/app/oracle/oradata/ORCL/temp01.dbf TEMP YES 100
/u02/app/oracle/oradata/ORCL/temp_new.dbf TEMP_NEW YES 100
SQL>
SQL> alter database tempfile '/u02/app/oracle/oradata/ORCL/temp_new.dbf' resize 150m;
Database altered.
SQL>
SQL> select FILE_NAME, TABLESPACE_NAME, AUTOEXTENSIBLE, BYTES/1024/1024 "Size in MB" from dba_temp_files;
FILE_NAME TABLESPACE_NAME AUT Size in MB
------------------------------------------------------------ ------------------------------ --- ----------
/u02/app/oracle/oradata/ORCL/temp01.dbf TEMP YES 100
/u02/app/oracle/oradata/ORCL/temp_new.dbf TEMP_NEW YES 150
SQL>
SQL> alter tablespace TEMP add tempfile '/u02/app/oracle/oradata/ORCL/temp02.dbf' size 100m autoextend on;
Tablespace altered.
SQL>
SQL> select FILE_NAME, TABLESPACE_NAME, AUTOEXTENSIBLE, BYTES/1024/1024 "Size in MB" from dba_temp_files;
FILE_NAME TABLESPACE_NAME AUT Size in MB
------------------------------------------------------------ ------------------------------ --- ----------
/u02/app/oracle/oradata/ORCL/temp01.dbf TEMP YES 100
/u02/app/oracle/oradata/ORCL/temp02.dbf TEMP YES 100
/u02/app/oracle/oradata/ORCL/temp_new.dbf TEMP_NEW YES 150
SQL>
Drop Temp tablespace
SQL>
SQL> select FILE_NAME, TABLESPACE_NAME, AUTOEXTENSIBLE, BYTES/1024/1024 "Size in MB" from dba_temp_files;
FILE_NAME TABLESPACE_NAME AUT Size in MB
------------------------------------------------------------ ------------------------------ --- ----------
/u02/app/oracle/oradata/ORCL/temp01.dbf TEMP YES 100
/u02/app/oracle/oradata/ORCL/temp02.dbf TEMP YES 100
/u02/app/oracle/oradata/ORCL/temp_new.dbf TEMP_NEW YES 150
SQL>
SQL>
SQL> drop tablespace TEMP_NEW including contents and datafiles;
Tablespace dropped.
SQL>
SQL> select FILE_NAME, TABLESPACE_NAME, AUTOEXTENSIBLE, BYTES/1024/1024 "Size in MB" from dba_temp_files;
FILE_NAME TABLESPACE_NAME AUT Size in MB
------------------------------------------------------------ ------------------------------ --- ----------
/u02/app/oracle/oradata/ORCL/temp01.dbf TEMP YES 100
/u02/app/oracle/oradata/ORCL/temp02.dbf TEMP YES 100
SQL>
SQL>
SQL> alter tablespace TEMP drop tempfile '/u02/app/oracle/oradata/ORCL/temp02.dbf';
Tablespace altered.
SQL>
SQL> select FILE_NAME, TABLESPACE_NAME, AUTOEXTENSIBLE, BYTES/1024/1024 "Size in MB" from dba_temp_files;
FILE_NAME TABLESPACE_NAME AUT Size in MB
------------------------------------------------------------ ------------------------------ --- ----------
/u02/app/oracle/oradata/ORCL/temp01.dbf TEMP YES 100
SQL>
Move a temp datafile ONLINE using below command :
APPLIES TO: Oracle Database – Enterprise Edition – Version 12.1.0.1 and later Information in this document applies to any platform.
SQL> alter database move datafile '/u01/app/oracle/oradata/ORCL/temp01.dbf' to '/u02/app/oracle/oradata/ORCL/temp01.dbf';Got below error :
SQL>
SQL> alter database move datafile '/u01/app/oracle/oradata/ORCL/temp01.dbf' to '/u02/app/oracle/oradata/ORCL/temp01.dbf';
alter database move datafile '/u01/app/oracle/oradata/ORCL/temp01.dbf' to '/u02/app/oracle/oradata/ORCL/temp01.dbf'
*
ERROR at line 1:
ORA-01516: nonexistent log file, data file, or temporary file "/u01/app/oracle/oradata/ORCL/temp01.dbf" in the current container
SQL>
CAUSE : “alter database move” only applicable for non TEMP datafile.
SQL>
SQL> select FILE_NAME, TABLESPACE_NAME, AUTOEXTENSIBLE, BYTES/1024/1024 "Size in MB" from dba_temp_files;
FILE_NAME TABLESPACE_NAME AUT Size in MB
------------------------------------------------------------ ------------------------------ --- ----------
/u02/app/oracle/oradata/ORCL/temp01.dbf TEMP YES 100
SQL>
SQL> alter database move tempfile '/u01/app/oracle/oradata/ORCL/temp01.dbf' to '/u02/app/oracle/oradata/ORCL/temp01.dbf';
alter database move tempfile '/u01/app/oracle/oradata/ORCL/temp01.dbf' to '/u02/app/oracle/oradata/ORCL/temp01.dbf'
*
ERROR at line 1:
ORA-00905: missing keyword
SQL>
SOLUTION : Since this is for TEMPORARY tablespace, below steps can be done while database is running.
Step-1 Create new Temporary tablespace
SQL>
SQL> create temporary tablespace temp_new tempfile '/u02/app/oracle/oradata/ORCL/temp_new.dbf' size 100m autoextend on;
Tablespace created.
SQL>
SQL> select FILE_NAME, TABLESPACE_NAME, AUTOEXTENSIBLE, BYTES/1024/1024 "Size in MB" from dba_temp_files;
FILE_NAME TABLESPACE_NAME AUT Size in MB
------------------------------------------------------------ ------------------------------ --- ----------
/u02/app/oracle/oradata/ORCL/temp01.dbf TEMP YES 100
/u02/app/oracle/oradata/ORCL/temp_new.dbf TEMP_NEW YES 100
SQL>
Step-2 Alter database default temporary tablespace
SQL>
SQL> alter database default temporary tablespace TEMP_NEW;
Database altered.
SQL>
Step-3 Drop old temp tablespace
SQL>
SQL> alter database default temporary tablespace TEMP_NEW;
Database altered.
SQL>
*** You cannot drop default temporary tablespace.
Before drop existing default tablespace you need to make default tablespace to another temporary tablespace or create a temporary tablespace and make it default tablespace. Then you could drop your old temporary tablespace.
SQL>
SQL> drop tablespace TEMP_NEW including contents and datafiles;
drop tablespace TEMP_NEW including contents and datafiles
*
ERROR at line 1:
ORA-12906: cannot drop default temporary tablespace
SQL>
I hope my written content will helped you. Your suggestions/feedback are most welcome from orakldba.com.
Keep learning… Have a great day!!!
Please comment here for your any query related to above content. You could write to mail me through : orakldba@gmail.com.