Code by Example: Drop Datafile

There are many tablespaces, that actually do not hold any segments.
These are historical tablespaces.
The segments were dropped, but tablespaces, and related datafiles, were not.

Step 1.
Fetch all empty tablespaces

SELECT tablespace_name, file_name
  FROM DBA_DATA_FILES 
 WHERE tablespace_name IN (
  SELECT tablespace_name FROM (
    SELECT tablespace_name FROM DBA_TABLESPACES 
     MINUS 
    SELECT tablespace_name FROM DBA_SEGMENTS

  )

around 100 entries where returned!!

Here is an example for a single tablespace

Step 2.
Checks before dropping the tablespace and datafile.
Shrink the datafile.
This is a good practice before dropping a datafile, because if the datafile indded contain data, and an attampt is made to shring the datafile beyond the data limit, an error would be raised.  

Before resize: 
-rw-r-----  1 oracle dba 10493952 Jun  1 09:13 /oracle_db/db1/db_igt/GATES_DB_TABLE.dbf

ALTER DATABASE DATAFILE '/oracle_db/db1/db_igt/GATES_DB_TABLE.dbf' RESIZE 2M;
oracle@isr-sdc-1-cgw-1:~>% ls -l /oracle_db/db1/db_igt/GATES_DB_TABLE.dbf
-rw-r-----  1 oracle dba 2105344 Jun  1 09:15 /oracle_db/db1/db_igt/GATES_DB_TABLE.dbf

ALTER DATABASE DATAFILE '/oracle_db/db1/db_igt/GATES_DB_TABLE.dbf' RESIZE 1M;

oracle@isr-sdc-1-cgw-1:~>% ls -l /oracle_db/db1/db_igt/GATES_DB_TABLE.dbf
-rw-r-----  1 oracle dba 1056768 Jun  1 09:26 /oracle_db/db1/db_igt/GATES_DB_TABLE.dbf

ALTER DATABASE DATAFILE '/oracle_db/db1/db_igt/GATES_DB_TABLE.dbf' RESIZE 512K;
 Database altered

ALTER DATABASE DATAFILE '/oracle_db/db1/db_igt/GATES_DB_TABLE.dbf' RESIZE 256K;
 Database altered

ALTER DATABASE DATAFILE '/oracle_db/db1/db_igt/GATES_DB_TABLE.dbf' RESIZE 128K;

 Database altered

ALTER DATABASE DATAFILE '/oracle_db/db1/db_igt/GATES_DB_TABLE.dbf' RESIZE 64K

ORA-03214: File Size specified is smaller than minimum required

oracle@isr-sdc-1-cgw-1:~>% ls -l /oracle_db/db1/db_igt/GATES_DB_TABLE.dbf
-rw-r-----  1 oracle dba 139264 Jun  1 09:28 /oracle_db/db1/db_igt/GATES_DB_TABLE.dbf

Although the Tablespace is empty, the datafile cannot be shrinked below one extent size.

SQL> SELECT COUNT(*) FROM DBA_SEGMENTS WHERE tablespace_name = 'GATES_DB_TABLE';
  COUNT(*)
----------
         0

SQL> SELECT ROUND(bytes/1024) As Kb, 
            ROUND(user_bytes/1024) AS USER_KB, 
            tablespace_name 
       FROM DBA_DATA_FILES 
      WHERE file_name = '/oracle_db/db1/db_igt/GATES_DB_TABLE.dbf' ;

        KB    USER_KB TABLESPACE_NAME
---------- ---------- ------------------------------
       128         64 GATES_DB_TABLE

SQL> SELECT tablespace_name, 
            block_size/1024 AS block_size_kb, 
            initial_extent/1024 AS initial_extent_kb  
       FROM DBA_TABLESPACES 
      WHERE tablespace_name = 'GATES_DB_TABLE';

TABLESPACE_NAME                BLOCK_SIZE_KB INITIAL_EXTENT_KB
------------------------------ ------------- -----------------
GATES_DB_TABLE                             8                64



Step 3.
Drop tablespace and datafile
Option A.
Drop the datafile using ALTER TABLESPACE DROP DATAFILE Syntax.

ALTER TABLESPACE GATES_DB_TABLE DROP DATAFILE '/oracle_db/db1/db_igt/GATES_DB_TABLE.dbf'

ORA-02142: missing or invalid ALTER TABLESPACE option

It is not allowed to drop the first or the only datafile in tablespace.

SELECT COUNT(*) FROM DBA_TABLESPACES WHERE tablespace_name = 'GATES_DB_TABLE';

  COUNT(*)
----------
         1

Option B.
Drop datafile using DROP TABLESPACE INCLUDING CONTENTS Syntax.
This would drop the tablespace, and related datafile(s) from Oracle dictionary but not the physical file on the host.
To do so, need to run OS command.
   
DROP TABLESPACE GATES_DB_TABLE INCLUDING CONTENTS;

Tablespace dropped

ls -l /oracle_db/db1/db_igt/GATES_DB_TABLE.dbf
-rw-r-----  1 oracle dba 139264 Jun  1 11:15 /oracle_db/db1/db_igt/GATES_DB_TABLE.dbf


SELECT ROUND(bytes/1024) As Kb, ROUND(user_bytes/1024) AS USER_KB, tablespace_name 
  FROM DBA_DATA_FILES 
  WHERE file_name = '/oracle_db/db1/db_igt/GATES_DB_TABLE.dbf' ;

        KB    USER_KB TABLESPACE_NAME
---------- ---------- ------------------------------

rm /oracle_db/db1/db_igt/GATES_DB_TABLE.dbf