Hi Mladen,
I've just checked it on my 23.2:
SQL> select banner_full from v$version;
BANNER_FULL
--------------------------------------------------------------------------------
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
Version 23.2.0.0.0
SQL> desc x$ksppi
Name Null? Type
----------------------------------------- --------
----------------------------
ADDR RAW(8)
INDX NUMBER
INST_ID NUMBER
CON_ID NUMBER
KSPPINM VARCHAR2(80)
KSPPITY NUMBER
KSPPDESC VARCHAR2(255)
KSPPIFLG NUMBER
KSPPILRMFLG NUMBER
KSPPIHASH NUMBER
On Sat, Mar 30, 2024 at 2:13 AM Mladen Gogala <gogala.mladen@xxxxxxxxx>
wrote:
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
