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

migrated from stackoverflow.com Aug 19 '15 at 1:37

This question came from our site for professional and enthusiast programmers.

  • The error seems relatively self-explanatory. Does that DBF exist? – Ben Aug 5 '15 at 7:39
  • Yes.. my question is why 'DROP TABLESPACE' is not able clean the alias ? – malatesh Aug 5 '15 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 Aug 5 '15 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 Aug 5 '15 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 Aug 5 '15 at 9:08
0

Resolved by removing Alias

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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