ORA-01940 cannot drop a user that is currently connected and kill inactive sessions in oracle

Dear Database Techies , I had really much bad experience with this API calls to Oracle RDS and resolve restore problem in testing phase

What is the Task ?

Trying to restore test dump file into Oracle RDS 19c , In Oracle RDS we need to drop and import the schemas only . Because only table_exists_actions is available in data dump method

What is the Problem ?

To drop the user getting below error on Oracle Server . Virtual connections generated from the JMeter not properly killed also even tried to kill multiple times its repeated to coming into the server

What is the steps tried to Kill ?

  • Tried to send error exceptions using Oracle Logon Trigger , But its not worked out for Inactive Sessions
  • Automated to kill jobs every minute , but its keep coming into the server
  • Even though , tried all basic steps for past 3 hours , Tried to lock the user with below command

Still we got 260 virtual connections from API calls , so all our trying is collapsed and decided to restrict access then proceed

  • Restrict connect privileges on particular user

What happened after ?

So finally after 20 Minutes incoming sessions are stopped and we are able to drop user from Oracle RDS 19c

What Next ?

Start Oracle RDS Restore using data pump Import ,

AWS RDS Services gives lots of features for Database still its have some complications to achieve tasks on daily basis

Thanks for Reading !!!



Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store