I have a PL/SQL procedure which has a couple prompts which require the user to enter some information for processing. For example, item numbers, business units & so on.
This as well prompts confirmation at the end of the script.
This allows several users to rely on the same script to massively setup data for testing (projects & so…). However, there are some certainly couldn’t-care-less users that would leave the COMMIT confirmation prompt open, thus locking the record & preventing other users from working.
When we go to the users that has the record locked (we can only identify them with help of a DBA Team, which takes a lot of time), we ask them to close their tabs, & it always shows:
Is there a way to force a timeout (via the same SQL script) when a specific timelapse goes, which would call my WHENEVER SQLERROR handler for automatic rollback?
Advertisement
Answer
You could try to limit the IDLE_TIME of the users to a short time. I’ve used 1 minute for the demonstration:
CREATE PROFILE couldntcareless LIMIT IDLE_TIME 1; ALTER USER xxx PROFILE couldntcareless;
If the user is now idle for longer than a minute, the changes are rolled back and it’s session is killed.
Social hint: I assume you won’t make any friends with this feature. I normally indentify the offending users by a look in the database, contact them and tell them that they do block the work of colleagues. Usually they were not aware of the fact, are quite embarrased and tend to improve their behaviour. If you don’t have permission to v$locked_object, speak to a DBA to create a view that filters out your table(s) and make this view available to you.
Technical hint: The system parameter RESOURCE_LIMIT must be set to enfource those limits, aparently directly in the PDB, and you might have to restart the database:
ALTER SYSTEM SET RESOURCE_LIMIT=TRUE SCOPE=BOTH;