0

I have tried dropping the datafile by itself but I get this error.

SQL> alter tablespace USERS drop datafile '/u01/app/oracle/oradata/orcl/users01.dbf';
alter tablespace USERS drop datafile '/u01/app/oracle/oradata/orcl/users01.dbf'
*
ERROR at line 1:
ORA-03261: the tablespace USERS has only one file

I have also tried taking the tablespace offline and dropping the whole tablespace but that doesn't work either.

SQL> drop tablespace users including contents and datafiles;
drop tablespace users including contents and datafiles
*
ERROR at line 1:
ORA-12919: Can not drop the default permanent tablespace

Edit: This is for an assignment to simulate the failure of a datafile that I will be recovering. I would delete it manually but the assignment is requiring me to use SQL.

swanhella
  • 441
  • 6
  • 14
  • 1
    "*Can not drop the default permanent tablespace*" is pretty clear isn't it? You need to make a different tablespace the default tablespace before you can drop `USERS` –  Jan 29 '15 at 22:44
  • One (or more) users has the `USERS` tablespace set as the default tablespace. You'll need to change that before you can drop the tablespace. – Justin Cave Jan 29 '15 at 22:45
  • I just want to drop the datafile and not the whole tablespace to simulate a hardware failure. – swanhella Jan 29 '15 at 22:47
  • Create another file in that tablespace, and drop the file you wanted to drop in the first place. If the assignment is to drop and recover 1 file, that should work out just nice. – tvCa Jan 29 '15 at 23:23

2 Answers2

4

Since the USERS is the default tablespace, you can't drop any of its datafiles and would get the error ORA-12919. The solution is to create another tablespace and then change the default tablespace:

SQL> CREATE TABLESPACE USERS_TEMP DATAFILE '/u01/app/oracle/oradata/orcl/users_temp_01.dbf' size 10M AUTOEXTEND ON;
SQL>alter database default tablespace USERS_TEMP;

Now you can remove USERS:

SQL>DROP TABLESPACE USERS INCLUDING CONTENTS AND DATAFILES;

To delete the datafile located on 'app\Ora11gHome\oradata\someName' , you must turn the database service off. Open Oracle Administration Assistant and stop the service, now you can delete the file.

Danilo Meira
  • 41
  • 4
2

If you only want to drop the datafile to simulate a hardware failure, than you can simply delete them from the machine.

You can know where are stored your datafile using a simple query :

select name
from V$DATAFILE;

This will give the path to all data files.

Edit : To simulate an even more cool system failure to make sure your backup scripts or whatever you are testing is properly working, you could also corrupt the data (open a file and write some random gibberish in it)

Jean-François Savard
  • 20,626
  • 7
  • 49
  • 76
  • Yeah that's what I ended up doing. Our instructor realized it wasn't as easy as one SQL statement so he said we can just delete it manually. – swanhella Jan 29 '15 at 23:07