Re: Retrieving data without specifying table owner

In article <9CEW3.4533$T4.1223283_at_news1.rdc1.on.wave.home.com>,   "Jeff Van Dusseldorp" <jeff.van_at_bigfoot.com> wrote:

> In the example you showed below, can't you simply create different
database
> instances?
> Is it hard to create multiple database instances on
one
> Oracle server? More accurately, is there some reason why you
shouldn't?

It is not hard. At least no on Unix.
(Can't speak for NT personally, although I understand it can be done.)

The reason for not doing it, is to allow the Oracle to share the resources of the RBS, TEMP and SYSTEM tablespace and the shared memory structures of the SGA (e.g. the database block buffer cache). Multiple Oracle RDBMS instances prevents sharing. (e.g. Each instance has a TEMP tablespace of 500M in size. Along comes a query which needs a temporary segment of 600M, which will of course fail. If you had only had one TEMP tablespace of 1000M then it would have succeeded.)

> I imagine you could get into some
> errors that might be hard to track down. What if a synonym was left
out of
> one of the scripts, or was left pointing to the wrong table?
>
> It also seems like a lot of extra work to create synonyms for each
database
> object for each user, not to mention recreating them each time you
want to
> change the tables you see. Selecting the appropriate database seems a
lot
> easier.

On these points, I totally agree. A hell of a lot easier.

We use PL/SQL packages to create new users. The arguments are the name and the password of the user, plus the role to grant to the user. We check the dba_tab_privs table and look for all the objects the role has been granted a privlege to. We then create a private user synonym for every table (more accurately object) the user will have access to (i.e. it is all automated). Even so we have a routine to check for synonyms which are out of sync. with the role privleges, which will optionally fix any discrepancies it finds.

Another way, at least for different applications, is to insist that each application in the database obeys the convention of having a unique prefix on all object names. Then just one set of synonyms needs to be created for public. This is obviously no use when you are talking about different versions of the same application. e.g. Development versus QA.

Ben Ryan

P.S. A Sybase server (and hence a MS-SQL server) is made up of multiple databases which share TEMP, SYSTEM and shared memory. (i.e. you have the best of both worlds.) I wish Oracle would permit this to, but it doesn't so we have to live with the consequences.

I suppose Oracle concentrates on making it stuff leading edge, robust and scalable to large systems. Oracle sites, normally have highly paid DBA as a member of staff. So making things simple and easy to setup, is perhaps not as important, as it is for, say, Microsoft. However, for me at least, this does not really let Oracle Corp. off the hook.

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Nov 11 1999 - 16:42:08 CST