Tried to drop a tablespace in the simplest way, but it failed with ORA-01549.
SQL> drop tablespace erptbs;
drop tablespace erptbs
ERROR at line 1:
ORA-01549: tablespace not empty, use INCLUDING CONTENTS option
ORA-01549 means that there're still some contents (i.e. tables or indexes) stored in the tablespace, you cannot drop it in such simple way.
In fact, I see the error as a warning. Oracle tries to remind you that the tablespace is not empty, there're some objects in it, you should take care of them before actually dropping it.
To know what segments are still in the tablespace, you may perform a query.
SQL> select segment_type, owner, segment_name from dba_segments where tablespace_name = 'ERPTBS' order by 1,2,3;
You have 3 options to solve ORA-01549.
1. Moving Contents to Another Tablespace
To move segments to another tablespace, you may refer to these posts:
- Move table to another tablespace
- Move partition to another tablespace
- Move LOB to another tablespace
- Move index to another tablespace
Then drop the tablespace.
2. Dropping Contents Manually
Segments in the tablespace might be useless to you, you can drop each of every segment in the tablespace by yourself. Then drop the tablespace.
3. Dropping Tablespace Including Contents
Yes, you may add INCLUDING CONTENTS option to indicate that you want to drop the tablespace as well as the contents.
SQL> drop tablespace erptbs including contents;
We made it.