0

How to drop the temp table space including alias. 

SQL> CREATE TEMPORARY TABLESPACE TEMPRM_TEMP;
SQL> ALTER  TABLESPACE TEMPRM_TEMP ADD TEMPFILE '+TEMPDATA/rm/datafile/TEMPRM_TEMPFILE_1.dbf' SIZE 2M;
SQL> DROP TABLESPACE TEMPRM_TEMP INCLUDING CONTENTS AND DATAFILES;     
SQL> CREATE TEMPORARY TABLESPACE TEMPRM_TEMP;       
SQL> ALTER  TABLESPACE TEMPRM_TEMP ADD TEMPFILE '+TEMPDATA/rm/datafile/TEMPRM_TEMPFILE_1.dbf' SIZE 2M
         *
         ERROR at line 1:
         ORA-01119: error in creating database file
         '+TEMPDATA/rm/datafile/TEMPRM_TEMPFILE_1.dbf'
         ORA-17502: ksfdcre:4 Failed to create file
         +TEMPDATA/rm/datafile/TEMPRM_TEMPFILE_1.dbf
         ORA-15005: name "rm/datafile/TEMPRM_TEMPFILE_1.dbf" is already used by an
         existing alias

Please explain, how to clean Alias in Oracle ASM ? thanks

Improve this question
6
  • The error seems relatively self-explanatory. Does that DBF exist?
    – Ben
    Commented Aug 5, 2015 at 7:39
  • Yes.. my question is why 'DROP TABLESPACE' is not able clean the alias ?
    – malatesh
    Commented Aug 5, 2015 at 7:44
  • You can see you're using two file names, right? Some other temporary TS is using, or has used, TEMPRM_TEMPFILE_1.dbf. Maybe you tried to create it before with a different TS name? What does dba_temp_files show? Or you created and dropped it with that name previously, but without including datafiles?
    – Alex Poole
    Commented Aug 5, 2015 at 8:24
  • Your edit is making things even less clear - what did you really run? What temporary file name was allocated by the CREATE TEMPORARY TABLESPACE TEMPRM_TEMP statement?
    – Alex Poole
    Commented Aug 5, 2015 at 9:05
  • Steps as below. 1)Create TS 2)Add file TS 3)DROP TS 4) Create TS with same name as in step 1 2)Add file with same name in step 2
    – malatesh
    Commented Aug 5, 2015 at 9:08

1 Answer 1

Reset to default
1

Resolved by removing Alias

ASMCMD> rmalias '+TEMPDATA/rm/datafile/TEMPRM_TEMPFILE_1.dbf'
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.