Just need a small hint re: mirroring tables

#1

Oracle 8i / Win2K

This seems absurdly simple, but there isn't any coverage of it in all my
Oracle books or in several Google searches.

I have 2 duplicate tables in different schemas of the same database. All
I need to do is continuously synchronize table2 to always update itself
to be the same as table1 every time table1 changes.

My first thought was to write a trigger that covers insert, update and
delete events on table1. I started to write it (using

ld and :new
values for the update part). I think this will work fine, but I thought
I might be hand writing a built-in feature of Oracle.

Then I got an idea that it might be better to do a truncate table2,
insert into table2 select * from table1 for each event. This doesn't
sound like a good idea performance-wise, although the tables are on the
small side (just ordinary varchars and numbers, about 30 cols, a few
hundred rows).

Then I thought, I wonder if materialized views would be more efficient,
although all the chapters about them concern moving subsets or
calculated data to a remote database.

Finally, I thought one of you might be kind enough to just give me a
nudge in the right direction:

1. Stick to the triggers.
2. Go for the truncate method.
3. Use materialized views.
4. Go with the super-secret Oracle Mirrored Tables option (that I
suspect is there, but can't find).

Thanks!
-Paul

#2

Paul Murphy <pmurphy (AT) scsinet (DOT) com> wrote

Quote:

Oracle 8i / Win2K

This seems absurdly simple, but there isn't any coverage of it in all my
Oracle books or in several Google searches.

I have 2 duplicate tables in different schemas of the same database. All
I need to do is continuously synchronize table2 to always update itself
to be the same as table1 every time table1 changes.

My first thought was to write a trigger that covers insert, update and
delete events on table1. I started to write it (using

ld and :new
values for the update part). I think this will work fine, but I thought
I might be hand writing a built-in feature of Oracle.

Then I got an idea that it might be better to do a truncate table2,
insert into table2 select * from table1 for each event. This doesn't
sound like a good idea performance-wise, although the tables are on the
small side (just ordinary varchars and numbers, about 30 cols, a few
hundred rows).

Then I thought, I wonder if materialized views would be more efficient,
although all the chapters about them concern moving subsets or
calculated data to a remote database.

Finally, I thought one of you might be kind enough to just give me a
nudge in the right direction:

1. Stick to the triggers.
2. Go for the truncate method.
3. Use materialized views.
4. Go with the super-secret Oracle Mirrored Tables option (that I
suspect is there, but can't find).

Thanks!
-Paul

Forget about 4. It doesn't exist. The obvious reason of course is you
will have a database with a completely out of order datamodel.
I would go for
5 Drop either one of the tables and set up proper synonyms and grants
Without that you will continue symptom figthing and end up in a mess.
IMO, any other solution is just stupid.

Sybrand Bakker
Senior Oracle DBA

#3

"Paul Murphy" <pmurphy (AT) scsinet (DOT) com> wrote

Quote:

Oracle 8i / Win2K

This seems absurdly simple, but there isn't any coverage of it in all my
Oracle books or in several Google searches.

I have 2 duplicate tables in different schemas of the same database. All
I need to do is continuously synchronize table2 to always update itself
to be the same as table1 every time table1 changes.

I suspect your problem is with your problem definition. You are stating the
problem as

"I need the data in schema 2 to be synchronized in real time", whereas I
suspect your problem is "I need schema 2 to be able to 'see' the data from
schema 1", to which the solution is to grant the relevant access rights on
the table in schema 1 to schema 2. You would probably setup a synonym so
that the code doesn't need altering.

--
Niall Litchfield
Oracle DBA
Audit Commission UK

#4

sybrandb (AT) yahoo (DOT) com wrote:

Quote:

Paul Murphy <pmurphy (AT) scsinet (DOT) com> wrote

Oracle 8i / Win2K

This seems absurdly simple, but there isn't any coverage of it in all my
Oracle books or in several Google searches.

I have 2 duplicate tables in different schemas of the same database. All
I need to do is continuously synchronize table2 to always update itself
to be the same as table1 every time table1 changes.

My first thought was to write a trigger that covers insert, update and
delete events on table1. I started to write it (using

ld and :new
values for the update part). I think this will work fine, but I thought
I might be hand writing a built-in feature of Oracle.

Then I got an idea that it might be better to do a truncate table2,
insert into table2 select * from table1 for each event. This doesn't
sound like a good idea performance-wise, although the tables are on the
small side (just ordinary varchars and numbers, about 30 cols, a few
hundred rows).

Then I thought, I wonder if materialized views would be more efficient,
although all the chapters about them concern moving subsets or
calculated data to a remote database.

Finally, I thought one of you might be kind enough to just give me a
nudge in the right direction:

1. Stick to the triggers.
2. Go for the truncate method.
3. Use materialized views.
4. Go with the super-secret Oracle Mirrored Tables option (that I
suspect is there, but can't find).

Thanks!
-Paul

Forget about 4. It doesn't exist. The obvious reason of course is you
will have a database with a completely out of order datamodel.
I would go for
5 Drop either one of the tables and set up proper synonyms and grants
Without that you will continue symptom figthing and end up in a mess.
IMO, any other solution is just stupid.

Sybrand Bakker
Senior Oracle DBA

I'm trying to get two pieces of software to share the same data, but
they both have hardcoded into the executables the different
schema.tablename. Because I can't modify the software, I have to create
two duplicate tables of the same name in two separate schemas, but I
need both executables using the same data, so I need them to be
constantly synchronized. Maybe this is not a common problem and that's
why I'm having so much trouble finding anything in books. Any ideas
given the situation? Thanks! -Paul

#5

"Paul Murphy" <pmurphy (AT) scsinet (DOT) com> wrote

Quote:

I'm trying to get two pieces of software to share the same data, but
they both have hardcoded into the executables the different
schema.tablename. Because I can't modify the software, I have to create
two duplicate tables of the same name in two separate schemas, but I
need both executables using the same data, so I need them to be
constantly synchronized. Maybe this is not a common problem and that's
why I'm having so much trouble finding anything in books. Any ideas
given the situation? Thanks! -Paul

Sybrands suggestion should fit the bill admirably.

SQL> create user u01 identified by u01;

User created.

SQL> create user u02 identified by u02;

User created.

SQL> grant app_user to u01,u02;

Grant succeeded.

SQL> alter user u01 default tablespace users temporary tablespace temp quota
100m on users;

User altered.

SQL> alter user u02 default tablespace users temporary tablespace temp quota
100m on users;

User altered.

SQL> conn u01/u01
Connected.
SQL> create table app_table(c1 char(20));

Table created.

SQL> grant all on app_table to u02;

Grant succeeded.

SQL> insert into app_table values('here is my data');

1 row created.

SQL> commit;

Commit complete.

SQL> conn u02/u02
Connected.
SQL> create synonym app_table for u01.app_table;

Synonym created.

SQL> select * from u02.app_table;

C1
--------------------
here is my data

SQL> insert into u02.app_table values('here is u02 working');

1 row created.

SQL> commit;

Commit complete.

SQL> conn u01/u01
Connected.
SQL> select * from u01.app_table;

C1
--------------------
here is my data
here is u02 working

SQL> spool off

#6

Paul Murphy wrote:

Quote:

snipped

I'm trying to get two pieces of software to share the same data, but
they both have hardcoded into the executables the different
schema.tablename. Because I can't modify the software, I have to create
two duplicate tables of the same name in two separate schemas, but I
need both executables using the same data, so I need them to be
constantly synchronized. Maybe this is not a common problem and that's
why I'm having so much trouble finding anything in books. Any ideas
given the situation? Thanks! -Paul

No you don't. Look up synonyms at http://tahiti.oracle.com

If you disagree post the names of the software manufacturers, the applications, and the names of the tables.

This reads as somehwere between highly unlikely and not very believable.
--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
damorgan@x.washington.edu
(replace 'x' with a 'u' to reply)

#7

"Paul Murphy" <pmurphy (AT) scsinet (DOT) com> wrote

---snip--

Quote:

1. They don't want to give any permissions whatsoever across schemas
(even though the data is going to be the exact same data and permissions
would be granted only on those 3 specified tables).

2. They are worried about Maximo putting locks on the table and Lawson
not being able to handle the situation (both apps are supposedly
designed to assume 100% availablity to those 3 tables for their own use).

I know this sounds irrational, but their attitude is: "We want mirrored
tables in separate schemas, period. Can you do it or not?"

I think I can do it with something like this:

CREATE OR REPLACE TRIGGER MyTrigger

AFTER INSERT OR UPDATE OR DELETE ON Schema1.Table1
--snip--

ok ... They don't want to give *any*permissions whatsoever across schemas...
How do you think your trigger will work without you granting any
permissions?
How do you think a materialized view approach will work without granting any
permissions?
How do you think *any* approach will work without granting any permissions?

Tell them to contact Ms Cleo. She might be able to help

Anurag

#8

Paul Murphy was kind enough to write:

Quote:

Daniel Morgan wrote:
Paul Murphy wrote:

snipped

I'm trying to get two pieces of software to share the same data, but
they both have hardcoded into the executables the different
schema.tablename. Because I can't modify the software, I have to create
two duplicate tables of the same name in two separate schemas, but I
need both executables using the same data, so I need them to be
constantly synchronized. Maybe this is not a common problem and that's
why I'm having so much trouble finding anything in books. Any ideas
given the situation? Thanks! -Paul

No you don't. Look up synonyms at http://tahiti.oracle.com

If you disagree post the names of the software manufacturers, the applications, and the names of the tables.

This reads as somehwere between highly unlikely and not very believable.
--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
damorgan@x.washington.edu
(replace 'x' with a 'u' to reply)

The apps are Maximo and Lawson, the tables are: uzbprem, uzbmetr and
uzbsvco. The Maximo schema and the Lawson schema are in the same
database (Oracle 8i / Win2K). I understand what you said about granting
all to user2 and creating a private synonym inside user2's schema and
that certainly sounds like the correct thing to do to me. When I
suggested this approach, I was told:

1. They don't want to give any permissions whatsoever across schemas
(even though the data is going to be the exact same data and permissions
would be granted only on those 3 specified tables).

2. They are worried about Maximo putting locks on the table and Lawson
not being able to handle the situation (both apps are supposedly
designed to assume 100% availablity to those 3 tables for their own use).

I know this sounds irrational, but their attitude is: "We want mirrored
tables in separate schemas, period. Can you do it or not?"

I think I can do it with something like this:

CREATE OR REPLACE TRIGGER MyTrigger

AFTER INSERT OR UPDATE OR DELETE ON Schema1.Table1

FOR EACH ROW

BEGIN

IF INSERTING THEN

CODE

ELSIF UPDATING THEN

CODE

ELSIF DELETING THEN

CODE

END IF;

END;

/

I'll fill in the code areas myself, I'm not asking for that type of
help, just to know if there is a more efficient way to mirror tables
across schemas other than the type of trigger I posted above.

It does seem like a bad idea from a design standpoint to mirror the
tables, but I have no choice except to have mirrored tables unless there
is a way to eliminate their 2 requirements (permissions and locks).

I really do appreciate getting advice and when I've learned enough, I'll
be in a position to help the future newbies. Thanks again.

-Paul

Yes, triggers like this are an acceptable way. At one company where I
worked, we used this technique to sync several "data marts" on different
servers.

Sigh. If you had 9i, I could recommend Oracle Streams. Quoting from the
9i New Features manual:

"Oracle Streams enables the propagation of data, transactions and events
in a data stream, either within a database or from one database to
another. The stream routes published information to subscribed
destinations. This provides the functionality and flexibility to capture
and to manage events and then to share those events with other databases
and applications..."

Since it apparently uses Advanced Queueing, perhaps you can implement
something similar in 8i. Or it might be a good time to push for an
upgrade to 9i.

--
[:%s/Karsten Farrell/Oracle DBA/g]

#9

Anurag Varma wrote:

Quote:

"Paul Murphy" <pmurphy (AT) scsinet (DOT) com> wrote in message
news:2WDMa.31337$iZ3.30149 (AT) twister (DOT) nyroc.rr.com...
---snip--

1. They don't want to give any permissions whatsoever across schemas
(even though the data is going to be the exact same data and permissions
would be granted only on those 3 specified tables).

2. They are worried about Maximo putting locks on the table and Lawson
not being able to handle the situation (both apps are supposedly
designed to assume 100% availablity to those 3 tables for their own use).

I know this sounds irrational, but their attitude is: "We want mirrored
tables in separate schemas, period. Can you do it or not?"

I think I can do it with something like this:

CREATE OR REPLACE TRIGGER MyTrigger

AFTER INSERT OR UPDATE OR DELETE ON Schema1.Table1

--snip--

ok ... They don't want to give *any*permissions whatsoever across schemas...
How do you think your trigger will work without you granting any
permissions?
How do you think a materialized view approach will work without granting any
permissions?
How do you think *any* approach will work without granting any permissions?

Tell them to contact Ms Cleo. She might be able to help

Anurag

I should have been more specific. They don't want to grant any maximo
user account any lawson object permissions or vice versa. They're
perfectly OK with me creating a totally separate user that's not part of
maximo or lawson and giving it permissions on both tables.
-Paul

#10

"Karsten Farrell" <kfarrell (AT) belgariad (DOT) com> wrote

---snip---

Quote:

Yes, triggers like this are an acceptable way. At one company where I
worked, we used this technique to sync several "data marts" on different
servers.

Sigh. If you had 9i, I could recommend Oracle Streams. Quoting from the
9i New Features manual:

"Oracle Streams enables the propagation of data, transactions and events
in a data stream, either within a database or from one database to
another. The stream routes published information to subscribed
destinations. This provides the functionality and flexibility to capture
and to manage events and then to share those events with other databases
and applications..."

Since it apparently uses Advanced Queueing, perhaps you can implement
something similar in 8i. Or it might be a good time to push for an
upgrade to 9i.

--
[:%s/Karsten Farrell/Oracle DBA/g]

Using Oracle Streams to sync tables in the same database but different
schemas would be an overkill
according to me. Its a relatively new technology and not that easy to set
up. Oracle uses Streams
for its logical standby databases. Its quite similar to the MM replication
in terms of what it does.
I would still agree with Daniel's and Sybrand's suggestions of using
synonyms.

Also, I'm not sure if Oracle Streams would work at all for his case.

In the end it all depends on how synced up these tables need to be. If they
need to be exactly
in sync at a certain point of time then synonyms are the way to go. If OP
can afford to sync them up
at the end of the day then any crude method would do.

my 2 cents.
Anurag