//********************To Find the Bind values *******************//
column value_string format a30
column name format a30
column datatype_String format a20
select
sql_id,NAME,POSITION,DATATYPE_STRING,VALUE_STRING from v$sql_bind_capture where
sql_id = '&sqlid';
To find the col status
column histogram format a24
select
column_name,num_nulls,num_distinct,histogram,last_analyzed from dba_tab_columns
where table_name = '&tname' ORDER BY 1
/
=======================================================================
Description: This
script will show you the user's OS name, Username in the database and the SQL
Text they are running
Code:
SELECT osuser, username, sql_text
from v$session a, v$sqltext b
where a.sql_address =b.address
order by address, piece
=====================================================================
To find full query on the basis of hash value
select SQL_TEXT from v$sqltext where
HASH_VALUE='&HASH_VALUE' order by piece
/
To get the hash value
====================
select
s.username,s.sid,s.serial#,s.sql_hash_value from v$session s,v$process p where
s.paddr=p.addr and p.spid=&PID;
To get the SQL_TEXT
===================
select
s.username,s.sid,s.serial#,s.sql_hash_value,substr(t.sql_text,1,100) from
v$session s,v$process p,v$sqlarea t
where s.paddr=p.addr and p.spid=15490 and t.address=s.sql_address;
OR
select
s.username,s.sid,s.serial#,s.sql_hash_value,t.sql_text from v$session
s,v$process p,v$sqlarea t where
s.paddr=p.addr and p.spid=15490 and t.address=s.sql_address;
Del_Stats
analyze table sysadm.PS_GP_EXCL_WRK delete statistics;
analyze table sysadm.PS_GP_HST_WRK delete statistics;
analyze table sysadm.PS_GP_JOB2_WRK delete statistics;
analyze table sysadm.PS_GP_PYE_HIST_WRK delete statistics;
analyze table sysadm.PS_GP_PYE_STAT_WRK delete statistics;
analyze table sysadm.PS_GP_RTO_PRC_WRK delete statistics;
analyze table sysadm.PS_GP_RTO_TRG_WRK1 delete statistics;
analyze table sysadm.PS_GP_RTO_TRGR_WRK delete statistics;
analyze table sysadm.PS_GP_TLPTM_WRK delete statistics;
analyze table sysadm.PS_GP_TLTRC_WRK delete statistics;
event.sql
select event, count(1) from v$session group by event order
by 2
/
Explain plan
@$ORACLE_HOME/rdbms/admin/utlxpls.sql
EXPLAIN PLAN FOR
SELECT BUSINESS
FROM PS_Z_BUSS_UNIT_VW
WHERE BUSINESS_LEVEL =2
AND BUSINESS_UNIT =
(SELECT
A.BUSINESS_UNIT
FROM PS_JOB A
WHERE A.EMPLID=
'090578'
AND A.EMPL_RCD=0
AND A.EFFDT =
(SELECT
MAX(B.EFFDT)
FROM PS_JOB B
WHERE A.EMPLID =
B.EMPLID
AND B.EMPL_RCD =
A.EMPL_RCD
AND B.EFFDT <=SYSDATE
)
AND A.EFFSEQ =
(SELECT
MAX(C.EFFSEQ)
FROM PS_JOB C
WHERE A.EMPLID =
C.EMPLID
AND A.EMPL_RCD =
C.EMPL_RCD
AND A.EFFDT = C.EFFDT
)
);
EXPLAIN PLAN FOR
SELECT BUSINESS
FROM ps_z_buss_unit_tbl
WHERE BUSINESS_LEVEL =2
AND BUSINESS_UNIT =
(SELECT
A.BUSINESS_UNIT
FROM PS_JOB A
WHERE A.EMPLID=
'090578'
AND A.EMPL_RCD=0
AND A.EFFDT =
(SELECT
MAX(B.EFFDT)
FROM PS_JOB B
WHERE A.EMPLID =
B.EMPLID
AND B.EMPL_RCD =
A.EMPL_RCD
AND B.EFFDT <=SYSDATE
)
AND A.EFFSEQ =
(SELECT
MAX(C.EFFSEQ)
FROM PS_JOB C
WHERE A.EMPLID =
C.EMPLID
AND A.EMPL_RCD =
C.EMPL_RCD
AND A.EFFDT = C.EFFDT
)
);
utlxpls.sql
//******* AWR Report Generate Steps *******///
AWR Report : $ORACLE_HOME/rdbms/admin/awrrpt.sql
Generate ADDM Report: @$ORACLE_HOME/rdbms/admin/addmrpt.sql
Find PID
set pages 500
set line 180
col MACHINE for a10
col PROGRAM for a30
col OSUSER for a10
col USERNAME for a10
col sql_text for a65
col a.sid for a5
col a.serial# for a5
select
c.sql_text,a.username,a.osuser,a.sid,a.serial#,a.program,a.machine,a.process
"APPPROC" ,
b.spid "DB PROC",hash_value
from v$session a , v$process b , v$sqlarea c
where a.sql_address=c.address (+) and
a.paddr=b.addr and
b.spid='&1'
//* To Find the database hang session *//
set pagesize 100
col client_info format a40
col hangtime format a45
Select
VS.CLIENT_INFO,
' Hanging for '|| floor(vs.last_call_et/3600)||' Hours,'||
floor(mod(vs.last_call_et,3600)/60)||' Minutes, '||
mod(mod(vs.last_call_et,3600),60)||' Secs ' "HANGTIME",
vl.sql_text
From V$SqlArea VL, V$Process VP, V$Session VS
Where VP.Addr = VS.Paddr(+)
And Nvl(
VS.Sql_Address, 'FFFFFFFF') = VL.Address(+)
And Nvl( VS.SERIAL#,
0) > 1
and VS.Status =
'ACTIVE'
and vs.client_info
is not null
and last_call_et
> 180
/
//*Find the Execution plan using the SQL_ID*//
select * from table(dbms_xplan.display_cursor('&sqlid'))
Plana.sql
select * from table(dbms_xplan.display_awr('&sqlid'))
//*Find the Execution plan history using the SQL_ID*//
set pages 500
set lines 145
col
BEGIN_INTERVAL_TIME format a35
select ss.snap_id,
ss.instance_number node, begin_interval_time, sql_id, plan_hash_value,
nvl(executions_delta,0) execs,
(elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000
avg_etime,
(buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta))
avg_lio
from DBA_HIST_SQLSTAT
S, DBA_HIST_SNAPSHOT SS
where sql_id =
nvl('&sql_id','4dqs2k5tynk61')
and ss.snap_id =
S.snap_id
and
ss.instance_number = S.instance_number
and executions_delta
> 0 --and plan_hash_value=3099046451
order by 1, 2, 3
/
//* To find the PS Application session's at database level using the modules or program*//
Psrun.sql
column event format a40
column lt format a25
select
sid,serial#,module,sql_id,to_char(logon_time,'dd-MON-YYYY hh24:mi:ss') LT
,event from v$session where username = 'SYSADM' and program like 'PSRUN%'
//* To Find the Table Stats *//
Table_stat.sql
select owner, table_name,
num_rows,sample_size,last_analyzed,partitioned from dba_tables where table_name
= '&tname'
//*To Find the view DDL statement*//
set long 10000
select text from dba_views where view_name = '&viewname'
//*To Find the Blocking Session*//
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 ;
//*To Find the block Sessions*//
select 'SID ' ||
l1.sid ||' is blocking ' || l2.sid
blocking
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
column object_name format a27
column oracle_username format a15
column os_user_name format a10
select /*+ ORDERED */
b.object_name,
a.session_id,
a.oracle_username,
os_user_name ,
decode (a.locked_mode,1,'Null', 2,'Row-S', 3,'Row-X',
4,'Share', 5,'S/Row-X', 6,'Exclusive') lock_mode
from v$locked_object a, all_objects b
where a.object_id = b.object_id
order by b.object_name
/
//************ALL OTHER IMPORTANT PEOPLE SOFT DB SCRIPTS***********//
Process in processing status
set lines 999
set pages 999
select b.sid,b.serial#,
prcsinstance,
prcstype,prcsname,
to_char(rqstdttm,'mm-dd-yy hh24:mi:ss') Start_Date,
oprid,
decode(runstatus,1,'Cancel',
2,
'Delete',
3, 'Error',
4, 'Hold',
5,
'Queued',
6,
'Initiated',
7,
'Processing',
8,
'Cancelled',
9,
'Success',
10,'No
Success',
11,'Posted',
12,'Not
Posted',
13,'Resend',
14,'Posting',
15,'Generated') "Status",
origprcsinstance
from SYSADM.PSPRCSQUE a, gv$session b
where runstatus='7'
and b.process=to_char(a.sessionidnum)
and b.client_info is not null
/
Privelage_gen.sql
set pagesize 10000
set echo off;
spool /home/oracle/scripts/05-04-2014_HYP_privilege.sql;
select 'set echo on;' from dual;
select 'spool privilege.log;' from dual;
select 'GRANT SELECT on '||TABLE_NAME|| ' to HYP;' from
DBA_TABLES where OWNER='SYSADM' and TABLE_NAME not in (select table_name from
dba_tab_privs where grantee='HYP');
select 'GRANT SELECT on '||VIEW_NAME|| ' to HYP;' from
DBA_VIEWS where OWNER='SYSADM' and VIEW_NAME not in (select table_name from
dba_tab_privs where grantee='HYP');
select 'spool off;' from dual;
spool off;
running_AE.sql
set pages 500
set lines 145
column LOGON_TIME format a24
SELECT
r.prcsinstance, h.module, h.action, sum(1) ash_secs
FROM v$active_Session_history h
, psprcsque q
, psprcsrqst r
WHERE r.prcsinstance = q.prcsinstance
AND h.module = 'PSAE.'||q.prcsname||'.'||q.sessionidnum
AND h.sample_time BETWEEN r.begindttm AND
NVL(r.enddttm,SYSDATE)
and r.prcsinstance = 867
GROUP BY r.prcsinstance, h.module, h.action
ORDER BY ash_secs DESC;
You can check whether or not your tables are histogramed by
looking in DBA_TAB_COL_STATISTICS:
SELECT OWNER, TABLE_NAME, HISTOGRAM, NUM_BUCKETS FROM DBA_TAB_COL_STATISTICS;
Session lock find query
######Check Session Lock##########
Abghr#987!
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;
SELECT a.sid,a.serial#,
a.username,c.os_user_name,a.terminal, b.object_id,substr(b.object_name,1,40)
object_name
from v$session a, dba_objects b, v$locked_object c where
a.sid = c.session_id and b.object_id = c.object_id;
ALTER SYSTEM KILL SESSION 'sid,serial#';
alter system kill session '28,46634';
alter system kill session '21,63485';
#########Database segment space ###########
SELECT SUM(BYTES) FROM USER_SEGMENTS WHERE
SEGMENT_TYPE='TABLE';
######### PRCS Queue procss
##########3
select count(*) from psprcsrqst where runstatus<>'9';
delete from psprcsrqst where runstatus<>'9';
##########SYSADM SCHEMA REFRESH #################
Step 1:
Export SYSADM schema from host database
Copy .dmp file to target server
Step 2:
If SYSADM in target database contains objects, remove them:
> sqlplus sysadm/<password>
SQL> set heading off
SQL> set feedback off
SQL> set echo off
SQL> spool <name>.sql
SQL> select 'drop '||object_type||'
'||object_name||decode(object_type,'TABLE',' cascade constraints;',';')
from user_objects
where object_type in
('FUNCTION','PACKAGE','PROCEDURE','SEQUENCE','TABLE','VIEW', 'INDEX',
'TRIGGER');
Edit <name>.sql and remove the non-drop statements at
the beginning and end of the file. Log onto the database as sysadm once again
and run the sql. Clean up by purging the recyclebin:
SQL> @<name>.sql
...
SQL> connect sys as sysdba
<password>
SQL> purge dba_recyclebin;
Step 3:
SYSADM should now contain no objects. Log onto grid control
and import from the .dmp file, or manually import using imp command
#################################################
Now, lets create a session as a DBA User to monitor the
system, this query will tell the locking and waiting SIDs.
SELECT vh.sid locking_sid,vs.status status,vs.program
program_holding,vw.sid waiter_sid,vsw.program program_waiting
FROM
v$lock vh,v$lock vw, v$session vs,v$session vsw WHERE (vh.id1, vh.id2) IN
(SELECT id1, id2 FROM v$lock WHERE request = 0 INTERSECT SELECT id1, id2 FROM
v$lock WHERE lmode = 0) AND vh.id1 = vw.id1 AND vh.id2 = vw.id2 AND vh.request
= 0 AND vw.lmode = 0 AND vh.sid = vs.sid
AND vw.sid = vsw.sid;
By Prasanth Bhaskaran (Oracle People Soft Upgrade & DBA Specialist )
