Deleting Oracle datafiles

Last updated
Save as PDF
Share
1. Share
2. Tweet
3. Share

Article Type: General
Product: Aleph
Product Version: 19.01

Description:
We accidentally added a 20 gig datafile to the TEMP tablespace when we meant to add a 2 gig. How can we remove it?

Resolution:
The datafiles once added may not be deleted. The only option is to drop the tablespace and create it again with correct datafiles. Since it's a temporary tablespace , there shouldn't be a problem with data. This should be done when the system is not in use since the TEMP tablespace is also used by the online....

As described at http://www.idevelopment.info/data/Oracle/DBA_tips/Tablespaces/TBS_3.shtml , the following sequence needs to be used for Oracle 9-up:

In cases where the temporary tablespace you want to resize (using the drop/recreate method) is the default temporary tablespace for the database, you have several more steps to perform, all documented below.

The first step you need to perform is create another temporary tablespace (lets call it TEMP2). The next step would be to remove the temporary tablespace you want to resize from being the default temporary tablespace (in our example, this will be a tablespace named TEMP) by making TEMP2 the default. Drop / recreate the TEMP tablespace to the size you want. Finally, make the newly created TEMP tablespace your default temporary tablespace for the database and drop the TEMP2 tablespace. A full example session is provided below. [You need to substitute the proper datafile path for your installation.] :

SQL> CREATE TEMPORARY TABLESPACE temp2
2 TEMPFILE '/u02/oradata/TESTDB/temp2_01.dbf' SIZE 5M REUSE
3 AUTOEXTEND ON NEXT 1M MAXSIZE unlimited
4 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

Tablespace created.

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

Database altered.

SQL> DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

SQL> CREATE TEMPORARY TABLESPACE temp
2 TEMPFILE '/u02/oradata/TESTDB/temp01.dbf' SIZE 500M REUSE
3 AUTOEXTEND ON NEXT 100M MAXSIZE unlimited
4 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

Tablespace created.

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

Database altered.

SQL> DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

<end answer for Oracle 9-up>

*************Old Answer (for Oracle 8 or earlier)*********************:

To drop the tablespace:

SQL> drop tablespace TEMP including contents;.

This will not delete the file(s) but the file(s) should now be released from Oracle so that you can delete them with the "rm" command.

I think you will need to be logged on as the Oracle user to do this. (Or as root and then "su - oracle".).
You should do this only with the TEMP tablespace.

Here's sample SQL for re-creating it (substituting your own filenames and sizes):.

CREATE TABLESPACE TEMP DATAFILE.
'/ora04/oradata/aleph2/temp01.dbf' SIZE 1000 M,.
'/ora04/oradata/aleph2/temp02.dbf' SIZE 1000 M.
DEFAULT STORAGE (INITIAL 256K NEXT 256K MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0) TEMPORARY;.

This should be done by your Oracle DBA.

Article last edited: 10/8/2013