如何获得 user_objects 等系统视图的完整定义 (sql)?

我想得到像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_ROLEEXP_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.

第二个问题(SQL不完整)来自数据类型LONG,根据 Oracle 的说法,不应再使用它.但是,它仍然被 Oracle 用于视图定义、默认值、约束文本等.因为它很难处理,视图 ALL_VIEWSLONG 中有原始文本列和一个截断的文本,主要是前 4000 个字符,列在 text_vc 列中,大概是varchar 中的文本".

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 中不可用.据推测,您使用的是容器化数据库.如果是这样,那么请查看 CDB 中的数据字典架构.显然,Oracle 提供的定义(如视图和包)仅在根容器中可见.叹气!!

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!!

相关文章