Every night I have to DROP one user in order to recreate the entire schema. In order to do this I have to disconnect the user from the DB by killing its sessions. I am using following PL/SQL code
ALTER SYSTEM ENABLE RESTRICTED SESSION;
for x in (
select Sid, Serial#, machine, program
where username = 'STARSTAGING_DB'
execute immediate 'Alter System DISCONNECT Session '''|| x.Sid
|| ',' || x.Serial# || ''' IMMEDIATE';
DROP USER starstaging_db CASCADE;
ALTER SYSTEM DISABLE RESTRICTED SESSION;
I have also tried using keyword KILL instead of DISCONNECT of session.
Most of the day this works, user is dropped and new one is created, but sometimes I get the following problem.
The problem that has occurred in a log file is:
PL/SQL procedure successfully completed.
DROP USER starstaging_db CASCADE
ERROR at line 1:
ORA-01940: cannot drop a user that is currently connected
CREATE USER STARSTAGING_DB IDENTIFIED BY *****
ERROR at line 1:
ORA-01920: user name 'STARSTAGING_DB' conflicts with another user or role name
Does anyone have an idea how I can make sure that the user for sure disconnects, so that I can drop him?
thank you very much for your fast reply.
1. is there a better approach? I am very open to learn something new! Please do not hesitate to explain me or point me to some good link, concerning this topic!
2. do you think that the solution would be to make a break between killing sesions and droping the user, so that rollback has time to be executed. Would you maybe in that case let me know how to tell the bash script to make like 5 minute break? Is 5 minutes enough? I know it depends on what the user was doing. As I am beginner in bash scripting, is the sleep 300 command that would make my script to make 5 minutes delay ?
Cannot comment on a better approach as that means a proper analysis of the business requirements. Just keep in mind that killing sessions is a brutal way to manage user connectivity to the database - and should be seen as an exception and the last resort.
You can wrap your schema drop code into a retry loop. Something as follows:
loop begin ..do the drop.. success := true; exception when FAILED then dbms_lock.sleep(60); -- sleep 60 seconds end; retry := retry + 1; exit when success or retry > HAVE_WAITED_LONG_ENOUGH; end loop;