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?

Answer 1

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.

Answer 2

thanks cletus.

I haven't tried this option yet. You have given the answer an example of the case of domain1 only but how will I capture for the whole list domain as I have mentioned earlier. How will the statement look like? Honestly I am not an expert in sql but just learning due to my new role.

Also can anyone show me how can I make a new column that combine the domain_schema name with "_" and the SERV so that this new column will have the record as domain_serv.

Answer 3

Thanks guys.

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?

Answer 4

Guys... 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.