Home » RDBMS Server » Server Administration » Multiple Instances
Multiple Instances [message #253808] Tue, 24 July 2007 21:43 Go to next message
vsharmac
Messages: 16
Registered: July 2005
Junior Member
Hi all,

I have a bit complex scenario here.
There are 2 separate business which have their own
separate networks. Business X uses a oracle 9i
database and it wants Business Y to have access
to part of its data(read-only).

However creating a read only view is one of a
solution here. Business Y has a requirement that
the data they access should be a live one that
Business X has.There will be a lease line across
this 2 businesses through which the application
will connect to this view.

Since addition of this view and addition of the
users from business Y might have some performance
issue with the server at the Business X end.

Business X wants a separate server for that view
to be held in so that the Business Y users access
only this new server. This view is suppose to
contain same data as it is in the live system at
any given time.

My question here is what are the ways of doing
this? Which other products or tools will be used
here?Need some help urgently

Thanks
vsharma
Re: Multiple Instances [message #253811 is a reply to message #253808] Tue, 24 July 2007 22:10 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
>creating a read only view
Please post the DDL that creates a "read only view".

If a user does NOT have INSERT, UPDATE or DELETE privs,
how does a "read only" view differ from a regular view?

How does creating a view increase the load on the DB or system?

>Since addition of this view and addition of the users from business Y
>might have some performance issue with the server at the Business X end.
^^^^^ Then again the performance might not change at all

Without some ball park numbers, it is anyone's guess what viable solution is appropriate.

How many new users as compared to existing users?
How loaded is the current database?
How much data is changing within the "view"?
How big a pipe (network speed?) will exist between 2 sites?

[Updated on: Tue, 24 July 2007 22:11] by Moderator

Report message to a moderator

Re: Multiple Instances [message #253821 is a reply to message #253811] Tue, 24 July 2007 23:36 Go to previous message
vsharmac
Messages: 16
Registered: July 2005
Junior Member
Hi,

This is how i write the read-only view

CREATE OR REPLACE VIEW myview2
AS SELECT empno, ename, sal
FROM emp
WHERE deptno=10
WITH READ ONLY OPTION;

Currently there are 100 users from Business X and there will be another 30 that will come from Business Y side.

Well this current machine is not a very powerful machine. Business X wants another machine to balance the load.

The table for which this view will be created currently has about 2,000,000 records and there are about 4,000 to 5,000 changes everyday to that table.

We are thinking of getting 1M Line.

The extra 30 Business Y users will only query the data so iam thinking this query will have some impact on the performance of the DB. It is etimated that one new user will atleast run 100 queries per day.

Was reading about Materialized Views. Don't know if that can be a solution here.

Thanks & Regrds,








Previous Topic: Oracle 10g Installation : Listener Error
Next Topic: Error During Oracle 10g Installation
Goto Forum:
  


Current Time: Tue Dec 06 02:42:24 CST 2016

Total time taken to generate the page: 0.05361 seconds