Author Archives: admin

Password Profile

create profile PASS_EXCEP_PROFILE limit
COMPOSITE_LIMIT UNLIMITED
SESSIONS_PER_USER UNLIMITED
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL UNLIMITED
LOGICAL_READS_PER_SESSION UNLIMITED
LOGICAL_READS_PER_CALL UNLIMITED
IDLE_TIME UNLIMITED
CONNECT_TIME UNLIMITED
PRIVATE_SGA UNLIMITED
FAILED_LOGIN_ATTEMPTS UNLIMITED
PASSWORD_LIFE_TIME UNLIMITED
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_VERIFY_FUNCTION NULL
PASSWORD_LOCK_TIME UNLIMITED
PASSWORD_GRACE_TIME 5
;

alter user user_name_here profile PASS_EXCEP_PROFILE;

remove long running db session

set pages 100
col username       format a20
col “SID,SESSION#” format a20
col sess_id        format a10
col object format a30
col mode_held      format a10
select     oracle_username || ‘ (‘ || s.osuser || ‘)’ username
,  s.sid || ‘,’ || s.serial# “SID,SESSION#”
,  owner || ‘.’ || object_name object
,  object_type
,  decode( l.block
,       0, ‘Not Blocking’
,       1, ‘Blocking’
,       2, ‘Global’) status
,  decode(v.locked_mode
,       0, ‘None’
,       1, ‘Null’
,       2, ‘Row-S (SS)’
,       3, ‘Row-X (SX)’
,       4, ‘Share’
,       5, ‘S/Row-X (SSX)’
,       6, ‘Exclusive’, TO_CHAR(lmode)) mode_held
from       v$locked_object v
,  dba_objects d
,  v$lock l
,  v$session s
where      v.object_id = d.object_id
and        v.object_id = l.id1
and        v.session_id = s.sid
order by oracle_username,session_id;

select ‘ALTER SYSTEM KILL SESSION ”’ || sid  || ‘,’ || serial# || ”’ IMMEDIATE;’ from v$session where sid in ( select sid from dbms_lock_allocated d,v$lock l where d.lockid = l.id1 and ctime >30);

 

set head off;
set pagesize 2000;
select ‘ALTER SYSTEM KILL SESSION ”’ || sid  || ‘,’ || serial# || ”’ IMMEDIATE;’
from v$session where sid in ( select sid from dbms_lock_allocated d,v$lock l where d.lockid = l.id1 and ctime >30);
set linesize 140
set pages 100
col username       format a20
col “SID,SESSION#” format a20
col sess_id        format a10
col object format a30
col mode_held      format a10
select     oracle_username || ‘ (‘ || s.osuser || ‘)’ username
,  s.sid || ‘,’ || s.serial# “SID,SESSION#”
,  owner || ‘.’ || object_name object
,  object_type
,  decode( l.block
,       0, ‘Not Blocking’
,       1, ‘Blocking’
,       2, ‘Global’) status
,  decode(v.locked_mode
,       0, ‘None’
,       1, ‘Null’
,       2, ‘Row-S (SS)’
,       3, ‘Row-X (SX)’
,       4, ‘Share’
,       5, ‘S/Row-X (SSX)’
,       6, ‘Exclusive’, TO_CHAR(lmode)) mode_held
from       v$locked_object v
,  dba_objects d
,  v$lock l
,  v$session s
where      v.object_id = d.object_id
and        v.object_id = l.id1
and        v.session_id = s.sid
order by oracle_username,session_id;

Adding jdbc connection string for failover

jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=hst20w110m3)(PORT=1511))(SERVICE_NAME=PS1SID))(ADDRESS=(PROTOCOL=TCP)(HOST=hst20w114m3)(PORT=1511))(LOAD_BALANCE=yes)(CONNECT_DATA=(SERVICE_NAME=PS2SID)(FAILOVER_MODE=(TYPE=SELECT)(METHOD=BASIC)(RETRIES=180))))

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=hst20w110m3)(PORT=1511))(SERVICE_NAME=PS1SID)(ADDRESS=(PROTOCOL=TCP)(HOST=hst20w114m3)(PORT=1511))(LOAD_BALANCE=yes)(CONNECT_DATA=(SERVICE_NAME=PS2SID)(FAILOVER_MODE=(TYPE=SELECT)(METHOD=BASIC)(RETRIES=180))))

 

