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

Code Example: Send mail from PLSQL using Oracle UTL_SMTP Package.

Example of sending an email from PL/SQL using Oracle UTL_SMTP Package.

CREATE OR REPLACE PROCEDURE SEND_MAIL (
pSender    VARCHAR2,
pRecipient VARCHAR2,UTL_SMTP
pSubject   VARCHAR2,
pMessage   VARCHAR2) IS

mailhost  CONSTANT VARCHAR2(30) := '66.777.888.99';
crlf      CONSTANT VARCHAR2(2):= CHR(13) || CHR(10);
mesg      VARCHAR2(15000);
mail_conn UTL_SMTP.connection;

BEGIN
   mail_conn := UTL_SMTP.open_connection(mailhost, 25);

   mesg := 'Date: ' ||
        TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss') || crlf ||
           'From: <'|| pSender ||'>' || crlf ||
           'Subject: '|| pSubject || crlf ||
           'To: '||pRecipient || crlf || '' || crlf || pMessage;

   UTL_SMTP.helo(mail_conn, mailhost);
   UTL_SMTP.mail(mail_conn, pSender);
   UTL_SMTP.rcpt(mail_conn, pRecipient);
   UTL_SMTP.DATA(mail_conn, mesg);
   UTL_SMTP.quit(mail_conn);
EXCEPTION
  WHEN UTL_SMTP.transient_error
    OR UTL_SMTP.permanent_error THEN
    BEGIN
      UTL_SMTP.quit(mail_conn);
    EXCEPTION
      WHEN UTL_SMTP.transient_error
        OR UTL_SMTP.permanent_error THEN
        NULL;
    END;
      RAISE_APPLICATION_ERROR(-20000, SQLERRM);

 END SEND_MAIL ;