All geek questions in one place

Oracle DB, reset live connections after changing user grants

When there is a service window, we change the grants of our user of the application for reading. After we restarted our platform (after servicing), we returned the grants, but all existing connections still retain read-only mode. Is there a way to reset my connections without rebooting my platform or lost active sessions?

+4
source share
2 answers

If your application uses a connection pool and can detect broken connections, simply delete all sessions of your pool on the Oracle side after changing permissions.

To do this, you will need to run "ALTER SYSTEM KILL SESSION" in a separate connection that is not allocated from this connection pool. You also need to make an SQL query to find the SID, SERIAL # pairs for the exact sessions of your application.

If the connection pool level in the application is well written, it will detect every broken connection and open a new one, now with new permissions. It is better not to use the IMMEDIATE parameter to maintain data consistency for your application and prevent error messages. This will take some time until the completion of active transactions. If your application makes long transactions and you cannot wait too much for them, you can use the IMMEDIATE parameter.

ALTER SYSTEM KILL SESSION 'sid,serial#' [IMMEDIATE];

In the Oracle documentation, you can find how to find sessions belonging to your application.

0

Java- JdbcTemplate , Hikari, .

creationTime, , , 5 .

Java Proxy , "ORA-01031", , .

0

Source: https://habr.com/ru/post/1650348/

More articles:


All Articles