I know one can query what is the primary key of a user table in Oracle database. But how can I do this for a system table, for example to know what is the primary key of all_objects table?
我知道可以查询Oracle数据库中用户表的主键是什么。但是我如何为系统表执行此操作,例如知道all_objects表的主键是什么?
2 个解决方案
#1
There is not difference between "user tables" and "system tables" - their primary keys can all be queried from the data dictionary in the same fashion.
“用户表”和“系统表”之间没有区别 - 它们的主键可以以相同的方式从数据字典中查询。
all_objects, however, is not a table - its a view. Therefore, it doesn't have a primary key, so you cannot query it.
但是,all_objects不是一个表 - 它是一个视图。因此,它没有主键,因此您无法查询它。
#2
Figure out what the actual table is first.
找出实际表格的第一个内容。
Do an explain plan on SELECT * FROM ALL_TABLES;
对SELECT * FROM ALL_TABLES做一个解释计划;
You'll see a scan of SYS.OBJ$ - that's the table you're looking for.
您将看到SYS.OBJ $的扫描 - 这是您正在寻找的表。
A query of constraints of type 'P' from ALL_CONSTRAINTS doesn't show any entries for that table, so no primary key on that table.
从ALL_CONSTRAINTS查询“P”类型的约束不会显示该表的任何条目,因此该表上没有主键。
I don't think this is your real question though. What are you really looking to get at?
我不认为这是你真正的问题。你真的想要得到什么?
By the way, views can actually have constraints defined on them, that's been a feature since 10g I believe.
顺便说一句,视图实际上可以在它们上定义约束,这是我认为10g以来的一个特性。
#1
There is not difference between "user tables" and "system tables" - their primary keys can all be queried from the data dictionary in the same fashion.
“用户表”和“系统表”之间没有区别 - 它们的主键可以以相同的方式从数据字典中查询。
all_objects, however, is not a table - its a view. Therefore, it doesn't have a primary key, so you cannot query it.
但是,all_objects不是一个表 - 它是一个视图。因此,它没有主键,因此您无法查询它。
#2
Figure out what the actual table is first.
找出实际表格的第一个内容。
Do an explain plan on SELECT * FROM ALL_TABLES;
对SELECT * FROM ALL_TABLES做一个解释计划;
You'll see a scan of SYS.OBJ$ - that's the table you're looking for.
您将看到SYS.OBJ $的扫描 - 这是您正在寻找的表。
A query of constraints of type 'P' from ALL_CONSTRAINTS doesn't show any entries for that table, so no primary key on that table.
从ALL_CONSTRAINTS查询“P”类型的约束不会显示该表的任何条目,因此该表上没有主键。
I don't think this is your real question though. What are you really looking to get at?
我不认为这是你真正的问题。你真的想要得到什么?
By the way, views can actually have constraints defined on them, that's been a feature since 10g I believe.
顺便说一句,视图实际上可以在它们上定义约束,这是我认为10g以来的一个特性。