0

I have created public synonym as suggested in my other question about creating view at system level. Having said that I have created individual public synonym out of the view so that I don't have to connect to the individual domain anymore. My problem now is how to create a master kind of public synonym to capture all those synonyms which I have created earlier. To what extent the "for" statement can be used?

Example: At system level, I will run query for each domain where MYVIEW is created : Create or replace public synonym domain1_myview for domain1.myview; Create or replace public synonym domain2_myview for domain2.myview; Create or replace public synonym domain3_myview for domain3.myview;

Then I have 3 public synonym above.

Then I tried writing statement (out of desperation - not as expert) like below hoping to have just ONE Master_MYVIEW but failed: Create or replace public synonym MASTER_MYVIEW for (select * from domain1_myview union all select * from domain2_myview union all select * from domain3_myview);

The error with the above : "ORA-00995: missing or invalid synonym identifier"

Hope someone can assist me on this task. Have a nice :) day.

gbn
  • 422,506
  • 82
  • 585
  • 676
Fadzil
  • 83
  • 1
  • 4
  • I have also tried : CREATE OR REPLACE PUBLIC SYNONYM MASTER_MYVIEW for (select * from domain1.myview union all select * from domain2.myview) and so on. But I got error : "insufficient privilege". Common guys, help me out here. Appreciate any response. – Fadzil Jul 29 '09 at 04:56
  • Hi guys... I am still waiting for intelligence response from any of you out there... really desperate here. I am out of option and I also need to know if it is possible or not to create a master synonym out of the synonym that I have mentioned earlier. Can it be other than PUBLIC owner and PUBLIC SYNONYM? – Fadzil Jul 30 '09 at 03:20

1 Answers1

1

Try creating a view called MASTER_MYVIEW first (you may need to deal with privileges there as well):

create view master_myview as select ...;

Then create a public synonym for that new view:

create or replace public synonym master_myview for <owner>.master_myview;
Pop
  • 3,932
  • 1
  • 17
  • 12
  • The restriction with insufficient previlege is due to my grant. GRANT SELECT ANY TABLE TO admin (eg admin is the user) – Fadzil Aug 28 '09 at 06:06