Oracle Kill/Terminate Session: Example

 Introduction:

When a SQL session is hanging for a long time, it make sense to terminate/kill the session. This is to improve developer time as well as to free up system resources in server side.

Here are some scenarios where KILL SESSION is useful:

1. System is in deadlock
2. While testing a query, developer missed a join, which makes internal query processing prohibitively expensive
3. Some sessions are taking up a lot of resources and important processes or users get very slow response

 

Steps to Kill Session:

Find the Problem Session:

Identify the session that needs to be killed. Easiest way is to get the user by Database Schema/User name. But it is possible that a single DB user may have many sessions open due to multiple reasons. In that case, finding session is tricky. Use any of the WHERE clauses in the below query to identify the session.

 

-- get session by User/Schema
SELECT SID, SERIAL# FROM V$SESSION WHERE USERNAME = 'BAI';
 
-- Get session by other parameters/inputs
SELECT SID || ',' || SERIAL# AS KILLSTR, SID, SERIAL#, X.*
FROM V$SESSION X
WHERE USERNAME = 'APPS'
AND MACHINE NOT LIKE 'XXABCWEB%' -- REMOVE WEB SERVER TO DB Server Connection
AND OSUSER NOT IN ('APPLPRD') -- REMOVE UNIX AND APPLN SERVER USERS
AND PROGRAM IN ('TOAD.EXE', 'SQL DEVELOPER', 'SQLPLUS.EXE') -- FILTER BY SQL Client Program
AND ROWNUM < 21;

 

Kill/Terminate the Problem Session:

Use inputs from above query in the KILL command

ALTER SYSTEM KILL SESSION '<SID>,<SERIAL>' IMMEDIATE;
 
ALTER SYSTEM KILL SESSION '1794,64680' IMMEDIATE
/
 

 

Keywords:

Kill Session, Terminate, Oracle, ALTER SESSION, PL/SQL, Query, SQL, SQLPLUS

Oracle Date Conversion Functions : Random Examples

 

Introduction:

Some random examples of Oracle PL/SQL Date Conversion functions. Might be useful for some and thought it worth sharing.

 

Examples:

Get current system date in default format (Easy !):

select sysdate from dual;

  

Get current system date in different formats:

select 'x ' || to_char(sysdate, 'yyyymmdd') from dual;
 
select 'x ' || to_char(sysdate, 'yyyymmdd hh24miss') from dual;
 
select 'x ' || to_char(sysdate, 'hh24miss') from dual;

 

Number of seconds since beginning of the day:

-- number of seconds since beginning of the day
select 'x ' || to_char(sysdate, 'sssss') from dual;

  

Number of days since beginning of the year:

-- number of days since beginning of year
select 'x ' || to_char(sysdate, 'ddd') from dual;
 

Get System Timestamp:

select systimestamp from dual;

This will return time up to 6 decimal places. Very useful for performance tuning/improvement as the time is recorded with microsecond accuracy.

Few other Timestamp functions/examples:

select 'x ' || to_char(systimestamp, 'dd-mon-yyyy hh.mi.ss.ff4') from dual;
select 'x ' || to_char(systimestamp, 'yyyymmdd hhmissff4') from dual;
 
select 'x ' || to_char(systimestamp, 'yyyymmdd hhmissff6') from dual;
select 'x ' || to_char(systimestamp, 'hhmissff6') from dual;
select 'x ' || to_char(systimestamp, 'missff6') from dual;
 

Keywords:

Date Conversion, Format, Timestamp, Systimestamp, Date Function, Oracle, PL/SQL, Query, SQL, SQLPLUS

RegEx / Regular Expression: Folder Structure: Get File Name

 

Problem:

You have a list of file names in different directories. You need to get just the file name, stripping directory names.

Here is an example input:

