CODEWITHSUNDEEP
oracle-databasetablespace
WT86
WT86

Reputation: 833

CREATE and DROP TABLESPACE Oracle

I have created this tablespace

CREATE TABLESPACE IA643_TBS
DATAFILE 'IA643_dat' SIZE 500K
AUTOEXTEND ON NEXT 300K MAXSIZE 100M;

I tried to drop it using this command

DROP TABLESPACE IA643_TBS;

And it said that it was dropped, when I tried to create it again, I got those error messages:

ERROR at line 1: 
ORA-01119: error in creating database file 'IA643_dat' 
ORA-27038: created file already exists 
OSD-04010: <create> option specified, file already exists

How can I delete the datafile and recreate the tablespace with same file names?

Upvotes: 13

Views: 13833

Answers (2)

Avnish Patel
Avnish Patel

Reputation: 141

Answer of @Allan correct but for more clarity, let me show my example

SQL> CREATE TEMPORARY TABLESPACE tbs_temp_01 
 2    TEMPFILE 'tbs_temp_01.dbf'
 3    SIZE 5M reuse
 4    AUTOEXTEND ON;

enter image description here

Upvotes: 1

Allan
Allan

Reputation: 17429

You can either login to the operating system and actually delete the file or add the reuse keyword after the size in your create tablespace command.

Upvotes: 13

Related Questions