【 使用环境 】测试环境
【 OB or 其他组件 】
【 使用版本 】v3.2.4企业版
【问题描述】OceanBase不会为每个数据库对象分配唯一的 OBJECT_ID，那ob怎么保证查找到的对象是唯一的？
1 个赞
这个问题的题干结论是怎么得来的？
2 个赞
select * from dba_objects;
select * from dba_ob_table_locations;
结合这两个表看下
2 个赞
有系统对应字典吧
2 个赞
占个楼学习一下
2 个赞
你好，你提的这个技术问题牵涉到OceanBase企业版范围内的功能细节。建议你通过以下方式寻求帮助：
1.如你所在的企业客户已签署OceanBase企业版销售合同，请你联系客户经理；
2.如你所在的企业客户尚未签署OceanBase企业版销售合同，你可通过OceanBase官网商务咨询页面留下你的联系方式，OceanBase企业版的业务顾问会在一个工作日内与你联系。
OceanBase官网商务咨询
分享一下自己学习ob的一些方法
1.假设dba_objects中，OBJECT_ID是唯一的，那么按照数据库的知识，这个列应该是有主键或者非空唯一索引的，然而desc dba_objects看这个列信息，没有这些信息
desc dba_objects;
+-------------------+---------------+------+-----+---------+-------+
| FIELD | TYPE | NULL | KEY | DEFAULT | EXTRA |
+-------------------+---------------+------+-----+---------+-------+
| OWNER | VARCHAR2(128) | YES | NULL | NULL | NULL |
| OBJECT_NAME | VARCHAR2(128) | NO | NULL | NULL | NULL |
| SUBOBJECT_NAME | VARCHAR2(128) | NO | NULL | NULL | NULL |
| OBJECT_ID | NUMBER | NO | NULL | NULL | NULL |
| DATA_OBJECT_ID | NUMBER | NO | NULL | NULL | NULL |
| OBJECT_TYPE | VARCHAR2(23) | NO | NULL | NULL | NULL |
| CREATED | DATE | NO | NULL | NULL | NULL |
| LAST_DDL_TIME | DATE | NO | NULL | NULL | NULL |
| TIMESTAMP | VARCHAR2(19) | NO | NULL | NULL | NULL |
| STATUS | VARCHAR2(7) | NO | NULL | NULL | NULL |
| TEMPORARY | VARCHAR2(1) | NO | NULL | NULL | NULL |
| GENERATED | VARCHAR2(1) | NO | NULL | NULL | NULL |
| SECONDARY | VARCHAR2(1) | NO | NULL | NULL | NULL |
| NAMESPACE | NUMBER | NO | NULL | NULL | NULL |
| EDITION_NAME | VARCHAR2(128) | NO | NULL | NULL | NULL |
| SHARING | VARCHAR2(18) | NO | NULL | NULL | NULL |
| EDITIONABLE | VARCHAR2(1) | NO | NULL | NULL | NULL |
| ORACLE_MAINTAINED | VARCHAR2(1) | NO | NULL | NULL | NULL |
| APPLICATION | VARCHAR2(1) | NO | NULL | NULL | NULL |
| DEFAULT_COLLATION | VARCHAR2(1) | NO | NULL | NULL | NULL |
| DUPLICATED | VARCHAR2(1) | NO | NULL | NULL | NULL |
| SHARDED | VARCHAR2(1) | NO | NULL | NULL | NULL |
| IMPORTED_OBJECT | VARCHAR2(1) | NO | NULL | NULL | NULL |
| CREATED_APPID | NUMBER | NO | NULL | NULL | NULL |
| CREATED_VSNID | NUMBER | NO | NULL | NULL | NULL |
| MODIFIED_APPID | NUMBER | NO | NULL | NULL | NULL |
| MODIFIED_VSNID | NUMBER | NO | NULL | NULL | NULL |
+-------------------+---------------+------+-----+---------+-------+
27 rows in set (0.024 sec)
2.那只能看看这个表是如何创建的，看看这些数据来自哪里，
使用show create table dba_objects查看这个表(一般情况下这个其实都是视图)，如果自己不缺他是视图还是表，最笨的办法就是都试一下(最开始的时候我也是这么试出来的，不知道别人有没有更好的方法，后来知道了系统表，就可以直接在系统表中知道这个对象是表还是视图)
可以看到的sql是
CREATE VIEW "DBA_OBJECTS" AS
SELECT
CAST(B.DATABASE_NAME AS VARCHAR(128)) AS OWNER,
CAST(A.OBJECT_NAME AS VARCHAR(128)) AS OBJECT_NAME,
CAST(A.SUBOBJECT_NAME AS VARCHAR2(128)) AS SUBOBJECT_NAME,
CAST(A.OBJECT_ID AS NUMBER) AS OBJECT_ID,
CAST(A.DATA_OBJECT_ID AS NUMBER) AS DATA_OBJECT_ID,
CAST(A.OBJECT_TYPE AS VARCHAR2(23)) AS OBJECT_TYPE,
CAST(A.GMT_CREATE AS DATE) AS CREATED,
CAST(A.GMT_MODIFIED AS DATE) AS LAST_DDL_TIME,
CAST(TO_CHAR(A.GMT_CREATE) AS VARCHAR2(19)) AS TIMESTAMP,
CAST(A.STATUS AS VARCHAR2(7)) AS STATUS,
CAST(A.TEMPORARY AS VARCHAR2(1)) AS TEMPORARY,
CAST(A."GENERATED" AS VARCHAR2(1)) AS "GENERATED",
CAST(A.SECONDARY AS VARCHAR2(1)) AS SECONDARY,
CAST(A.NAMESPACE AS NUMBER) AS NAMESPACE,
CAST(A.EDITION_NAME AS VARCHAR2(128)) AS EDITION_NAME,
CAST(NULL AS VARCHAR2(18)) AS SHARING,
CAST(NULL AS VARCHAR2(1)) AS EDITIONABLE,
CAST(NULL AS VARCHAR2(1)) AS ORACLE_MAINTAINED,
CAST(NULL AS VARCHAR2(1)) AS APPLICATION,
CAST(NULL AS VARCHAR2(1)) AS DEFAULT_COLLATION,
CAST(NULL AS VARCHAR2(1)) AS DUPLICATED,
CAST(NULL AS VARCHAR2(1)) AS SHARDED,
CAST(NULL AS VARCHAR2(1)) AS IMPORTED_OBJECT,
CAST(NULL AS NUMBER) AS CREATED_APPID,
CAST(NULL AS NUMBER) AS CREATED_VSNID,
CAST(NULL AS NUMBER) AS MODIFIED_APPID,
CAST(NULL AS NUMBER) AS MODIFIED_VSNID
FROM
(
SELECT
A.TENANT_ID,
(
TO_DATE('19700101', 'YYYYMMDD') + B.SCHEMA_VERSION / 86400 / 1000000 + TO_NUMBER(SUBSTR(TZ_OFFSET(SESSIONTIMEZONE), 1, 3)) / 24
) AS GMT_CREATE,
(
TO_DATE('19700101', 'YYYYMMDD') + A.SCHEMA_VERSION / 86400 / 1000000 + TO_NUMBER(SUBSTR(TZ_OFFSET(SESSIONTIMEZONE), 1, 3)) / 24
) AS GMT_MODIFIED,
A.DATABASE_ID,
A.TABLE_NAME AS OBJECT_NAME,
NULL AS SUBOBJECT_NAME,
CAST(A.TABLE_ID AS NUMBER) AS OBJECT_ID,
A.TABLE_ID AS DATA_OBJECT_ID,
'TABLE' AS OBJECT_TYPE,
'VALID' AS STATUS,
'N' AS TEMPORARY,
'N' AS "GENERATED",
'N' AS SECONDARY,
0 AS NAMESPACE,
NULL AS EDITION_NAME
FROM
SYS.ALL_VIRTUAL_CORE_ALL_TABLE A
JOIN SYS.ALL_VIRTUAL_CORE_ALL_TABLE B ON A.TENANT_ID = B.TENANT_ID
WHERE
B.TABLE_NAME = '__all_core_table'
AND A.TENANT_ID = EFFECTIVE_TENANT_ID()
UNION ALL
SELECT
TENANT_ID,
GMT_CREATE,
GMT_MODIFIED,
DATABASE_ID,
CAST(
(
CASE
WHEN DATABASE_ID = 201004 THEN TABLE_NAME
WHEN TABLE_TYPE = 5 THEN SUBSTR(
TABLE_NAME,
7 + INSTR(SUBSTR(TABLE_NAME, 7), '_')
)
ELSE TABLE_NAME
END
) AS VARCHAR2(128)
) AS OBJECT_NAME,
NULL SUBOBJECT_NAME,
TABLE_ID OBJECT_ID,(
CASE
WHEN TABLET_ID != 0 THEN TABLET_ID
ELSE NULL
END
) DATA_OBJECT_ID,
CASE
WHEN TABLE_TYPE IN (0, 3, 6, 8, 9, 14) THEN 'TABLE'
WHEN TABLE_TYPE IN (2) THEN 'VIRTUAL TABLE'
WHEN TABLE_TYPE IN (1, 4) THEN 'VIEW'
WHEN TABLE_TYPE IN (5) THEN 'INDEX'
WHEN TABLE_TYPE IN (7) THEN 'MATERIALIZED VIEW'
ELSE NULL
END
AS OBJECT_TYPE,
CAST(
CASE
WHEN TABLE_TYPE IN (5) THEN CASE
WHEN INDEX_STATUS = 2 THEN 'VALID'
WHEN INDEX_STATUS = 3 THEN 'CHECKING'
WHEN INDEX_STATUS = 4 THEN 'INELEGIBLE'
WHEN INDEX_STATUS = 5 THEN 'ERROR'
ELSE 'UNUSABLE'
END
ELSE CASE
WHEN OBJECT_STATUS = 1 THEN 'VALID'
ELSE 'INVALID'
END
END
AS VARCHAR2(10)
) AS STATUS,
CASE
WHEN TABLE_TYPE IN (6, 8, 9) THEN 'Y'
ELSE 'N'
END
AS TEMPORARY,
CASE
WHEN TABLE_TYPE IN (0, 1) THEN 'Y'
ELSE 'N'
END
AS "GENERATED",
'N' AS SECONDARY,
0 AS NAMESPACE,
NULL AS EDITION_NAME
FROM
SYS.ALL_VIRTUAL_TABLE_REAL_AGENT
WHERE
TENANT_ID = EFFECTIVE_TENANT_ID()
AND TABLE_TYPE != 12
AND TABLE_TYPE != 13
AND BITAND((TABLE_MODE / 4096), 15) IN (0, 1)
AND BITAND(INDEX_ATTRIBUTES_SET, 16) = 0
UNION ALL
SELECT
CST.TENANT_ID,
CST.GMT_CREATE,
CST.GMT_MODIFIED,
DB.DATABASE_ID,
CST.CONSTRAINT_NAME AS OBJECT_NAME,
NULL AS SUBOBJECT_NAME,
TBL.TABLE_ID AS OBJECT_ID,
NULL AS DATA_OBJECT_ID,
'INDEX' AS OBJECT_TYPE,
'VALID' AS STATUS,
'N' AS TEMPORARY,
'N' AS "GENERATED",
'N' AS SECONDARY,
0 AS NAMESPACE,
NULL AS EDITION_NAME
FROM
SYS.ALL_VIRTUAL_CONSTRAINT_REAL_AGENT CST,
SYS.ALL_VIRTUAL_TABLE_REAL_AGENT TBL,
SYS.ALL_VIRTUAL_DATABASE_REAL_AGENT DB
WHERE
CST.TENANT_ID = EFFECTIVE_TENANT_ID()
AND DB.DATABASE_ID = TBL.DATABASE_ID
AND TBL.TABLE_ID = CST.TABLE_ID
and CST.CONSTRAINT_TYPE = 1
AND TBL.TABLE_TYPE != 12
AND TBL.TABLE_TYPE != 13
AND BITAND((TBL.TABLE_MODE / 4096), 15) IN (0, 1)
AND BITAND(TBL.INDEX_ATTRIBUTES_SET, 16) = 0
UNION ALL
SELECT
P.TENANT_ID,
P.GMT_CREATE,
P.GMT_MODIFIED,
T.DATABASE_ID,
CAST(
(
CASE
WHEN T.DATABASE_ID = 201004 THEN T.TABLE_NAME
WHEN T.TABLE_TYPE = 5 THEN SUBSTR(
T.TABLE_NAME,
7 + INSTR(SUBSTR(T.TABLE_NAME, 7), '_')
)
ELSE T.TABLE_NAME
END
) AS VARCHAR2(128)
) AS OBJECT_NAME,
P.PART_NAME SUBOBJECT_NAME,
P.PART_ID OBJECT_ID,
CASE
WHEN P.TABLET_ID != 0 THEN P.TABLET_ID
ELSE NULL
END
AS DATA_OBJECT_ID,
DECODE (
T.TABLE_TYPE,
5,
'INDEX PARTITION',
'TABLE PARTITION'
) AS OBJECT_TYPE,
'VALID' AS STATUS,
'N' AS TEMPORARY,
NULL AS "GENERATED",
'N' AS SECONDARY,
0 AS NAMESPACE,
NULL AS EDITION_NAME
FROM
SYS.ALL_VIRTUAL_TABLE_REAL_AGENT T
JOIN SYS.ALL_VIRTUAL_PART_REAL_AGENT P ON T.TABLE_ID = P.TABLE_ID
WHERE
T.TENANT_ID = EFFECTIVE_TENANT_ID()
AND P.TENANT_ID = EFFECTIVE_TENANT_ID()
AND T.TABLE_TYPE != 12
AND T.TABLE_TYPE != 13
AND BITAND((T.TABLE_MODE / 4096), 15) IN (0, 1)
AND BITAND(T.INDEX_ATTRIBUTES_SET, 16) = 0
UNION ALL
SELECT
SUBP.TENANT_ID,
SUBP.GMT_CREATE,
SUBP.GMT_MODIFIED,
T.DATABASE_ID,
CAST(
(
CASE
WHEN T.DATABASE_ID = 201004 THEN T.TABLE_NAME
WHEN T.TABLE_TYPE = 5 THEN SUBSTR(
T.TABLE_NAME,
7 + INSTR(SUBSTR(T.TABLE_NAME, 7), '_')
)
ELSE T.TABLE_NAME
END
) AS VARCHAR2(128)
) AS OBJECT_NAME,
SUBP.SUB_PART_NAME SUBOBJECT_NAME,
SUBP.SUB_PART_ID OBJECT_ID,
SUBP.TABLET_ID AS DATA_OBJECT_ID,
DECODE (
T.TABLE_TYPE,
5,
'INDEX SUBPARTITION',
'TABLE SUBPARTITION'
) AS OBJECT_TYPE,
'VALID' AS STATUS,
'N' AS TEMPORARY,
'Y' AS "GENERATED",
'N' AS SECONDARY,
0 AS NAMESPACE,
NULL AS EDITION_NAME
FROM
SYS.ALL_VIRTUAL_TABLE_REAL_AGENT T,
SYS.ALL_VIRTUAL_PART_REAL_AGENT P,
SYS.ALL_VIRTUAL_SUB_PART_REAL_AGENT SUBP
WHERE
T.TABLE_ID = P.TABLE_ID
AND P.TABLE_ID = SUBP.TABLE_ID
AND P.PART_ID = SUBP.PART_ID
AND T.TENANT_ID = EFFECTIVE_TENANT_ID()
AND P.TENANT_ID = EFFECTIVE_TENANT_ID()
AND SUBP.TENANT_ID = EFFECTIVE_TENANT_ID()
AND T.TABLE_TYPE != 12
AND T.TABLE_TYPE != 13
AND BITAND((T.TABLE_MODE / 4096), 15) IN (0, 1)
AND BITAND(T.INDEX_ATTRIBUTES_SET, 16) = 0
UNION ALL
SELECT
EFFECTIVE_TENANT_ID() AS TENANT_ID,
GMT_CREATE,
GMT_MODIFIED,
CAST(201006 AS NUMBER) AS DATABASE_ID,
PACKAGE_NAME AS OBJECT_NAME,
NULL AS SUBOBJECT_NAME,
PACKAGE_ID OBJECT_ID,
NULL AS DATA_OBJECT_ID,
CASE
WHEN TYPE = 1 THEN 'PACKAGE'
WHEN TYPE = 2 THEN 'PACKAGE BODY'
ELSE NULL
END
AS OBJECT_TYPE,
'VALID' AS STATUS,
'N' AS TEMPORARY,
'N' AS "GENERATED",
'N' AS SECONDARY,
0 AS NAMESPACE,
NULL AS EDITION_NAME
FROM
SYS.ALL_VIRTUAL_PACKAGE_SYS_AGENT
UNION ALL
SELECT
P.TENANT_ID,
P.GMT_CREATE,
P.GMT_MODIFIED,
P.DATABASE_ID,
P.PACKAGE_NAME AS OBJECT_NAME,
NULL AS SUBOBJECT_NAME,
P.PACKAGE_ID OBJECT_ID,
NULL AS DATA_OBJECT_ID,
CASE
WHEN TYPE = 1 THEN 'PACKAGE'
WHEN TYPE = 2 THEN 'PACKAGE BODY'
ELSE NULL
END
AS OBJECT_TYPE,
CASE
WHEN EXISTS (
SELECT
OBJ_ID
FROM
SYS.ALL_VIRTUAL_TENANT_ERROR_REAL_AGENT E
WHERE
P.TENANT_ID = E.TENANT_ID
AND P.PACKAGE_ID = E.OBJ_ID
AND (
E.OBJ_TYPE = 3
OR E.OBJ_TYPE = 5
)
) THEN 'INVALID'
ELSE 'VALID'
END
AS STATUS,
'N' AS TEMPORARY,
'N' AS "GENERATED",
'N' AS SECONDARY,
0 AS NAMESPACE,
NULL AS EDITION_NAME
FROM
SYS.ALL_VIRTUAL_PACKAGE_REAL_AGENT P
WHERE
P.TENANT_ID = EFFECTIVE_TENANT_ID()
UNION ALL
SELECT
R.TENANT_ID,
R.GMT_CREATE,
R.GMT_MODIFIED,
R.DATABASE_ID,
R.ROUTINE_NAME AS OBJECT_NAME,
NULL AS SUBOBJECT_NAME,
R.ROUTINE_ID OBJECT_ID,
NULL AS DATA_OBJECT_ID,
CASE
WHEN ROUTINE_TYPE = 1 THEN 'PROCEDURE'
WHEN ROUTINE_TYPE = 2 THEN 'FUNCTION'
ELSE NULL
END
AS OBJECT_TYPE,
CASE
WHEN EXISTS (
SELECT
OBJ_ID
FROM
SYS.ALL_VIRTUAL_TENANT_ERROR_REAL_AGENT E
WHERE
R.TENANT_ID = E.TENANT_ID
AND R.ROUTINE_ID = E.OBJ_ID
AND (
E.OBJ_TYPE = 9
OR E.OBJ_TYPE = 12
)
) THEN 'INVALID'
ELSE 'VALID'
END
AS STATUS,
'N' AS TEMPORARY,
'N' AS "GENERATED",
'N' AS SECONDARY,
0 AS NAMESPACE,
NULL AS EDITION_NAME
FROM
SYS.ALL_VIRTUAL_ROUTINE_REAL_AGENT R
WHERE
(
ROUTINE_TYPE = 1
OR ROUTINE_TYPE = 2
)
AND R.TENANT_ID = EFFECTIVE_TENANT_ID()
UNION ALL
SELECT
EFFECTIVE_TENANT_ID() AS TENANT_ID,
GMT_CREATE,
GMT_MODIFIED,
CAST(201006 AS NUMBER) AS DATABASE_ID,
TS.TYPE_NAME AS OBJECT_NAME,
NULL AS SUBOBJECT_NAME,
TS.TYPE_ID AS OBJECT_ID,
NULL AS DATA_OBJECT_ID,
'TYPE' AS OBJECT_TYPE,
CASE
WHEN EXISTS (
SELECT
OBJ_ID
FROM
SYS.ALL_VIRTUAL_TENANT_ERROR_REAL_AGENT E
WHERE
TS.TENANT_ID = E.TENANT_ID
AND TS.TYPE_ID = E.OBJ_ID
AND E.OBJ_TYPE = 4
) THEN 'INVALID'
ELSE 'VALID'
END
AS STATUS,
'N' AS TEMPORARY,
'N' AS "GENERATED",
'N' AS SECONDARY,
0 AS NAMESPACE,
NULL AS EDITION_NAME
FROM
SYS.ALL_VIRTUAL_TYPE_SYS_AGENT TS
UNION ALL
SELECT
TENANT_ID,
GMT_CREATE,
GMT_MODIFIED,
DATABASE_ID,
TYPE_NAME AS OBJECT_NAME,
NULL AS SUBOBJECT_NAME,
TYPE_ID OBJECT_ID,
NULL AS DATA_OBJECT_ID,
'TYPE' AS OBJECT_TYPE,
CASE
WHEN EXISTS (
SELECT
OBJ_ID
FROM
SYS.ALL_VIRTUAL_TENANT_ERROR_REAL_AGENT E
WHERE
TY.TENANT_ID = E.TENANT_ID
AND TY.TYPE_ID = E.OBJ_ID
AND E.OBJ_TYPE = 4
) THEN 'INVALID'
ELSE 'VALID'
END
AS STATUS,
'N' AS TEMPORARY,
'N' AS "GENERATED",
'N' AS SECONDARY,
0 AS NAMESPACE,
NULL AS EDITION_NAME
FROM
SYS.ALL_VIRTUAL_TYPE_REAL_AGENT TY
WHERE
TENANT_ID = EFFECTIVE_TENANT_ID()
UNION ALL
SELECT
EFFECTIVE_TENANT_ID() AS TENANT_ID,
GMT_CREATE,
GMT_MODIFIED,
CAST(201006 AS NUMBER) AS DATABASE_ID,
OBJECT_NAME,
NULL AS SUBOBJECT_NAME,
OBJECT_TYPE_ID OBJECT_ID,
NULL AS DATA_OBJECT_ID,
'TYPE BODY' AS OBJECT_TYPE,
CASE
WHEN EXISTS (
SELECT
OBJ_ID
FROM
SYS.ALL_VIRTUAL_TENANT_ERROR_REAL_AGENT E
WHERE
EFFECTIVE_TENANT_ID() = E.TENANT_ID
AND TS.OBJECT_TYPE_ID = E.OBJ_ID
AND E.OBJ_TYPE = 6
) THEN 'INVALID'
ELSE 'VALID'
END
AS STATUS,
'N' AS TEMPORARY,
'N' AS "GENERATED",
'N' AS SECONDARY,
0 AS NAMESPACE,
NULL AS EDITION_NAME
FROM
SYS.ALL_VIRTUAL_TENANT_OBJECT_TYPE_SYS_AGENT TS
WHERE
TYPE = 2
UNION ALL
SELECT
TENANT_ID,
GMT_CREATE,
GMT_MODIFIED,
DATABASE_ID,
OBJECT_NAME,
NULL AS SUBOBJECT_NAME,
OBJECT_TYPE_ID OBJECT_ID,
NULL AS DATA_OBJECT_ID,
'TYPE BODY' AS OBJECT_TYPE,
CASE
WHEN EXISTS (
SELECT
OBJ_ID
FROM
SYS.ALL_VIRTUAL_TENANT_ERROR_REAL_AGENT E
WHERE
TY.TENANT_ID = E.TENANT_ID
AND TY.OBJECT_TYPE_ID = E.OBJ_ID
AND E.OBJ_TYPE = 6
) THEN 'INVALID'
ELSE 'VALID'
END
AS STATUS,
'N' AS TEMPORARY,
'N' AS "GENERATED",
'N' AS SECONDARY,
0 AS NAMESPACE,
NULL AS EDITION_NAME
FROM
SYS.ALL_VIRTUAL_TENANT_OBJECT_TYPE_REAL_AGENT TY
WHERE
TENANT_ID = EFFECTIVE_TENANT_ID()
and TYPE = 2
UNION ALL
SELECT
T.TENANT_ID,
T.GMT_CREATE,
T.GMT_MODIFIED,
T.DATABASE_ID,
T.TRIGGER_NAME AS OBJECT_NAME,
NULL AS SUBOBJECT_NAME,
T.TRIGGER_ID OBJECT_ID,
NULL AS DATA_OBJECT_ID,
'TRIGGER' OBJECT_TYPE,
CASE
WHEN EXISTS (
SELECT
OBJ_ID
FROM
SYS.ALL_VIRTUAL_TENANT_ERROR_REAL_AGENT E
WHERE
T.TENANT_ID = E.TENANT_ID
AND T.TRIGGER_ID = E.OBJ_ID
AND (E.OBJ_TYPE = 7)
) THEN 'INVALID'
ELSE 'VALID'
END
AS STATUS,
'N' AS TEMPORARY,
'N' AS "GENERATED",
'N' AS SECONDARY,
0 AS NAMESPACE,
NULL AS EDITION_NAME
FROM
SYS.ALL_VIRTUAL_TENANT_TRIGGER_REAL_AGENT T
WHERE
T.TENANT_ID = EFFECTIVE_TENANT_ID()
UNION ALL
SELECT
TENANT_ID,
GMT_CREATE,
GMT_MODIFIED,
DATABASE_ID,
SEQUENCE_NAME AS OBJECT_NAME,
NULL AS SUBOBJECT_NAME,
SEQUENCE_ID OBJECT_ID,
NULL AS DATA_OBJECT_ID,
'SEQUENCE' AS OBJECT_TYPE,
'VALID' AS STATUS,
'N' AS TEMPORARY,
'N' AS "GENERATED",
'N' AS SECONDARY,
0 AS NAMESPACE,
NULL AS EDITION_NAME
FROM
SYS.ALL_VIRTUAL_SEQUENCE_OBJECT_REAL_AGENT
WHERE
TENANT_ID = EFFECTIVE_TENANT_ID()
UNION ALL
SELECT
TENANT_ID,
GMT_CREATE,
GMT_MODIFIED,
DATABASE_ID,
SYNONYM_NAME AS OBJECT_NAME,
NULL AS SUBOBJECT_NAME,
SYNONYM_ID OBJECT_ID,
NULL AS DATA_OBJECT_ID,
'SYNONYM' AS OBJECT_TYPE,
'VALID' AS STATUS,
'N' AS TEMPORARY,
'N' AS "GENERATED",
'N' AS SECONDARY,
0 AS NAMESPACE,
NULL AS EDITION_NAME
FROM
SYS.ALL_VIRTUAL_SYNONYM_REAL_AGENT
WHERE
TENANT_ID = EFFECTIVE_TENANT_ID()
UNION ALL
SELECT
TENANT_ID,
GMT_CREATE,
GMT_MODIFIED,
CAST(201006 AS NUMBER) AS DATABASE_ID,
NAMESPACE AS OBJECT_NAME,
NULL AS SUBOBJECT_NAME,
CONTEXT_ID OBJECT_ID,
NULL AS DATA_OBJECT_ID,
'CONTEXT' AS OBJECT_TYPE,
'VALID' AS STATUS,
'N' AS TEMPORARY,
'N' AS "GENERATED",
'N' AS SECONDARY,
21 AS NAMESPACE,
NULL AS EDITION_NAME
FROM
SYS.ALL_VIRTUAL_CONTEXT_REAL_AGENT
WHERE
TENANT_ID = EFFECTIVE_TENANT_ID()
UNION ALL
SELECT
TENANT_ID,
GMT_CREATE,
GMT_MODIFIED,
DATABASE_ID,
DATABASE_NAME AS OBJECT_NAME,
NULL AS SUBOBJECT_NAME,
DATABASE_ID AS OBJECT_ID,
NULL AS DATA_OBJECT_ID,
'DATABASE' AS OBJECT_TYPE,
'VALID' AS STATUS,
'N' AS TEMPORARY,
'N' AS "GENERATED",
'N' AS SECONDARY,
0 AS NAMESPACE,
NULL AS EDITION_NAME
FROM
SYS.ALL_VIRTUAL_DATABASE_REAL_AGENT
WHERE
TENANT_ID = EFFECTIVE_TENANT_ID()
UNION ALL
SELECT
TENANT_ID,
GMT_CREATE,
GMT_MODIFIED,
CAST(201001 AS NUMBER) AS DATABASE_ID,
TABLEGROUP_NAME AS OBJECT_NAME,
NULL AS SUBOBJECT_NAME,
TABLEGROUP_ID AS OBJECT_ID,
NULL AS DATA_OBJECT_ID,
'TABLEGROUP' AS OBJECT_TYPE,
'VALID' AS STATUS,
'N' AS TEMPORARY,
'N' AS "GENERATED",
'N' AS SECONDARY,
0 AS NAMESPACE,
NULL AS EDITION_NAME
FROM
SYS.ALL_VIRTUAL_TABLEGROUP_REAL_AGENT
WHERE
TENANT_ID = EFFECTIVE_TENANT_ID()
) A
JOIN SYS.ALL_VIRTUAL_DATABASE_REAL_AGENT B ON A.TENANT_ID = B.TENANT_ID
AND A.DATABASE_ID = B.DATABASE_ID
AND B.TENANT_ID = EFFECTIVE_TENANT_ID() WITH READ ONLY
3.看这个dba_objects查询出来的数据同一个id，但是名字不一样，这时候如果要区分这些数据，其实就是在查出来的数据里面看看有哪些东西是不一样的，公共属性的部分，一般这种重要的都在前面几列就能区分出来，
我用我的举个例子
select OWNER,OBJECT_NAME,OBJECT_ID,OBJECT_TYPE from dba_objects where OBJECT_ID='503312';
+-------+-------------------------------+-----------+-------------+
| OWNER | OBJECT_NAME | OBJECT_ID | OBJECT_TYPE |
+-------+-------------------------------+-----------+-------------+
| YHX1 | DEL_LOG_OBPK_1697092446800475 | 503312 | INDEX |
| YHX1 | DEL_LOG | 503312 | TABLE |
+-------+-------------------------------+-----------+-------------+
2 rows in set (0.162 sec)
我的数据库中，他两OBJECT_ID 是相同的，但是OBJECT_TYPE 不一样
分析上面的创建视图的sql,其实这一列数据来至于这个sql(sql比较长，因为我看的多了，我直接在里面搜索TABLE关键字)
3.先看这个type是table的部分，
SELECT
TENANT_ID,
GMT_CREATE,
GMT_MODIFIED,
DATABASE_ID,
CAST(
(
CASE
WHEN DATABASE_ID = 201004 THEN TABLE_NAME
WHEN TABLE_TYPE = 5 THEN SUBSTR(
TABLE_NAME,
7 + INSTR(SUBSTR(TABLE_NAME, 7), '_')
)
ELSE TABLE_NAME
END
) AS VARCHAR2(128)
) AS OBJECT_NAME,
NULL SUBOBJECT_NAME,
TABLE_ID OBJECT_ID,(
CASE
WHEN TABLET_ID != 0 THEN TABLET_ID
ELSE NULL
END
) DATA_OBJECT_ID,
CASE
WHEN TABLE_TYPE IN (0, 3, 6, 8, 9, 14) THEN 'TABLE'
WHEN TABLE_TYPE IN (2) THEN 'VIRTUAL TABLE'
WHEN TABLE_TYPE IN (1, 4) THEN 'VIEW'
WHEN TABLE_TYPE IN (5) THEN 'INDEX'
WHEN TABLE_TYPE IN (7) THEN 'MATERIALIZED VIEW'
ELSE NULL
END
AS OBJECT_TYPE,
CAST(
CASE
WHEN TABLE_TYPE IN (5) THEN CASE
WHEN INDEX_STATUS = 2 THEN 'VALID'
WHEN INDEX_STATUS = 3 THEN 'CHECKING'
WHEN INDEX_STATUS = 4 THEN 'INELEGIBLE'
WHEN INDEX_STATUS = 5 THEN 'ERROR'
ELSE 'UNUSABLE'
END
ELSE CASE
WHEN OBJECT_STATUS = 1 THEN 'VALID'
ELSE 'INVALID'
END
END
AS VARCHAR2(10)
) AS STATUS,
CASE
WHEN TABLE_TYPE IN (6, 8, 9) THEN 'Y'
ELSE 'N'
END
AS TEMPORARY,
CASE
WHEN TABLE_TYPE IN (0, 1) THEN 'Y'
ELSE 'N'
END
AS "GENERATED",
'N' AS SECONDARY,
0 AS NAMESPACE,
NULL AS EDITION_NAME
FROM
SYS.ALL_VIRTUAL_TABLE_REAL_AGENT
WHERE
TENANT_ID = EFFECTIVE_TENANT_ID()
AND TABLE_TYPE != 12
AND TABLE_TYPE != 13
AND BITAND((TABLE_MODE / 4096), 15) IN (0, 1)
AND BITAND(INDEX_ATTRIBUTES_SET, 16) = 0
而这个OBJECT_ID就是TABLE_ID，
我们直接查询这个id
SELECT
-> TABLE_ID,
-> TABLE_NAME,
-> DATABASE_ID,
-> TABLE_TYPE
-> FROM
-> SYS.ALL_VIRTUAL_TABLE_REAL_AGENT
-> WHERE
-> TABLE_ID = '503312';
+----------+------------+-------------+------------+
| TABLE_ID | TABLE_NAME | DATABASE_ID | TABLE_TYPE |
+----------+------------+-------------+------------+
| 503312 | DEL_LOG | 500006 | 3 |
+----------+------------+-------------+------------+
1 row in set (0.014 sec)
desc SYS.ALL_VIRTUAL_TABLE_REAL_AGENT;
+------------------------------------+-----------------------------------+------+-----+---------+-------+
| FIELD | TYPE | NULL | KEY | DEFAULT | EXTRA |
+------------------------------------+-----------------------------------+------+-----+---------+-------+
| TENANT_ID | NUMBER(38) | NO | PRI | NULL | NULL |
| TABLE_ID | NUMBER(38) | NO | PRI | NULL | NULL |
| TABLE_NAME | VARCHAR2(256) | YES | MUL | NULL | NULL |
| DATABASE_ID | NUMBER(38) | NO | MUL | NULL | NULL |
| TABLE_TYPE | NUMBER(38) | NO | NULL | NULL | NULL |
看这个表信息其实是租户id和表id组成了复合主键，
4.我们再来找一个另一个index在哪里(我们把sql贴到odc里面直接搜关键字index)
SELECT
CST.TENANT_ID,
CST.GMT_CREATE,
CST.GMT_MODIFIED,
DB.DATABASE_ID,
CST.CONSTRAINT_NAME AS OBJECT_NAME,
NULL AS SUBOBJECT_NAME,
TBL.TABLE_ID AS OBJECT_ID,
NULL AS DATA_OBJECT_ID,
'INDEX' AS OBJECT_TYPE,
'VALID' AS STATUS,
'N' AS TEMPORARY,
'N' AS "GENERATED",
'N' AS SECONDARY,
0 AS NAMESPACE,
NULL AS EDITION_NAME
FROM
SYS.ALL_VIRTUAL_CONSTRAINT_REAL_AGENT CST,
SYS.ALL_VIRTUAL_TABLE_REAL_AGENT TBL,
SYS.ALL_VIRTUAL_DATABASE_REAL_AGENT DB
WHERE
CST.TENANT_ID = EFFECTIVE_TENANT_ID()
AND DB.DATABASE_ID = TBL.DATABASE_ID
AND TBL.TABLE_ID = CST.TABLE_ID
and CST.CONSTRAINT_TYPE = 1
AND TBL.TABLE_TYPE != 12
AND TBL.TABLE_TYPE != 13
AND BITAND((TBL.TABLE_MODE / 4096), 15) IN (0, 1)
AND BITAND(TBL.INDEX_ATTRIBUTES_SET, 16) = 0
(看的比较多了所以直接定位这个sql可能来自这里)
SELECT
-> TBL.TABLE_ID,CONSTRAINT_ID,CONSTRAINT_NAME,CONSTRAINT_TYPE
-> FROM
-> SYS.ALL_VIRTUAL_CONSTRAINT_REAL_AGENT CST,
-> SYS.ALL_VIRTUAL_TABLE_REAL_AGENT TBL,
-> SYS.ALL_VIRTUAL_DATABASE_REAL_AGENT DB
-> WHERE
-> CST.TENANT_ID = EFFECTIVE_TENANT_ID()
-> AND DB.DATABASE_ID = TBL.DATABASE_ID
-> AND TBL.TABLE_ID = CST.TABLE_ID
-> and CST.CONSTRAINT_TYPE = 1
-> AND TBL.TABLE_TYPE != 12
-> AND TBL.TABLE_TYPE != 13
-> AND BITAND((TBL.TABLE_MODE / 4096), 15) IN (0, 1)
-> AND BITAND(TBL.INDEX_ATTRIBUTES_SET, 16) = 0
-> and TBL.TABLE_ID='503312';
+----------+---------------+-------------------------------+-----------------+
| TABLE_ID | CONSTRAINT_ID | CONSTRAINT_NAME | CONSTRAINT_TYPE |
+----------+---------------+-------------------------------+-----------------+
| 503312 | 503313 | DEL_LOG_OBPK_1697092446800475 | 1 |
+----------+---------------+-------------------------------+-----------------+
1 row in set (0.046 sec)
通过添加tableid我们直接找到了另一个是index的东西
他其实是来自这个表
select * from SYS.ALL_VIRTUAL_CONSTRAINT_REAL_AGENT where TABLE_ID='503312'
desc SYS.ALL_VIRTUAL_CONSTRAINT_REAL_AGENT
+---------------------+-----------------------------------+------+-----+---------+-------+
| FIELD | TYPE | NULL | KEY | DEFAULT | EXTRA |
+---------------------+-----------------------------------+------+-----+---------+-------+
| TENANT_ID | NUMBER(38) | NO | PRI | NULL | NULL |
| TABLE_ID | NUMBER(38) | NO | PRI | NULL | NULL |
| CONSTRAINT_ID | NUMBER(38) | NO | PRI | NULL | NULL |
这个的主键其实也是租户id和表id(这里其实是索引id)
总结一下：
dba_objects 中OBJECT_ID，如果对象是表，其实是租户id+表id组成唯一键，如果是索引，其实是租户id+索引id组成唯一键，然后表来自SYS.ALL_VIRTUAL_TABLE_REAL_AGENT，索引来自SYS.ALL_VIRTUAL_CONSTRAINT_REAL_AGENT ，这个体现在这个OBJECT_TYPE不一样，
其实这两个表对应在sys租户(系统默认创建的)中也可以找
select table_id,table_name from oceanbase.__all_virtual_table where table_id=503312;
+----------+------------+
| table_id | table_name |
+----------+------------+
| 503312 | DEL_LOG |
+----------+------------+
1 row in set (0.057 sec)
obclient [(none)]> select table_id,constraint_name from oceanbase.__all_virtual_constraint where table_id=503312;
+----------+-------------------------------+
| table_id | constraint_name |
+----------+-------------------------------+
| 503312 | DEL_LOG_OBPK_1697092446800475 |
+----------+-------------------------------+
1 row in set (0.036 sec)