CODEWITHSUNDEEP
oracle-database
Malatesh
Malatesh

Reputation: 1954

Oracle tablespace can I drop all files

I am trying drop the temp tables space it has three files

/tmp/TEMPRM/create/TEMPRM/datafile/o1_mf_temprm_t_bw3t4zkp_.tmp
+TEMPDATA/rm/datafile/temprm_tempfile_1.dbf
+TEMPDATA/rm/datafile/temprm_tempfile_2.dbf

Before dropping table space I want to delete all the file, I am able to remove the first two files last one gives the error.

 SQL> SQL> ALTER  TABLESPACE TEMPRM_TEMP DROP TEMPFILE '+TEMPDATA/rm/datafile/temprm_tempfile_2.dbf'
 *
 ERROR at line 1:
 ORA-03261: the tablespace TEMPRM_TEMP has only one file

If it is not allowed to delete all the files in table space, How to clean the table space?

Upvotes: 2

Views: 2916

Answers (2)

Rene
Rene

Reputation: 10541

You cannot make a tablespace file-less. You can however drop a tablespace and it's datafiles in one statement:

DROP TABLESPACE temp_tablespace including contents and datafiles;

Make sure that you have a new temporary tablespace and make it the default before you drop the old one. Follow the below link for an example:

http://dbatricksworld.com/how-to-create-temporary-tablespace-and-drop-existing-temprary-tablespace-in-oracle-11g/

Upvotes: 3

davegreen100
davegreen100

Reputation: 2115

create a new temp tablespace, make this the default for the users. Once there are no connections using the old temp tablespace you should be able to drop the old temp tablespace

Upvotes: 0

Related Questions