Views Based on which tables ?

[Sonia]

Thanx jurij,
Wonderful query....

But it seems that it doesn't work
for v$views...

[jmodic]

Ah, yes, it doesn't work for "fixed views" (V$ views). That's simply because those views are not based on real tables, so data dictionary does not contain any information about their underlying objects. For those views you'll have to check their deffinition and find out their underlying fixed tables (X$ tables) from the FROM clause of their deffinition. The definition can be found by querying V$FIXED_VIEW_DEFINITION. For example, if you are interested on what fixed tables is V$ACCESS based on, you would isue a following query:

SELECT view_definition FROM v$fixed_view_definition
WHERE view_name = 'GV$ACCESS';

From the output of the query you'll find out that it is based on x$ksuse, x$kglob, x$kgldp and x$kgllk.

Note that in most cases you will need to query on GV$ views (not directly on V$), because V$ views are usualy defined on top of GV$ views.