ORA-55641: Cannot drop tablespace used by Flashback Data Archive
Posted by Mir Sayeed Hassan on July 26th, 2020
ORA-55641: Cannot drop tablespace used by Flashback Data Archive
I was trying to drop the tablespace from the test database & got the below error
LOGIN TO DATABASE
[oracle@testdb ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Sun Jul 26 05:17:21 2020 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> sho user USER is "SYS"
CHECK THE TABLESPACE EXIST IN DATABASE
SQL> select * from v$tablespace; TS# NAME INC BIG FLA ENC ---------- ------------------------------ --- --- --- --- 0 SYSTEM YES NO YES 1 SYSAUX YES NO YES 2 UNDOTBS1 YES NO YES 4 USERS YES NO YES 3 TEMP NO NO YES 6 EXAMPLE YES NO YES 7 TESTING3 YES NO YES 7 rows selected.
DROP THE TABLESPACE WHICH IS NOT REQUIRE
SQL> drop tablespace testing3 including contents and datafiles; drop tablespace testing3 including contents and datafiles * ERROR at line 1: ORA-55641: Cannot drop tablespace used by Flashback Data Archive
Note:
The above tablespace cannot be drop due to the flashback data archive is present in it.
CHECK THE FLASHBACK IS ENABLE OR NOT
SQL> select flashback_on from v$database; FLASHBACK_ON ------------------ NO
CHECK THE FLASHBACK ARCHIVE
SQL> select flashback_archive_name, status from dba_flashback_archive; FLASHBACK_ARCHIVE_NAME STATUS ------------------------------------ FLA1 DEFAULT
Note: Due to this flashback archive exist in database are not allowing to drop the tablespace, Try to drop this flashback archive & then execute
DROP THE FLASHBACK ARCHIVE
SQL> drop flashback archive fla1; Flashback archive dropped.
VERIFY
SQL> select flashback_archive_name, status from dba_flashback_archive; no rows selected
DROP THE TABLESPACE INCLUDING CONTENTS AND DATAFILE
SQL> drop tablespace testing3 including contents and datafiles; Tablespace dropped.