hey guys in this session let us practically see how to kill a particular session which is requested by the application team so sometimes either the DBA or the application admin or developer execute some uh SQL query uh just for testing purpose or uh for administration activities during the business hour but um unknowingly the that particular query um May consume lot of resource either due to uh Full Table scan um of uh highly populated uh table uh which may contain millions of row and um if other critical jobs are running during that particular time then um you may get complaints Okay so either the DBA May um not be aware of the uh criticality of those other jobs or even the application uh Team uh if some new um application admin was hired so he may not know um right so if he is given a task to uh complete to get some reports uh within the deadline he he immediately uh execute some select queries in the database uh which may um cause some uh trouble okay so in this session uh let us see how to uh kill that particular session um in terms of DBA perspective okay say for example this is the application session okay so mostly application uh Team use SQL Developer to execute any SQL queries okay just consider uh this session as an application uh session and this session is used to monitor that particular query or you can say this is DBA session okay so now just consider that some uh some of the um guy from application team is trying to get the details regarding uh the text of a particular query okay so what he is doing is he is just executing select star from DBS source during the business Arts okay so he just need all these um text uh related to the procedures in the database okay so in this case application team which is executing a critical job is getting uh slowness issue because of this particular query okay so since this is a select star from table so obviously uh there should be a full table scan okay so if there's a full table scan then definitely um the whole table will be widely scanned right so in this case uh application team is requesting the DBA to kill this particular session who is executing the query okay so now this query is completed just uh just imagine uh that this is the query that uh is consuming a lot of resource and this needs to be killed okay so the DBA should first of all collect all the details okay what is the action or module or program which is being executed okay so action in the sense what is the um task uh for which the query is performing okay and what is the module uh you can say module means the application package okay the name of the module and program is also similar to uh module okay what is the program that is being executed okay so if I just select module program action from V assession where rum less than two so I will also where include where module is not null and program is not null this is just for demonstration purpose okay I'm just showing you an example to give you all the details okay so this is a background process okay similarly if there is an application session so there will be some name for this module and from where that module is getting executed and what is the task that module is performing okay so once uh if you get these details then well and good also you can collect the username so who is the user who is executing this um query or if you uh directly get the SQL uh text then that is also well and good okay so let us prepare the query so username module program action also you can get the SQL ID from V session where module name is not null program uh you can just remove all these okay sometimes um module name may be null okay so in those cases you may miss the exact session that needs to be uh uh retrieved now let me again execute this query so if you are aware of the SQL text then you can identify the SQL ID from V SQL where SQL text like d ba Source okay so this is the query which was given by application team and we are going to identify the SQL ID of this query so there will be two outputs okay why because so you have executed this query right so for this query also there will be a SQL ID separate SQL ID and that will be recorded So we need the exact SQL so this is the exact SQL okay since we executed this select query two times so this is the first time and this is the second time so for both the executions there are two SQL IDs uh created right so here I have only used SQL ID but here I have used SQL text okay since the syntax is different two SQL IDs are um created so we need to kill this SQL ID okay so let us see if uh yeah the execution is completed so let us again start the execution so now I can identify the session details with this information right so where SQL ID is equal to R SQ ID like this one so I don't need to give like okay directly I can give the SQL ID itself since this is not the SQL text okay now I got all these details right okay but the main detail that is required are S ID and serial number so we missed that so let me me get the Sid and the serial number okay so again so now to kill this particular session what you have to do is Alter system kill session you have to provide the Sid followed by the serial number okay so you can uh just directly kill like this or you can specify immediate Clause okay so the moment I executed the alra system command you can see a message saying that connection lost contact okay so this session is no longer active now if I try to execute any query I will get some error okay so you got this not connected to Oracle error so this means that this session is removed from the database connection I can no longer access the session I have to exit this and again connect to SQL plus okay so this is how you can um kill a particular session uh which was requested by the application team okay so this activity is a known activity okay uh known activity in the sense application team already notified you and you are aware of the details of the um SQL ID or the SQL text so even sometimes application team may uh give you this S ID and serial number as well if they have some uh extra privileges okay but but you have to collect uh some basic information and based on that you have to narrow down the particular uh session details and kill that session okay but uh there are other scenarios where you may not be aware of all these details and even application team does not know what is happening okay if application team is executing uh sequence of jobs which is spawning multiple processes so in those cases um DBA may not be aware and he has to identify the root process and kill all the child process and the root process okay so that's uh the difference between known activity and unknown activity so in this case you already got the SQL details so that you identify the SQL ID and based on the SQL ID you got all these session related information and killed that session okay so if you are not able to kill the session using alra system command you can directly uh use OS skill command Okay so that we'll see in the next section