Oracle pagination query example

    select
        * 
    from
        ( select
            row_.*,
            rownum rownum_ 
        from
            ( 
YOUR SELECT STATEMENT WITH WHERE,GROUP,ORDER,AGGREGATES,ETC

 ) row_ ) 
        where
            rownum_ <= ? 
            and rownum_ > ?

Important Oracle queries

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';

ALTER SYSTEM KILL SESSION '8,11';


SELECT O.OBJECT_NAME, S.SID, S.SERIAL#, P.SPID, S.PROGRAM,S.USERNAME,
S.MACHINE,S.PORT , S.LOGON_TIME,SQ.SQL_FULLTEXT
FROM V$LOCKED_OBJECT L, DBA_OBJECTS O, V$SESSION S,
V$PROCESS P, V$SQL SQ
WHERE L.OBJECT_ID = O.OBJECT_ID
AND L.SESSION_ID = S.SID AND S.PADDR = P.ADDR
AND S.SQL_ADDRESS = SQ.ADDRESS;



  SET AUTOTRACE ON;
  SET TIMING ON;

  SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());
 
  explain plan FOR ...
 
 
 
   alter user e2etracking identified by e2etracking account unlock;

Using ComboPooledDataSource in Spring context

destroy-method="close">

jdbc.Driver=oracle.jdbc.OracleDriver

jdbc.url=jdbc:oracle:thin:@serverhost:1521:XE

jdbc.username=xxxxx

jdbc.password=xxxxxx

#jdbc.url=jdbc:oracle:thin:@serverhost:1521/SERVICE_NAME

jdbc.initialSize=2

jdbc.maxActive=5

jdbc.maxIdle=2

jdbc.minIdle=1

jdbc.validationQuery=select 1 from dual

jdbc.removeAbandoned=true

Oracle Group by a String column by string concatenation of its values

CREATE OR REPLACE VIEW SOWCUSTOMERPROJECTVIEW

AS

  SELECT OPPRTNTY.CUST_ACNT_NBR,

    OPPRTNTY.OPPRTNTY_ID,

    OPPRTNTY.EXT_OPPRTNTY_ID,

    OPPRTNTY.OPPRTNTY_NM,

    OPPRTNTY.OPPRTNTY_DSC,

    LISTAGG(PROJECT.PROJECT_NM, ', ') WITHIN GROUP (

  ORDER BY PROJECT.PROJECT_NM) AS SUB_PRODUCT,

    LISTAGG(BATCH_CLASS.BATCH_CLASS_NM, ', ') WITHIN GROUP (

  ORDER BY BATCH_CLASS.BATCH_CLASS_NM) AS SUB_PRODUCT2,

    --PROJECT.PROJECT_NM,

    --BATCH_CLASS.BATCH_CLASS_NM,

    COUNT(PROJECT.PROJECT_ID) AS cnt

  FROM OPPRTNTY

  INNER JOIN PROJECT

  ON OPPRTNTY.OPPRTNTY_ID = PROJECT.OPPRTNTY_ID

  INNER JOIN BATCH_CLASS

  ON BATCH_CLASS.BATCH_CLASS_ID = PROJECT.BATCH_CLASS_ID

  GROUP BY OPPRTNTY.CUST_ACNT_NBR,

    OPPRTNTY.OPPRTNTY_ID,

    OPPRTNTY.EXT_OPPRTNTY_ID,

    OPPRTNTY.OPPRTNTY_NM,

    OPPRTNTY.OPPRTNTY_DSC;

  --PROJECT.PROJECT_NM,

  --BATCH_CLASS.BATCH_CLASS_NM ;