We help IT Professionals succeed at work.

Getting the connected users out.

sakthikumar
sakthikumar asked
on
Hi,

I have an ETL process, that does
disables the triggers in target schema,
completes the ETL process and
enables all the triggers.

My question is, If I disable the triggers, there could be any user, who might
insert some junk data. we need to prevent this.

So, for this situation, what could be the best solution?
Comment
Watch Question

Most Valuable Expert 2011
Top Expert 2012
Commented:
you could lock the tables so only the etl process can manipulate them

you could leave the triggers enabled, but modify them so they don't do anything when invoked by an ETL change

you could kill all user sessions and lock the accounts so they can't log in until the etl is complete
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
The only way I know of to guarantee this is to not let users connect during the ETL process.
1) Restart database in restrict mode
2) Do ETL
3) Restart db in normal mode

Author

Commented:
sdstuber,

you could lock the tables so only the etl process can manipulate them

how to achieve this?
Most Valuable Expert 2011
Top Expert 2012

Commented:
to lock a table so nobody else can do anything
     lock table your_schema.your_table in exclusive mode

to lock a table so nobody else can lock it or modify but can still read it
     lock table your_schema.your_table in share row exclusive mode


for more information,  see the "lock table" command

http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_9015.htm#i2064405
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
We should also point out that you can only lock a table if/when there are no active (uncommitted) transactions.

You may or may not be able to get the lock immediately.  Depending on the apps using the database, it might be almost impossible.

Author

Commented:
1) Restart database in restrict mode
2) Do ETL
3) Restart db in normal mode

How to do this? Mean,  what commands?
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
>>How to do this? Mean,  what commands?

Have you looked at startup options in the online docs?

Look for the section labeled: Restricting Access to an Instance at Startup

http://docs.oracle.com/cd/E11882_01/server.112/e25494/start001.htm