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;
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';
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 for looking at the definitions of the system views!
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".
EDIT:
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!!