NAME TYPE VALUE
———————————— ———– ——————————
db_files integer 100
Tag Archives: ORACLE
NAME TYPE VALUE
Well they (Apps or users) can’t hide any longer … The Oracle’s best Spy is on your side – here is the SQL which can bring them to the open. ( you need to be the SysDBA to do this though )
1. set linesize 30000 – to make sure everything appears in a single line
2. SQL> select username, sid, serial#, status, osuser, process, machine, terminal, program, logon_time from v$session where username is not null and username not in (‘SYSTEM’,’SYS’,’DBSNMP’);
– All the agents are displayed in a table with their USERNAME, SID,SERIAL#,STATUS,OSUSER,PROCESS,MACHINE,TERMINAL,PROGRAM
you have to decide if you will shoot (force kill ) or negotiate ( stop the services in the machines they are running in )
So you want to update the schema, which requires to drop all users and you see that your script is complaining that some of the users (in oracle users are the schemas) are still conneted and cannot be dropped. This would normally mean some one is running an application/service/App server, which is holding some connections to your database.
You have been nice to all, mailed the group, pinged some of your fellow developers, but there is a rogue application holding on to a user/schema. What would you do. wait for ever or take matter into your hands
Here is some Mojo and fire power for you to boot/kill those users force fully ( ofcourse you need to have SysDba access to the database, otherwise you are out of luck )
- find the sessions for the users/schema , as a DBA use :
select sid,serial# from v$session where username = '<user_schema>'
- Then kill them with :
alter system kill session '<sid>,<serial#>'
A query that produces ready-built kill-statements could be
select 'alter system kill session ''' || sid || ',' || serial# || ''';' from v$session where username = '<your_schema>'
This will return one kill statement per session for that user – something like:
alter system kill session '375,64855';
alter system kill session '346,53146';
- Now Drop the user : drop user <
That’s it – The User is vaporized !