Trafodion 从元数据信息查找对象所属表

来源：互联网发布：网络红人丁可编辑：程序博客网时间：2024/05/16 16:54

今天遇到一个问题，在创建表的时候因为指定了contraint名称而报冲突错误，如下

create table test...constraint aaa primary key (test_id)+>);*** ERROR[8102] The operation is prevented by a unique constraint.*** ERROR[1423] Insert into metadata table OBJECTS failed.--- SQL operation failed with errors.

更换constraint名称aaa为其他或者是移除”constraint aaa”(即使用数据库系统生成的名称)之后可以正常创建表，所以怀疑是constraint aaa在当前数据库已存在，那么该如何去检验呢？
我们知道，Trafodion中有一个元数据相关的schema，用get schemas可以看到，通过set set schema进去可以看到下面有相关的元数据表，用于保存对象的元数据信息，

SQL>get schemas;Schemas in Catalog TRAFODION============================SEABASE_LIBMGR__MD__REPOS__TENANT_MD_--- SQL operation complete.SQL>set schema "_MD_";--- SQL operation complete.SQL>get tables;Tables in Schema TRAFODION._MD_===============================AUTHSCOLUMNSDEFAULTSINDEXESKEYSLIBRARIESLIBRARIES_USAGEOBJECTSREF_CONSTRAINTSROUTINESSEQ_GENTABLESTABLE_CONSTRAINTSTEXTUNIQUE_REF_CONSTR_USAGEVERSIONSVIEWSVIEWS_USAGE--- SQL operation complete.

上面的元数据表中最重要的一个是OJBECTS表，它保存所有的对象的信息，包括OBJECT_NAME、OBJECT_UID等，因此我们可以基于OBJECTS表查找constraints aaa，

>>select object_name, object_type, object_uid from "_MD_".objects where object_name = 'AAA' ;OBJECT_NAME                                                                                                                                                                                                                                                       OBJECT_TYPE  OBJECT_UID----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  -----------  --------------------AAA                                                                                                                                                                                                                                                  PK            5004197302713865672--- 1 row(s) selected.

通过上面SQL语句找到aaa对应的的OBJECT_ID，然后通过OBJECT_ID在TABLE_CONSTRAINTS找到对应的TABLE_UID，

>>select * from "_MD_".table_constraints where constraint_uid = 5004197302713865672;TABLE_UID             CONSTRAINT_UID        CONSTRAINT_TYPE  DISABLED  DROPPABLE  IS_DEFERRABLE  ENFORCED  VALIDATED  LAST_VALIDATED        COL_COUNT    INDEX_UID             FLAGS--------------------  --------------------  ---------------  --------  ---------  -------------  --------  ---------  --------------------  -----------  --------------------  --------------------5004197302713865655   5004197302713865672  P                N         N          N              Y         Y            212367304638767804            3                     0                     0--- 1 row(s) selected.

通过以下结果可以发现，5004197302713865655即为contraint aaa所在表的UID，下面我们通过这个UID再去OBJECTS表中查找对应的表名称，

>>select * from objects where object_uid =  5004197302713865655 ;CATALOG_NAME                                                                                                                                                                                                                                                      SCHEMA_NAME                                                                                                                                                                                                                                                       OBJECT_NAME                                                                                                                                                                                                                                                       OBJECT_TYPE  OBJECT_UID            CREATE_TIME           REDEF_TIME            VALID_DEF  DROPPABLE  OBJECT_OWNER  SCHEMA_OWNER  FLAGS----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  -----------  --------------------  --------------------  --------------------  ---------  ---------  ------------  ------------  --------------------TRAFODION                                                                                                                                                                                                                                                         SEABASE                                                                                                                                                                                                                                                          STG_TEST                                                                                                                                                                                                                                           BT            5004197302713865655    212367304638561613    212367304642149588  Y          N                 33333         33333                     0--- 1 row(s) selected.

那么，STG_TEST就是我们要找的表了，这时，我们可以通过SHOWDDL STG_TEST来查看此表的DDL或者找到原始的DDL来确认是否这个表上有constraint aaa了。。。

阅读全文

1 0