0

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?

1
  • 1
    Can you access it as SYS? Commented Feb 7, 2019 at 17:46

1 Answer 1

1

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.

1
  • Phew! I didn't realize you had to be sys to access those. I was in my DBA account and am used to being able to access just about everything else. On test I usually log in as sys without thinking about it. Commented Feb 7, 2019 at 17:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.