Use Service Name in Oracle to Disable Database Login

by Seth Miller

Recently I was trying to do some database updates to a development database, but I was running into problems truncating and deleting from tables because other users on the system had rows locked with NOWAIT. I wanted to find a way to disable user access without actually taking down the database. I also had the problem of having other production databases on the same server so just shutting down the listener was not an option. The third option was to take down the forms application server, but that is also serving multiple instances. So the best option seemed to be to disable the service that the listener was connecting to.

First, I confirmed the tnsnames.ora on the app server was pointing to service name instead of sid. Then, I used dbms_service to disable the service.

execute dbms_service.STOP_SERVICE(‘serv’);

That didn’t seem to do anything though so I looked at the dba_services table to make sure it was disabled. What I found was that all of the services were disabled.

SYS$BACKGROUND NO
SYS$USERS NO
servXDB NO
serv NO

I’m not sure why this didn’t work or why all of the services are disabled, but I ended up altering the tnsnames.ora on the application server so that the forms couldn’t connect. If there are any thoughts as to how to accomplish what I was trying to do, please leave a comment.