Monthly Archives: October 2014

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;

PL/SQL Kill Db connections

select max(ctime), sid from dbms_lock_allocated d,v$lock l
where d.lockid = l.id1 and ctime >30
group by sid

 
select
    s1.username || ‘@’ || s1.machine
    || ‘ ( SID,S#=’ || s1.sid || ‘,’ || s1.serial# || ‘ )  is blocking ‘
    || s2.username || ‘@’ || s2.machine
    || ‘ ( SID,S#=’ || s2.sid || ‘,’ || s2.serial# || ‘ )’
        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;
select  ‘ALTER SYSTEM KILL SESSION ”’ || sid  || ‘,’ || serial# || ”’;’ from v$session where sid in ( select sid from dbms_lock_allocated d,v$lock l where d.lockid = l.id1 and ctime >300);
column oracle_username format a15
column os_user_name format a15
column object_name format a37
column object_type format a37
select a.session_id,a.oracle_username, a.os_user_name, b.owner “OBJECT OWNER”, b.object_name,b.object_type,a.locked_mode from
(select object_id, SESSION_ID, ORACLE_USERNAME, OS_USER_NAME, LOCKED_MODE from v$locked_object) a,
(select object_id, owner, object_name,object_type from dba_objects) b
where a.object_id=b.object_id
select c.owner, c.object_name, c.object_type,b.sid, b.serial#, b.status, b.osuser, b.machine
from v$locked_object a , v$session b, dba_objects c
where b.sid = a.session_id
and a.object_id = c.object_id;
select
     username,
     v$lock.sid,
     trunc(id1/power(2,16)) rbs,
     bitand(id1,to_number(‘ffff’,’xxxx’))+0 slot,
     id2 seq,
     lmode,
     request
from v$lock, v$session
where v$lock.type = ‘TX’
and v$lock.sid = v$session.sid
and v$session.username = USER
/

 

 

Check free tablespace

select b.tablespace_name, tbs_size SizeMb, to_char(a.free_space/tbs_size * 100,’99.99′) || ‘%’ FreeMb
from  (select tablespace_name, round(sum(bytes)/1024/1024 ,2) as free_space
       from dba_free_space
       group by tablespace_name) a,
      (select tablespace_name, sum(bytes)/1024/1024 as tbs_size
       from dba_data_files
       group by tablespace_name) b
where a.tablespace_name(+)=b.tablespace_name;
column owner format A7
column bytes format 99999999999
break ON owner  ON report
compute SUM  OF bytes ON owner report
 
SELECT owner, table_name, NVL(num_rows*avg_row_len,0) bytes
FROM dba_tables
WHERE tablespace_name=’OWB_DATA’  and NVL(num_rows*avg_row_len,0) >10000
ORDER BY NVL(num_rows*avg_row_len,0) desc
/
select distinct(a.TABLESPACE_NAME) TABLESPACE_NAME,NVL(a.AUTOEXTENSIBLE,’NO’) AUTOEXTD,
(a.INCREMENT_BY*(select value from v$parameter where lower(name)=’db_block_size’))/1024/1024 NEXT_SIZE,
a.bytes/1024/1024 SIZE_MB,
nvl(a.MAXBYTES/1024/1024,’0′) MAXSIZE,a.file_id,
REPLACE(
REPLACE(a.file_name,’/etrade/pkgs/linux/intel/oracle’,’${ORACLE_BASE}’)
,’/etrade/pkgs/oracle’,’${ORACLE_BASE}’)
as DATA_FILE_NAME
from dba_data_files a,dba_tablespaces b
where a.tablespace_name=b.tablespace_name and a.tablespace_name like ‘SPOTFIRE_DATA_500′
order by NVL(a.AUTOEXTENSIBLE,’NO’);

for the shrink (which has datafile number listed) :

select a.file_id, file_name,
       ceil( (nvl(hwm,1)*(select value from v$parameter where name = ‘db_block_size’ )/1024/1024 )) smallest,
       ceil( blocks*(select value from v$parameter where name = ‘db_block_size’)/1024/1024) currsize,
       ceil( blocks*(select value from v$parameter where name = ‘db_block_size’)/1024/1024) –
       ceil( (nvl(hwm,1)*(select value from v$parameter where name = ‘db_block_size’)/1024/1024 )) savings
from dba_data_files a,
     ( select file_id, max(block_id+blocks-1) hwm
         from dba_extents
        group by file_id ) b
where a.file_id = b.file_id(+)  and a.tablespace_name=’&tsname’
/

select a.file_id, file_name,
       ceil( (nvl(hwm,1)*(select value from v$parameter where name = ‘db_block_size’ )/1024/1024 )) smallest,
       ceil( blocks*(select value from v$parameter where name = ‘db_block_size’)/1024/1024) currsize,
       ceil( blocks*(select value from v$parameter where name = ‘db_block_size’)/1024/1024) –
       ceil( (nvl(hwm,1)*(select value from v$parameter where name = ‘db_block_size’)/1024/1024 )) savings
from dba_data_files a,
     ( select file_id, max(block_id+blocks-1) hwm
         from dba_extents
        group by file_id ) b
where a.file_id = b.file_id(+)  and a.tablespace_name=’&tsname’
/

see if this helps:
alter table <tbl name> enable row movement;
alter table <tbl name> shrink space;

Db Performance check

1) Get the Process ID (PID) from TOP command which consume high CPU Usages.

So the query to get the session details (SID) from OS PID (SPID) will be as per

following.

select s.sid from v$process p, v$session s
where s.paddr=p.addr and p.spid = (PID) ;

3) Once we get the session ID, base on this information we can get the actual SQL

statement which is causing
HIGH CPU usage on database server.

We can use the following query to get the actual SQL STATEMENT.

SELECT SQL_TEXT from V$SQLTEXT_WITH_NEWLINES where HASH_VALUE
= (select sql_hash_value from v$session
where SID = (SID_WITCH_CAPTURED_IN_STEP_2) ;

–# from below query you will findout sid:-

SELECT se.username, ss.sid, ROUND (value/100) “CPU Usage”
FROM v$session se, v$sesstat ss, v$statname st
WHERE ss.statistic# = st.statistic#
AND name LIKE ‘%CPU used by this session%’
AND se.sid = ss.SID
AND se.username IS NOT NULL
ORDER BY value DESC;

select
ss.username,
se.SID,
VALUE/100 cpu_usage_seconds
from
v$session ss,
v$sesstat se,
v$statname sn
where
se.STATISTIC# = sn.STATISTIC#
and
NAME like ‘%CPU used by this session%’
and
se.SID = ss.SID
and
ss.status=’ACTIVE’
and
ss.username is not null
order by VALUE desc;

select sql_hash_value, count(*) from v$session
where status = ‘ACTIVE’ group by sql_hash_value order by 2 desc;

select sql_text,users_executing from v$sql where hash_value = <give hash value here

which is out put of above query>;