jdbc:oracle:thin:@(DESCRIPTION_LIST = (FAILOVER = yes)(LOAD_BALANCE = no)(DESCRIPTION =(LOAD_BALANCE = yes)(ADDRESS = (PROTOCOL= TCP) (HOST= uathst5w80m7) (PORT = 1611)) (CONNECT_DATA = (SERVICE_NAME = US1SID)))(DESCRIPTION =(LOAD_BALANCE = yes)(ADDRESS = (PROTOCOL= TCP) (HOST= uathst5w80m7) (PORT= 1611))(CONNECT_DATA = (SERVICE_NAME = US1SID))))
jdbc:oracle:thin:@(DESCRIPTION_LIST = (FAILOVER = yes)(LOAD_BALANCE = no)(DESCRIPTION =(LOAD_BALANCE = yes)(ADDRESS = (PROTOCOL= TCP) (HOST= hst20w110m3) (PORT = 1511)) (CONNECT_DATA = (SERVICE_NAME = PS1SID)))(DESCRIPTION =(LOAD_BALANCE = yes)(ADDRESS = (PROTOCOL= TCP) (HOST= hst20w114m3) (PORT= 1511))(CONNECT_DATA = (SERVICE_NAME = PS2SID))))
jdbc:oracle:thin:@(DESCRIPTION=(LOAD_BALANCE=on)
(ADDRESS=(PROTOCOL=TCP)(HOST=host1) (PORT=1521))
(ADDRESS=(PROTOCOL=TCP)(HOST=host2) (PORT=1521))
(CONNECT_DATA=(SERVICE_NAME=orcl)))

jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=sithst5w88m7.xyzcorp.com)(PORT =1690)))(CONNECT_DATA=(SERVICE_NAME=SS1SIDX)))
jhstc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=sithst4w100m7.xyzcorp.com)(PORT =1881)))(CONNECT_DATA=(SERVICE_NAME=SR1BPMS)))

 

Insert file content into oracle table

create or replace
PACKAGE BODY     ORI_POST_FILE AS
    PROCEDURE LOAD_FILE_CONTENT (I_REPORT_TYPE_ID NUMBER , I_FILE_NAME VARCHAR2)
    AS
      src_loc bfile:= bfilename(‘FLAT_MOD_ORI_INCOMING_LOC’,LOAD_FILE_CONTENT.I_FILE_NAME);
      dest_loc BLOB;
      var_seq_id NUMBER;
      var_STATUS_ID NUMBER :=200;
      var_USER_ID NUMBER :=1;
      var_REPORT_SEQUENCE_ID number;
      var_file_size NUMBER :=0;
      begin
        –select REPORT_SEQUENCE_SEQID_SEQ.nextval into var_REPORT_SEQUENCE_ID from dual;
        INSERT INTO REPORT_SEQUENCE(
            , REPORT_TYPE_ID
            , CREATED_TS
            , CREATED_USER_ID
            , STATUS_ID)
        VALUES (I_REPORT_TYPE_ID
        , CURRENT_TIMESTAMP
        , var_USER_ID
        , var_STATUS_ID) returning REPORT_SEQUENCE_ID into var_REPORT_SEQUENCE_ID;

 
       UPDATE ORI.REPORT_SEQUENCE
   SET REPORT_URL = XMLTYPE(‘<Links>
           <Link>/site/generatereport?reportpostid=’|| var_REPORT_SEQUENCE_ID ||'</Link>
         </Links>’)
    WHERE REPORT_SEQUENCE_ID = var_REPORT_SEQUENCE_ID;
  
      dbms_lob.open(src_loc,DBMS_LOB.LOB_READONLY);
      var_file_size := DBMS_LOB.getLength(src_loc);

      –insert into blob_test values(10,empty_blob()) returning docs into dest_loc;
      insert into ORD.report_post (report_sequence_id, file_name, file_size,file_content) values (var_REPORT_SEQUENCE_ID,LOAD_FILE_CONTENT.I_FILE_NAME,var_file_size,empty_blob() )  returning file_content into dest_loc;

      DBMS_LOB.OPEN(dest_loc, DBMS_LOB.LOB_READWRITE);
      DBMS_LOB.LOADFROMFILE(
      dest_lob => dest_loc
      ,src_lob => src_loc
      ,amount => DBMS_LOB.getLength(src_loc)
     );
      DBMS_LOB.CLOSE(dest_loc);
      DBMS_LOB.CLOSE(src_loc);
     — Update report_post set file_size = amount where REPORT_SEQUENCE_ID = var_report_sequence_id;
      COMMIT;
    end;
END;

Alter system enable session trace

exec dbms_system.set_sql_trace_in_session(130 /* sid */,9 /* serial# */,true);
exec dbms_system.set_sql_trace_in_session(451 /* sid */,3 /* serial# */,true);
exec dbms_system.set_sql_trace_in_session(642 /* sid */,17 /* serial# */,true);

 

exec dbms_system.set_sql_trace_in_session(130 /* sid */,9 /* serial# */,false);
exec dbms_system.set_sql_trace_in_session(451 /* sid */,3 /* serial# */,false);
exec dbms_system.set_sql_trace_in_session(642 /* sid */,17 /* serial# */,false);

 

 
TKPROF tracefile.trc ora_trans_file_name.prf SORT = (PRSDSK, EXEDSK, FCHDSK) PRINT = 10

 

