CODEWITHSUNDEEP
databaseoracle-databasetablespacesql-drop
user1459976
user1459976

Reputation: 207

Oracle SQL DROP CREATE TABLESPACE error

so im trying to drop a tablespace temp with the command 

DROP TABLESPACE temp INCLUDING CONTENTS;

but i get this error: tablespace 'temp' does not exist. however when i try and create the tablespace with this command 

CREATE TEMPORARY TABLESPACE temp
TEMPFILE 'C:/Oracle/oradata/orcl/temp.dbf'
SIZE 400M REUSE
AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED EXTENT
MANAGEMENT LOCAL;

i get this error: 'cannot add file... - file already apart of database'. anyone know what is going on?

Upvotes: 0

Views: 1048

Answers (1)

Mark J. Bobak
Mark J. Bobak

Reputation: 14393

My guess would be that the file is already part of the database, but part of a tablespace named something other than temp.

What do you get from the following query:

select tablespace_name from dba_data_files where file_name = 'C:/Oracle/oradata/orcl/temp.dbf'
union all
select tablespace_name from dba_temp_files where file_name = 'C:/Oracle/oradata/orcl/temp.dbf';

Upvotes: 1

Related Questions