Very ugly Large Hammer ™ approach..
What could be happening is that rollback still needs to happened after you have whacked that user session - and that this causes the drop cascade to fail.
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 ?
1 person found this helpful
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;