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

selvackp
2 min readDec 15, 2022

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

ALTER USER datablogs ACCOUNT LOCK;

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

REVOKE CONNECT FROM datablogs;

What happened after ?

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

DROP USER datablogs CASCADE;

What Next ?

Start Oracle RDS Restore using data pump Import ,

DECLARE hdnl NUMBER;

BEGIN

hdnl := DBMS_DATAPUMP.OPEN( operation => ‘IMPORT’, job_mode => ‘SCHEMA’, job_name=>null);

DBMS_DATAPUMP.ADD_FILE( handle => hdnl, filename => ‘testrestoreblogs.dmp’, directory => ‘DATA_PUMP_DIR’,filetype => dbms_datapump.ku$_file_type_dump_file);

DBMS_DATAPUMP.ADD_FILE( handle => hdnl, filename => ‘testrestoreimportlogs.dmp’, directory => ‘DATA_PUMP_DIR’, filetype => dbms_datapump.ku$_file_type_log_file);

DBMS_DATAPUMP.START_JOB(hdnl);

END;

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

Thanks for Reading !!!

--

--