Temp file issue
674757Apr 9 2009 — edited Apr 9 2009Oracle 11g - 11.1.0.7 - Redhat 5.3 (all 64 bit)
-----------------------------------------------------------
Greetings all,
I need your help in sorting out a tempfile issue. Our normal temp file is stored at u01/oradata/CUPS/temp01.dbf. We created
an additional one to expand temp01, but placed it in the following directory: u02/oradata/CUPS/temp02.dbf (I know, this was by accident). Weeks
later, this file grew to almost 23GB. After the close of a business, I went into DB control Enterprsise Manager, and while it's on-line, selected
and deleted the temp02.dbf file in the /u02/oradata/CUPS folder. So far, should be routine, however, I noticed that it is not removing itself from
the list, even though it specifies "temp02.dbf successfully deleted." It still comes up in the datafile list as well. So, given that we needed
the space back in /u02, we deleted the temp02.dbf (23GB) file from the /u02 folder directly in the OS (acctually copied it to another folder).
The database size in DB control shows 23GB less, which is a good thing, but if I try to create a temp02.dbf file in the same directory: /u02/oradata/CUPS
folder, an ORA message tells me that a datafile already exsists is in the database. I can create temp02.dbf in another folder, but not
in /u02/oradata/CUPS. Here's what I get when I run the SQL command:
SQL> select file_name, tablespace_name from dba_temp_files;
FILE_NAME TABLESPACE_NAME
/u01/oradata/CUPS/temp01.dbf TEMP (Ok - originally created)
/u01/oradata/CUPS/temp02.dbf TEMP (Ok - Created afterwards)
/u02/oradata/CUPS/temp02.dbf TEMP (How do I get this to disappear permently?) Datafile temp02.dbf in the /u02 folder no longer exists).
I don't want any Oracle shutdown or start up issues when I bounce th DB.. Your help would be appreciated.
Thanks,
Ron