Category Archives: Uncategorized

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)))

 

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 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
/