/appsR122/ABCDEV/fs1/FMW_Home/webtier/instances/EBS_web_OHS1/diagnostics/logs/OHS/EBS_web/EBS_web.log
/appsR122/ABCDEV/fs1/FMW_Home/user_projects/domains/EBS_domain/servers/oacore_server1/logs/access.log
/appsR122/ABCDEV/fs1/FMW_Home/user_projects/domains/EBS_domain/servers/oacore_server1/logs/oacore_server1.log
/appsR122/ABCDEV/fs1/FMW_Home/user_projects/domains/EBS_domain/servers/forms_server1/logs/access.log
/appsR122/ABCDEV/fs1/FMW_Home/user_projects/domains/EBS_domain/servers/forms_server1/logs/forms_server1.log
/appsR122/ABCDEV/fs1/FMW_Home/user_projects/domains/EBS_domain/servers/oacore_server2/logs/access.log
/appsR122/ABCDEV/fs1/FMW_Home/user_projects/domains/EBS_domain/servers/oacore_server2/logs/oacore_server2.log
/appsR122/ABCDEV/fs1/FMW_Home/user_projects/domains/EBS_domain/servers/forms_server2/logs/access.log
/appsR122/ABCDEV/fs1/FMW_Home/user_projects/domains/EBS_domain/servers/forms_server2/logs/forms_server2.log
/appsR122/ABCDEV/fs1/FMW_Home/user_projects/domains/EBS_domain/servers/AdminServer/logs/AdminServer.log
/appsR122/ABCDEV/fs1/FMW_Home/user_projects/domains/EBS_domain/servers/AdminServer/logs/AdminServer-diagnostic.log
/appsR122/ABCDEV/fs1/FMW_Home/user_projects/domains/EBS_domain/servers/oafm_server1/logs/oafm_server1.log
/appsR122/ABCDEV/fs1/FMW_Home/user_projects/domains/EBS_domain/aferror.log
/appsR122/ABCDEV/fs1/inst/apps/ABCDEV_hdctabcdevap/logs/appl/rgf/javacache.log
/appsR122/ABCDEV/fs1/inst/apps/ABCDEV_hdctabcdevap/logs/10890.dispatcher.log

 

Expected Output:

Just names of the files:

EBS_web.log
access.log
oacore_server1.log
access.log
forms_server1.log
access.log
oacore_server2.log
access.log
forms_server2.log
AdminServer.log
AdminServer-diagnostic.log
oafm_server1.log
aferror.log
javacache.log
10890.dispatcher.log
 

 

Query:

Use Regular Expression to strip out first part and keep only delimiter:

SELECT REGEXP_SUBSTR(<Input String>, '[^/]+$', 1, 1)
FROM DUAL;
 

Eg:

SELECT REGEXP_SUBSTR('/appsR122/ABCDEV/fs1/FMW_Home/user_projects/domains/EBS_domain/servers/oacore_server1/logs/access.log', '[^/]+$', 1, 1)
FROM DUAL;
 

This can be used to last part of any string, separated by a delimiter. Just substitute the second parameter ('[^/]+$') for the new delimiter.

 

 

Keywords:

RegEx, Regular Expression, REGEXP_SUBSTR, String Function, PL/SQL, Query, SQL, SQLPLUS

 

TCA Basic Queries: Country, State, Zip

 Introduction:

Basic SQLs to get list of Countries, States and Zips (Postal Codes) from Oracle EBS TCA (Trading Community Architecture) Setup.

 

SQLs:

List of Countries:

-- get all countries
select * from FND_TERRITORIES;
 

 

List of City / State / Zip Combinations:

-- get all state/zip/city combinations
SELECT GEOGRAPHY_ELEMENT4 CITY, GEOGRAPHY_ELEMENT3 COUNTY,
    GEOGRAPHY_ELEMENT2_CODE STATE, GEOGRAPHY_ELEMENT5 ZIP_CODE,
    GEOGRAPHY_ELEMENT1_CODE COUNTRY
