Home » RDBMS Server » Server Administration » how to delete tablespace "TEMP" data file normally? (oracle10gR1 Solaris5.9)
how to delete tablespace "TEMP" data file normally? [message #310130] Mon, 31 March 2008 06:32 Go to next message
wxfjordan
Messages: 92
Registered: December 2006
Member

SQL> select file_name, status, file_id from dba_temp_files;

FILE_NAME STATUS FILE_ID
--------------------------------------------- --------- ----------
/u01/app/oracle/oradata/msi/temp01.dbf AVAILABLE 1
+DATA/msi/tempfile/temp.264.650818167 AVAILABLE 2


"+DATA" is ASM diskgroup. Another is file system folder.

how to delete tablespace "TEMP" data file normally?
Re: how to delete tablespace "TEMP" data file normally? [message #310134 is a reply to message #310130] Mon, 31 March 2008 06:52 Go to previous messageGo to next message
wxfjordan
Messages: 92
Registered: December 2006
Member

My step is following. Is this normally?


SQL> alter database tempfile '/u01/app/oracle/oradata/msi/temp01.dbf' offline;

Database altered.

SQL> alter database tempfile '/u01/app/oracle/oradata/msi/temp01.dbf' drop;

Database altered.

SQL> select file_name, tablespace_name, status, autoextensible from dba_temp_files;

FILE_NAME TABLESPACE_NAME STATUS
--------------------------------------------- ----------------- ---------
+DATA/msi/tempfile/temp.264.650818167 TEMP AVAILABLE







Re: how to delete tablespace "TEMP" data file normally? [message #310205 is a reply to message #310130] Mon, 31 March 2008 10:03 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
1) create temporary tablespace temp1 tempfile '...';
2)alter database default temporary tablespace temp1;
3)drop tablespace temp including  contents and datafiles;
4)alter tablespace rename temp1 to temp;
5)alter database default temporary tablespace temp;
Re: how to delete tablespace "TEMP" data file normally? [message #310316 is a reply to message #310130] Mon, 31 March 2008 22:54 Go to previous message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

alter database tempfile '/u01/app/oracle/oradata/msi/temp01.dbf' drop including datafiles; 
Previous Topic: pfile and control file query
Next Topic: split from hijacked thread Re: ORA-27101 SHARED MEMORY REALM DOES'NT EXIST
Goto Forum:
  


Current Time: Thu Dec 08 10:42:43 CST 2016

Total time taken to generate the page: 0.12389 seconds