Newbie: Need Help in sharing common data tables across schemas

Newbie: Need Help in sharing common data tables across schemas

Post by Jerry Gitome » Sun, 31 Dec 1899 09:00:00



        Persevere in setting up your db links and only have the data in one
place.  It will simplify maintenance and avoid the problem of
inconsistent data.

Original Message dated 4/10/00, 10:03:16 PM

Re: Newbie: Need Help in sharing common data tables across schemas:

Hello.
Will this idea(s) work?
I will have a schema called common where I have various reusable data
tables that are used by various other schemas for foreign keys and
various other things. I think that I need to construct a db link to
provide access
from the other schema to the common schema however I am having
problems setting that up and also the permissions.
Is this the best method for doing this type of data reuse or should I
be duplicating and pushing out the data to each schema. What is the
preferred way to do this and what are the pros/cons?
STeve

 
 
 

Newbie: Need Help in sharing common data tables across schemas

Post by Tom Zaman » Sun, 31 Dec 1899 09:00:00


We have done something like this,  we used private synonyms.
you could use public sysnonyms so by default every one could access them.
Tom


  Hello.
  Will this idea(s) work?

  I will have a schema called common where I have various reusable data tables that are used by various other schemas for foreign keys and various other things. I think that I need to construct a db link to provide access
  from the other schema to the common schema however I am having problems setting that up and also the permissions.

  Is this the best method for doing this type of data reuse or should I be duplicating and pushing out the data to each schema. What is the preferred way to do this and what are the pros/cons?

  STeve

 
 
 

Newbie: Need Help in sharing common data tables across schemas

Post by DNP » Sun, 31 Dec 1899 09:00:00


Are all the schemas in one unique database (a set / collection of
datafiles)?

If so, use synonyms.

Another idea is to use schema-name-prefixes.

e.g. DEMO could use SCOTT.EMP

SCOTT could use DEMO.ITEM and so on.

The owner of the tables needs to GRANT <object permissions> to <other
schemas / users>.

Until version 8.1.6, schema = user.

Ignore Db Links unless your schemas are on different databases (thus you
need to transfer data / read data across different respectice instances
( a shared memory area and a set of background OS processes).

Source the Oracle manuals and research the topics listed above.

David P.

Oracle Certified DBA.

=======================================================================


> Persevere in setting up your db links and only have the data in one
> place. It will simplify maintenance and avoid the problem of
> inconsistent data.

> ----------------------------------------------------------------------

> Original Message dated 4/10/00, 10:03:16 PM


> Re: Newbie: Need Help in sharing common data tables across schemas:

> ----------------------------------------------------------------------

> Hello.

> Will this idea(s) work?

> I will have a schema called common where I have various reusable data
> tables that are used by various other schemas for foreign keys and
> various other things. I think that I need to construct a db link to
> provide access
> from the other schema to the common schema however I am having
> problems setting that up and also the permissions.

> Is this the best method for doing this type of data reuse or should I
> be duplicating and pushing out the data to each schema. What is the
> preferred way to do this and what are the pros/cons?

> STeve

 
 
 

1. Insert across 2 tables - newbie help!

Please help, there must be a simple solution to this, but I can't find it!

I'm trying to do an insert across 2 tables that have a primary key/foreign
key relationship. I want to use a stored procedure. I'm figuring that
nesting one stored procedure inside another would be a good idea, the first
could insert into the primary table and return the primary key as an Output
value, but I can't even find how to this.

Please help!

Steven

2. Y2 compliance Dbase4

3. SQL 2000 on Server 2003 Problem

4. Insert across 2 tables - please help newbie!

5. SQL Server & VB - Deployment

6. informix newbie needs help - out of shared memory segments, c

7. Date

8. informix newbie needs help - out of shared memory segments, check system SHMSEG

9. informix newbie needs help - out of shared memory

10. Two Databases Share Common Table?

11. TechTips: Common Paradox-table sharing mistakes