0

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?

Midhun MP
  • 103,496
  • 31
  • 153
  • 200
user1459976
  • 207
  • 6
  • 14

1 Answers1

1

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';
Mark J. Bobak
  • 13,720
  • 6
  • 39
  • 67
  • it returns: no rows selected – user1459976 Dec 05 '13 at 01:22
  • Does the file 'C:/Oracle/oradata/orcl/temp.dbf' exist in the filesystem? Are any other databases running on the server? If so, try running the query above on all the databases installed on the server. – Mark J. Bobak Dec 05 '13 at 01:25
  • your insights are correct. i remember i mistyped something along the way and i tried to guess my mistyped word and i was able to drop it and also create.. thank you – user1459976 Dec 05 '13 at 01:25