Minimize imp-effect on users

From: Tobias Rangne <nospam_tobias.rangne_at_sensor-data.se>
Date: Fri, 01 Aug 2003 16:45:16 GMT
Message-ID: <wOwWa.18357$mU6.16405@newsb.telia.net>

Hi!

As quite a Newbie on Oracle i would very much appreciate if one/some of those experiented posters in this newsgroup could just give me a hint if i am at least moving in the right direction, or if my idea is just plain stupid...

Background:
One part of our oracle-database consists of some tables that (until now) only had to be updated on a 6-8 week basis from a different server (not on the same network). Now we have been given the oportunity to recieve this data on a daily basis.

The mechanism used today is a quite ugly one: Drop all objects that belongs to the oracle-user (a read only one only used for this: 'userx') holding the data and then doing a IMP on the dump-file. This means the data is unavalible during the time of the import (not to speak of what would happen if the import would fail after the table-drops...)

After a 'litle bit' of RTFM i came up whith :

Create 2 new users temp1 and temp2
importing the file with FULL=Y to user temp1
Create synonyms for all tables and granting select for them to userx.

After this i could switch every second day between the users temp1 and temp2 to perform the following:
1. Import to the user NOT used yesterday. 2. When the import is finished just drop and recreate the synonyms and giving select-grants to userx to point at 'this days' temp-user. 3. drop the tables belonging to the temp-user used yesterday.

Still i think that i would have the problem of something going wrong in the middle of the

DROP SYNONYM1 ..
DROP SYNONYM2 ..
CREATE SYNONOM ..

CREATE SYNONOM2 ..
GRANT SELECT .. etc

sequence, (what i read in the manuals and my own testing makes me think that ( at least I ;-) am not able to perform a bunch of DML in a transaction. Am i right?)

but this would at least speed up things from a user-point of view (and the import could run 'slow and easy' with some consumer-limited-profile for temp1 and temp2 not to make things go slow for others)

I have tried this in a test-environment and it seems that everything works, and i also think that i have read the books that could be involved in solutions like this (concepts, admin, reference,sql, and backup and recovery.) but there might be much more safer, faster and better ways to accomplish this that i missed.

Oh sorry: 8.1.7.3 on W2000 and (do i dare?: 'soon to bee gone' 8.0.4 on novell 4.11)

Thanks in advance.

Best regards and a nice weekend to all.

Tobias (Hopefully soon to be able to contribute to this group, i actually love to RTFM...)

P.S. This is my first entry in a NG ever, so if I (not by intention) did cross-post, please just tell me how not to... D.S Received on Fri Aug 01 2003 - 11:45:16 CDT