Generating Data to UI mappings in Siebel

April 20, 2014 Leave a comment

This is one of those tasks which is fairly simple to do. However, can be very time consuming considering you have to generate a mapping for an entire/multiple repositories. We’ve all have had to do this at some point, not enjoying it one bit!

Well, here is a code that will save you some time and your sanity :).

The below code generates a screen to Applet, and an Applet to BC mapping which can be then exported to excel.

Screen to Applet - 

select scr.name “Screen Name”
,nvl(nvl(ptabi.tab_text, scri.viewbar_text), scr.viewbar_text) “Screen”
,scrv.sequence “View Seq”
,vw.name “View Name”
,vwi.title “View”
,vw.busobj_name “Business Object”
,vwti.item_num “Item Num”
,ap.name “Applet Name”
,api.title “Applet”
,ap.buscomp_name “Business Component”
from   siebel.s_repository rep
inner join siebel.s_screen scr on scr.repository_id = rep.row_id
left outer join siebel.s_screen_intl scri on scri.screen_id = scr.row_id and scri.repository_id = rep.row_id and scri.name = ‘ENU-STD’
inner join siebel.s_screen_view scrv on scrv.screen_id = scr.row_id and scrv.repository_id = rep.row_id
inner join siebel.s_application appl on rep.row_id = appl.repository_id
left outer join siebel.s_page_tab ptab on ptab.application_id = appl.row_id and ptab.repository_id = rep.row_id and ptab.screen_name = scr.name
left outer join siebel.s_page_tab_intl ptabi on ptabi.page_tab_id = ptab.row_id and ptabi.repository_id = rep.row_id and ptabi.name = ‘ENU-STD’
inner join siebel.s_view vw on vw.name = scrv.view_name and vw.repository_id = rep.row_id
left outer join siebel.s_view_intl vwi on vwi.view_id = vw.row_id and vwi.repository_id = rep.row_id and vwi.name = ‘ENU-STD’
inner join siebel.s_view_web_tmpl vwt on vwt.view_id = vw.row_id and vwt.repository_id = rep.row_id
left outer join siebel.s_view_wtmpl_it vwti on vwti.view_web_tmpl_id = vwt.row_id and vwti.repository_id = rep.row_id
inner join siebel.s_applet ap on ap.name = vwti.applet_name and ap.repository_id = rep.row_id
left outer join siebel.s_applet_intl api on api.applet_id = ap.row_id and api.repository_id = rep.row_id and api.name = ‘ENU-STD’
where  rep.name = ‘Siebel Repository’
and    appl.name = ‘Siebel Power Communications’
and    nvl(rep.inactive_flg,’N’) = ‘N’
and    nvl(scr.inactive_flg,’N’) = ‘N’
and    nvl(scri.inactive_flg,’N’) = ‘N’
and    nvl(scrv.inactive_flg,’N’) = ‘N’
and    nvl(vw.inactive_flg,’N’) = ‘N’
and    nvl(vwi.inactive_flg,’N’) = ‘N’
and    nvl(vwt.inactive_flg,’N’) = ‘N’
and    nvl(vwti.inactive_flg,’N’) = ‘N’
and    nvl(ap.inactive_flg,’N’) = ‘N’
and    nvl(api.inactive_flg,’N’) = ‘N’
union
select scr.name “Screen Name”
,nvl(nvl(ptabi.tab_text, scri.viewbar_text), scr.viewbar_text) “Screen”
,scrv.sequence “View Seq”
,vw.name “View Name”
,vwi.title “View”
,vw.busobj_name “Business Object”
,vwti.item_num “Item Num”
,apta.name “Applet Name”
,api.title “Applet”
,apta.buscomp_name “Business Component”
from   siebel.s_repository rep
inner join siebel.s_screen scr on scr.repository_id = rep.row_id
left outer join siebel.s_screen_intl scri on scri.screen_id = scr.row_id and scri.repository_id = rep.row_id and scri.name = ‘ENU-STD’
inner join siebel.s_screen_view scrv on scrv.screen_id = scr.row_id and scrv.repository_id = rep.row_id
inner join siebel.s_application appl on rep.row_id = appl.repository_id
left outer join siebel.s_page_tab ptab on ptab.application_id = appl.row_id and ptab.repository_id = rep.row_id and ptab.screen_name = scr.name
left outer join siebel.s_page_tab_intl ptabi on ptabi.page_tab_id = ptab.row_id and ptabi.repository_id = rep.row_id and ptabi.name = ‘ENU-STD’
inner join siebel.s_view vw on vw.name = scrv.view_name and vw.repository_id = rep.row_id
left outer join siebel.s_view_intl vwi on vwi.view_id = vw.row_id and vwi.repository_id = rep.row_id and vwi.name = ‘ENU-STD’
inner join siebel.s_view_web_tmpl vwt on vwt.view_id = vw.row_id and vwt.repository_id = rep.row_id
left outer join siebel.s_view_wtmpl_it vwti on vwti.view_web_tmpl_id = vwt.row_id and vwti.repository_id = rep.row_id
inner join siebel.s_applet ap on ap.name = vwti.applet_name and ap.repository_id = rep.row_id
inner join siebel.s_applet_toggle apt on apt.applet_id = ap.row_id and apt.repository_id = rep.row_id
inner join siebel.s_applet apta on apta.name = apt.applet_name and apta.repository_id = rep.row_id
left outer join siebel.s_applet_intl api on api.applet_id = apta.row_id and apta.repository_id = rep.row_id and api.name = ‘ENU-STD’
where  rep.name = ‘Siebel Repository’
and    appl.name = ‘Siebel Power Communications’
and    nvl(rep.inactive_flg,’N’) = ‘N’
and    nvl(scr.inactive_flg,’N’) = ‘N’
and    nvl(scri.inactive_flg,’N’) = ‘N’
and    nvl(scrv.inactive_flg,’N’) = ‘N’
and    nvl(vw.inactive_flg,’N’) = ‘N’
and    nvl(vwi.inactive_flg,’N’) = ‘N’
and    nvl(vwt.inactive_flg,’N’) = ‘N’
and    nvl(vwti.inactive_flg,’N’) = ‘N’
and    nvl(ap.inactive_flg,’N’) = ‘N’
and    nvl(api.inactive_flg,’N’) = ‘N’
order by “Screen”
,”View Seq”
,”View Name”
,”Item Num”
,”Applet Name”

