Associating Data Dictionary Views with the Related X$ Tables
Once you are comfortable with the Oracle RDBMS as a DBA, you will find a lot of valuable information in x$ tables that won't be available anywhere else. Oracle expert Megh Thakkar shows how you can associate data dictionary tables with the related x$ tables.
The Oracle data dictionary is a repository of information about all the objects in the database. It also contains information about database security such as users, privileges, roles, auditing, and so on. The data dictionary is read-only, and you should not attempt to make direct modifications to it. The data dictionary contains four parts:
-
Internal tables (x$ tables)
-
Data dictionary tables
-
Dynamic performance views (v$ views)
-
Data dictionary views
X$ tables are the heart of the Oracle RDBMS and contain information required by the database to function. These x$ tables have cryptic names and are undocumented because you are not supposed to manipulate them directly. However, once you are comfortable with the Oracle RDBMS as a DBA, you can find a lot of valuable information in these x$ tables that isn't available anywhere else. This article shows how you can associate data dictionary tables with the related x$ tables.
Data dictionary tables obtain all their information from these x$ tables. To find out which x$ table supplies information to a particular data dictionary table, you can perform an autotrace on that data dictionary table.
For example, you would follow these steps:
-
Step 1—Log in to SQL*PLUS as SYS (or an account that has been granted access to the x$ and v$ tables).
-
Step 2: At the SQL prompt, issue the command set autotrace on:
SQL> set autotrace on
-
Step 3—Issue a query against the desired data dictionary table.
SQL> select * from v$sysstat where 0 = 1; no rows selected Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 FILTER 2 1 FIXED TABLE (FULL) OF 'X$KSUSGSTA' Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 0 consistent gets 0 physical reads 0 redo size 1081 bytes sent via SQL*Net to client 560 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 0 rows processed
As you can see, the X$KSUSGSTA table is used to obtain information displayed by the v$sysstat data dictionary table.
NOTE
For you to be able to use the autotracing facility you may have to run the SQL script "utlxplan.sql," found in the $ORACLE_HOME/rdbms/admin directory.
About the Author
Megh Thakkar is the Director of Database Technologies at Quest Software in Australia. Previously, he worked as a technical specialist at Oracle Corporation. He holds a master's degree in computer science and a bachelor's degree in electronics engineering. Megh also holds several industry vendor certifications, including OCP, MCSE, Novell Certified ECNE, and SCO Unix ACE, and he is a Lotus Notes Certified Consultant. He is a frequent presenter at Oracle OpenWorld and various international Oracle User Groups.
Megh is the author of E-commerce Applications Using Oracle8i and Java from Scratch and SAMS Teach Yourself Oracle8i on Windows NT in 24 Hours. He has also co-authored several books, including Migrating to Oracle8i, Special Edition Using Oracle8/8i, Oracle8 Server Unleashed, C++ Unleashed, COBOL Unleashed, Oracle Certified DBA, and Using Oracle8. Megh is a renowned Oracle specialist who has performed Oracle development, consulting, support, and DBA functions worldwide over the past 10 years.