Hello!

I have an application, an ecommerce website in fact, which sits on top of
an Oracle database and uses two schemas, one for the live data, and one
for content preparation. Both schemas are in the same database.

I need to move data from the preparation schema to the live schema.
Essentially, i want to do a perfectly straightforward "insert into
livetable (a, b, c) select a, b, c from preptable where ...", but where
livetable and preptable are in different schemas.

How do i do this? Is it as simple as prefixing the table names with the
schema names? Do i need to set up database links? Will this all work from
a transactional point of view?

Thanks,
tom

--
taxidermy, high tide marks, sabotage, markets, folklore, subverting, .

Yes.


Only if those two schemas are located on two different physical servers


If those schemas are on the same server, yes.

Another option would be to use exp/imp (or expdb/impdb) to move a consistent snapshot of the data from one schema (or server) to another.

Thomas

On Sat, 14 Aug 2010, Thomas Kellerer wrote:

Excellent. For some reason, i'd been thinking this would be very
complicated.

The application logs into the two schemas with different usenames and
passwords. Presumably, i can somehow give one of the usernames access to
both schemas, right? Or rather, i can ask my DBA to do that.

Good point. And i can do an expdp with a query, although i don't know if
it'll do arbitrary ones, so that covers that angle.

tom

--
One horse laugh is worth a thousand syllogisms. -- H. L. Mencken

In article <alpine.DEB.1.10.1008160045140.16335 (AT) urchin (DOT) earth.li>,
Tom Anderson <twic (AT) urchin (DOT) earth.li> wrote:

Yes, a suitable database link includes the username and password:

<http://download.oracle.com/docs/cd/E11882_01/server.112/e10595/ds_concepts002.htm#ADMIN12083>

"Using Synonyms to Create Location Transparency" is handy, too.

--
John B. Matthews
trashgod at gmail dot com
<http://sites.google.com/site/drjohnbmatthews>