Drop datafile in Oracle.

With the statement ALTER TABLESPACE tablespace_name DROP DATAFILE {file_name|file_id} you can drop an empty datafile from a tablespace. This casues datafile will be deleted from data dictionary as well as from operating system. With ALTER TABLESPACE ... DROP TEMPFILE you can also delete any temporary data file.

Though we can drop datafile from tablespace. There is restriction on dropping.

•The file must be empty.
•Cannot be the first file that was created in the tablespace. In such cases,in order to drop the datafile drop the tablespace instead.
•Cannot be in a read-only tablespace.
•If there is only one file exist on the tablespace then that file can't be deleted which is analogous to file file of the tablespace.

With an example I will try to demonstrate the imposed restriction of dropping datafile.

1)Create Tablespace with Three Datafiles.

SQL> CREATE TABLESPACE TEST_DF_DELETE DATAFILE '/oradata1/arju/1.dbf' SIZE 500K, '/oradata1/arju/2.dbf' SIZE 600K, '/oradata1/arju/3.dbf' SIZE 1m;
Tablespace created.

2)Create a Table Under it.

SQL> CREATE TABLE TEST_TABLE TABLESPACE TEST_DF_DELETE AS SELECT LEVEL COL1 FROM DUAL CONNECT BY LEVEL<=1000;
Table created.

3)Check for in which datafile the Table span for.

SQL> SELECT FILE_NAME, FILE_ID FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='TEST_DF_DELETE';
FILE_NAME FILE_ID
---------------------------------------- ----------
/oradata1/arju/1.dbf 8
/oradata1/arju/2.dbf 9
/oradata1/arju/3.dbf 10

SQL> SELECT SEGMENT_NAME , FILE_ID FROM DBA_EXTENTS WHERE FILE_ID IN (8,9,10);
SEGMENT_NAME FILE_ID
-------------------- ----------
TEST_TABLE 8

4)Try to drop file_id 8.

Since it satisfy restriction so the file 8 can't be deleted. The restriction is not exmpty and first file of the tablespace. So error comes.

SQL> ALTER TABLESPACE TEST_DF_DELETE DROP DATAFILE 8;
ALTER TABLESPACE TEST_DF_DELETE DROP DATAFILE 8
*
ERROR at line 1:
ORA-03263: cannot drop the first file of tablespace TEST_DF_DELETE

5)Try to drop file_id 10.

Since it does not satisfy any of the restriction so it will be deleted.

SQL> ALTER TABLESPACE TEST_DF_DELETE DROP DATAFILE 10;
Tablespace altered.

6)Insert more rows into the table in order to span both file_id 8 and 9.

SQL> INSERT INTO TEST_TABLE SELECT LEVEL COL1 FROM DUAL CONNECT BY LEVEL<=20000;
20000 rows created.

SQL> COMMIT;
Commit complete.

SQL> SELECT DISTINCT SEGMENT_NAME , FILE_ID FROM DBA_EXTENTS WHERE FILE_ID IN (8,9);
SEGMENT_NAME FILE_ID
-------------------- ----------
TEST_TABLE 8
TEST_TABLE 9

7)Now try to drop file_id 9.

As now data is inside file_id 9 and so it is not empty which is the restriction of dropping datafile ans hence we can't drop file_id 9.

SQL> ALTER TABLESPACE TEST_DF_DELETE DROP DATAFILE 9;
ALTER TABLESPACE TEST_DF_DELETE DROP DATAFILE 9
*
ERROR at line 1:
ORA-03262: the file is non-empty

8)Drop the table and try to drop the data file 9.

SQL> DROP TABLE TEST_TABLE;
Table dropped.

SQL> SELECT DISTINCT SEGMENT_NAME , FILE_ID FROM DBA_EXTENTS WHERE FILE_ID IN (8,9);
no rows selected

SQL> ALTER TABLESPACE TEST_DF_DELETE DROP DATAFILE 9;
ALTER TABLESPACE TEST_DF_DELETE DROP DATAFILE 9
*
ERROR at line 1:
ORA-03262: the file is non-empty

After dropping it is failed because yet it is not empty though under dba_extents it is not showing. It is because the object resides in recyclebin.

9)Delete object from recyclebin and try to drop.

SQL> PURGE RECYCLEBIN;
Recyclebin purged.

SQL> ALTER TABLESPACE TEST_DF_DELETE DROP DATAFILE 9;
Tablespace altered.

It is succeed. But still you can't be able to drop datafile 8 since it does satisfy restriction.

SQL> ALTER TABLESPACE TEST_DF_DELETE DROP DATAFILE 8;
ALTER TABLESPACE TEST_DF_DELETE DROP DATAFILE 8
*
ERROR at line 1:
ORA-03261: the tablespace TEST_DF_DELETE has only one file

SQL> ALTER TABLESPACE TEST_DF_DELETE ADD DATAFILE '/oradata2/arju/new.dbf' size 1M;
Tablespace altered.

SQL> ALTER TABLESPACE TEST_DF_DELETE DROP DATAFILE 8;
ALTER TABLESPACE TEST_DF_DELETE DROP DATAFILE 8
*
ERROR at line 1:
ORA-03263: cannot drop the first file of tablespace TEST_DF_DELETE