Experience: is what you get soon after you need it.

Experience: is what you get soon after you need it.

Rasul Allah (sal Allahu alaihi wa sallam) said: "Restore the trusts of those who trust you, and deal not falsely with him who deals falsely with you." [Abu Dawud, Tirmidhi]

Search This Blog

Monday, September 28, 2015

ORA-55626: Cannot remove the Flashback Archive's primary tablespace


Trying to remove tablespace from the default flashback archive but it throws an error until I delete the whole flashback archive:


ORA-55626: Cannot remove the Flashback Archive's primary tablespace


SHAIKDB>create flashback archive flash_days tablespace tbs2 quota 100m retention 10 day;

Flashback archive created.

SHAIKDB>alter flashback archive flash_days set default;

Flashback archive altered.

SHAIKDB>alter flashback archive flash_days add tablespace tbs3;

Flashback archive altered.

SHAIKDB>col FLASHBACK_ARCHIVE_NAME for a20
SHAIKDB>select * from dba_flashback_archive_ts;

FLASHBACK_ARCHIVE_NA FLASHBACK_ARCHIVE# TABLESPACE_NAME            QUOTA_IN_MB
-------------------- ------------------ ------------------------------ ----------------------------------------
FLASH_ARCH_DEFAULT             1 TBS1                  1024
FLASH_DAYS                 2 TBS2                  100
FLASH_MONTHS                 3 TBS3                  1024
FLASH_DAYS                 2 TBS3                  10240

fix:-

Delete the damn archive itself:


SHAIKDB>alter flashback archive flash_days remove tablespace tbs2;
alter flashback archive flash_days remove tablespace tbs2
                                                     *
ERROR at line 1:
ORA-55626: Cannot remove the Flashback Archive's primary tablespace


SHAIKDB>drop flashback archive flash_days;

Flashback archive dropped.

SHAIKDB>select * from dba_flashback_archive_ts;

FLASHBACK_ARCHIVE_NA FLASHBACK_ARCHIVE# TABLESPACE_NAME            QUOTA_IN_MB
-------------------- ------------------ ------------------------------ ----------------------------------------
FLASH_ARCH_DEFAULT              1 TBS1                   1024
FLASH_MONTHS                  3 TBS3                   1024

No comments: