Sample Header Ad - 728x90

How to drop a datafile of an already dropped tablespace

0 votes
0 answers
564 views
In an 18c Oracle database I have a tablespace that has been dropped. I need to add this tablespace anew, but when I try to do so I get this error: > ORA-01537: cannot add file 'MYTABLESPACE.DBF' - file already part of database By the way I create my tablespace like this: CREATE TABLESPACE MYTABLESPACE DATAFILE 'MYTABLESPACE.DBF' SIZE 1024M AUTOEXTEND ON NEXT 102400K I know that I can use ALTER to remove a datafile like this: ALTER TABLESPACE MYTABLESPACE DROP DATAFILE '{path}\MYTABLESPACE.DBF'; But this doesn't work, and I get: > ORA-00959: tablespace does not exist When I run: SELECT* FROM v$datafile I can see the datafile with the Id of dropped tablespace listed as online. When I query the TS$ table: SELECT * FROM TS$ I can see that the tablespace is dropped (ONLINE$ column has the value of 3). I know that I can change the name of tablespace and datafile, but for reasons I need them to stay the same. The question is, how do I get rid of this orphaned datafile?
Asked by jahu (113 rep)
Nov 22, 2023, 10:04 AM