Oracle – $X Tables Do Not Exist Issue

oracleoracle-11g-r2performance

In troubleshooting why a database application is working infinitely better on test than production, I ran across this phenomenon: I cannot query the X$ tables in production.

SQL> desc x$ksppi
ERROR:
ORA-04043: object "SYS"."X_$KSPPI" does not exist


SQL> select * from dba_synonyms where synonym_name = 'X$KSPPI';

OWNER                          SYNONYM_NAME                   TABLE_OWNER                    TABLE_NAME
------------------------------ ------------------------------ ------------------------------ ------------------------------
DB_LINK
--------------------------------------------------------------------------------------------------------------------------------
PUBLIC                         X$KSPPI                        SYS                            X_$KSPPI



SQL> desc sys.x$ksppi;
ERROR:
ORA-04043: object sys.x$ksppi does not exist

On test, I actually receive the table description back.

It is my limited understanding that these are actually defined outside of the database as C structures or some such thing and are created upon startup to populate the V$ views. If my production database, specifically the optimizer, does not have access to the hidden parameters, I could definitely see why it could have a negative impact on performance.

Why wouldn't the X$ tables be created upon startup? Issues with this application began some time after it was upgraded to 11.2.0.4, so could a botched upgrade have done this? What troubleshooting steps can I take to analyze the root issue?

Best Answer

There is no such thing as X_$KSPPI. It is X$KSPPI. You have a synonym pointing to a non-existent object.

That synonym does not exist in Oracle databases, that was created by someone or something else.

Your attempt to desc sys.x$ksppi; fails if you do that any other user than SYS.

If your database could not access parameters, you would have a much bigger problem with more obvious symptoms.

Related Question