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