Output looks like -

Applet to BC mapping -

select  “Applet Name”
,”BC Name”
,”BC Field”
,”Required”
,”Calculated”
,”Calculated Value”
,”Join Name”
,”Table”
,”Column”
,”Data Type”
,”Length”
,”Multi-valued”
,”MV Link”
,”Pick List”
,”LOV Name”
,min(“Caption”) “Caption”
,”Display Order”
from (
select ap.name “Applet Name”
,bc.name “BC Name”
,fld.name “BC Field”
,fld.required “Required”
,fld.calculated “Calculated”
,fld.calcval “Calculated Value”
,fld.join_name “Join Name”
,(case when fld.mvlink_name is null then nvl(nvl(jotab.name, fld.join_name), case when fld.calculated = ‘Y’ then null else bc.table_name end) else null end) “Table”
,fld.col_name “Column”
,fld.type “Data Type”
,(case when fld.prec_num is null then to_char(fld.textlen)
else to_char(fld.prec_num) || to_char(case when fld.scale is null or fld.scale = 0 then ” else ‘,’ || fld.scale end)
end) “Length”
,fld.multi_valued “Multi-valued”
,fld.mvlink_name “MV Link”
,pl.name “Pick List”
,pl.type_value “LOV Name”
,coi.caption “Caption”
,co.sequence “Display Order”
from   siebel.s_control co
inner join siebel.s_control_intl coi on coi.control_id = co.row_id and coi.name = ‘ENU-STD’
inner join siebel.s_applet ap on co.applet_id = ap.row_id
inner join siebel.s_buscomp bc on ap.buscomp_name = bc.name
inner join siebel.s_field fld on fld.name = co.field_name and fld.buscomp_id = bc.row_id
inner join siebel.s_repository rep on bc.repository_id = rep.row_id
left outer join siebel.s_join jo on jo.buscomp_id = fld.buscomp_id and fld.join_name = jo.name
left outer join siebel.s_table jotab on jotab.name = jo.dest_tbl_name and jotab.repository_id = rep.row_id
left outer join siebel.s_picklist pl on fld.picklist_name = pl.name and pl.repository_id = rep.row_id
where  rep.name = ‘Siebel Repository’
and    ap.repository_id = rep.row_id
and    co.repository_id = rep.row_id
and    bc.repository_id = rep.row_id
and    fld.repository_id = rep.row_id
and    nvl(co.inactive_flg,’N’) = ‘N’
and    nvl(ap.inactive_flg,’N’) = ‘N’
and    nvl(bc.inactive_flg,’N’) = ‘N’
and    nvl(fld.inactive_flg,’N’) = ‘N’
and    nvl(rep.inactive_flg,’N’) = ‘N’
and    nvl(jo.inactive_flg,’N’) = ‘N’
union all
select ap.name “Applet Name”
,bc.name “BC Name”
,fld.name “BC Field”
,fld.required “Required”
,fld.calculated “Calculated”
,fld.calcval “Calculated Value”
,fld.join_name “Join Name”
,(case when fld.mvlink_name is null then nvl(nvl(jotab.name, fld.join_name), case when fld.calculated = ‘Y’ then null else bc.table_name end) else null end) “Table”
,fld.col_name “Column”
,fld.type “Data Type”
,(case when fld.prec_num is null then to_char(fld.textlen)
else to_char(fld.prec_num) || to_char(case when fld.scale is null or fld.scale = 0 then ” else ‘,’ || fld.scale end)
end) “Length”
,fld.multi_valued “Multi-valued”
,fld.mvlink_name “MV Link”
,pl.name “Pick List”
,pl.type_value “LOV Name”
,coi.display_name “Caption”
,co.sequence “Display Order”
from   siebel.s_list li
inner join siebel.s_applet ap on li.applet_id = ap.row_id
inner join siebel.s_list_column co on co.list_id = li.row_id
left outer join siebel.s_list_col_intl coi on coi.list_column_id = co.row_id and coi.name = ‘ENU-STD’
inner join siebel.s_buscomp bc on ap.buscomp_name = bc.name
inner join siebel.s_field fld on fld.name = co.field_name and fld.buscomp_id = bc.row_id
inner join siebel.s_repository rep on bc.repository_id = rep.row_id
left outer join siebel.s_join jo on jo.buscomp_id = fld.buscomp_id and fld.join_name = jo.name
left outer join siebel.s_table jotab on jotab.name = jo.dest_tbl_name and jotab.repository_id = rep.row_id
left outer join siebel.s_picklist pl on fld.picklist_name = pl.name and pl.repository_id = rep.row_id
where  rep.name = ‘Siebel Repository’
and    li.repository_id = rep.row_id
and    ap.repository_id = rep.row_id
and    co.repository_id = rep.row_id
and    bc.repository_id = rep.row_id
and    fld.repository_id = rep.row_id
and    nvl(li.inactive_flg,’N’) = ‘N’
and    nvl(co.inactive_flg,’N’) = ‘N’
and    nvl(ap.inactive_flg,’N’) = ‘N’
and    nvl(bc.inactive_flg,’N’) = ‘N’
and    nvl(fld.inactive_flg,’N’) = ‘N’
and    nvl(rep.inactive_flg,’N’) = ‘N’
and    nvl(jo.inactive_flg,’N’) = ‘N’
)
group by  “Applet Name”
,”BC Name”
,”BC Field”
,”Required”
,”Calculated”
,”Calculated Value”
,”Join Name”
,”Table”
,”Column”
,”Data Type”
,”Length”
,”Multi-valued”
,”MV Link”
,”Pick List”
,”LOV Name”
,”Display Order”
order by “Applet Name”
,”BC Name”
,”MV Link” desc
,”Table”
,”Display Order”
Output looks like -

