Re: Describing X$ tables in Oracle 21c and 23c

Hi Mladen,

I've just checked it on my 23.2:
SQL> select banner_full from v$version;

BANNER_FULL

 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_at_gmail.com> 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
>
>

-- 
Best regards,
Sayan Malakshinov
Oracle performance tuning engineer
Oracle ACE
http://orasql.org

--
http://www.freelists.org/webpage/oracle-l