-- drop tablespace: 
DROP TABLESPACE <tablespace_name>;
DROP TABLESPACE tspace;
 
-- drop tablespace including contents: 
DROP TABLESPACE <tablespace_name>
INCLUDING CONTENTS;
DROP TABLESPACE tspace INCLUDING CONTENTS;
 
-- drop tablespace including contents & datafiles:      
DROP TABLESPACE <tablespace_name>
INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE tspace INCLUDING CONTENTS AND DATAFILES;
 
-- drop tablespace including contents & datafiles 
-- where referential constraints exist:     
DROP TABLESPACE <tablespace_name> INCLUDING CONTENTS AND DATAFILES
CASCADE CONSTRAINTS;
DROP TABLESPACE tspace INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;
 
-- drop tablespace after datafile was accidentally dropped: 
conn / AS sysdba
 
CREATE TABLESPACE testbed
DATAFILE 'c:\temp\testbed.dbf' SIZE 10M
BLOCKSIZE 4096
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K
SEGMENT SPACE MANAGEMENT AUTO
ONLINE;
 
SELECT tablespace_name
FROM dba_tablespaces;
 
SELECT file_name
FROM dba_data_files;
 
shutdown IMMEDIATE;
 
-- drop the file c:\temp\testbed.dbf
 
startup
 
-- record the error message
 
shutdown IMMEDIATE;
startup mount;
ALTER database datafile 'c:\temp\testbed.dbf' offline DROP;
ALTER database OPEN;
 
SELECT tablespace_name
FROM dba_tablespaces;
 
SELECT file_name
FROM dba_data_files;
 
DROP tablespace testbed including contents;
 
SELECT tablespace_name
FROM dba_tablespaces;
 
SELECT file_name
FROM dba_data_files;