So there you go…you could later consolidate both to have a full UI to Data level mapping.

Categories: Siebel Tags: ,

What Administrators Should Do for Siebel Reporting with BI Publisher?

April 18, 2014 Leave a comment

There are a couple of things you want to perform as Administrators to manage the Siebel Reporting environment and maintain the BI Publisher reports in Siebel. Here is a list of the tasks that the Administrators should be responsible for.

  • Purging Siebel Reports
  • Optimize Siebel Reporting Environment
  • Enabling Logging for Debugging

Purging Siebel Reports

When the users run the Siebel reports all the generated reports output files will be stored on the Siebel database. These files can be opened from the ‘My BI Publisher Reports’ menu. However, at a certain point of time you might want to delete some of the files due to the size of keeping all the reports on the file system and also due to a compliance matter.

You as Siebel Administrator can purge such report files from the Siebel database using filters or by running a workflow process. I’m going to talk about how to set up reports to be automatically or manually purged.

Automatic Purge

You can set up to automatically purge your reports from the Siebel database after a specified time interval. The ‘BIP Delete After Days’ system preference allows you to specify any non-zero positive value that executes the Auto Purge workflow to purge the reports. Based on the value that you enter, the reports are purged from the database after the number of days specified.

To automatically purge reports

  1. Log in to the Siebel application with system administrator privileges.
  2. Navigate to the Administration – Application screen, then System Preferences view.
  3. In the System Preferences list, select ‘BIP Delete After Days’, and change the value to any positive nonzero value.
    By default, the value is set to -1 (minus 1), which means it never purge.

    BIP_Delete_After_Days

  4. Navigate to the Administration – Server Management screen, then Jobs view.
  5. Add a new job
  6. Click the search icon to select ‘Workflow Process Manager’
  7. Click ‘New’ button to add a Job Parameter to the job
  8. Select Workflow Process Name for the name
  9. Type ‘XMLP Purge Records’ as the value
  10. Click Submit.

