如何获得像user_objects这样的系统视图的完整定义(sql)? [英] How can I get the complete definition (sql) of system views like user_objects?
问题描述
我想获取USER_OBJECTS之类的系统视图的完整SQL代码.但是，当我执行下面的查询时，它返回一个错误，指出在SYS模式中找不到视图.
I want to get the complete SQL code of the system views like USER_OBJECTS. However, when I execute the query below, it returns an error saying view not found in the SYS schema.
select dbms_metadata.get_ddl('VIEW', 'USER_OBJECTS', 'SYS') from dual;
当我执行下面的查询时，它在text_vc列中返回一些代码，但不返回完整的代码.我看不到表格和where子句等.
When I execute the query below, it returns some codes in the text_vc column, but not the complete one. I cannot see the tables and where clause etc.
select * from ALL_VIEWS where VIEW_NAME = 'USER_OBJECTS';
但是通过此查询，我可以看到它在该名称的SYS模式中. 那么，我看不到整个查询的原因是什么?有没有办法看到这一切?
But with this query, I can see that it is in the SYS schema with that name. So, what is the reason that I cannot see the whole query? And is there a way to see it all?
推荐答案
+1，用于查看系统视图的定义！
+1 for looking at the definitions of the system views!
第一个问题(DBMS_METADATA为空)是特权问题.根据文档，普通用户将只能看到自己的对象.您需要角色SELECT_CATALOG_ROLE或EXP_FULL_DATABASE来查看其他用户对象.
The first problem (DBMS_METADATA empty) is a privilege problem. According to the documentation, normal users will see only their own objects. You'll need the role SELECT_CATALOG_ROLE or EXP_FULL_DATABASE to see other users objects.
The second problem (SQL is not complete) comes from the datatype LONG, which - according to Oracle - should not be used anymore. However, it is still used by Oracle for view definitions, defaults, constraint text etc. Because it is so hard to handle, the view ALL_VIEWS has the original text in the LONG column and a truncated text, mostly the first 4000 characters, in the column text_vc, presumably for "text in varchar".
我相信您提到列text_vc时会使用Oracle 12，Oracle 11中没有此列.想必您正在使用容器化数据库.如果是这样，请查看
I believe you use Oracle 12 as you mention the column text_vc, which is not available in Oracle 11. Presumably, you are using a containerized database. If so, then please have a look at Data Dictionary Architecture in a CDB. Apparently, the definition of Oracle supplied things like views and packages are only visible in the root container. Sigh!!
这篇关于如何获得像user_objects这样的系统视图的完整定义(sql)?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
