Views Based on which tables ?

HI,
Is there any way to find out on which
tables the views are built on.
Regards
sonia

Hi
If you have TOAD then extract the script of that view and you can find out from which tables it's built.

thanks

You can get the information of view from user_tab_columns table.

Retrieve Table_name, column_name from user_tab_columns for table_name like 'Viewname'

Thanks

Hi,
I am working on unix machine and i don't
think TOAD is available on unix; is there
any other way ?
regards
sonia

Quote:

---

Originally posted by pnrdba
Hi
If you have TOAD then extract the script of that view and you can find out from which tables it's built.

thanks

---

:D Without TOAD its not possible :)

You can use TEXT column from any of these dba_views, user_views, all_views

TEXT column stores the defination of view

Sameer

If you don't have TOAD you can select the TEXT from the USE R_VIEWS view.

e.g.

SELECT text
FROM user_views
WHERE view_name = 'my_view';

Regards

select table_name, column_name from user_tab_columns where table_name like 'Viewname';

Thanks

Quote:

---

Originally posted by shandj
select table_name, column_name from user_tab_columns where table_name like 'Viewname';

---

This doesn't tell you anything about underlyning tables - the TABLE_NAME in the above query is in fact the name of the view 'Viewname'.

Maybe you could use any variant of the following:

SELECT referenced_name FROM DBA_DEPENDENCIES
WHERE owner='VIEW_OWNER'
AND name = 'MY_VIEW'
AND referenced_type = 'TABLE';

Although the query itself might be terribly slow and inefficient.

********************************************

Hi,
I am working on unix machine and i don't
think TOAD is available on unix; is there
any other way ?
regards
sonia
*********************************************


With TOAD, you can connect to any oracle DB that you will be able to access from local windows box. It's merely a front end tool...

There can't be Toad for WIN,T for U, T for Linux etc...

Badrinath

Quote:

---

Originally posted by badrinathn
********************************************

Hi,
I am working on unix machine and i don't
think TOAD is available on unix; is there
any other way ?
regards
sonia
*********************************************


With TOAD, you can connect to any oracle DB that you will be able to access from local windows box. It's merely a front end tool...

There can't be Toad for WIN,T for U, T for Linux etc...

Badrinath

---

Mr Badrinath,
i know that it is a tool,
and as far as my company is concerned, we dont have a single
machine running on windows.

Thanx jurij,
Wonderful query....

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

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.