Alternatively you can schedule this job to run periodically using the Siebel workflow, see Siebel Business Process Framework: Workflow Guide for the detail.

Manual Purge

When manually purging Siebel reports, you can specify criteria by which the reports are purged. Reports meeting that criteria will be removed from the Siebel database. You can also purge multiple reports together by selecting a date range, the reports that were generated within the specified date range will be purged. If both the report name and the date range are entered, then only those reports with that name and that were generated within that specified date range are purged. You can also purge reports for a specific user by entering his/her user ID as the criteria.

To manually purge a report:

  1. Log in to the Siebel application with system administrator privileges.
  2. Navigate to the Administration – BIP Reports screen, then Purge Administration view.

    Purge_Admin

  3. In the Purge Administration form, you can select a report name, type From Date, To Date, and select User ID. You don’t need to fill all of them. Whatever you enter will be used as a criteria to select the reports to be purged.
  4. Click Run.
    The reports that meet the specified criteria are purged.

Optimizing Performance for Siebel Reports

Now the next topic is to optimize the Siebel Reports runtime environment to have better performance and scalability. There are several attributes that you can use to best fit to your reporting and system requirements from performance and scalability perspective.

Here is a list of the attributes.

  • Report Execution Wait Time
  • Server Request Processor Wait Time
  • Setting Concurrency Parameters
  • Max Fetch Size (DSMaxFetchArraySize) for Large Data Volumes
  • Enabling Scalable Mode
Execution Wait Time

By setting this threshold value you can cancel some of the reports, which would take longer than the specified time duration, to run. In some cases some reports might take a long time and when you have multiple reports taking long time at the same time other users might have to wait for those reports to finish the jobs. Typically such long running reports should be executed at when most of the users are not accessing such as at night time as a batch process, but there is no control to prevent the users to run such reports as long as they have access to them. So you can use this attribute to control such.

Here is a list of the steps to set the Report Execution Wait Time for Siebel Reports

  1. Navigate to the Administration – Application screen, then System Preferences view.
  2. In the System Preferences list, select BIP Report Wait Time, and then change the value to any number greater than 100. By default, the threshold is set to 100 seconds.
