EsgynDB schema不存在但get schemas仍然显示

2022-07-06 00:00:00 专区 订阅 付费 数据表 残留

现象

在某些特殊情况下,我们已经清除了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


相关文章