Database Administration (MOSC)

MOSC Banner

Successfully running invalid sql subquery for dba_objects view

edited Feb 5, 2020 9:10AM in Database Administration (MOSC) 5 commentsAnswered ✓

Following test case from 19.6 database

But same applies also for 12.1.0.2.0 database

This is the test case:

-- -- It should give me "ORA-00904: "OWNER": invalid identifier" instead-- set lines 200;col owner for a20;col object_name for a20;SQL> select owner, object_name from dba_objects where owner in (select owner from dba_users) and rownum < 5;OWNER                OBJECT_NAME-------------------- --------------------SYS                  C_TS#SYS                  I_TS#SYS                  C_FILE#_BLOCK#SYS                  I_FILE#_BLOCK#---- Note that I filtered SYS user out, but still I get SYS objects and no ORA-00904--SQL> select owner, object_name from dba_objects where owner in (select owner from dba_users where username != 'SYS') and rownum < 5;OWNER                OBJECT_NAME-------------------- --------------------SYS                  C_TS#SYS                  I_TS#SYS                  C_FILE#_BLOCK#SYS                  I_FILE#_BLOCK#-- Bear in mind that dba_users view does not have owner column, there is username instead !SQL>

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center