Search code examples
databaseoracle-databaseoracle12c

Using synonym in Oracle DB in SYS schema

There is V_$SQL view in SYS schema. In public schema I have synonym v$sql for it. If I connect to DB with my user dbuser and I try to get something from v$sql it works. Problem is that when I change schema to SYS (but still I am dbuser) and try to use v$sql again my synonym doesn't work. I get error

[42000][942] ORA-00942: table or view does not exist

If I use V_$SQL instead of v$sql it works. Why after schema change my synonym doesn't work?

Solution

  • When you have your current schema set to 'SYS', then V$SQL resolves to SYS.V$SQL, which you do not have access to.

    "But, I checked DBA_OBJECTS and there is no SYS.V$SQL!!!"

    SYS.V$SQL is a fixed table. It does not show up in DBA_OBJECTS. You can see it in V$FIXED_TABLE.

    select * from v$fixed_table
where name like 'V$SQL';

    +-------+------------+------+-----------+--------+
| NAME  | OBJECT_ID  | TYPE | TABLE_NUM | CON_ID |
+-------+------------+------+-----------+--------+
| V$SQL | 4294951148 | VIEW |     65537 |      0 |
+-------+------------+------+-----------+--------+

    The SYS schema is special and weird and for Oracle's use only. It's best not to mess around with it.