OraFAQ Forum: Server Administration » how to delete tablespace "TEMP" data file normally?

Home » RDBMS Server » Server Administration » how to delete tablespace "TEMP" data file normally? (oracle10gR1 Solaris5.9)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

how to delete tablespace "TEMP" data file normally? [message #310130]

Mon, 31 March 2008 06:32

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?

Report message to a moderator

Re: how to delete tablespace "TEMP" data file normally? [message #310134 is a reply to message #310130]

Mon, 31 March 2008 06:52

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

Report message to a moderator

Re: how to delete tablespace "TEMP" data file normally? [message #310205 is a reply to message #310130]

Mon, 31 March 2008 10:03

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;

Report message to a moderator

Re: how to delete tablespace "TEMP" data file normally? [message #310316 is a reply to message #310130]

Mon, 31 March 2008 22:54

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;

Report message to a moderator

Previous Topic:	pfile and control file query
Next Topic:	split from hijacked thread Re: ORA-27101 SHARED MEMORY REALM DOES'NT EXIST

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Sep 06 16:54:49 CDT 2024