EsgynDB schema不存在但get schemas仍然显示
现象
在某些特殊情况下,我们已经清除了schema,但get schemas仍然存在,使用drop schema或cleanup schema均提示schema已经不存在,如下述步骤所示,
SQL>get schemas;
Schemas in Catalog TRAFODION
============================
AFA_LT
AFA_SIT
SEABASE
_LIBMGR_
_MD_
_PRIVMGR_MD_
_REPOS_
=======================
8 row(s) returned
--- SQL operation complete.
SQL>cleanup schema afa_lt;
*** WARNING[4255] Provided schema name does not exist in metadata. Cleanup will be performed on objects in this schema, if they exist. [2020-07-25 09:40:30]
--- SQL operation complete.
SQL>cleanup schema afa_sit;
*** WARNING[4255] Provided schema name does not exist in metadata. Cleanup will be performed on objects in this schema, if they exist. [2020-07-25 09:40:36]
--- SQL operation complete.
SQL>drop schema afa_lt;
*** ERROR[1003] Schema TRAFODION.AFA_LT does not exist. [2020-07-25 09:40:51]
SQL>drop schema afa_sit;
*** ERROR[1003] Schema TRAFODION.AFA_SIT does not exist. [2020-07-25 09:40:54]
解决
get schemas中仍然显示的数据来自于元数据表,这说明schema虽然清理了但元数据中仍然遗留有相关信息。
SQL>select count(*) from "_MD_".objects where schema_name in ('AFA_LT','AFA_SIT');
(EXPR)
--------------------
63
--- 1 row(s) selected.
通过以上SQL可以确认objects元数据表中仍然残留相关schema的信息。
我们删除残留记录后get schemas不再显示不存在的schema名称。
set parserflags 131072;
delete from "_MD_".objects where schema_name in ('AFA_LT','AFA_SIT');
SQL>get schemas;
Schemas in Catalog TRAFODION
============================
SEABASE
_LIBMGR_
_MD_
_PRIVMGR_MD_
_REPOS_
=======================
6 row(s) returned
相关文章