Generate create table, view, index scripts

set linesize 2000

set trimspool on
set pages 0
set long 1000000000

SET TERMOUT OFF

set echo off

spool c:\temp\table_index_cr.sql

SELECT DBMS_METADATA.GET_DDL(‘TABLE’,ut.table_name)
     FROM USER_TABLES ut;

SELECT DBMS_METADATA.GET_DDL(‘VIEW’,ut.table_name)
     FROM USER_VIEWS ut;

  SELECT DBMS_METADATA.GET_DDL(‘INDEX’,ui.index_name) FROM USER_INDEXES ui;

spool off;

Grant Access to all objects on a schema

Grant all privs on a schema

REM connect ord/ord;
set verify off
set echo off
set sqlnumber off
set heading off
set feedback off
set termout off
set linesize 300
spool ‘/tmp/grant_privs_for_ord_Role.sql’

select ‘grant select on ‘ ||owner || ‘.’ || table_name || ‘ to ORD_RO_ROLE;’ from all_tables where owner like ‘Schema_name’;

select ‘grant select on ‘ ||owner || ‘.’|| view_name || ‘ to ORD_RO_ROLE;’ from all_views  where owner like ‘Schema_name’;

select ‘grant select,insert,update,delete on ‘ ||owner || ‘.’|| table_name || ‘ to ORD_RW_ROLE;’ from all_tables where owner like ‘Schema_name’;

select ‘grant select,insert,update,delete on ‘ ||owner || ‘.’|| View_name || ‘ to ORD_RW_ROLE;’ from all_views  where owner like ‘Schema_name’;

select ‘grant EXECUTE on ‘ ||owner || ‘.’|| object_name || ‘ to ORD_RO_ROLE, ORD_RW_ROLE;’ from all_objects where owner = ‘Schema_name’ and object_type in (‘PROCEDURE’,’FUNCTION’,’PACKAGE’ );

select ‘grant ORD_RW_ROLE to ETL_ORI_TGT;’ from dual;
select ‘grant ORD_RW_ROLE to ETL_REI_TGT;’ from dual;
select ‘grant ORD_RW_ROLE to ETL_BPM_TGT;’ from dual;
spool off

PL/SQL convert time stamp (linux/unix)

SELECT
  SUBJECT,
  RECPTYPE,
  RECPVALUE,
  OCCURREDDT
FROM
  US_EVENT.EVENT
  WHERE
   OCCURREDDT  >=  ROUND(SYSDATE-1 – TO_DATE(’01/01/1970′,’MM/DD/YYYY’))* 86400
   AND
   OCCURREDDT  <  ROUND(SYSDATE – TO_DATE(’01/01/1970′,’MM/DD/YYYY’))* 86400
   AND
   ALERTTYPE  IN  ( 1973, 1974  )

PL/SQL check blocking sessions

—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

SELECT sid, id1 FROM v$lock WHERE TYPE=’TM’;

 
use this scripts i hope its it will help u to get blocking locks

set echo off
set line 10000
column blocker format a11;
column blockee format a10;
column sid format 99999;
column blocker_module format a15 trunc;
column blockee_module format a15 trunc;

alter session set optimizer_mode=rule;

select a.inst_id,
(select username from gv$session s where s.inst_id=a.inst_id and s.sid=a.sid) blocker,
a.sid,
(select module from gv$session s where s.inst_id=a.inst_id and s.sid=a.sid) blocker_module ,
‘ is blocking ‘ “IS BLOCKING”,
b.inst_id,
(select username from gv$session s where s.inst_id=b.inst_id and s.sid=b.sid) blockee,
b.sid ,
(select module from gv$session s where s.inst_id=b.inst_id and s.sid=b.sid) blockee_module
from gv$lock a, gv$lock b
where
–Modified to take care of issue where sid blocked on difft instances are not showing
— Original Script
/* a.block = 1
and b.request > 0
and a.id1 = b.id1
and a.inst_id = b.inst_id
and a.id2 = b.id2
*/
— Modified script
a.block <>0 and
b.request > 0
and a.id1 = b.id1
and a.id2 = b.id2
and a.sid <> b.sid
order by 1, 2
/
alter session set optimizer_mode=choose;

 

—————–==============================
—————–==============================

 

 

 

 

 

 
select * from ec_branch_hurdles
where book_size is not null

select count(*)
–delete
from ec_branch_hurdles where report_id>20130101;
commit;

select * from ec_report_date_details order by batch_id desc;

–delete from ec_employee_branches where report_id =20130426;
–delete from ec_et_employees where report_id =20130426 ;
select * from ec_acct_credits where report_id =20130426 ;
select * from ec_employee_hurdles  where report_id =20130426 ;
select * from ec_branch_costs  where report_id =20130426 ;
select * from ec_branch_hurdles  where report_id =20130426 ;

