ORA-38881: Cannot drop tablespace TABLESPACE_STG on primary database due to guaranteed restore points.

Issue:

Unable to drop a tablespace due to below error.

SQL> drop tablespace TABLESPACE_STG including contents and datafiles;

ORA-38881: Cannot drop tablespace TABLESPACE_STG on primary database due to guaranteed restore points.

Soloution:

We need to set below underscore parameter as "true"

SQL> show parameter _allow_drop_ts_with_grp

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

_allow_drop_ts_with_grp              boolean     FALSE

SQL>

FIX:

SQL> alter system set "_allow_drop_ts_with_grp"=true;

System altered.

SQL> show parameter _allow_drop_ts_with_grp

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

_allow_drop_ts_with_grp              boolean     TRUE

SQL>

SQL> drop tablespace TABLESPACE_STG including contents and datafiles;

Tablespace dropped.

Revert the Changes :
-------------------------

You can revert the underscore parameter again.

alter system set "_allow_drop_ts_with_grp"=false;

SQL> show parameter _allow_drop_ts_with_grp

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
_allow_drop_ts_with_grp              boolean     FALSE
SQL>