FROM HZ_GEOGRAPHIES
WHERE GEOGRAPHY_ELEMENT5_ID = GEOGRAPHY_ID AND GEOGRAPHY_ELEMENT1_CODE = 'US';

 

Note: Based on Address Validation profile option, the system will validate and give error, if the City / State / Zip Combination mismatch while address input.

 

List of State Codes (Eg: AL, NY, MO, IL, etc):

-- get all state codes
SELECT DISTINCT GEOGRAPHY_ELEMENT2_CODE STATE
FROM HZ_GEOGRAPHIES
WHERE GEOGRAPHY_ELEMENT5_ID = GEOGRAPHY_ID AND GEOGRAPHY_ELEMENT1_CODE = 'US';

 

Keywords:

TCA, Customer Address, Bill To Address, Ship To Address, Trading Community Architecture, FND_TERRITORIES, HZ_GEOGRAPHIES, HZ_PARTIES, HZ_PARTY_SITES, HZ_LOCATIONS, EBS, R12, Query, SQL, SQLPLUS

 

Oracle PL/SQL – Create Temp Table (T0101) Complete Script

 

Business Need:

Have a basic local table in Oracle. Can be used for any logging or tracking of program execution by inserting data. The indexes and timesstamps are pre-populated by database triggers.

 

SQL Script (Execute one by one, not as a single script):

Create Table:

C1 – Index, populated by trigger

C2 – Main content, add from program

C3 – Timestamp, populated by trigger

C4 – SYSTIMESTAMP, required only for microsecond time (may be required in SQL Performance Optimization). Otherwise remove or do not use)

CREATE TABLE T0101 (
            C1 NUMBER,
            C2 VARCHAR2(4000),
            C3 DATE DEFAULT SYSDATE,
            C4 TIMESTAMP DEFAULT SYSTIMESTAMP);
 
 
CREATE PUBLIC SYNONYM T0101 FOR APPS.T0101;
 
COMMIT;

Create Sequence for Primary Key:

 
CREATE SEQUENCE T0101SEQ
  START WITH 1
  MAXVALUE 999999999999999999999999999
  MINVALUE 1;
 
 
COMMIT;

 

Create DB Trigger:

 
CREATE OR REPLACE TRIGGER T0101B4I
  BEFORE INSERT
ON T0101   FOR EACH ROW
DECLARE
    V_C1   NUMBER;
BEGIN
    SELECT T0101SEQ.NEXTVAL INTO V_C1 FROM DUAL;
 :NEW.C1 := V_C1;
EXCEPTION
    WHEN OTHERS THEN
      RAISE;
END T0101B4I;
/
 
 
GRANT ALTER, DELETE, INDEX, INSERT, REFERENCES, SELECT, UPDATE, ON COMMIT REFRESH, QUERY REWRITE, DEBUG, FLASHBACK ON  T0101 TO PUBLIC WITH GRANT OPTION;
 
 
COMMIT;
 

Example Insert/Select Scripts:

 
INSERT INTO T0101 (C2) VALUES ('TEST2');
COMMIT;
 
 
SELECT C1, C2, C3, C4 FROM T0101
WHERE C3 > SYSDATE-1
ORDER BY C1 DESC;
 
 
COMMIT;
 

Insert script from program:

 
INSERT INTO T0101 (C2) select 'SID:'||sys_context('userenv','sessionid')||';SoldTo:'||p_sold_to_org_id from dual;
 
 
CREATE OR REPLACE PROCEDURE t0101_log(V_STRING VARCHAR2) IS
   PRAGMA autonomous_transaction;
BEGIN
       INSERT INTO T0101 (C2) VALUES ('x'||V_STRING);
       COMMIT;
 
end t0101_log;
/
 
COMMIT;
 

Keywords:

Oracle EBS, R12, R12.2.8, Oracle Applications

XML, PL/SQL, Database, 19c, 12.1.0.0, SQL Plus, Parser, APEX JSON, Patch