view on a synonym

view on a synonym

Post by rkusene » Wed, 09 Jul 2003 23:57:04



IDS 9.21.UC4

If a view is created on a synonym, then the view definition
takes the base table and not the synonym table.

For e.g.

'table_a' has a synonym 'table'.

new if a view is created as follows

create view table_view
as select * from table ;

the definition of the view is recorded as
create view table_view
as select * from table_a ;

For us this is creating two problems.

1. our scripts to compare schemas is reporting a diff on this.

2. we can not guarantee that table_a will exist at all  times.
   We have two sets of same table table_a and table_b with
   a synonym pointing to one of them. The idea behind this is to
   keep the application online when all users use table_b via
   synonym, while we do data loading/modification to table_a.
   For that we even do drop and load the table and finally
   a switch in synonymn.

Since the view definition is static to base table, we have to change
the view definition also every time. An unnecessary step for us.

Is this a bug or an expected behavior with views.

ravi

 
 
 

view on a synonym

Post by Madison Prue » Thu, 10 Jul 2003 01:01:54


Synonyms are used to provide an alternate name for an object.  Views can be
used to do the same thing.

One of the subtitle differences between a synonym and a view is that the
view is realized by the optimizer while the synonym is realized by the
parser.

You can achieve what you want by using a view to define the base table
instead of a synonym and then define a view on top of the base view.  The
higher view will be referencing the lower view, not the base table.


Quote:> IDS 9.21.UC4

> If a view is created on a synonym, then the view definition
> takes the base table and not the synonym table.

> For e.g.

> 'table_a' has a synonym 'table'.

> new if a view is created as follows

> create view table_view
> as select * from table ;

> the definition of the view is recorded as
> create view table_view
> as select * from table_a ;

> For us this is creating two problems.

> 1. our scripts to compare schemas is reporting a diff on this.

> 2. we can not guarantee that table_a will exist at all  times.
>    We have two sets of same table table_a and table_b with
>    a synonym pointing to one of them. The idea behind this is to
>    keep the application online when all users use table_b via
>    synonym, while we do data loading/modification to table_a.
>    For that we even do drop and load the table and finally
>    a switch in synonymn.

> Since the view definition is static to base table, we have to change
> the view definition also every time. An unnecessary step for us.

> Is this a bug or an expected behavior with views.

> ravi


 
 
 

1. views,users,grants,synonyms

hi

I ve got a question about views and synonyms ! eg:
        user etienne have a table table_a
        grant select on table_a to rocco;
        grant all on table_a to pdm;

        user rocco have a synonym of etienne.table_a named table_A
        he use this table in a view view_B.
        grant select on view_B to pdm

        user pdm want to have a synonym of the view rocco.view_b but it s  
impossible ! when he do a select : ORA-01031: insufficient privileges

if he create a view :
ORA-01720: grant option does not exist for 'ETIENNE.table_A'

if someone can help me ???

Fred.

--

___________________________________________________________________
Frederic RIVALLAND
Centre de Ressources Informatiques
Universite de La Rochelle

2. Q: Find and delete duplicates, how?

3. Oracle ODBC driver v2.5.3.1.0b --- problem accessing views and public synonyms

4. US-CA-PROJECT ACCOUNTING SENIOR PRINCIPAL

5. Can I create a view or a synonym pointing to a table in Sybase?

6. blinker

7. Synonyms and views on Oracle

8. Universe VOC error

9. Difference between synonym and materialized view

10. Question-Synonym/View Definition Modifications

11. Using (public) synonyms on views using the Oracle type/object concept

12. Synonym or View?

13. Help on synonyms, aliases and view in Sybase