Setting the Server Request Processor Wait Time for Siebel Reports

Some times your database doesn’t respond due to heavy duty work currently being performed at the database or simply not available. Or there is a quick temporary issue on the network. Whatever the reason your report request trying to access to the database might not getting any response back. Typically the applications try to reconnect to the database after a certain waiting time. You can control this waiting time with this setting.

Follow the steps below to set the Server Request Processor Wait Time for Siebel Reports

  1. Navigate to the Administration – Server Configuration screen,  Servers, and then Components view.
  2. In the Components list, select Server Request Processor (alias SRProc).
  3. Scroll down and click the Parameters subview, and then click Hidden.
  4. In the Parameter list, select Database Polling Interval, and change value from 10 to 1. The Value on Restart and Default Values are updated as well.
  5. Restart the Siebel Server.
Setting Concurrency Parameters for Siebel Reports

You can set a maximum number of tasks per XMLP Report Server, which is not the BI Publisher Enterprise but it’s the engine that takes care of the report request and response within the Siebel, per a single Siebel server. Also you can set a maximum number of MT (Multi Threaded) Servers per a single Siebel server.

Typical guideline would be:

  • MaxTasks = peak (concurrent users)
  • MaxMTServers = MaxTasks / 100

So, for example, let’s say you have 1000 users accessing to the reporting at the same time then the MaxTasks would be 1000 and the MaxMTServers would be 10. Note that this is a generic guide line and these numbers should not be used as a static number for your environment. Each environment and reporting needs would impact on the above numbers and requires a series of performance testing to come up with the best numbers.

You can follow the steps below to set concurrency parameters for using the GUI

  1. Log in to the Siebel application with administrator privileges.
  2. Navigate to the Administration – Server Configuration screen, Servers, and then Components view.
  3. In the Components list, select XMLP Report Server.
  4. Click on the Parameters view tab and set accordingly.
Optimizing Performance of Siebel Reports for Large Data Volumes

When you have a large data coming back for your reports then you can set a Data Source maximum fetch array size (DSMaxFetchArraySize) profile parameter value to ‘-1’ so that the size is unlimited. But again, this value should be fitting to your specific environment so you might want to increase the value to a certain threshold instead of setting it as unlimited.

You can follow the steps below to optimize performance of Siebel Reports for large data volumes

  1. Navigate to the Administration – Server Configuration screen, Enterprises, and then Profile Configuration view.
  2. In the Profile Configuration view list, select Server Datasource (alias ServerDataSrc).
  3. Scroll down to the Profile Parameters, and then click Advanced.
  4. In the Profile Parameters list, select Datasource maximum fetch array size (alias DSMaxFetchArraySize), and then change the value to -1.
  5. Restart the Siebel Server.
Enabling Scalable Mode for Siebel Reports

When you have a large data volume or have complex XSL transformation logic you might want to enable BI Publisher’s XSLT engine to be scalable so that the engine will transform the raw data to XSL-FO template and generate the final output by using the hard disk instead of loading the file onto the memory space. In this way you can avoid ‘out-of-memory’ related issue at the BI Publisher’s reports generation process. You can enable the scalable mode parameter by configuring the BI Publisher’s configuration file, xdo.cfg file, which is located under the %JRE_HOME%\lib directory, where JRE_HOME is the one used for BI Publisher Enterprise Server.

To enable scalable mode for Siebel Reports

  1. Navigate to the jre\lib installation directory.
    NOTE: The path for the Java installation folder varies depending on where you installed JRE.
  2. Open the xdo.cfg file, and in the <Properties></Properties> tag, use the following syntax to set the Scalable Mode property to true (if not already set):
    <property name=”xslt-scalable”>true</property>
    NOTE: You can set Scalable mode to either true or false.
  3. Save the xdo.cfg file.
  4. Restart BI Publisher Server

Next

There are a few other critical things you can do as an Administrator such as Migrating the reports from Development environment to QA/Production environment, Enabling Logging for Debugging.

Categories: Oracle, Siebel Tags: ,

How to lock/unlock statistics on a table?

April 10, 2014 Leave a comment

