ORA-23515 and ORA-02449 on Drop Tablespace

請看指令

SQL> alter tablespace BLUH offline;

Tablespace altered

SQL> drop tablespace BLUH including contents;

drop tablespace BLUH including contents

ORA-23515: materialized views and/or their indices exist in the tablespace

SQL> select ‘drop materialized view ‘||owner||’.’||name||’ PRESERVE TABLE;’ from dba_registered_snapshots where name in  (select table_name from dba_tables where tablespace_name = ‘BLUH’);

drop materialized view ABC.CB_BLUH_DM_DATE_VW PRESERVE TABLE;
drop materialized view ABC.CB_BLUH_ROLE_VW PRESERVE TABLE;
drop materialized view ABC.CB_BLUH_DM_INTER_VW PRESERVE TABLE;

[After MVs were dropped]

SQL> drop tablespace BLUH including contents

ORA-02449: unique/primary keys in table referenced by foreign keys

SQL> drop tablespace BLUH including contents and datafiles CASCADE CONSTRAINTS;

Tablespace dropped

收工

廣告

發表迴響

在下方填入你的資料或按右方圖示以社群網站登入:

WordPress.com Logo

您的留言將使用 WordPress.com 帳號。 登出 / 變更 )

Twitter picture

您的留言將使用 Twitter 帳號。 登出 / 變更 )

Facebook照片

您的留言將使用 Facebook 帳號。 登出 / 變更 )

Google+ photo

您的留言將使用 Google+ 帳號。 登出 / 變更 )

連結到 %s

%d 位部落客按了讚: