🧩 How to Drop a Tablespace in Oracle Database

🧩 Introduction

Sometimes a tablespace is no longer needed, and dropping it helps free up storage. As a DBA, you must be careful because dropping a tablespace permanently removes all data stored in it.

Step 1: Drop a Tablespace Without Contents

DROP TABLESPACE my_tbs;

➡️ This will fail if the tablespace contains objects.

Step 2: Drop a Tablespace with Contents

DROP TABLESPACE my_tbs INCLUDING CONTENTS;

➡️ This removes all objects inside the tablespace but leaves the datafile on disk.

Step 3: Drop a Tablespace with Datafiles

DROP TABLESPACE my_tbs INCLUDING CONTENTS AND DATAFILES;

➡️ This completely removes the tablespace and its associated datafiles from disk.

Conclusion

Always double-check before dropping a tablespace, especially in production. Backup important data first, since this action cannot be undone.