Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Temp file cannot be dropped ?

USER101Feb 4 2015 — edited Feb 5 2015

Hi All,

Have any of you experienced this ? I refreshed my UAT database from PROD and my tempfile was not acessible. Rather renamed to the DISKGROUP name.. So, I created a new temp tablespace and moved all the users there.. But I am unable to drop the old tempfiles as it has DISK group name. Except for re-creating the controlfile, is there any other alternatives ?

SQL> select * from v$tempfile;

     FILE# CREATION_CHANGE# CREATION_TI        TS#     RFILE# STATUS ENABLED         BYTES     BLOCKS CREATE_BYTES BLOCK_SIZE NAME
---------- ---------------- ----------- ---------- ---------- ------- ---------- ---------- ---------- ------------ ---------- ------------------------------------------------------------
         1       1.0791E+13 09-JUN-2011          2          1 ONLINE READ WRITE          0          0   2.2750E+10       8192 +SHARED_DATA_DG01
         2       1.2427E+13 03-FEB-2015          2          2 ONLINE READ WRITE   52428800       6400     52428800       8192 +SHARED_DATA_DG01/olemetq3/tempfile/temp.401.870703885
         3       1.2427E+13 03-FEB-2015         20          1 ONLINE READ WRITE 1048576000     128000   1048576000       8192 +SHARED_DATA_DG01/olemetq3/tempfile/temp1.402.870705023
         4       1.2427E+13 03-FEB-2015         20          2 ONLINE READ WRITE 524288000      64000    524288000       8192 +SHARED_DATA_DG01/olemetq3/tempfile/temp1.403.870705023

SQL> alter database tempfile 2 drop;

Database altered.

SQL> alter database tempfile 1 drop;
alter database tempfile 1 drop
*
ERROR at line 1:
ORA-01516: nonexistent log file, data file, or temporary file "+SHARED_DATA_DG01"

SQL> alter database tempfile 1 offline;
alter database tempfile 1 offline
*
ERROR at line 1:
ORA-01516: nonexistent log file, data file, or temporary file "+SHARED_DATA_DG01"

SQL> select * from v$tempfile;

     FILE# CREATION_CHANGE# CREATION_TI        TS#     RFILE# STATUS ENABLED         BYTES     BLOCKS CREATE_BYTES BLOCK_SIZE NAME
---------- ---------------- ----------- ---------- ---------- ------- ---------- ---------- ---------- ------------ ---------- ------------------------------------------------------------
         1       1.0791E+13 09-JUN-2011          2          1 ONLINE READ WRITE          0          0   2.2750E+10       8192 +SHARED_DATA_DG01
         3       1.2427E+13 03-FEB-2015         20          1 ONLINE READ WRITE 1048576000     128000   1048576000       8192 +SHARED_DATA_DG01/olemetq3/tempfile/temp1.402.870705023
         4       1.2427E+13 03-FEB-2015         20          2 ONLINE READ WRITE 524288000      64000    524288000       8192 +SHARED_DATA_DG01/olemetq3/tempfile/temp1.403.870705023

Locked Post

New comments cannot be posted to this locked post.

Locked on Mar 5 2015

Added on Feb 4 2015

#general-database-discussions

11 comments

2,434 views