CREATE and DROP TABLESPACE Oracle

oracle tablespace
WT86 picture WT86 · Oct 23, 2014 · Viewed 11.6k times · Source

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?

Answer

Allan picture Allan · Oct 23, 2014

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.

Related questions

Find out free space on tablespace

Our application has failed a few times because an 'ORA-01536: space quota exceeded for tablespace', and we would like to be able to prevent this by checking regularly the free space on the tablespace and raising an alert when it …

Read More
ORA-01950: no privileges on tablespace 'USERS'

I'm getting this error: ORA-01950: no privileges on tablespace 'USERS' I have a default tablespace and I'm able to create tables, however, they do not show at the objects tab in Toad. I cannot insert anything into tables I create.

Read More
ORA-01653: unable to extend table by in tablespace ORA-06512

I tried to generate some test data by running the following sql. BEGIN FOR i IN 1..8180 LOOP insert into SPEEDTEST select 'column1', 'column2', 'column3', 'column4', 'column5', 'column6', 'column7', 'column8', 'column9', 'column10…

Read More