How to rollback a CREATE TABLESPACE in Oracle 11g?

Question

I executed:

CREATE BIGFILE TABLESPACE tspvr010
  datafile 'tspvr010.dbf'
  size 120g;

but while that was working, my PC crashed (unexpected shutdown). When PC start again, the tablespace tspvr010 was not created, and tablespace USERS grow up by 30 gb. I try to drop tspvr010 but not exists. Now, i can't create that because i have no enought empty space.

Is possible rollback that?

score 0 · Answer 1 · edited Sep 23 '14 at 14:14

sql> select name from v$datafile;

Check if tspvr010.dbf file is not available. Then datafile 'tspvr010.dbf' information are not written into controlfile. if you find this file in the Oracle file system, then you can remove it. at OS level tspvr010.dbf.

Follow these steps:

you change your data_file loaction dir.
you will check tspvr010.dbf is available $ll tspvr010.dbf if tspvr010.dbf is available
$ rm -fr tspvr010.dbf

sql>startup force

sql>select sum(bytes)/1024/1024 from dba_data_files;

How to rollback a CREATE TABLESPACE in Oracle 11g?

1 Answers1