如何获得 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.

第二个问题(SQL不完整)来自数据类型LONG，根据 Oracle 的说法，不应再使用它.但是，它仍然被 Oracle 用于视图定义、默认值、约束文本等.因为它很难处理，视图 ALL_VIEWS 在 LONG 中有原始文本列和一个截断的文本，主要是前 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!!

这篇关于如何获得 user_objects 等系统视图的完整定义 (sql)?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！