Describing X$ tables in Oracle 21c and 23c

From: Mladen Gogala <gogala.mladen_at_gmail.com>
Date: Fri, 29 Mar 2024 22:12:17 -0400
Message-ID: <57f9fd8727cb9a6836b3ac32a609a88fab839c6e.camel_at_gmail.com>

I am frequently playing with my 21c instance and I found something strange:

SQL> select count(*) from x$ksppi
  2* /

   COUNT(*)

---

       5997

Lots of parameters, it's daunting. However, when I try to describe the X$KSPPI table, I get the following:

SQL> desc x$ksppi

ERROR:
ORA-04043: object x$ksppi does not exist SQL> select * from v$version;

However, it's not just X$KSPPI. More or less, no X$ table can be described.

SQL> desc x$kglcursor

ERROR:
ORA-04043: object x$kglcursor does not exist SQL> select count(*) from x$kglcursor;

   COUNT(*)

---

        933

Elapsed: 00:00:00.034

I have tested this on both 21c and free 23c. It is no longer possible to describe X$ tables. It looks like Oracle is again playing silly games. Of course, it is possible to fetch one row from the table and get the column names that way.

SQL> select * from x$ksppi fetch first 1 rows only;

ADDR INDX INST_ID CON_ID KSPPINM KSPPITY KSPPDESC KSPPIFLG KSPPILRMFLG KSPPIHASH

___________________ _______ __________ _________ _____________ __________
_______________________________________ ___________ ______________

---

00007F953D527C98 0 1 0 _appqos_qt 3 System Queue time retrieval interval 65536 0 4264183346

Elapsed: 00:00:00.008

Column names are clearly visible so that's how we will have to use "describe" from now on. The importance of Rene Nyffenegger's page is now greater then ever. I tested both on OL 8.9. Both version 21c and version 19c have all of the available DBRU patches applied. I cannot fathom why would Oracle Corp. want to prevent database administrators from describing X$ tables. Such silly details convinced me that I was right to start learning Postgres.
--

Mladen Gogala
Database SME
https://dbwhisperer.wordpress.com

--

http://www.freelists.org/webpage/oracle-l Received on Sat Mar 30 2024 - 03:12:17 CET