How to Resolve ORA-01549: tablespace not empty, use INCLUDING CONTENTS option

ORA-01549

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;

Solution

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:

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;



Tablespace dropped.

We made it.