Creating VIEW in system level

Question

I have created a VIEW using the CREATE OR REPLACE VIEW statement at the domain schema level.

create or replace VIEW SERV as
select loop.loop_serv serv, component.cmpnt_id,component.loop_id
from component,loop where component.loop_id = loop.loop_id 
union select distinct ' ',0,0 from component,loop;

So if I login to the domain schema and run query - select * from domain1.SERV then I would get all the results as intended.

Now I have reuse the above create statement for all the domain schemas and if I want to see all the result in one go then I would use query with "union select" since as can be understood the view have the same format throughout.

select * from domain1.SERV union all 
select * from domain2.SERV union all 
select * from domain3.SERV union all 
select * from domain4.SERV union all 
select * from domain5.SERV union all

and so on until the last domain schema.

Now how could I create the same VIEW in the system level knowing that in the "all_object" table it will list my view (object_type='VIEW' and object_name='SERV') and the "owner" column would be all my domain schema list. The VIEW in this system level eg name say ALL_SERV where if I run query it would listed all the records from all the domain schemas.

So can you help?

I am not creating an object at all. I just want to create the same VIEW in the system level. The structure of the VIEW shall be the same of that I have created in the doamin level. I guess I have to create the VIEW pointing to each domain and that mean I have x number of views as the number of domain.

Any other simple way of doing this?

Are you looking for this to be dynamic? So if you add another domain it will automatically reflect in the view? — David
– David, Commented Jul 25, 2009 at 13:43
You're not actually creating objects under the SYSTEM schema at all are you? That would be A Bad Thing. — David Aldridge
– David Aldridge, Commented Jul 25, 2009 at 18:16

cletus · Accepted Answer · 2009-07-25 00:23:10Z

2

To put that view at the "system level" (as you put it) you need a public synonym. I assume this is Oracle by the names you're using. Try:

CREATE OR REPLACE PUBLIC SYNONYM serv FOR domain1.serv;

You need appropriate privileges for this, namely CREATE PUBLIC SYNONYM. The user will need object privileges on the target to select it (ie the synonym doesn't allow someone to access something they otherwise can't).

That being said, you're using an awful lot of unions. I'd be wary of that.

answered Jul 25, 2009 at 0:23

cletus

627k169 gold badges922 silver badges945 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Kev · Accepted Answer · 2011-09-18 12:28:45Z

0

I have sorted this out. All I need is the grant privilege.

GRANT SELECT ANY TABLE TO IN_DBAMN;

That's it.

Now I can create VIEW at system level.

CREATE OR REPLACE VIEW ALL_SERV 
as 
( 
    select * from domain1.serv union all select * from domain2.serv union all etc... 
);

This view also get updated once any data are modify at their domain level.

edited Sep 18, 2011 at 12:28

Kev

120k53 gold badges308 silver badges396 bronze badges

answered Jul 25, 2009 at 5:12

Fadzil

3 Comments

Jeffrey Kemp Over a year ago

Change "domain1" into "domain2", etc. separately for each schema. You could generate the SQL required by querying the ALL_OBJECTS view.

Jeffrey Kemp Over a year ago

If you have another question you may need to raise it as a new question.

Fadzil Over a year ago

Thanks Jeff. I have created Public Synonym for each domain already and now do you know how will I combine each of this public synonym to ONE public synonym. Is it possible to do ?

Collectives™ on Stack Overflow

Creating VIEW in system level

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related