What Happens to Data Files If a Tablespace Is Dropped?

If a tablespace is dropped then datafiles are remain in operating system .
The logical path is broken between datafile and tablespace.
If you want to delete the tablespace along with the datafiles use this clause
INCLUDING CONTENTS AND DATAFILES.


SQL>  CREATE TABLESPACE IMMU DATAFILE '/d01/oracle/oradata/prod/IMMU.dbf' size 100m;

Tablespace created.

SQL>  select tablespace_name,file_name from dba_data_files;

TABLESPACE_NAME           FILE_NAME
------------------------- -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
USERS                     /d01/oracle/oradata/prod/users01.dbf
UNDOTBS1                  /d01/oracle/oradata/prod/undotbs01.dbf
SYSAUX                    /d01/oracle/oradata/prod/sysaux01.dbf
SYSTEM                    /d01/oracle/oradata/prod/system01.dbf
USERS                     /d01/oracle/oradata/prod/users02.dbf
EXAMPLE                   /d01/oracle/oradata/prod/example01.dbf
ABDULWASIQ                /d01/oracle/oradata/prod/abdulwasiq.dbf
MYTBS                     /d01/oracle/oradata/prod/tbs.dbf
IMMU                      /d01/oracle/oradata/prod/IMMU.dbf

9 rows selected.

SQL> drop tablespace IMMU including contents and datafiles;

Tablespace dropped.

SQL> select tablespace_name,file_name from dba_data_files;

TABLESPACE_NAME           FILE_NAME
------------------------- -----------------------------------------------------------------------------------
USERS                     /d01/oracle/oradata/prod/users01.dbf
UNDOTBS1                  /d01/oracle/oradata/prod/undotbs01.dbf
SYSAUX                    /d01/oracle/oradata/prod/sysaux01.dbf
SYSTEM                    /d01/oracle/oradata/prod/system01.dbf
USERS                     /d01/oracle/oradata/prod/users02.dbf
EXAMPLE                   /d01/oracle/oradata/prod/example01.dbf
ABDULWASIQ                /d01/oracle/oradata/prod/abdulwasiq.dbf
MYTBS                     /d01/oracle/oradata/prod/tbs.dbf

8 rows selected.

SQL>

Tablespace is dropped along with datafile's.