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
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 !!!
