{"id":6703,"date":"2018-12-18T12:57:35","date_gmt":"2018-12-18T12:57:35","guid":{"rendered":"https:\/\/dbtut.com\/?p=6703"},"modified":"2018-12-18T12:57:35","modified_gmt":"2018-12-18T12:57:35","slug":"how-to-kill-session-without-alter-system-authorization","status":"publish","type":"post","link":"https:\/\/dbtut.com\/index.php\/2018\/12\/18\/how-to-kill-session-without-alter-system-authorization\/","title":{"rendered":"How To Kill Session Without ALTER SYSTEM Authorization"},"content":{"rendered":"<p>&nbsp;<\/p>\n<p>Application developers may want to kill long-running queries. However, since application developers do not have ALTER SYSTEM privileges, they cannot execute the kill session command. Also, this authorization should not be given to users who are not dba.<\/p>\n<p>There is a way for application developers to kill sessions without ALTER SYSTEM authorization. We may create a procedure to kill sessions and authorize the user to execute them.<\/p>\n<p>Let&#8217;s create the following procedure under the SYS user and authorize the user to execute it.<\/p>\n<p>&nbsp;<\/p>\n<p><strong>Create Procedure Script:<\/strong><\/p>\n<pre class=\"lang:default decode:true\">CREATE OR REPLACE PROCEDURE kill_session(prm_sid NUMBER,\r\nprm_inst_id NUMBER DEFAULT 1) AS\r\nl_user VARCHAR2(30);\r\nl_serial NUMBER;\r\nl_inst_id NUMBER;\r\nBEGIN\r\nSELECT t.username, t.SERIAL#, t.INST_ID\r\nINTO l_user, l_serial, l_inst_id\r\nFROM gv$session t\r\nWHERE t.sid = prm_sid\r\nAND t.INST_ID = prm_inst_id;\r\nIF l_user IS NOT NULL AND l_user NOT IN ('SYS', 'SYSTEM') THEN\r\nEXECUTE IMMEDIATE 'alter system kill session ''' || prm_sid || ',' ||\r\nl_serial || ',@' || prm_inst_id || '''';\r\ndbms_output.put_line(prm_sid || 'Session was killed.');\r\nELSE\r\nraise_application_error(-20000, 'System session cannot be killed');\r\nEND IF;\r\nEND;\r\n\/<\/pre>\n<p>&nbsp;<\/p>\n<p><strong>User Authorization:<\/strong><\/p>\n<pre class=\"lang:default decode:true\">grant execute on kill_session to xUser;<\/pre>\n\n<div class=\"pvc_clear\"><\/div>\n<p id=\"pvc_stats_6703\" class=\"pvc_stats all  \" data-element-id=\"6703\" style=\"\"><i class=\"pvc-stats-icon medium\" aria-hidden=\"true\"><svg aria-hidden=\"true\" focusable=\"false\" data-prefix=\"far\" data-icon=\"chart-bar\" role=\"img\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" viewBox=\"0 0 512 512\" class=\"svg-inline--fa fa-chart-bar fa-w-16 fa-2x\"><path fill=\"currentColor\" d=\"M396.8 352h22.4c6.4 0 12.8-6.4 12.8-12.8V108.8c0-6.4-6.4-12.8-12.8-12.8h-22.4c-6.4 0-12.8 6.4-12.8 12.8v230.4c0 6.4 6.4 12.8 12.8 12.8zm-192 0h22.4c6.4 0 12.8-6.4 12.8-12.8V140.8c0-6.4-6.4-12.8-12.8-12.8h-22.4c-6.4 0-12.8 6.4-12.8 12.8v198.4c0 6.4 6.4 12.8 12.8 12.8zm96 0h22.4c6.4 0 12.8-6.4 12.8-12.8V204.8c0-6.4-6.4-12.8-12.8-12.8h-22.4c-6.4 0-12.8 6.4-12.8 12.8v134.4c0 6.4 6.4 12.8 12.8 12.8zM496 400H48V80c0-8.84-7.16-16-16-16H16C7.16 64 0 71.16 0 80v336c0 17.67 14.33 32 32 32h464c8.84 0 16-7.16 16-16v-16c0-8.84-7.16-16-16-16zm-387.2-48h22.4c6.4 0 12.8-6.4 12.8-12.8v-70.4c0-6.4-6.4-12.8-12.8-12.8h-22.4c-6.4 0-12.8 6.4-12.8 12.8v70.4c0 6.4 6.4 12.8 12.8 12.8z\" class=\"\"><\/path><\/svg><\/i> <img loading=\"lazy\" decoding=\"async\" width=\"16\" height=\"16\" alt=\"Loading\" src=\"https:\/\/dbtut.com\/wp-content\/plugins\/page-views-count\/ajax-loader-2x.gif\" border=0 \/><\/p>\n<div class=\"pvc_clear\"><\/div>\n","protected":false},"excerpt":{"rendered":"<p>&nbsp; Application developers may want to kill long-running queries. However, since application developers do not have ALTER SYSTEM privileges, they cannot execute the kill session command. Also, this authorization should not be given to users who are not dba. There is a way for application developers to kill sessions without ALTER SYSTEM authorization. We may &hellip;<\/p>\n<div class=\"pvc_clear\"><\/div>\n<p id=\"pvc_stats_6703\" class=\"pvc_stats all  \" data-element-id=\"6703\" style=\"\"><i class=\"pvc-stats-icon medium\" aria-hidden=\"true\"><svg aria-hidden=\"true\" focusable=\"false\" data-prefix=\"far\" data-icon=\"chart-bar\" role=\"img\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" viewBox=\"0 0 512 512\" class=\"svg-inline--fa fa-chart-bar fa-w-16 fa-2x\"><path fill=\"currentColor\" d=\"M396.8 352h22.4c6.4 0 12.8-6.4 12.8-12.8V108.8c0-6.4-6.4-12.8-12.8-12.8h-22.4c-6.4 0-12.8 6.4-12.8 12.8v230.4c0 6.4 6.4 12.8 12.8 12.8zm-192 0h22.4c6.4 0 12.8-6.4 12.8-12.8V140.8c0-6.4-6.4-12.8-12.8-12.8h-22.4c-6.4 0-12.8 6.4-12.8 12.8v198.4c0 6.4 6.4 12.8 12.8 12.8zm96 0h22.4c6.4 0 12.8-6.4 12.8-12.8V204.8c0-6.4-6.4-12.8-12.8-12.8h-22.4c-6.4 0-12.8 6.4-12.8 12.8v134.4c0 6.4 6.4 12.8 12.8 12.8zM496 400H48V80c0-8.84-7.16-16-16-16H16C7.16 64 0 71.16 0 80v336c0 17.67 14.33 32 32 32h464c8.84 0 16-7.16 16-16v-16c0-8.84-7.16-16-16-16zm-387.2-48h22.4c6.4 0 12.8-6.4 12.8-12.8v-70.4c0-6.4-6.4-12.8-12.8-12.8h-22.4c-6.4 0-12.8 6.4-12.8 12.8v70.4c0 6.4 6.4 12.8 12.8 12.8z\" class=\"\"><\/path><\/svg><\/i> <img loading=\"lazy\" decoding=\"async\" width=\"16\" height=\"16\" alt=\"Loading\" src=\"https:\/\/dbtut.com\/wp-content\/plugins\/page-views-count\/ajax-loader-2x.gif\" border=0 \/><\/p>\n<div class=\"pvc_clear\"><\/div>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_uf_show_specific_survey":0,"_uf_disable_surveys":false,"footnotes":""},"categories":[4],"tags":[],"class_list":["post-6703","post","type-post","status-publish","format-standard","","category-oracle"],"aioseo_notices":[],"a3_pvc":{"activated":true,"total_views":1324,"today_views":0},"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v24.9 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>How To Kill Session Without ALTER SYSTEM Authorization - Database Tutorials<\/title>\n<meta name=\"description\" content=\"How To Grant Kill Session Right Without ALTER SYSTEM Authorization\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/dbtut.com\/index.php\/2018\/12\/18\/how-to-kill-session-without-alter-system-authorization\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How To Kill Session Without ALTER SYSTEM Authorization - Database Tutorials\" \/>\n<meta property=\"og:description\" content=\"How To Grant Kill Session Right Without ALTER SYSTEM Authorization\" \/>\n<meta property=\"og:url\" content=\"https:\/\/dbtut.com\/index.php\/2018\/12\/18\/how-to-kill-session-without-alter-system-authorization\/\" \/>\n<meta property=\"og:site_name\" content=\"Database Tutorials\" \/>\n<meta property=\"article:published_time\" content=\"2018-12-18T12:57:35+00:00\" \/>\n<meta name=\"author\" content=\"dbtut\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"dbtut\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/12\/18\/how-to-kill-session-without-alter-system-authorization\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/12\/18\/how-to-kill-session-without-alter-system-authorization\/\"},\"author\":{\"name\":\"dbtut\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408\"},\"headline\":\"How To Kill Session Without ALTER SYSTEM Authorization\",\"datePublished\":\"2018-12-18T12:57:35+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/12\/18\/how-to-kill-session-without-alter-system-authorization\/\"},\"wordCount\":102,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/dbtut.com\/#organization\"},\"articleSection\":[\"ORACLE\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/dbtut.com\/index.php\/2018\/12\/18\/how-to-kill-session-without-alter-system-authorization\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/12\/18\/how-to-kill-session-without-alter-system-authorization\/\",\"url\":\"https:\/\/dbtut.com\/index.php\/2018\/12\/18\/how-to-kill-session-without-alter-system-authorization\/\",\"name\":\"How To Kill Session Without ALTER SYSTEM Authorization - Database Tutorials\",\"isPartOf\":{\"@id\":\"https:\/\/dbtut.com\/#website\"},\"datePublished\":\"2018-12-18T12:57:35+00:00\",\"description\":\"How To Grant Kill Session Right Without ALTER SYSTEM Authorization\",\"breadcrumb\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/12\/18\/how-to-kill-session-without-alter-system-authorization\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/dbtut.com\/index.php\/2018\/12\/18\/how-to-kill-session-without-alter-system-authorization\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/12\/18\/how-to-kill-session-without-alter-system-authorization\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/dbtut.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How To Kill Session Without ALTER SYSTEM Authorization\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/dbtut.com\/#website\",\"url\":\"https:\/\/dbtut.com\/\",\"name\":\"Database Tutorials\",\"description\":\"MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux\",\"publisher\":{\"@id\":\"https:\/\/dbtut.com\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/dbtut.com\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/dbtut.com\/#organization\",\"name\":\"dbtut\",\"url\":\"https:\/\/dbtut.com\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg\",\"contentUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg\",\"width\":223,\"height\":36,\"caption\":\"dbtut\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408\",\"name\":\"dbtut\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g\",\"caption\":\"dbtut\"},\"description\":\"We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.\",\"sameAs\":[\"http:\/\/NurullahCAKIR\"],\"url\":\"https:\/\/dbtut.com\/index.php\/author\/dbtut\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"How To Kill Session Without ALTER SYSTEM Authorization - Database Tutorials","description":"How To Grant Kill Session Right Without ALTER SYSTEM Authorization","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/dbtut.com\/index.php\/2018\/12\/18\/how-to-kill-session-without-alter-system-authorization\/","og_locale":"en_US","og_type":"article","og_title":"How To Kill Session Without ALTER SYSTEM Authorization - Database Tutorials","og_description":"How To Grant Kill Session Right Without ALTER SYSTEM Authorization","og_url":"https:\/\/dbtut.com\/index.php\/2018\/12\/18\/how-to-kill-session-without-alter-system-authorization\/","og_site_name":"Database Tutorials","article_published_time":"2018-12-18T12:57:35+00:00","author":"dbtut","twitter_card":"summary_large_image","twitter_misc":{"Written by":"dbtut","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/dbtut.com\/index.php\/2018\/12\/18\/how-to-kill-session-without-alter-system-authorization\/#article","isPartOf":{"@id":"https:\/\/dbtut.com\/index.php\/2018\/12\/18\/how-to-kill-session-without-alter-system-authorization\/"},"author":{"name":"dbtut","@id":"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408"},"headline":"How To Kill Session Without ALTER SYSTEM Authorization","datePublished":"2018-12-18T12:57:35+00:00","mainEntityOfPage":{"@id":"https:\/\/dbtut.com\/index.php\/2018\/12\/18\/how-to-kill-session-without-alter-system-authorization\/"},"wordCount":102,"commentCount":0,"publisher":{"@id":"https:\/\/dbtut.com\/#organization"},"articleSection":["ORACLE"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/dbtut.com\/index.php\/2018\/12\/18\/how-to-kill-session-without-alter-system-authorization\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/dbtut.com\/index.php\/2018\/12\/18\/how-to-kill-session-without-alter-system-authorization\/","url":"https:\/\/dbtut.com\/index.php\/2018\/12\/18\/how-to-kill-session-without-alter-system-authorization\/","name":"How To Kill Session Without ALTER SYSTEM Authorization - Database Tutorials","isPartOf":{"@id":"https:\/\/dbtut.com\/#website"},"datePublished":"2018-12-18T12:57:35+00:00","description":"How To Grant Kill Session Right Without ALTER SYSTEM Authorization","breadcrumb":{"@id":"https:\/\/dbtut.com\/index.php\/2018\/12\/18\/how-to-kill-session-without-alter-system-authorization\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/dbtut.com\/index.php\/2018\/12\/18\/how-to-kill-session-without-alter-system-authorization\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/dbtut.com\/index.php\/2018\/12\/18\/how-to-kill-session-without-alter-system-authorization\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/dbtut.com\/"},{"@type":"ListItem","position":2,"name":"How To Kill Session Without ALTER SYSTEM Authorization"}]},{"@type":"WebSite","@id":"https:\/\/dbtut.com\/#website","url":"https:\/\/dbtut.com\/","name":"Database Tutorials","description":"MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux","publisher":{"@id":"https:\/\/dbtut.com\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/dbtut.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/dbtut.com\/#organization","name":"dbtut","url":"https:\/\/dbtut.com\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/#\/schema\/logo\/image\/","url":"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg","contentUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg","width":223,"height":36,"caption":"dbtut"},"image":{"@id":"https:\/\/dbtut.com\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408","name":"dbtut","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g","caption":"dbtut"},"description":"We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.","sameAs":["http:\/\/NurullahCAKIR"],"url":"https:\/\/dbtut.com\/index.php\/author\/dbtut\/"}]}},"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts\/6703","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/comments?post=6703"}],"version-history":[{"count":0,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts\/6703\/revisions"}],"wp:attachment":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/media?parent=6703"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/categories?post=6703"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/tags?post=6703"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}