In certain cases you may want to lock statistics in a table in certain cases, for example if you want a table not be analyzed by automatic statistics job but analyze it later or in cases where you want prevent from analyzing statistics in cases where data in the table doesn’t change.


-How to find table where statistics are locked.

select owner, table_name, stattype_locked from dba_tab_statistics where stattype_locked is not null; 

– unlock statistics
SQL> exec dbms_stats.unlock_table_stats(‘<schema>’, ‘<Table>’);
– To gather statistics on a table
SQL> exec dbms_stats.gather_index_stats(‘<schema>’, ‘<Table>’);
–To Lock statistics
exec dbms_stats.lock_table_stats(‘<schema>’, ‘<Table>’);
 
 
– shows when stats is locked the value of stattype_locked is ALL

SQL> SELECT stattype_locked FROM dba_tab_statistics WHERE table_name = ‘<table_name>’ and owner = ‘<Schema>’;

Find blocking sessions

April 10, 2014 Leave a comment

Problem – find blocking sessions

Blocking sessions occur when one sessions holds an exclusive lock on an object and doesn’t release it before another sessions wants to update the same data. This will block the second until the first one has done its work.

From the view of the user it will look like the application completely hangs while waiting for the first session to release its lock. You’ll often have to identify these sessions in order to improve your application to avoid as many blocking sessions as possible.

Recipie #1 – find blocking sessions with v$session

SELECT
   s.blocking_session, 
   s.sid, 
   s.serial#, 
   s.seconds_in_wait
FROM
   v$session s
WHERE
   blocking_session IS NOT NULL

Recipie #2 – find blocking sessions using v$lock

SELECT 
   l1.sid || ' is blocking ' || l2.sid blocking_sessions
FROM 
   v$lock l1, v$lock l2
WHERE
   l1.block = 1 AND
   l2.request > 0 AND
   l1.id1 = l2.id1 AND
   l1.id2 = l2.id2

Recipie #3 – blocking sessions with all available information

The next query prints a few more information, it let’s you quickly see who’s blocking who. Run this query and you can immediately call the colleague who’s locking your table:

SELECT s1.username || '@' || s1.machine
    || ' ( SID=' || s1.sid || ' )  is blocking '
    || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
    FROM v$lock l1, v$session s1, v$lock l2, v$session s2
    WHERE s1.sid=l1.sid AND s2.sid=l2.sid
    AND l1.BLOCK=1 AND l2.request > 0
    AND l1.id1 = l2.id1
    AND l2.id2 = l2.id2 ;

Recipie #4 – identifying blocked objects

The view v$lock we’ve already used in the queries above exposes even more information. There are differnet kind of locks – check this site for a complete list: http://download.oracle.com/docs/cd/B13789_01/server.101/b10755/dynviews_1123.htm#sthref3198

If you encounter a TM lock is means that two sessions are trying to modify some data but blocking each other. Unless one sessions finished (commit or rollback), you’ll never have to wait forever.

The following queries shows you all the TM locks:

SELECT sid, id1 FROM v$lock WHERE TYPE='TM'
SID ID1
92 20127
51 20127

The ID you get from this query refers to the actual database object which can help you to identify the problem, look at the next query:

SELECT object_name FROM dba_objects WHERE object_id=20127

March 27, 2014 Leave a comment

Killing Oracle Sessions

There are a number of ways to kill rogue sessions both within Oracle and externally.

  • Identify the Session to be Killed
  • ALTER SYSTEM KILL SESSION
  • ALTER SYSTEM DISCONNECT SESSION
  • The Windows Approach
  • The UNIX Approach

Identify the Session to be Killed

Killing sessions can be very destructive if you kill the wrong session, so be very careful when identifying the session to be killed. If you kill a session belonging to a background process you will cause an instance crash.

Identify the offending session using the [G]V$SESSION and [G]V$PROCESS views as follows.

SET LINESIZE 100
COLUMN spid FORMAT A10
COLUMN username FORMAT A10
COLUMN program FORMAT A45

SELECT s.inst_id,
       s.sid,
       s.serial#,
       p.spid,
       s.username,
       s.program
