Pages

Thursday, January 12, 2012

Killing a running query in Oracle

If you are working with Oracle using an IDE like SQL Developer or TOAD, sometimes it could happen that some of your past queries are still running and therefore blocking your current work.

In case, you can (using an admin account) access the Oracle system tables using something like this simple query to get your current running job list:

SELECT    p.spid   , q.sql_fulltext   , s.* FROM   v$session s INNER JOIN   v$session_wait wON    s.sid=w.sidLEFT OUTER JOIN   v$process pON    s.paddr=p.addrWHERE   s.schemaname='SCHEMANAME'AND   LOWER(s.osuser)='USERNAME';



Each query runs as at OS level a separate thread/process. Most of the Oracle installations runs over an Unix machine: in case, you can simple kill the corresponding process with the command:

# kill -9 PID_NUMBER



Obtaining the Job Hierarchy with SAP BusinessObjects

BusinessObjects Data Integrator is a well-known market-leading, enterprise-class data integration tool part of the SAP BusinessObjects suite.

Many enterprises prefer for its prebuilt data integration capabilities for SAP source-specific enterprise applications, as well as Siebel, PeopleSoft, and Oracle. Furthermore, not many other products provide deeper metadata-based integration with the market-leader ERPs.


The BusinessObjects 6.5 Data Integrator.

As every ETL tool and  in order to accelerate the BI deployment, BusinessObjects Data Integrator  shows an intuitive graphic environment for ETL mapping, for easily managing, documenting and validating each design phase of an ETL job.

If your ETL environment is simple enough you don't need real job scheduler, and you can simply rely on the Cron or the Windows Scheduler. But, for istance, if you need to manage any dependencies between jobs, you often have to develop your own scheduler and managing tool.

If your BusinessObjects metadata repository is Oracle-based, then you're lucky - you can rely on the full capabilities of Oracle PL/SQL language.

In this article is shown how to obtain the jobs hierarchy with a simple query over the BusinessObjects metadata repository. The example refers to the BusinessObjects v. 6.5 and Oracle 11g:

SELECT   parent_obj_type   , parent_obj   , parent_obj_desc   , descen_obj   , descen_obj_type   , descen_obj_desc   , LEVELFROM   schemaname.AL_PARENT_CHILDSTART WITH    descen_obj_type='TABLE'    AND descen_obj='CURRENT_STOCK_STATS'  CONNECT BY    descen_obj=PRIOR parent_job    AND descen_obj_type=PRIOR parent_obj_typeWHERE    parent_obj='JOB'    ;



In this query we made use of the 'START WITH... CONNECT_BY...' Oracle clause, that allows the usage of recursion in order to build the desired hierarchy.


The Oracle 'START WITH... CONNECT BY...' clause.

This construct can be used with any table containing hierarchical data, for example any kind of parent->child, boss->employee or thing->parts relationships.
The root is specified by the 'START WITH...' and the father-child relationship by the 'CONNECT BY' keywords.


For other information, see: