A popular saying is “It ain’t so much the things we don’t know that get us into trouble. It’s the things we know that just ain’t so.” Wikiquote attributes it to a 19th century American humorist named Artemus Ward but The Quote Verifier: Who Said What, Where, and When attributes it to another 19th century humorist named Josh Billings who actually wrote: “I honestly beleave it iz better tew know nothing than two know that ain’t so.” Another versions attributed to Josh Billings are “It iz better to kno less than to kno so much that ain’t so” and “You’d better not kno so much than know so many things that ain’t so.” The misattribution to Artemus Ward is just another example of the problem that Josh Billings was talking about.

Tom Kyte has an interesting twist: “It ain’t so much the things we don’t know that get us into trouble. It’s the things you know that just ain’t so or just ain’t so anymore or just ain’t always so.” I was reminded of it today when the need arose to delete a data file from an Oracle database. When I was being interviewed for a database administrator position in early 2004, the interviewing manager asked me only one question: “Is it possible to drop an Oracle data file?” He did not ask me any other questions because he knew very little about Oracle. I probably got the job on the strength of my OCP certificate and being available immediately though the manager did mention that I had the best-formatted resume he had every seen.

At that time, it was not possible to drop a data file from an Oracle database; the capability was introduced in Oracle Database 10gR2 which was released in late 2004. Here is a demonstration.

SQL> -- Check the database version
SQL> SELECT version FROM v$instance;

VERSION
-----------------
10.2.0.1.0

SQL> -- Create a tablespace named Test
SQL> CREATE TABLESPACE test DATAFILE
  2  'C:\ORACLE\PRODUCT\10.2.0\ORADATA\IGGY\TEST01.dbf'
  3  SIZE 1 m;

Tablespace created.

SQL> -- Create a table named Test in the new tablespace
SQL> CREATE TABLE test TABLESPACE test
  2  AS SELECT * FROM DUAL;

Table created.

SQL> -- Add a second data file to the tablespace
SQL> ALTER TABLESPACE test ADD DATAFILE
  2  'C:\ORACLE\PRODUCT\10.2.0\ORADATA\IGGY\TEST02.dbf'
  3  SIZE 1 m;

Tablespace altered.

SQL> -- Add a third data file to the tablespace
SQL> ALTER TABLESPACE test ADD DATAFILE
  2  'C:\ORACLE\PRODUCT\10.2.0\ORADATA\IGGY\TEST03.dbf'
  3  SIZE 1 m;

Tablespace altered.

SQL> -- Manually allocate an extent to the new table
SQL> ALTER TABLE test ALLOCATE EXTENT (DATAFILE
  2  'C:\ORACLE\PRODUCT\10.2.0\ORADATA\IGGY\TEST03.dbf'
  3  SIZE 64 k);

Table altered.

SQL> -- Add a fourth data file to the tablespace
SQL> ALTER TABLESPACE test ADD DATAFILE
  2  'C:\ORACLE\PRODUCT\10.2.0\ORADATA\IGGY\TEST04.dbf'
  3  SIZE 1 m;

Tablespace altered.

SQL> -- List the data files
SQL> SELECT file_id
  2  FROM dba_data_files
  3  WHERE tablespace_name = 'TEST';

   FILE_ID
----------
         6
         7
         8
         9

SQL> -- List the extents of the table
SQL> SELECT extent_id, file_id, BYTES / 1024 AS kb
  2  FROM dba_extents
  3  WHERE  tablespace_name = 'TEST';

 EXTENT_ID    FILE_ID         KB
---------- ---------- ----------
         0          6         64
         1          8         64

SQL> -- Drop the data files
SQL> ALTER TABLESPACE test DROP DATAFILE
  2  'C:\ORACLE\PRODUCT\10.2.0\ORADATA\IGGY\TEST01.dbf';
ALTER TABLESPACE test DROP DATAFILE
*
ERROR at line 1:
ORA-03263: cannot drop the first file of tablespace TEST

SQL> ALTER TABLESPACE test DROP DATAFILE
  2  'C:\ORACLE\PRODUCT\10.2.0\ORADATA\IGGY\TEST02.dbf';

Tablespace altered.

SQL> ALTER TABLESPACE test DROP DATAFILE
  2  'C:\ORACLE\PRODUCT\10.2.0\ORADATA\IGGY\TEST03.dbf';
ALTER TABLESPACE test DROP DATAFILE
*
ERROR at line 1:
ORA-03262: the file is non-empty

SQL> ALTER TABLESPACE test DROP DATAFILE
  2  'C:\ORACLE\PRODUCT\10.2.0\ORADATA\IGGY\TEST04.dbf';

Tablespace altered.

SQL> -- List the data files again
SQL> SELECT file_id
  2  FROM dba_data_files
  3  WHERE tablespace_name = 'TEST';

   FILE_ID
----------
         6
         8

Obviously you cannot drop an Oracle data file if it contains data. For extra credit, what are the other restrictions on dropping Oracle data files?