Tablespaces in Oracle

SQL Queries to drop oracle tablespace,

-- drop tablespace:
syntax: DROP TABLESPACE <tablespace_name>;
eg: DROP TABLESPACE tspace;

-- drop tablespace including contents:
syntax: DROP TABLESPACE <tablespace_name> INCLUDING CONTENTS;
eg: DROP TABLESPACE tspace INCLUDING CONTENTS;

-- drop tablespace including contents & datafiles:    
syntax: DROP TABLESPACE <tablespace_name> INCLUDING CONTENTS AND DATAFILES;
eg: DROP TABLESPACE tspace INCLUDING CONTENTS AND DATAFILES;

-- drop tablespace including contents & datafiles
-- where referential constraints exist:    
syntax: 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;