Home » Infrastructure » Windows » drop temp tablespace TEMP01.dbf
drop temp tablespace TEMP01.dbf [message #639960] Mon, 20 July 2015 14:06 Go to next message
imuharram
Messages: 48
Registered: January 2009
Member
TEMP01.DBF datafile has a huge size and while trying to drop and a new temp file using below commands

alter database tempfile 'E:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\TEMP01.DBF' drop including datafiles;


database altered, however, temp01.dbf datafile was not physically deleted
therefore, when executing command

alter tablespace temp add tempfile 'E:\oracle\product\10.1.0\oradata\orcl\TEMP01.DBF' SIZE 500m;


to add a datafile to temp tablespace an error is raised due to having the old datafile

should datafile be deleted manually?
Re: drop temp tablespace TEMP01.dbf [message #639963 is a reply to message #639960] Mon, 20 July 2015 14:36 Go to previous messageGo to next message
Michel Cadot
Messages: 68042
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

It is Windows, you can't unless you restart the instance.
Why don't you give another name for your new file, the old one will disappear when you will restart the instance.

Re: drop temp tablespace TEMP01.dbf [message #640045 is a reply to message #639963] Tue, 21 July 2015 08:57 Go to previous messageGo to next message
imuharram
Messages: 48
Registered: January 2009
Member
I gave it another name as suggested.
however, when trying to export database it is terminated unsuccessfully.
It was working fine 4 days ago, but face this disk space problem and wanted to solve it through dropping temp tablespace

Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in AR8MSWIN1256 character set and AL16UTF16 NCHAR character set
server uses WE8MSWIN1252 character set (possible charset conversion)

About to export specified users ...
. exporting pre-schema procedural objects and actions
EXP-00008: ORACLE error 4031 encountered
ORA-04031: unable to allocate 4096 bytes of shared memory ("shared pool","select /*+ rule */ bucket, e...","Typecheck heap","kgghteInit")
ORA-06512: at "SYS.DBMS_RULE_EXP_RL_INTERNAL", line 278
ORA-06512: at "SYS.DBMS_RULE_EXP_RULES", line 147
ORA-06512: at line 1
EXP-00083: The previous problem occurred when calling SYS.DBMS_RULE_EXP_RULES.schema_info_exp
EXP-00008: ORACLE error 604 encountered
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable to allocate 4096 bytes of shared memory ("shared pool","select /*+ index(idl_char$ i...","Typecheck heap","kgghteInit")
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at "SYS.DBMS_REPCAT_EXP", line 87
ORA-06512: at line 1
EXP-00083: The previous problem occurred when calling SYS.DBMS_REPCAT_EXP.schema_info_exp
EXP-00008: ORACLE error 600 encountered
ORA-00600: internal error code, arguments: [k2dgetgdnm-5], [604], [ORA-00604: error occurred at recursive SQL level 3
ORA-04031: unable to allocate 4096 bytes of shared memory ("shared pool","select /*+ rule */ bucket, e...","Typecheck heap","kgghteInit")
], [], [], [], [], []
ORA-06512: at "SYS.DBMS_LOGREP_EXP", line 2574
ORA-06512: at line 1
EXP-00083: The previous problem occurred when calling SYS.DBMS_LOGREP_EXP.schema_info_exp
. exporting foreign function library names for user WORK_ORDER 
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user WORK_ORDER 
About to export WORK_ORDER's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
EXP-00056: ORACLE error 604 encountered
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable to allocate 4096 bytes of shared memory ("shared pool","select /*+ index(idl_ub1$ i_...","Typecheck heap","kgghteInit")
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at "SYS.DBMS_METADATA", line 2576
ORA-06512: at "SYS.DBMS_METADATA", line 2719
ORA-06512: at line 1
EXP-00000: Export terminated unsuccessfully
Re: drop temp tablespace TEMP01.dbf [message #640046 is a reply to message #640045] Tue, 21 July 2015 08:59 Go to previous messageGo to next message
Michel Cadot
Messages: 68042
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You have to restart your database.

Re: drop temp tablespace TEMP01.dbf [message #640080 is a reply to message #640045] Tue, 21 July 2015 16:25 Go to previous messageGo to next message
imuharram
Messages: 48
Registered: January 2009
Member
I restarted the database.. export is working fine now
However, old datafile TEMP01.dbf is still there??

[Updated on: Tue, 21 July 2015 17:00]

Report message to a moderator

Re: drop temp tablespace TEMP01.dbf [message #640081 is a reply to message #640080] Tue, 21 July 2015 19:31 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>However, old datafile TEMP01.dbf is still there??
What did you do so that Oracle would not use it when the DB restarted?

If Oracle did not open it & use it, then you will be able to delete it.
If Oracle has opened the file, then the OS won't allow you to delete it.
Re: drop temp tablespace TEMP01.dbf [message #640086 is a reply to message #640080] Wed, 22 July 2015 00:55 Go to previous messageGo to next message
Michel Cadot
Messages: 68042
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
imuharram wrote on Tue, 21 July 2015 23:25
I restarted the database.. export is working fine now
However, old datafile TEMP01.dbf is still there??


Now you can manually drop it.

Re: drop temp tablespace TEMP01.dbf [message #640092 is a reply to message #640080] Wed, 22 July 2015 02:57 Go to previous messageGo to next message
gazzag
Messages: 1103
Registered: November 2010
Location: Bedwas, UK
Senior Member
SQL> SELECT name FROM v$tempfile;
Re: drop temp tablespace TEMP01.dbf [message #640127 is a reply to message #640092] Wed, 22 July 2015 15:11 Go to previous messageGo to next message
imuharram
Messages: 48
Registered: January 2009
Member
NAME
--------------------------------------------------------------------------------
E:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\TEMP001.DBF


which is the new tempfile created
Re: drop temp tablespace TEMP01.dbf [message #640140 is a reply to message #640127] Thu, 23 July 2015 00:12 Go to previous messageGo to next message
Michel Cadot
Messages: 68042
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Is this not the same than the previous one?

[Updated on: Thu, 23 July 2015 00:12]

Report message to a moderator

Re: drop temp tablespace TEMP01.dbf [message #640157 is a reply to message #640140] Thu, 23 July 2015 02:56 Go to previous messageGo to next message
gazzag
Messages: 1103
Registered: November 2010
Location: Bedwas, UK
Senior Member
No. Previously:
'E:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\TEMP01.DBF'

Now:
E:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\TEMP001.DBF

@OP, if the above is correct, you can delete TEMP01.DBF via the O/S. As BlackSwan said, if Oracle is using it the O/S won't let you delete it.
Re: drop temp tablespace TEMP01.dbf [message #640161 is a reply to message #640157] Thu, 23 July 2015 03:13 Go to previous message
Michel Cadot
Messages: 68042
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Ah! didn't see the adding 0.

Previous Topic: ODBC Drivers
Next Topic: Unable to connect SQLState=IM004 Driver's SQLAllocHandle on SQL_HANDLE_ENV failed
Goto Forum:
  


Current Time: Tue Nov 30 05:52:16 CST 2021