Dropped temp datafile still referred to in BRTOOLS

[Former Member]

Hello All,

We have recently dropped an unused last tempfile from PSAPTEMP tablespace with the help og BRTOOLS . The datafile was dropped, the file was deleted from the file system. But now if I run any BRTOOLS, it tries to find this file and reports this file as "offline", for ex brcheck reports error that the file is not found and that the file is "offline". How can we avoid these messages.

We queried dba_

[Reagan]

Hello

I dont see any logs or any output of the SQL queries in this discussion showing how the tempfile was dropped and the status of the tempfile in the database.

If the tempfile is shown as offline then the tempfile was not dropped. Instead it was taken offline.

It looks like the tempfile was dropped with an OFFLINE DROP clause.

By doing this it will not drop the tempfile instead it will be taken Offline and Oracle will stop accessing the tempfile. It will still be part of the database.

Refer to this link:

http://docs.oracle.com/cd/B19306_01/server.102/b14231/dfiles.htm

Regards

RB

[former_member184473]

Hello Sonia,

If you try to drop the temp file:

ALTER DATABASE TEMPFILE <temp_file_name>' DROP INCLUDING DATAFILES;

Does the file continue on dba_temp_files?

select * from dba_temp_files;

Regards,

Eduardo Rezende

[Former Member]

Hello Eduardo,

Please see my previous reply. Thanks.

Regards,

Sonia

[former_member206552]

Hi Sonia,

can you please upload the log file

can you please also check sqlplus and do a

select NAME,STATUS from v$tempfile;

and see if the files was delete from the database

best regards

marius

[Former Member]

Hello Marius,

Yes, the files continue to show in dba_temp_files. Hence I suspect that the data dictionary is not updated following the DROP action. I can confirm the  control file is updated. What can be done now?

Regards,

Sonia

[former_member206552]

Hi Sonia,

I would recreate the temp tablespace as a quick solution.

How to drop and recreate TEMP Tablespace in Oracle 9i/10g/11g

1. Create Temporary Tablespace Temp

CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE  ‘/u01/app/oradata/temp01′ SIZE 2000M, ‘/u01/app/oradata/temp02′ SIZE 2000M';

2. Move Default Database temp tablespace

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

3. Make sure No sessions are using your Old Temp tablespace

   a.  Find Session Number from V$SORT_USAGE:

       SELECT USERNAME, SESSION_NUM, SESSION_ADDR FROM V$SORT_USAGE;

   b.  Find Session ID from V$SESSION:

       If the resultset contains any tows then your next step will be to find the SID from the V$SESSION view. You can find session id by using SESSION_NUM or SESSION_ADDR from previous resultset.

       SELECT SID, SERIAL#, STATUS FROM V$SESSION WHERE SERIAL#=SESSION_NUM;

       OR

       SELECT SID, SERIAL#, STATUS FROM V$SESSION WHERE SADDR=SESSION_ADDR;

    c.  Kill Session:

    Now kill the session with IMMEDIATE.

    ALTER SYSTEM KILL 'SID,SERIAL#' IMMEDIATE;

4. Drop temp tablespace

DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

5. Recreate Tablespace Temp

CREATE TEMPORARY TABLESPACE TEMP TEMPFILE /u01/app/temp/temp01′ SIZE 2000M;

6 Move Tablespace Temp, back to new temp tablespace

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

7. Drop temporary for tablespace temp

DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;

Have you tried to run the drop command like Eduardo suggested ?

Best Regards

Marius

[former_member188883]

Hi Sonia,

Could you check the presence of temp file under dba_data_files ?

If the files are present as data file & not temp files, you may need to drop them as well.

Also as per oracle docs, if the data file is offline it cannot be dropped.

http://docs.oracle.com/cd/B19306_01/server.102/b14231/dfiles.htm#i1006556

you may try this command to drop the offline datafile

ALTER DATABASE "DATAFILE '' OFFLINE DROP;

Hope this helps.

Regards,

Deepak Kori