FROM   gv$session s
       JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE  s.type != 'BACKGROUND';

   INST_ID        SID    SERIAL# SPID       USERNAME   PROGRAM
---------- ---------- ---------- ---------- ---------- ---------------------------------------------
         1         30         15 3859       TEST       sqlplus@oel5-11gr2.localdomain (TNS V1-V3)
         1         23        287 3834       SYS        sqlplus@oel5-11gr2.localdomain (TNS V1-V3)
         1         40        387 4663                  oracle@oel5-11gr2.localdomain (J000)
         1         38        125 4665                  oracle@oel5-11gr2.localdomain (J001)

SQL>

The SID and SERIAL# values of the relevant session can then be substituted into the commands in the following sections.

ALTER SYSTEM KILL SESSION

The basic syntax for killing a session is shown below.

SQL> ALTER SYSTEM KILL SESSION 'sid,serial#';

In a RAC environment, you optionally specify the INST_ID, shown when querying the GV$SESSION view. This allows you to kill a session on different RAC node.

SQL> ALTER SYSTEM KILL SESSION 'sid,serial#,@inst_id';

The KILL SESSION command doesn’t actually kill the session. It merely asks the session to kill itself. In some situations, like waiting for a reply from a remote database or rolling back transactions, the session will not kill itself immediately and will wait for the current operation to complete. In these cases the session will have a status of “marked for kill”. It will then be killed as soon as possible.

In addition to the syntax described above, you can add the IMMEDIATE clause.

SQL> ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

This does not affect the work performed by the command, but it returns control back to the current session immediately, rather than waiting for confirmation of the kill.

If the marked session persists for some time you may consider killing the process at the operating system level. Before doing this it’s worth checking to see if it is performing a rollback. You can do this by running this script (session_undo.sql). If the USED_UREC value is decreasing for the session in question you should leave it to complete the rollback rather than killing the session at the operating system level.

ALTER SYSTEM DISCONNECT SESSION

The ALTER SYSTEM DISCONNECT SESSION syntax is an alternative method for killing Oracle sessions. Unlike the KILL SESSION command which asks the session to kill itself, the DISCONNECT SESSION command kills the dedicated server process (or virtual circuit when using Shared Sever), which is equivalent to killing the server process from the operating system. The basic syntax is similar to the KILL SESSION command with the addition of the POST_TRANSACTION clause. The SID and SERIAL# values of the relevant session can be substituted into one of the following statements.

SQL> ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' POST_TRANSACTION;
SQL> ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' IMMEDIATE;

The POST_TRANSACTION clause waits for ongoing transactions to complete before disconnecting the session, while the IMMEDIATE clause disconnects the session and ongoing transactions are rolled back immediately.

The POST_TRANSACTION and IMMEDIATE clauses can be used together, but the documentation states that in this case the IMMEDIATE clause is ignored. In addition, the syntax diagram suggests both clauses are optional, but in reality, one or both must be specified or you receive an error.

SQL> alter system disconnect session '30,7';
alter system disconnect session '30,7'
                                     *
ERROR at line 1:
ORA-02000: missing POST_TRANSACTION or IMMEDIATE keyword

SQL>

This command means you should never need to switch to the operating system to kill sessions, which reduces the chances of killing the wrong process.

The Windows Approach

To kill the session on the Windows operating system, first identify the session, then substitute the relevant SID and SPID values into the following command issued from the command line.

C:> orakill ORACLE_SID spid

The session thread should be killed immediately and all resources released.

The UNIX Approach

Warning: If you are using the Multithreaded Model in Oracle 12c, you should not attempt to kill operating system processes. To know why, read this.

To kill the session on UNIX or Linux operating systems, first identify the session, then substitute the relevant SPID into the following command.

% kill spid

If after a few minutes the process hasn’t stopped, terminate the session using the following.

% kill -9 spid

If in doubt check that the SPID matches the UNIX PROCESSID shown using.

% ps -ef | grep ora

The session thread should be killed immediately and all resources released.

Follow

Get every new post delivered to your Inbox.

Join 527 other followers