set linesize 140
set pages 100
col username       format a20
col “SID,SESSION#” format a20
col sess_id        format a10
col object format a30
col mode_held      format a10
select     oracle_username || ‘ (‘ || s.osuser || ‘)’ username
,  s.sid || ‘,’ || s.serial# “SID,SESSION#”
,  owner || ‘.’ || object_name object
,  object_type
,  decode( l.block
,       0, ‘Not Blocking’
,       1, ‘Blocking’
,       2, ‘Global’) status
,  decode(v.locked_mode
,       0, ‘None’
,       1, ‘Null’
,       2, ‘Row-S (SS)’
,       3, ‘Row-X (SX)’
,       4, ‘Share’
,       5, ‘S/Row-X (SSX)’
,       6, ‘Exclusive’, TO_CHAR(lmode)) mode_held
from       v$locked_object v
,  dba_objects d
,  v$lock l
,  v$session s
where      v.object_id = d.object_id
and        v.object_id = l.id1
and        v.session_id = s.sid
order by oracle_username,session_id;

PL/SQL run time tunning

create or replace
PROCEDURE SP_UPDATE_BPSA_MARGIN_RISK_ADV
AS
var_cusip BPSA_MARGIN_RISK.cusip%type;
var_thirtydayaverage BPSA_MARGIN_RISK.average_daily_volume%type;
var_counter number;
var_max_date VARCHAR2(20);
var_sql varchar2(1000);
BEGIN
  var_counter :=0;
 
    –DBMS_OUTPUT.PUT_LINE(‘Started updating Cusips at ‘ || to_char(SYSDATE,’YYYYMMDD HH:MI:SS’) );
   
    SELECT MAX(PROCESS_DATE)
    INTO var_max_date
    FROM BPSA_MARGIN_RISK;
   
    — Declare cusip cursor
    DECLARE CURSOR CUSIP_CURSOR IS
    SELECT distinct cusip,thirtydayaverage
      FROM vw_daily_average_volume
     WHERE vw_daily_average_volume.cusip in ( select distinct cusip from BPSA_MARGIN_RISK where process_date =var_max_date)
       AND created_ts = (select max(created_ts) from vw_daily_average_volume);
     
    BEGIN

      — Open  Curosor
      OPEN CUSIP_CURSOR;
     
      LOOP
     
          Fetch CUSIP_CURSOR into var_cusip, var_thirtydayaverage;
         
          EXIT when CUSIP_CURSOR%NOTFOUND;
         
          var_sql :=’update BPSA_MARGIN_RISK set average_daily_volume = :1 where process_date = :2 and cusip = :3 and average_daily_volume is null’;
         
          execute immediate var_sql using var_max_date, var_cusip, var_cusip;
         
          /*
          update BPSA_MARGIN_RISK
             set average_daily_volume = var_thirtydayaverage
           where process_date = var_max_date
             and cusip = var_cusip
             and var_cusip is null;
          */
         
          –DBMS_OUTPUT.PUT_LINE(var_counter || ‘. Cusip:’ || ‘ ‘ || var_cusip);
          — var_counter := var_counter + 1;
         
      END LOOP;
   
      COMMIT;
     
      –DBMS_OUTPUT.PUT_LINE(var_counter || ‘Cusips updated by’ || to_char(SYSDATE,’YYYYMMDD HH:MI:SS’) );
   
    CLOSE CUSIP_CURSOR;
   
  END;
 
END SP_UPDATE_BPSA_MARGIN_RISK_ADV;

 

create or replace
PROCEDURE SP_UPDATE_BPSA_MARGIN_RISK_ADV
AS
var_cusip BPSA_MARGIN_RISK.cusip%type;
var_thirtydayaverage BPSA_MARGIN_RISK.average_daily_volume%type;
var_counter number;
var_max_date VARCHAR2(20);
var_sql varchar2(4000);

BEGIN

  var_counter :=0;
      
MERGE into BPSA_MARGIN_RISK  a

USING( SELECT distinct cusip,thirtydayaverage
        FROM vw_daily_average_volume
        WHERE vw_daily_average_volume.cusip in ( select distinct cusip from BPSA_MARGIN_RISK
                                              where process_date =( SELECT MAX(PROCESS_DATE) FROM BPSA_MARGIN_RISK)
                                             )
          AND created_ts = (select max(created_ts) from vw_daily_average_volume)
     )t1 
  ON (
      a.cusip = t1.cusip and
       a.process_date = (SELECT MAX(PROCESS_DATE) FROM BPSA_MARGIN_RISK)
  )
  WHEN matched then
  UPDATE set a.average_daily_volume = NVL(a.average_daily_volume,t1.thirtydayaverage);
      COMMIT;
 
END;