Below is the commands to drop a tablespace.
1. Drop a tablespace without removing the physical database files.
SQL> select file_name from dba_data_files where tablespace_name='TESTING'; FILE_NAME -------------------------------------------------------------------------------- /home/oracle/app/oracle/oradata/cdb1/testin1.dbf SQL> drop tablespace TESTING; Tablespace dropped. SQL> select file_name from dba_data_files where tablespace_name='TESTING'; no rows selected SQL> SQL> !ls -ltr /home/oracle/app/oracle/oradata/cdb1/testin1.dbf -rw-rw----. 1 oracle oracle 104865792 Aug 17 10:30 /home/oracle/app/oracle/oradata/cdb1/testin1.dbf
You can see the datafile is still present at os level.
2. Drop tablespace including the physical datafiles:
SQL> select file_name from dba_data_files where tablespace_name='TESTING'; FILE_NAME -------------------------------------------------------------------------------- /home/oracle/app/oracle/oradata/cdb1/testin1.dbf SQL> drop tablespace TESTING including contents and datafiles; Tablespace dropped. SQL> select file_name from dba_data_files where tablespace_name='TESTING'; no rows selected SQL> !ls -ltr /home/oracle/app/oracle/oradata/cdb1/testin1.dbf ls: cannot access /home/oracle/app/oracle/oradata/cdb1/testin1.dbf: No such file or directory
Now with including contents and datafile key word, the datafile has been removed from os level too.
