My Blog

September 17, 2007

x$ table & v$ view in oracle

Filed under: Oracle — by enotebook @ 3:07 pm

We can get the structure of both objects of x$&v$, like follows:
The names for the x$ tables can be queried with:
select kqftanam from x$kqfta

The names for the v$ views can be queried with:
select * from v$fixed_view_definition order by view_name

Additional, a oracle tip from Donald K.Burleson:
Querying the X$ structures
One of the most hidden areas of the Oracle database is the X$ structures. As we may know, the X$ structures are the innermost tables that are used to build the common v$ views that we use to query the data dictionary.
Here is an idea of the naming of these X$ structures. Of course, these internal structures change between Oracle releases, and this list is from Oracle 8.1.5.

X$ks – Kernel Services

x$ksmfs kernel services, memory fixed SGA
x$ksmfsv kernel services, memory fixed SGA vectors
x$ksmjs kernel services, memory java_pool summary
x$ksmlru kernel services, memory LRU
x$ksmls kernel services, memory large_pool summary
x$ksmmem kernel services, memory
x$ksmpp kernel services, memory process pool
x$ksmsd kernel services, memory SGA definition
x$ksmsp kernel services, memory shared pool
x$ksmspr kernel services, memory shared pool reserved
x$ksmss kernel services, memory shared_pool summary
x$ksmup kernel services, memory user pool
x$ksqst kernel services, enqueue status
x$ksulop kernel services, user long operation
x$ksulv kernel services, user locale value
x$ksupr kernel services, user process

X$kc – kernel cache

x$kcbfwait kernel cache, block file wait
x$kcbwait kernel cache, block wait
x$kcccp kernel cache, controlfile checkpoint progress
x$kcfio kernel cache, file I/O
x$kclfh kernel cache, lock file header
x$kclfi kernel cache, lock file index
x$kcluh kernel cache, lock undo header
x$kclui kernel cache, lock undo index

X$kq – kernel query

x$kqfco kernel query, fixed table columns
x$kqfdt kernel query, fixed table
x$kqfp kernel query, fixed procedure
x$kqfsz kernel query, fixed size
x$kqfta kernel query, fixed table
x$kqfvi kernel query, fixed view
x$kqfvt kernel query, fixed view table

X$kg – kernel generic

x$kghlu kernel generic, heap LRUs
x$kgllk kernel generic, library cache lock
x$kglob kernel generic, library cache object
x$kglpn kernel generic, library cache pin
x$kglst kernel generic, library cache status

X$kz – Kernel security

x$kzsro kernel security, system role

X$le – lock element

x$le lock element
x$le_stat lock element status

The X$ views are completely undocumented, and Oracle does not provide any assistance for deriving the meaning of the contents of the X$ structures.

Blog at WordPress.com.