A Data Transform Issue

[mzhu]

I woulk like to transform the data of schema1 into schema2. In order to make the data in schema2 are the exactly same as schema1, no more updates in schema1 are allowed during data transforming. My question is:

How can I prevent any user from updating( or any transaction) during data transforming ?

I know that I can use ALTER USER schema1 ACCOUNT LOCK to prevent any new connection. But how about those who have already be connected to schema1 before data transforming? Is there any way not to allow them to do any update/transaction?

Thanks for any help.

[gandolf989]

You can stop the listener or revoke the connect privilege from all of the users you want to keep out. If you are really concerned you should select username, machine from v$session where username is not null; before you start. Of course if you do it off hours there should not be anyone around to muck up the works.

[marist89]

export schemaA with consistent=y and import fromuser=schemaA touser=schemaB

[slimdave]

Make the tablespace(s) that the schema 1 tables are on read-only? Supposing that the two are on different TS's, that is.

[gandolf989]

Originally posted by marist89
export schemaA with consistent=y and import fromuser=schemaA touser=schemaB

I was unaware of the consistant=y. So does that prevent DML/DDL while the import is happening? So that's why they call you a super-genious!!

[nagarjuna]

Originally posted by slimdave
Make the tablespace(s) that the schema 1 tables are on read-only? Supposing that the two are on different TS's, that is.

read only tablespace??? inthis case,we can truncate, drop the tables

[nagarjuna]

Originally posted by gandolf989
I was unaware of the consistant=y. So does that prevent DML/DDL while the import is happening? So that's why they call you a super-genious!!

consistent=y prevents tables that have been connected by primarykey-foreign key relations being changed. It puts locks on all of these tables untill all the objects in the relation get exported.

and your solution of stopping listener or revoking connect privileges does not affect the users who are allready connected.

Right possible solution is either to kill all those existing sessions/bounce the database. In both cases we have to stop the listener to avoid further connections to database.. I know thise is not the best solution but, right one.

[Calvin_Qiu]

Originally posted by marist89
export schemaA with consistent=y and import fromuser=schemaA touser=schemaB

I suppose that is the best solution for this case. You have no way to prevent privilege users from updating the data unless you kill all session and restrict further logon. This is terrible for a running production system.

It may give you an error of 'too old image'.

[slimdave]

Originally posted by nagarjuna
read only tablespace??? inthis case,we can truncate, drop the tables

You could drop it, but not truncate it. I don't think that consistant exports will protect you against table dropping either, but then if you have someone roaming your database dropping tables that you need, you have bigger problems than getting a consistant copy of your data

[pgoenka]

hi
It is possible to make Table space read only its a feature with 9i am not sure of its there with 8i or not!!! that can be one option too if the db is 9i...
Also one way can be to have database in restricted mode using
alter system [enable/diable] resticted session
this way you can get consistant export ...
this can also be helpful whn u wanna do some major structural changes to the db