************************ Dataguard Status ************************
set lines 110
select p.prim_max_sequence,
d.dr_max_archived_sequence,
ad.dr_max_applied_sequence,
p.prim_max_sequence-d.dr_max_archived_sequence archive_lag_in_dr,
p.prim_max_sequence-ad.dr_max_applied_sequence applied_lag_in_dr
from
(select max(sequence#) prim_max_sequence from gv$archived_log where dest_id=1 and archived='YES') p,
(select max(sequence#) dr_max_archived_sequence from gv$archived_log where standby_dest='YES' and archived='YES') d,
(select max(sequence#) dr_max_applied_sequence from gv$archived_log where standby_dest='YES' and archived='YES' and applied='YES') ad;
set lines 150
select p.prim_max,
f.fall_max_archived,
d.dr_max_archived,
af.fall_max_applied,
ad.dr_max_applied,
p.prim_max-f.fall_max_archived archive_lag_fall,
p.prim_max-d.dr_max_archived archive_lag_dr,
p.prim_max-af.fall_max_applied applied_lag_fall,
p.prim_max-ad.dr_max_applied applied_lag_dr
from
(select max(sequence#) prim_max from v$archived_log where dest_id=1 and archived='YES') p,
(select max(sequence#) fall_max_archived from v$archived_log where standby_dest='YES' and archived='YES' and dest_id=2) f,
(select max(sequence#) dr_max_archived from v$archived_log where standby_dest='YES' and archived='YES' and dest_id=3) d,
(select max(sequence#) fall_max_applied from v$archived_log where standby_dest='YES' and archived='YES' and applied='YES' and dest_id=2) af,
(select max(sequence#) dr_max_applied from v$archived_log where standby_dest='YES' and archived='YES' and applied='YES' and dest_id=3) ad;
************************ Data File space status ******************
col name for a60
select dd.tablespace_name, dd.name,
round(dd.bytes / 1024 / 1024) "Allocated space MB",
round((dd.bytes / 1024 / 1024) - sum(df.bytes / 1024 / 1024)) "Used Space MB",
round(sum(df.bytes / 1024 / 1024)) "Free space MB"
from v$datafile_header dd, dba_free_space df
where dd.file# = df.file_id
-- and dd.tablespace_name in ('&tablespace_name')
group by dd.tablespace_name,v#ses
dd.name,
dd.status,
dd.creation_time,
dd.bytes / 1024 / 1024
order by 1;
************************ Data File max resize ********************
set verify off
column file_name format a70 word_wrapped
column smallest format 999,990 heading "Smallest|Size|Poss."
column currsize format 999,990 heading "Current|Size"
column savings format 999,990 heading "Poss.|Savings"
break on report
compute sum of savings on report
column tb_name new_val tablespace_name
select '&tablespace_name' tb_name from dual
/
column value new_val blksize
select value from v$parameter where name = 'db_block_size'
/
select /*+ RULE */ file_name,
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
ceil( blocks*&&blksize/1024/1024) currsize,
ceil( blocks*&&blksize/1024/1024) -
ceil( (nvl(hwm,1)*&&blksize)/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 tablespace_name='&tablespace_name'
/
column cmd format a75 word_wrapped
select /*+ RULE */ 'alter database datafile '''||file_name||''' resize ' ||
ceil( (nvl(hwm,1)*8192)/1024/1024 ) || 'm;' cmd
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 ceil( blocks*&&blksize/1024/1024) -
ceil( (nvl(hwm,1)*8192)/1024/1024 ) > 0
and tablespace_name='&tablespace_name'
/
******************************************* DB Growth ********************************************
set pagesize 50000
tti "Database growth per month for last year"
select to_char(creation_time, 'RRRR Month') "Month",
sum(bytes)/1024/1024 "Growth in Meg"
from sys.v$datafile
where creation_time > SYSDATE-365
group by to_char(creation_time, 'RRRR Month')
order by 1;
select sum(a.BYTES/1024/1024/1024) "Database Size GB",
sum(b.BYTES/1024/1024/1024) "Database Free Size GB",
sum(c.BYTES/1024/1024/1024) "Database Used Size GB"
from sm$ts_avail a, sm$ts_free b, sm$ts_used c
where a.TABLESPACE_NAME=b.TABLESPACE_NAME
and a.TABLESPACE_NAME=c.TABLESPACE_NAME;
***************************************** Temp Space Usage Growth **********************************
col "Usage Date" for a20
select trunc(a.BEGIN_INTERVAL_TIME) "Usage Date",round(sum(VALUE/1024/1024)) "Temp Space Usage in MB"
from DBA_HIST_SNAPSHOT a, DBA_HIST_SYSSTAT b
where a.SNAP_ID=b.SNAP_ID
and a.SNAP_ID between 17993 and 18175
and b.STAT_NAME IN('physical writes direct temporary tablespace',
'sorts (disk)',
'workarea executions - multipass')
group by trunc(a.BEGIN_INTERVAL_TIME)
order by 1;
********************************************** Create User ******************************************
set lines 150
set pages 2500
select username, default_tablespace, temporary_tablespace, profile
from dba_users;
create user username
identified by username_123
default tablespace tablespace_name
temporary tablespace tablespace_name
password expire;
grant connect, resource, unlimited tablespace to username;
************************** User session count in History ****************
select a.USERNAME, count(distinct b.session_id) SessionCount
from dba_users a, dba_hist_active_sess_history b
where a.user_id=b.user_id
and b.SNAP_ID in (23617,23618)
group by a.USERNAME
order by 2 desc;
******************* Disable sqlplus access ***********************
INSERT into SYSTEM.product_user_profile (product,userid,attribute,char_value,date_value)
values('SQL*Plus','CANFINNQRY','CONNECT','DISABLED',NULL);
****************To enable explain plan privilege to User**********
Creates a role with access to Dynamic Performance Tables for the SQL*Plus SET AUTOTRACE ... STATISTICS command.
After this script has been run, each user requiring access to the AUTOTRACE feature should be granted the PLUSTRACE role by
the DBA.
Create plustrace role : @$ORACLE_HOME/SQLPLUS/ADMIN/PLUSTRCE.SQL
grant plustrace to user;
alter session set current_schema=username;
Create plan_table in user schema : $ORACLE_HOME/RDBMS/ADMIN/UTLXPLAN.SQL
************************** Dynamic SGA Resizing *******************
set linesize 120
set pagesize 60
set trimspool on
break on timed_at skip 1
column oper_type format a14
column component format a24
column parameter format a21
select start_time timed_at,
oper_type,
component,
parameter,
oper_mode,
initial_size,
final_size
from $sga_resize_ops
where start_time between trunc(sysdate-3) and trunc(sysdate)
order by start_time, component;
************************** underscore param ***********************
set lines 150
set pages 500
col name for a40
col value for a15
col description for a65
SELECT ksppinm name,ksppstvl value,ksppdesc description
FROM x$ksppi x, x$ksppcv y
WHERE (x.indx = y.indx)
AND x.inst_id=userenv('instance')
AND x.inst_id=y.inst_id
AND ksppinm LIKE '%&media%'
ORDER BY name;
************************** spid from sid ***************************
set lines 123
col USERNAME for a15
col OSUSER for a8
col MACHINE for a15
col PROGRAM for a20
select b.spid, a.username,a.inst_id, a.program , a.osuser ,a.machine, a.sid, a.serial#, a.status from gv$session a, gv$process b
where addr=paddr(+) and sid=&sid
************************** sid from spid *******************************
set lines 150
set pages 500
col USERNAME for a15
col OSUSER for a8
col MACHINE for a15
col PROGRAM for a20
select a.username, a.program , a.osuser ,a.machine,a.sid, a.serial#,b.spid, a.status from gv$session a, gv$process b
where a.paddr=b.addr and b.spid=&spid
********************** Active SID for Specific PLSQL Object ************************
select sid, serial#, status, username, program
from v$session
where PLSQL_ENTRY_OBJECT_ID in (select object_id
from dba_objects
where object_name in ('REPLACE_MAIL_TEMPLATE_PROC','REPLACE_MAIL_TEMPLATE_TEST'));
****************************** hot_buffer from database cache********************************
set line 130
set pages 99,99,999
column avg_touches format 99,99,999
column myname heading 'Name' format a30
column mytype heading 'Type' format a10
column buffers format 999,999
column owner format a12
select object_type mytype,
b.owner owner ,
object_name myname,
blocks ,
count(1) buffer,
AVG(tch) avg_touches
From x$bh a,
dba_objects b,
dba_segments s
Where a.obj=b.object_id
and b.object_name=s.segment_name
and b.owner not in ('SYS','SYSTEM')
group by object_name,object_type,b.owner,blocks,obj
having avg(tch) > 5
and count(1) > 20;
select sid, p1raw, p2, p3, seconds_in_wait, wait_time, state
from v$session_wait
where event like '%latch%'
order by p2, p1raw;
select a.hladdr, a.file#, a.dbablk, a.tch, a.obj, b.object_name
from x$bh a, dba_objects b
where (a.obj = b.object_id or a.obj = b.data_object_id)
and a.hladdr = '&ADDR'
union
select hladdr, file#, dbablk, tch, obj, null
from x$bh
where obj in (select obj from x$bh
where hladdr = '&ADDR'
minus
select object_id from dba_objects
minus
select data_object_id
from dba_objects)
and hladdr = '&ADDR'
order by 4;
************************ Identify Address For Higher Sleep Counts *******************
First determine which latch id(ADDR) are interesting by examining the number of
sleeps for this latch. The higher the sleep count, the more interesting the
latch id(ADDR) is:
select CHILD# "cCHILD"
, ADDR "sADDR"
, GETS "sGETS"
, MISSES "sMISSES"
, SLEEPS "sSLEEPS"
from v$latch_children
where name = 'cache buffers chains'
order by 5, 1, 2, 3 desc;
Get Segment Name
================
Depending on the TCH column (The number of times the block is hit by a SQL
statement), you can identify a hotblock. The higher the value of the TCH column,
the more frequent the block is accessed by SQL statements.
column segment_name format a35
select /*+ RULE */
e.owner ||'.'|| e.segment_name segment_name,
e.extent_id extent#,
x.dbablk - e.block_id + 1 block#,
x.tch,
l.child#
from
sys.v$latch_children l,
sys.x$bh x,
sys.dba_extents e
where
--x.hladdr = '&ADDR' and
e.file_id = x.file# and
x.hladdr = l.addr and
x.dbablk between e.block_id and e.block_id + e.blocks -1
order by x.tch desc ;
col OBJECT_NAME for a30
col OBJECT_TYPE for a10
col OWNER for a10
col TCH for 99999
with bh_lc as
(select /*+ ORDERED */
lc.addr, lc.child#, lc.gets, lc.misses, lc.immediate_gets,
lc.immediate_misses, lc.spin_gets, lc.sleeps,
bh.hladdr, bh.tch tch, bh.file#, bh.dbablk, bh.class,
bh.state, bh.obj
from
x$kslld ld,
v$session_wait sw,
v$latch_children lc,
x$bh bh
where lc.addr =sw.p1raw
and sw.p2= ld.indx
and ld.kslldnam='cache buffers chains'
and lower(sw.event) like '%latch%'
--and sw.state='WAITING'
and bh.hladdr=lc.addr
)
select bh_lc.hladdr, bh_lc.tch, o.owner, o.object_name, o.object_type,
bh_lc.child#, bh_lc.gets,
bh_lc.misses, bh_lc.immediate_gets,
bh_lc.immediate_misses, spin_gets, sleeps
from
bh_lc,
dba_objects o
where bh_lc.obj = o.object_id(+)
union
select bh_lc.hladdr, bh_lc.tch, o.owner, o.object_name, o.object_type,
bh_lc.child#, bh_lc.gets, bh_lc.misses, bh_lc.immediate_gets,
bh_lc.immediate_misses, spin_gets, sleeps
from
bh_lc,
dba_objects o
where bh_lc.obj = o.data_object_id(+)
order by 1,2 desc;
************************ holder /waiter ****************************************
SELECT DECODE(request, 0,'HOLDER','WAITER'), sid , lmode, TYPE, ctime
FROM v$lock
WHERE (id1, id2, TYPE ) IN (SELECT id1, id2, TYPE
FROM v$lock
WHERE request>0)
ORDER BY id1, request;
set linesize 150
set echo off
col oruser format a20 heading "Oracle Username"
col osuser format a20 heading "O/S Username"
col obj format a30 heading "Locked Object"
col ss heading "SID/Ser#" format a12
col time heading "Logon Date/Time" format a19
col unix heading "Unix|Process" format a9
set linesize 120
select owner||'.'||object_name obj
,oracle_username||' ('||s.status||')' oruser
,os_user_name osuser
,l.process unix
,s.sid||','||s.serial# as ss
,to_char(s.logon_time,'yyyy/mm/dd hh24:mi:ss') time
from v$locked_object l
,dba_objects o
,v$session s
where l.object_id = o.object_id
and s.sid=l.session_id
order by osuser, ss, obj;
************************get sql from sid ***************************************
set linesize 132
SELECT SQL_TEXT
FROM V$SESSION, V$SQLtext
WHERE V$SESSION.SQL_ADDRESS = V$SQLtext.ADDRESS
AND V$SESSION.SID = &SID
order by piece;
**************************get sql from spid **************************
set linesize 132
SELECT SQL_HASH_VALUE,SQL_TEXT
FROM V$SESSION, V$SQLtext
WHERE V$SESSION.SQL_ADDRESS = V$SQLtext.ADDRESS
AND V$SESSION.SID in ( select a.sid from v$session a, v$process b
where a.paddr=b.addr and b.spid=&spid )
order by piece;
**************************wait events*****************************
col sid format 99999
col event format a30
col state format a25
col p1 format 99999999999999
col p2 format 99999999999999
set pages 150
set lines 100
break on event
compute count of event on event
select sid,event,p1,p2 , p3
from gv$session_wait
where event not like '%message%'
and state = 'WAITING'
order by event;
****************************************** Top 5 events in Database ****************************************
select event, total_waits,
round(time_waited/100) "TIME(s)",
average_wait*10 "AVG(ms)",
TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') time
from v$system_event
where time_waited > 100
and event not in ('rdbms ipc message','pmon timer','control file heartbeat','smon timer')
order by time_waited;
*************************Long Running Sessions*********************
SELECT SID, target || opname target , TOTALWORK, SOFAR, TIME_REMAINING, ELAPSED_SECONDS
FROM gV$SESSION_LONGOPS
where time_remaining > 0
order by time_remaining;
**********************Kill session**********************************
select 'Alter system kill session '||''''||sid||','||serial#||''''||' immediate ;'
from v$session
where sid =&sid;
select 'Alter system kill session '||''''||sid||','||serial#||''''||' immediate ;'
from v$session
where sid in (select sid
from v$session
where status='INACTIVE'
and to_char(logon_time,'dd-mon-yyyy hh24:mi')<'18-jun-2010 18:15'
and username='PROFUNDS');
select 'Alter system kill session '||''''||sid||','||serial#||''''||'immediate ;'
from v$session
where sid in (select sid
from v$session
where status='INACTIVE'
and username='CRMUSER'
and LAST_CALL_ET>3600);
select 'Alter system kill session '||''''||sid||','||serial#||''''||' immediate ;'
from v$session
where sid in (select sid
from v$session
where event in ('db file sequential read','read by other session')
and machine='sf1280-01');
******************** To kill All locked inactive sessions ***************
select OWNER, OBJECT_NAME, SID, SERIAL#,a.logon_time,a.status, locked_mode
from v$session a, v$locked_object b, dba_objects c
where a.SID=b.session_id
and b.OBJECT_ID=c.OBJECT_ID
and a.status='INACTIVE'
and b.ORACLE_USERNAME='IBSLSFA'
order by 5;
******************** No. of open cursor per session ********************
col sid for a12
col program for a30
col username for a15
select substr(a.sid,1,10) sid,
substr(nvl(b.program,machine),1,30) program,
username,
count(*)
from v$open_cursor a, v$session b
where a.saddr=b.saddr
and username='APPS'
group by substr(a.sid,1,10),
substr(nvl(b.program,machine),1,30),username
order by 4 desc ;
set lines 150
set pages 5000
col sql_text for a50
select sid,user_name,sql_id,cnt,(select sql_text
from v$sqlarea
where sql_id=a.sql_id) as sql_text
from (select sid,user_name,sql_id,count(*) as cnt
from v$open_cursor a
group by sid,user_name,sql_id) a
where user_name not in ('SYS','SYSTEM')
order by 4;
select max(a.value) as highest_open_cur, p.value as max_open_cur
from v$sesstat a, v$statname b, v$parameter p
where a.statistic# = b.statistic#
and b.name = 'opened cursors current'
and p.name= 'open_cursors'
group by p.value;
select cach.value cache_hits, prs.value all_parses,prs.value-cach.value sess_cur_cache_not_used
from v$sesstat cach, v$sesstat prs, v$statname nm1, v$statname nm2
where cach.statistic# = nm1.statistic#
and nm1.name = 'session cursor cache hits'
and prs.statistic#=nm2.statistic#
and nm2.name= 'parse count (total)'
and cach.sid= &sid
and prs.sid= cach.sid ;
*************************Table Import*******************************
select substr(sql_text,instr(sql_text,'INTO "'),30) table_name,
rows_processed,
round((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60,1) minutes,
trunc(rows_processed/((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60)) rows_per_min
from sys.v_$sqlarea
where sql_text like 'INSERT %INTO "%'
and command_type = 2
and open_versions > 0;
*********************who is using which UNDO or TEMP SEGMENT**************
col SID_SERIAL for a11
col ORAUSER for a10
col UNDOSEG for a11
col Undo for a5
col PROGRAM for a30
SELECT TO_CHAR(s.sid)||','||TO_CHAR(s.serial#) sid_serial,
NVL(s.username, 'None') orauser,s.program,r.name undoseg,
t.used_ublk * TO_NUMBER(x.value)/1024||'K' "Undo"
FROM sys.v_$rollname r,sys.v_$session s,sys.v_$transaction t,sys.v_$parameter x
WHERE s.taddr = t.addr
AND r.usn = t.xidusn(+)
AND x.name = 'db_block_size'
col username for a10
col SID_SERIAL for a11
col size for a10
col TABLESPACE for a10
col PROGRAM for a40
SELECT b.tablespace,
ROUND(((b.blocks*p.value)/1024/1024),2)||'M' "SIZE",
a.sid||','||a.serial# SID_SERIAL,
a.username,
a.program
FROM sys.v_$session a,
sys.v_$sort_usage b,
sys.v_$parameter p
WHERE p.name = 'db_block_size'
AND a.saddr = b.session_addr
ORDER BY b.tablespace, b.blocks;
col TIME for a10
SELECT sid, to_char(sysdate,'mi:ss') time,round(work_area_size/1048576, 1) work_area_size_mb,
round(max_mem_used/1048576, 1) max_mem_used_mb,
number_passes, nvl(tempseg_size/1048576, 0) tempseg_size_mb
FROM v$sql_workarea_active
ORDER BY 4 desc,sid;
-For values of PAT below 1 GB, _PGA_MAX_SIZE is 200 MB.
-For values of PAT between 1 GB and 2 GB, _PGA_MAX_SIZE is 20% of PAT.
-At values beyond 2 GB, _PGA_MAX_SIZE keeps on growing as PAT is increased,
but at alower rate, such that _PGA_MAX_SIZE is less than 20% of PAT.
-A limit of 480 MB on _PGA_MAX_SIZE takes effect at a PAT value of 4 GB.
-Increasing PAT beyond 4 GB does not result in higher values of _PGA_MAX_SIZE than 480 MB.
-In Oracle9i, _PGA_MAX_SIZE had a limit of 200 MB.
Process memory usage - AIX
sum2.awk
{
sum += $5
}
END {
print "Sum in MB: ", sum/1024;
print "Average in MB:", sum/NR/1024;
}
Total : ps -A -o user,pid,pcpu,pmem,vsz,time,args | grep EBA | sort +4 | awk -f sum2.awk
Only Local : ps -A -o user,pid,pcpu,pmem,vsz,time,args | grep EBA | grep "LOCAL" | sort +4 | awk -f sum2.awk
******************** Find out required space for undo tablespace if undo retention period is known ********************
SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
(TO_NUMBER(e.value) * TO_NUMBER(f.value) *
g.undo_block_per_sec) / (1024*1024)
"NEEDED UNDO SIZE [MByte]"
FROM (
SELECT SUM(a.bytes) undo_size
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#
) d,
v$parameter e,
v$parameter f,
(
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
undo_block_per_sec
FROM v$undostat
) g
WHERE e.name = 'undo_retention'
AND f.name = 'db_block_size'
/
**************************** OR (Dynamicly get undo retention time in hrs) *********************************
SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
(&reqiered_undoretentionin_hrs*60*60 * TO_NUMBER(f.value) *
g.undo_block_per_sec) / (1024*1024)
"NEEDED UNDO SIZE [MByte]"
FROM (
SELECT SUM(a.bytes) undo_size
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#
) d,
v$parameter e,
v$parameter f,
(
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
undo_block_per_sec
FROM v$undostat
) g
WHERE e.name = 'undo_retention'
AND f.name = 'db_block_size'
/
************************how full is current log file***********************
SELECT le.leseq "Current log sequence No",
100*cp.cpodr_bno/le.lesiz "Percent Full",
cp.cpodr_bno "Current Block No",
le.lesiz "Size of Log in Blocks"
FROM x$kcccp cp, x$kccle le
WHERE le.leseq =CP.cpodr_seq
AND bitand(le.leflg,24) = 8
********************DISTINCT PARTITIONS**********************************
select distinct substr(name,1,instr(name,'/',-1,1)-1) partion
from v$datafile
union
select substr(name,1,instr(name,'/',-1,1)-1) partion
from V$tempfile
union
select substr(name,1,instr(name,'/',-1,1)-1) partion
from v$controlfile
union
select substr(member,1,instr(member,'/',-1,1)-1) partion
from v$logfile;
**********************CRETAE TABLESPACE***********************************
create tablespace &TABLESPACE_NAME datafile '&datafile' size &size
segment space management auto
extent management local
uniform size 1M
***********************ADD DATAFILE****************************************
alter tablespace &TABLESPACE_NAME add datafile '&DATAFILE_NAME' size &SIZE
************************privileges*****************************************
select grantee,privilege from dba_sys_privs where privilege like '%&NAME%';
************************aud.sql********************************************
set lines 122
col grantee for a8
col grantor for a8
col owner for a10
col granted_role for a10
col temporary_tablespace for a5
col default_tablespace for a12
col privilege for a10
col profile for a27
Select grantee, granted_role
from dba_role_privs
where grantee in ('RQUERY','BQUERY','HQUERY');
Select userid, scope
from SYSTEM.PRODUCT_USER_PROFILE
where userid in ('RQUERY','BQUERY','HQUERY');
Select grantee,owner,table_name,grantor,SELECT_PRIV,
INSERT_PRIV,DELETE_PRIV,UPDATE_PRIV, REFERENCES_PRIV,
ALTER_PRIV,INDEX_PRIV
from table_privileges
where grantee in ('RQUERY','BQUERY','HQUERY');
Select *
from dba_tab_privs
where Owner != 'SYS'
and Owner != 'SYSTEM'
and Grantable ='YES';
select *
from dba_tab_privs
where table_name in ('UTL_TCP','UTL_HTTP','UTL_FILE','UTL_SMTP','UTL_FILE_DIR');
SELECT GRANTEE
FROM DBA_SYS_PRIVS
WHERE PRIVILEGE IN ('CREATE LIBRARY', 'ALTER SYSTEM');
Select username,PROFILE, Default_tablespace, temporary_tablespace
from dba_users
where username in ('RQUERY','BQUERY','HQUERY');
*************************FREE TABLESPACE QUERIES****************************
SELECT Total.tablespace_name "Tablespace Name",
Free_space, Max_Size, (total_space-Free_space) Used_space, total_space
FROM
(select tablespace_name, sum(bytes/1024/1024) Free_Space,max(bytes/1024/1024) Max_Size
from sys.dba_free_space
group by tablespace_name
) Free,
(select tablespace_name, sum(bytes/1024/1024) TOTAL_SPACE
from sys.dba_data_files
group by tablespace_name
) Total
WHERE Free.Tablespace_name = Total.tablespace_name;
OR
ttitle -
center 'Database Freespace Summary' skip 2
comp sum of nfrags totsiz avasiz on report
break on report
col tsname format a16 justify c heading 'Tablespace'
col nfrags format 999,990 justify c heading 'Free|Frags'
col mxfrag format 999,999,990 justify c heading 'Largest|Frag (KB)'
col totsiz format 999,999,990 justify c heading 'Total|(KB)'
col avasiz format 999,999,990 justify c heading 'Available|(KB)'
col pctusd format 990 justify c heading 'Pct|Used'
select total.tablespace_name tsname,
count(free.bytes) nfrags,
nvl(max(free.bytes)/1024,0) mxfrag,
total.bytes/1024 totsiz,
nvl(sum(free.bytes)/1024,0) avasiz,
(1-nvl(sum(free.bytes),0)/total.bytes)*100 pctusd
from dba_data_files total, dba_free_space free
where total.tablespace_name = free.tablespace_name(+)
and total.file_id=free.file_id(+)
group by total.tablespace_name,total.bytes;
OR
with ts as
(select name,blocksize,ts#,bitmapped from ts$)
select /*+ RULE */ a.name "Tablespace Name",total "Total Space",total-free "Used Space",free "Free Space",(free/total)*100 "% Free" from
(select
ts.name,
sum(f.length * ts.blocksize/1024/1024) free
from ts,sys.fet$ f
where ts.ts# = f.ts#
and ts.bitmapped = 0
group by ts.name
)a,
(select ts.name,
sum((ts.blocksize * f.blocks)/1024/1024) Total
from sys.file$ f,ts
where f.ts# = ts.ts#
and bitmapped=0
group by ts.name
)b
where a.name=b.name
union all
SELECT name,Total,total-free,free,(free/total)*100 "% used" from
(select name,
sum(round(KTFBHCSZ*blocksize/1024/1024,2)) total,
sum(round(KTFBHCFREE*blocksize/1024/1024,2)) free
FROM x$ktfbhc,ts where ts#=KTFBHCTSN
group by name )order by 5;
******************************* Check Table Fragmentation If analyzed ****************************************
select owner,table_name,last_analyzed,
round((blocks*&blksize_KB/1024),2) "Total Table size MB",
round(((num_rows*avg_row_len/1024/1024)+(blocks*8/1024/&pctfree)),2) "Actual Table size MB"
from dba_tables
where table_name='&table_name';
**************************archive generation**************************
select trunc(first_time) on_date,
thread# thread,
min(sequence#) min_sequence,
max(sequence#) max_sequence,
max(sequence#) - min(sequence#) nos_archives,
(max(sequence#) - min(sequence#)) * log_avg_mb req_space_mb
from v$log_history,
(select avg(bytes/1024/1024) log_avg_mb
from gv$log)
group by trunc(first_time), thread#, log_avg_mb
order by trunc(first_time);
HOURLY Statistics:
select to_char(first_time,'dd-mon-yy hh24') on_date,
min(sequence#) min_sequence,
max(sequence#) max_sequence,
max(sequence#) - min(sequence#) nos_archives,
(max(sequence#) - min(sequence#)) * log_avg_mb req_space_mb
from v$log_history,
(select avg(bytes/1024/1024) log_avg_mb
from v$log)
where trunc(first_time)='18-04-2020'
group by to_char(first_time,'dd-mon-yy hh24'), thread#, log_avg_mb
****************************** Archive Apply timing details *************************************************
alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss';
set lines 150
set pages 5000
select TIMESTAMP,completion_time "ArchTime",
SEQUENCE#,round((blocks*block_size)/(1024*1024),1) "SizeM",
round((TIMESTAMP-lag(TIMESTAMP,1,TIMESTAMP) OVER (order by TIMESTAMP))*24*60*60,1) "Diff(sec)",
round((blocks*block_size)/1024/ decode(((TIMESTAMP-lag(TIMESTAMP,1,TIMESTAMP)
OVER (order by TIMESTAMP))*24*60*60),0,1,
(TIMESTAMP-lag(TIMESTAMP,1,TIMESTAMP) OVER (order by TIMESTAMP))*24*60*60),1) "KB/sec",
round((blocks*block_size)/(1024*1024)/ decode(((TIMESTAMP-lag(TIMESTAMP,1,TIMESTAMP)
OVER (order by TIMESTAMP))*24*60*60),0,1,
(TIMESTAMP-lag(TIMESTAMP,1,TIMESTAMP) OVER (order by TIMESTAMP))*24*60*60),3) "MB/sec",
round(((lead(TIMESTAMP,1,TIMESTAMP) over (order by TIMESTAMP))-completion_time)*24*60*60,1) "Lag(sec)"
from v$archived_log a, v$dataguard_status dgs
where a.name = replace(dgs.MESSAGE,'Media Recovery Log ','')
and dgs.FACILITY = 'Log Apply Services'
order by TIMESTAMP desc;
*********************** Redo genrated per session *****************************
set linesize 200
col module format a20
col osuser format a20
select * from
(
sELECT s.sid, module, osuser,username,status, sql_hash_value, value redo, to_char(logon_time, 'dd/mm/yyyy hh24:mi:ss') lt
FROM gv$session s, gv$sesstat ss, gv$statname sn
WHERE s.sid = ss.sid
AND ss.statistic# = sn.statistic#
AND name = 'redo size'
ORDER BY redo desc
)
where rownum <=5
/
set linesize 120
col module format a20
select * from
(
SELECT distinct s.SESSION_ID, module, USER_ID,s.sql_id,value redo, to_char(SAMPLE_TIME, 'dd/mm/yyyy hh24:mi:ss') lt
FROM DBA_HIST_ACTIVE_SESS_HISTORY s, DBA_HIST_SYSSTAT ss
WHERE s.SNAP_ID = ss.SNAP_ID
AND ss.STAT_NAME = 'redo size'
and s.SNAP_ID between 21774 and 21776
ORDER BY redo desc
)
where rownum <=5
/
******************* See Logfile Contents using LogMnr **************************
exec DBMS_LOGMNR.ADD_LOGFILE('/dev/vgredo01/rlog1a');
exec DBMS_LOGMNR.START_LOGMNR(options => dbms_logmnr.dict_from_online_catalog);
For Pretty output:
exec dbms_logmnr.start_logmnr(options =>dbms_logmnr.dict_from_online_catalog + dbms_logmnr.print_pretty_sql + dbms_logmnr.no_sql_delimiter);
select username, to_char(timestamp,'mm/dd/yy hh24:mi:ss') timestamp,
seg_type_name, seg_name, table_space, session# SID, serial#
from v$logmnr_contents
where seg_owner = 'PRIMECC'
and operation in ('UPDATE','INSERT');
select sql_undo, sql_redo
from v$logmnr_contents
where seg_owner = 'PRIMECC'
and operation = 'UPDATE';
For Remote mining :
exec dbms_logmnr_d.build (options => dbms_logmnr_d.store_in_redo_logs);
exec dbms_logmnr.start_logmnr(options => dbms_logmnr.dict_from_redo_logs);
OR
utl_file_dir = '/tmp'
exec dbms_logmnr_d.build (dictionary_filename => 'ora_dict.txt',dictionary_location => '/tmp');
dbms_logmnr.start_logmnr(dictfilename => '/tmp/ora_dict.txt');
execute dbms_logmnr.end_logmnr;
***************************CREATE DATABASE*************************************
CREATE DATABASE PROFUNDS
LOGFILE
GROUP 1 ('/profundsdb1/PROFUNDS/oradata/log/PROFUNDS_redo1a.log',
'/profundsarch/PROFUNDS/log/PROFUNDS_redo1b.log') SIZE 100M ,
GROUP 2 ('/profundsdb1/PROFUNDS/oradata/log/PROFUNDS_redo2a.log',
'/profundsarch/PROFUNDS/log/PROFUNDS_redo2b.log') SIZE 100M ,
GROUP 3 ('/profundsdb1/PROFUNDS/oradata/log/PROFUNDS_redo3a.log',
'/profundsarch/PROFUNDS/log/PROFUNDS_redo3b.log') SIZE 100M ,
GROUP 4 ('/profundsdb1/PROFUNDS/oradata/log/PROFUNDS_redo4a.log',
'/profundsarch/PROFUNDS/log/PROFUNDS_redo4b.log') SIZE 100M ,
GROUP 5 ('/profundsdb1/PROFUNDS/oradata/log/PROFUNDS_redo5a.log',
'/profundsarch/PROFUNDS/log/PROFUNDS_redo5b.log') SIZE 100M
MAXLOGFILES 50
MAXLOGHISTORY 2336
MAXDATAFILES 1024
MAXINSTANCES 4
NOARCHIVELOG
CHARACTER SET US7ASCII
DATAFILE
'/profundsdb1/PROFUNDS/oradata/data/PROFUNDS_SYSTEM_01.dbf' size 1000M
Extent management local
sysaux datafile '/profundsdb1/PROFUNDS/oradata/data/PROFUNDS_SYSAUX_01.dbf' size 1000m
DEFAULT TEMPORARY TABLESPACE LMTEMP tempfile
'/profundsdb1/PROFUNDS/oradata/data/PROFUNDS_LMTEMP_01.dbf' size 1000M
Extent management local uniform size 1m
UNDO TABLESPACE UNDOTBS datafile
'/profundsdb1/PROFUNDS/oradata/data/PROFUNDS_UNDOTBS_01.dbf' size 1000M;
****************************** Change parameters for Oracle 10G database *********************************
exec DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(retention=>86400);
exec DBMS_STATS.SET_PARAM('METHOD_OPT','for all columns size 1');
exec DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');
******************************PARALLEL Query**********************************
set lines 200
column OSUSER format a25
column child_wait format a30
column parent_wait format a30
column server_name format a4 heading 'Name'
column x_status format a10 heading 'Status'
column schemaname format a10 heading 'Schema'
column x_sid format 9990 heading 'Sid'
column x_pid format 9990 heading 'Pid'
column p_sid format 9990 heading 'Parent'
break on p_sid skip 1
select x.server_name
, x.status as x_status
, x.pid as x_pid
, x.sid as x_sid
, w2.sid as p_sid
, v.osuser
, v.schemaname
, w1.event as child_wait
, w2.event as parent_wait
from gv$px_process x
, gv$lock l
, gv$session v
, gv$session_wait w1
, gv$session_wait w2
where x.sid <> l.sid(+)
and to_number (substr(x.server_name,2)) = l.id2(+)
and x.sid = w1.sid(+)
and l.sid = w2.sid(+)
and x.sid = v.sid(+)
and nvl(l.type,'PS') = 'PS'
order by 1,2
/
col username for a12
col "QC SID" for A6
col "SID" for A6
col "QC/Slave" for A8
col "Req. DOP" for 9999
col "Actual DOP" for 9999
col "Slaveset" for A8
col "Slave INST" for A9
col "QC INST" for A6
set pages 300 lines 300
col wait_event format a30
select
decode(px.qcinst_id,NULL,username,
' - '||lower(substr(pp.SERVER_NAME,
length(pp.SERVER_NAME)-4,4) ) )"Username",
decode(px.qcinst_id,NULL, 'QC', '(Slave)') "QC/Slave" ,
to_char( px.server_set) "SlaveSet",
to_char(s.sid) "SID",
to_char(px.inst_id) "Slave INST",
decode(sw.state,'WAITING', 'WAIT', 'NOT WAIT' ) as STATE,
case sw.state WHEN 'WAITING' THEN substr(sw.event,1,30) ELSE NULL end as wait_event ,
decode(px.qcinst_id, NULL ,to_char(s.sid) ,px.qcsid) "QC SID",
to_char(px.qcinst_id) "QC INST",
px.req_degree "Req. DOP",
px.degree "Actual DOP"
from gv$px_session px,
gv$session s ,
gv$px_process pp,
gv$session_wait sw
where px.sid=s.sid (+)
and px.serial#=s.serial#(+)
and px.inst_id = s.inst_id(+)
and px.sid = pp.sid (+)
and px.serial#=pp.serial#(+)
and sw.sid = s.sid
and sw.inst_id = s.inst_id
order by
decode(px.QCINST_ID, NULL, px.INST_ID, px.QCINST_ID),
px.QCSID,
decode(px.SERVER_GROUP, NULL, 0, px.SERVER_GROUP),
px.SERVER_SET,
px.INST_ID
/
set pages 300 lines 300
col wait_event format a30
select
sw.SID as RCVSID,
decode(pp.server_name,
NULL, 'A QC',
pp.server_name) as RCVR,
sw.inst_id as RCVRINST,
case sw.state WHEN 'WAITING' THEN substr(sw.event,1,30) ELSE NULL end as wait_event ,
decode(bitand(p1, 65535),
65535, 'QC',
'P'||to_char(bitand(p1, 65535),'fm000')) as SNDR,
bitand(p1, 16711680) - 65535 as SNDRINST,
decode(bitand(p1, 65535),
65535, ps.qcsid,
(select
sid
from
gv$px_process
where
server_name = 'P'||to_char(bitand(sw.p1, 65535),'fm000') and
inst_id = bitand(sw.p1, 16711680) - 65535)
) as SNDRSID,
decode(sw.state,'WAITING', 'WAIT', 'NOT WAIT' ) as STATE
from
gv$session_wait sw,
gv$px_process pp,
gv$px_session ps
where
sw.sid = pp.sid (+) and
sw.inst_id = pp.inst_id (+) and
sw.sid = ps.sid (+) and
sw.inst_id = ps.inst_id (+) and
p1text = 'sleeptime/senderid' and
bitand(p1, 268435456) = 268435456
order by
decode(ps.QCINST_ID, NULL, ps.INST_ID, ps.QCINST_ID),
ps.QCSID,
decode(ps.SERVER_GROUP, NULL, 0, ps.SERVER_GROUP),
ps.SERVER_SET,
ps.INST_ID
/
set pages 300 lines 300
col "Username" for a12
col "QC/Slave" for A8
col "Slaveset" for A8
col "Slave INST" for A9
col "QC SID" for A6
col "QC INST" for A6
col "operation_name" for A30
col "target" for A30
select
decode(px.qcinst_id,NULL,username,
' - '||lower(substr(pp.SERVER_NAME,
length(pp.SERVER_NAME)-4,4) ) )"Username",
decode(px.qcinst_id,NULL, 'QC', '(Slave)') "QC/Slave" ,
to_char( px.server_set) "SlaveSet",
to_char(px.inst_id) "Slave INST",
substr(opname,1,30) operation_name,
substr(target,1,30) target,
sofar,
totalwork,
units,
start_time,
timestamp,
decode(px.qcinst_id, NULL ,to_char(s.sid) ,px.qcsid) "QC SID",
to_char(px.qcinst_id) "QC INST"
from gv$px_session px,
gv$px_process pp,
gv$session_longops s
where px.sid=s.sid
and px.serial#=s.serial#
and px.inst_id = s.inst_id
and px.sid = pp.sid (+)
and px.serial#=pp.serial#(+)
order by
decode(px.QCINST_ID, NULL, px.INST_ID, px.QCINST_ID),
px.QCSID,
decode(px.SERVER_GROUP, NULL, 0, px.SERVER_GROUP),
px.SERVER_SET,
px.INST_ID
/
***************** Take a log before down any production database for any activity. ******************
set echo on;
set pagesize 150;
spool FINN_1OCT2010.log
select * from v$database;
select * from v$instance;
select * from v$version;
select * from v$controlfile;
select * from v$log;
select * from v$logfile;
select * from v$recover_file;
select file#,error,fuzzy from v$datafile_header;
select * from dba_data_files order by file_name;
select * from dba_temp_files order by file_name;
select file_name, status, bytes/1024/1024 sz_mb
from dba_data_files order by file_name;
select file_name, status, bytes/1024/1024 sz_mb
from dba_temp_files order by file_name;
select username, status, count(*) from v$session
where username is not null
group by username, status;
archive log list;
show parameter background;
show parameter;
--Ensure that the state is IDLE for all active processes.
select process, status, state from v$archive_processes;
alter system checkpoint;
alter system checkpoint;
alter system checkpoint;
alter system switch logfile;
alter database backup controlfile to trace;
select file_name from dba_data_files;
select distinct status from v$backup;
select distinct status from v$datafile;
select distinct status from v$datafile_header;
select * from v$recover_file;
select name , open_mode from v$database;
set linesize 100
set linesize 100
alter session set NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';
select usn, state, undoblockstotal "Total", undoblocksdone "Done", undoblockstotal-undoblocksdone "ToDo",
decode(cputime,0,'unknown',sysdate+(((undoblockstotal-undoblocksdone) / (undoblocksdone / cputime)) / 86400)) "Estimated time to complete" from v$fast_start_transactions;
select a.sid, a.username, b.xidusn, b.used_urec, b.used_ublk from v$session a, v$transaction b where a.saddr=b.ses_addr;
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
---------ENSURE if the used_ublk is large, it means that particular session will require more rollback if killed.
--------*********************************************************************************************************
select a.sid, a.username, b.xidusn, b.used_urec, b.used_ublk from v$session a, v$transaction b where a.saddr=b.ses_addr;
select ktuxeusn, to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') "Time", ktuxesiz, ktuxesta
from x$ktuxe
where ktuxecfl = 'DEAD';
spool off
**************************************** Shutdown Issue Resolving Steps *****************************************
Shut down immediate may hang/slowdown because of various reasons.
- processes still continue to be connected to the database and do not terminate.
- SMON is cleaning temp segments or performing delayed block cleanouts.
- Uncommitted transactions are being rolled back.
Perform the below tracing while shutting down the database.
select count(*) from v$session_longops where time_remaining>0;
select sum(used_ublk) from v$transaction;
select * from v$fast_start_transactions;
select * from v$fast_start_servers;
Enable Tracing:
alter session set tracefile_identifier='10046';
alter session set timed_statistics = true;
alter session set statistics_level=all;
alter session set max_dump_file_size = unlimited;
alter session set events '10046 trace name context forever,level 12';
alter session set events '10400 trace name context forever, level 1';
shutdown immediate;
Note:
10046 turns on extended SQL_TRACE for the shutdown process.
10400 dumps a systemstate every 5 minutes.
***************************** Rollback Remaining ****************************
select usn, state, undoblockstotal "Total", undoblocksdone "Done", undoblockstotal-undoblocksdone "ToDo",
decode(cputime,0,'unknown',
sysdate+(((undoblockstotal-undoblocksdone) / (undoblocksdone / cputime)) / 86400)) "Estimated time to complete"
from v$fast_start_transactions;
select a.sid, a.username, b.xidusn, b.used_urec, b.used_ublk
from v$session a, v$transaction b
where a.saddr=b.ses_addr;
****************Sessions waiting for a Library Cache Pin: *****************
select sid Waiter,
substr(rawtohex(p1),1,30) Handle,
substr(rawtohex(p2),1,30) Pin_addr
from v$session_wait where wait_time=0 and event like 'library cache pin%';
********Objets concerning to Library Cache Pin we are waiting for:*********
select to_char(SESSION_ID,'999') sid ,
substr(LOCK_TYPE,1,30) Type,
substr(lock_id1,1,23) Object_Name,
substr(mode_held,1,4) HELD, substr(mode_requested,1,4) REQ,
lock_id2 Lock_addr
from dba_lock_internal
where
mode_requested<>'None'
and mode_requested<>mode_held
and session_id in ( select sid
from v$session_wait where wait_time=0
and event like 'library cache pin%');
*********Library Cache Pin holders we are wainting for:********************
select sid Holder ,KGLPNUSE Sesion , KGLPNMOD Held, KGLPNREQ Req
from x$kglpn , v$session
where KGLPNHDL in (select p1raw from v$session_wait
where wait_time=0 and event like 'library cache pin%')
and KGLPNMOD <> 0
and v$session.saddr=x$kglpn.kglpnuse;
*****************What are the holders waiting for?**************************
select sid,substr(event,1,30),wait_time
from v$session_wait
where sid in (select sid from x$kglpn , v$session
where KGLPNHDL in (select p1raw from v$session_wait
where wait_time=0 and event like 'library cache pin%')
and KGLPNMOD <> 0
and v$session.saddr=x$kglpn.kglpnuse );
***********************To identify SQL: ************************************
select sid,sql_text
from v$session, v$sqlarea
where v$session.sql_address=v$sqlarea.address
and sid=&sid;
************************To get Enquence name********************************
col Name format a4
select sid,chr(bitand(p1, -16777216)/16777215) || chr(bitand(p1,16711680)/65535) "Name",
(bitand(p1, 65535)) "Mode"from
v$session_wait
where event = 'enqueue';
OR
set lines 200
set pagesize 66
break on Kill on sid on username on terminal
column Kill heading 'Kill String' format a13
column res heading 'Resource Type' format 999
column id1 format 9999990
column id2 format 9999990
column locking heading 'Lock Held/Lock Requested' format a40
column lmode heading 'Lock Held' format a20
column request heading 'Lock Requested' format a20
column serial# format 99999
column username format a10 heading "Username"
column terminal heading Term format a6
column tab format a30 heading "Table Name"
column owner format a9
column LAddr heading "ID1 - ID2" format a18
column Lockt heading "Lock Type" format a40
column command format a25
column sid format 990
select
nvl(S.USERNAME,'Internal') username,
L.SID,
nvl(S.TERMINAL,'None') terminal,
decode(command,
0,'None',decode(l.id2,0,U1.NAME||'.'||substr(T1.NAME,1,20),'None')) tab,
decode(command,
0,'BACKGROUND',
1,'Create Table',
2,'INSERT',
3,'SELECT',
4,'CREATE CLUSTER',
5,'ALTER CLUSTER',
6,'UPDATE',
7,'DELETE',
8,'DROP',
9,'CREATE INDEX',
10,'DROP INDEX',
11,'ALTER INDEX',
12,'DROP TABLE',
13,'CREATE SEQUENCE',
14,'ALTER SEQUENCE',
15,'ALTER TABLE',
16,'DROP SEQUENCE',
17,'GRANT',
18,'REVOKE',
19,'CREATE SYNONYM',
20,'DROP SYNONYM',
21,'CREATE VIEW',
22,'DROP VIEW',
23,'VALIDATE INDEX',
24,'CREATE PROCEDURE',
25,'ALTER PROCEDURE',
26,'LOCK TABLE',
27,'NO OPERATION',
28,'RENAME',
29,'COMMENT',
30,'AUDIT',
31,'NOAUDIT',
32,'CREATE EXTERNAL DATABASE',
33,'DROP EXTERNAL DATABASE',
34,'CREATE DATABASE',
35,'ALTER DATABASE',
36,'CREATE ROLLBACK SEGMENT',
37,'ALTER ROLLBACK SEGMENT',
38,'DROP ROLLBACK SEGMENT',
39,'CREATE TABLESPACE',
40,'ALTER TABLESPACE',
41,'DROP TABLESPACE',
42,'ALTER SESSION',
43,'ALTER USER',
44,'COMMIT',
45,'ROLLBACK',
46,'SAVEPOINT',
47,'PL/SQL EXECUTE',
48,'SET TRANSACTION',
49,'ALTER SYSTEM SWITCH LOG',
50,'EXPLAIN',
51,'CREATE USER',
52,'CREATE ROLE',
53,'DROP USER',
54,'DROP ROLE',
55,'SET ROLE',
56,'CREATE SCHEMA',
57,'CREATE CONTROL FILE',
58,'ALTER TRACING',
59,'CREATE TRIGGER',
60,'ALTER TRIGGER',
61,'DROP TRIGGER',
62,'ANALYZE TABLE',
63,'ANALYZE INDEX',
64,'ANALYZE CLUSTER',
65,'CREATE PROFILE',
66,'DROP PROFILE',
67,'ALTER PROFILE',
68,'DROP PROCEDURE',
69,'DROP PROCEDURE',
70,'ALTER RESOURCE COST',
71,'CREATE SNAPSHOT LOG',
72,'ALTER SNAPSHOT LOG',
73,'DROP SNAPSHOT LOG',
74,'CREATE SNAPSHOT',
75,'ALTER SNAPSHOT',
76,'DROP SNAPSHOT',
79,'ALTER ROLE',
85,'TRUNCATE TABLE',
86,'TRUNCATE CLUSTER',
87,'-',
88,'ALTER VIEW',
89,'-',
90,'-',
91,'CREATE FUNCTION',
92,'ALTER FUNCTION',
93,'DROP FUNCTION',
94,'CREATE PACKAGE',
95,'ALTER PACKAGE',
96,'DROP PACKAGE',
97,'CREATE PACKAGE BODY',
98,'ALTER PACKAGE BODY',
99,'DROP PACKAGE BODY',
command||' - ???') COMMAND,
decode(L.LMODE,1,'No Lock',
2,'Row Share',
3,'Row Exclusive',
4,'Share',
5,'Share Row Exclusive',
6,'Exclusive','NONE') lmode,
decode(L.REQUEST,1,'No Lock',
2,'Row Share',
3,'Row Exclusive',
4,'Share',
5,'Share Row Exclusive',
6,'Exclusive','NONE') request,
l.id1||'-'||l.id2 Laddr,
l.type||' - '||
decode(l.type,
'BL','Buffer hash table instance lock',
'CF',' Control file schema global enqueue lock',
'CI','Cross-instance function invocation instance lock',
'CS','Control file schema global enqueue lock',
'CU','Cursor bind lock',
'DF','Data file instance lock',
'DL','Direct loader parallel index create',
'DM','Mount/startup db primary/secondary instance lock',
'DR','Distributed recovery process lock',
'DX','Distributed transaction entry lock',
'FI','SGA open-file information lock',
'FS','File set lock',
'HW','Space management operations on a specific segment lock',
'IN','Instance number lock',
'IR','Instance recovery serialization global enqueue lock',
'IS','Instance state lock',
'IV','Library cache invalidation instance lock',
'JQ','Job queue lock',
'KK','Thread kick lock',
'MB','Master buffer hash table instance lock',
'MM','Mount definition gloabal enqueue lock',
'MR','Media recovery lock',
'PF','Password file lock',
'PI','Parallel operation lock',
'PR','Process startup lock',
'PS','Parallel operation lock',
'RE','USE_ROW_ENQUEUE enforcement lock',
'RT','Redo thread global enqueue lock',
'RW','Row wait enqueue lock',
'SC','System commit number instance lock',
'SH','System commit number high water mark enqueue lock',
'SM','SMON lock',
'SN','Sequence number instance lock',
'SQ','Sequence number enqueue lock',
'SS','Sort segment lock',
'ST','Space transaction enqueue lock',
'SV','Sequence number value lock',
'TA','Generic enqueue lock',
'TD','DDL enqueue lock',
'TE','Extend-segment enqueue lock',
'TM','DML enqueue lock',
'TO','Temporary Table Object Enqueue',
'TT','Temporary table enqueue lock',
'TX','Transaction enqueue lock',
'UL','User supplied lock',
'UN','User name lock',
'US','Undo segment DDL lock',
'WL','Being-written redo log instance lock',
'WS','Write-atomic-log-switch global enqueue lock',
'TS',decode(l.id2,0,'Temporary segment enqueue lock (ID2=0)',
'New block allocation enqueue lock (ID2=1)'),
'LA','Library cache lock instance lock (A=namespace)',
'LB','Library cache lock instance lock (B=namespace)',
'LC','Library cache lock instance lock (C=namespace)',
'LD','Library cache lock instance lock (D=namespace)',
'LE','Library cache lock instance lock (E=namespace)',
'LF','Library cache lock instance lock (F=namespace)',
'LG','Library cache lock instance lock (G=namespace)',
'LH','Library cache lock instance lock (H=namespace)',
'LI','Library cache lock instance lock (I=namespace)',
'LJ','Library cache lock instance lock (J=namespace)',
'LK','Library cache lock instance lock (K=namespace)',
'LL','Library cache lock instance lock (L=namespace)',
'LM','Library cache lock instance lock (M=namespace)',
'LN','Library cache lock instance lock (N=namespace)',
'LO','Library cache lock instance lock (O=namespace)',
'LP','Library cache lock instance lock (P=namespace)',
'LS','Log start/log switch enqueue lock',
'PA','Library cache pin instance lock (A=namespace)',
'PB','Library cache pin instance lock (B=namespace)',
'PC','Library cache pin instance lock (C=namespace)',
'PD','Library cache pin instance lock (D=namespace)',
'PE','Library cache pin instance lock (E=namespace)',
'PF','Library cache pin instance lock (F=namespace)',
'PG','Library cache pin instance lock (G=namespace)',
'PH','Library cache pin instance lock (H=namespace)',
'PI','Library cache pin instance lock (I=namespace)',
'PJ','Library cache pin instance lock (J=namespace)',
'PL','Library cache pin instance lock (K=namespace)',
'PK','Library cache pin instance lock (L=namespace)',
'PM','Library cache pin instance lock (M=namespace)',
'PN','Library cache pin instance lock (N=namespace)',
'PO','Library cache pin instance lock (O=namespace)',
'PP','Library cache pin instance lock (P=namespace)',
'PQ','Library cache pin instance lock (Q=namespace)',
'PR','Library cache pin instance lock (R=namespace)',
'PS','Library cache pin instance lock (S=namespace)',
'PT','Library cache pin instance lock (T=namespace)',
'PU','Library cache pin instance lock (U=namespace)',
'PV','Library cache pin instance lock (V=namespace)',
'PW','Library cache pin instance lock (W=namespace)',
'PX','Library cache pin instance lock (X=namespace)',
'PY','Library cache pin instance lock (Y=namespace)',
'PZ','Library cache pin instance lock (Z=namespace)',
'QA','Row cache instance lock (A=cache)',
'QB','Row cache instance lock (B=cache)',
'QC','Row cache instance lock (C=cache)',
'QD','Row cache instance lock (D=cache)',
'QE','Row cache instance lock (E=cache)',
'QF','Row cache instance lock (F=cache)',
'QG','Row cache instance lock (G=cache)',
'QH','Row cache instance lock (H=cache)',
'QI','Row cache instance lock (I=cache)',
'QJ','Row cache instance lock (J=cache)',
'QL','Row cache instance lock (K=cache)',
'QK','Row cache instance lock (L=cache)',
'QM','Row cache instance lock (M=cache)',
'QN','Row cache instance lock (N=cache)',
'QO','Row cache instance lock (O=cache)',
'QP','Row cache instance lock (P=cache)',
'QQ','Row cache instance lock (Q=cache)',
'QR','Row cache instance lock (R=cache)',
'QS','Row cache instance lock (S=cache)',
'QT','Row cache instance lock (T=cache)',
'QU','Row cache instance lock (U=cache)',
'QV','Row cache instance lock (V=cache)',
'QW','Row cache instance lock (W=cache)',
'QX','Row cache instance lock (X=cache)',
'QY','Row cache instance lock (Y=cache)',
'QZ','Row cache instance lock (Z=cache)','????') Lockt
from V$LOCK L,
V$SESSION S,
SYS.USER$ U1,
SYS.OBJ$ T1
where L.SID = S.SID
and T1.OBJ# = decode(L.ID2,0,L.ID1,1)
and U1.USER# = T1.OWNER#
and S.TYPE != 'BACKGROUND'
order by 1,2,5
/
*********************** Waiting Session - enq: TX - contention event ************************
SELECT sid, CHR (BITAND (p1,-16777216) / 16777215) ||
CHR (BITAND (p1, 16711680) / 65535) enq,
DECODE (BITAND (p1, 65535), 1, 'Null', 2, 'Sub-Share',
3, 'Sub-Exclusive', 4, 'Share', 5, 'Share/Sub-Exclusive',
6, 'Exclusive', 'Other') lock_mode
FROM v$session_wait WHERE sid = 481;
set linesize 100
set pagesize 66
col c1 for a15
col c1 heading "Program Name "
select l.inst_id,l.SID,program c1,l.TYPE,l.ID1,l.ID2,l.LMODE,l.REQUEST
from gv$lock l,gv$session s
where l.type like 'TX' and l.REQUEST =6
and l.inst_id=s.inst_id and l.sid=s.sid
order by id1
/
*************** Holding Session - enq: TX - contention event **********************
select sid,serial#, status
from v$session where taddr in
(select b.addr from v$session_wait a,v$transaction b
where a.event='enq: TX - contention' and trunc(a.p2/power(2,16)) = xidusn
and (bitand(a.p2,to_number('ffff','xxxx'))+0) = xidslot and a.p3 = xidsqn);
select sid,id1,id2,trunc(id1/power(2,16)) rbs,bitand(id1,to_number('ffff','xxxx'))+0 slot,
id2 seq,lmode,request,type
from v$lock
where type = 'TX' and sid=&sid;
select sid,trunc(id1/power(2,16)) rbs,bitand(id1,to_number('ffff','xxxx'))+0 slot,id2 seq,lmode,request
from v$lock where type='TX' and id1=&id1 and id2=&id2;
set linesize 100
set pagesize 66
col c1 for a15
col c1 heading "Program Name "
select l.inst_id,l.SID,program c1,l.TYPE,l.ID1,l.ID2,l.LMODE,l.REQUEST
from gv$lock l,gv$session s
where l.type like 'TX' and l.LMODE =6 and (l.ID1,l.ID2) in
(select id1,id2 from gv$lock where type like 'TX' and REQUEST =6)
and l.inst_id=s.inst_id and l.sid=s.sid
order by id1
/
*****************Oracle EXPORT using OS compression online****************************
/etc/mknod per1 p
nohup compress < per1 > exp_CIBIL_DETAIL_RAPG_22mar09.comp &
sleep 120
exp \"/ as sysdba\"
file=per1
log=exp_CIBIL_DETAIL_RAPG_22mar09.log
tables=FINNQUERY.CIBIL_DETAIL_RAPG
buffer=500000000
feedback=10000
statistics=none
compress=n
rm per1
OR
/etc/mknod /icicipru_archs/DATAEXT_EXPORT/per3 p
nohup compress < per3 > /icicipru_archs/DATAEXT_EXPORT/exp_DATAEXT_01May09.comp &
sleep 10
exp userid=\'/ as sysdba\' buffer=500000000 file=per3 log=exp_DATAEXT_01May09.log full=Y indexes=Y
******************** Install All Components (XML,JAVA,OLAP) ****************************
set echo on
set timing on
set ti on
spool FULL_OLAPINST.log;
alter system set "_system_trig_enabled" = false scope=memory;
CREATE TABLESPACE XDB DATAFILE
'/apsrussia2/oradata/testa/test10g/data/TEST_XDB_01.dbf' SIZE 1000M
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;
@/ora10204/oracle/rdbms/admin/catqm.sql password XDB LMTEMP
@/ora10204/oracle/rdbms/admin/catxdbj.sql
select count(*), object_type from dba_objects where owner='XDB' group by object_type;
@/ora10204/oracle/javavm/install/initjvm.sql
select count(*), object_type from all_objects where object_type like '%JAVA%' group by object_type;
@/ora10204/oracle/xdk/admin/initxml.sql
select count(*), object_type from all_objects where object_type like '%JAVA%' group by object_type;
@/ora10204/oracle/xdk/admin/xmlja.sql
select count(*), object_type from all_objects where object_type like '%JAVA%' group by object_type;
@/ora10204/oracle/rdbms/admin/catjava.sql
select count(*), object_type from all_objects where object_type like '%JAVA%' group by object_type;
@/ora10204/oracle/rdbms/admin/catexf.sql
select count(*), object_type from all_objects where object_type like '%JAVA%' group by object_type;
@/ora10204/oracle/olap/admin/olap.sql SYSAUX LMTEMP
spool off
exit
Excute utlrp.sql and restart the database.
******************** Install Oracle Database JVM server ******************************
The system requirements for a JVM installation are as follows :-
SHARED_POOL_SIZE >= 60Mb (minimum)
JAVA_POOL_SIZE >= 30Mb
The SYSTEM tablespace has at least 120Mb of free space
The RBS tablespace has at least 250Mb of free space
SQL>@$ORACLE_HOME/javavm/install/initjvm.sql
-- Loads the Java stored procedures as required by the
-- Summary Advisor.
SQL>@$ORACLE_HOME/rdbms/admin/initqsma.sql;
Check JAccelerator Installation
select dbms_java.full_ncomp_enabled from dual;
OR
drop table "JAVA$JVM$STATUS" cascade constraints;
shutdown
startup
alter system set "_system_trig_enabled" = false scope=memory;
@?/javavm/install/rmjvm
shutdown immediate
exit
startup
alter system set "_system_trig_enabled" = false scope=memory;
@?/javavm/install/initjvm.sql
@?/rdbms/admin/utlrp.sql
***************************** Install Oracle XDB ***********************************
XDB Installation :
CREATE TABLESPACE XDB DATAFILE
'/apsrussia2/oradata/testa/test10g/data/TEST_XDB_01.dbf' SIZE 1000M
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;
set echo on
set ti on
set timing on
set verify on
spool XDB.log
@/rdbms/admin/catqm.sql password XDB LMTEMP
@/ora10204/oracle/rdbms/admin/catxdbj.sql
spool off
exit
****************************** Install and Deinstall Oracle OLAP ***********************************
OLAP Installation :
set echo on
set ti on
set timing on
set verify on
spool olap.log
@/ora10204/oracle/olap/admin/olap.sql SYSAUX LMTEMP
spool off
exit
OLAP DeInstallation :
set echo on
set ti on
set timing on
set verify on
spool remove_olap.log
@/ora10204/oracle/olap/admin/catnoamd.sql
@/ora10204/oracle/olap/admin/olapidrp.plb
spool off
exit
****************************** Install and Deinstall Oracle Text ***********************************
OLAP Installation :
set echo on
set ti on
set timing on
set verify on
spool text_install.log
@?/ctx/admin/catctx.sql CTXSYS CTXDATA LMTEMP NOLOCK
Where:
CTXSYS - is the ctxsys user password
SYSAUX - is the default tablespace for ctxsys
TEMP - is the temporary tablespace for ctxsys
LOCK|NOLOCK - ctxsys user account will be locked or not
connect CTXSYS/password@tns_alias
@?/ctx/admin/defaults/drdefus.sql
spool off
exit
---------------------------------------------------------------------------------
Check with Following queries :
connect SYS/password as SYSDBA
set pages 1000
col object_name format a40
col object_type format a20
col comp_name format a30
column library_name format a8
column file_spec format a60 wrap
spool text_install_verification.log
-- check on setup
select comp_name, status, substr(version,1,10) as version from dba_registry where comp_id = 'CONTEXT';
select * from ctxsys.ctx_version;
select substr(ctxsys.dri_version,1,10) VER_CODE from dual;
select count(*) from dba_objects where owner='CTXSYS';
-- Get a summary count
select object_type, count(*) from dba_objects where owner='CTXSYS' group by object_type;
-- Any invalid objects
select object_name, object_type, status from dba_objects where owner='CTXSYS' and status != 'VALID' order by object_name;
spool off
-------------------------------------------------------------------------------
OLAP DeInstallation :
set echo on
set ti on
set timing on
set verify on
spool text_deinstall.log
@?/ctx/admin/catnoctx.sql
drop procedure sys.validate_context;
spool off
exit
*************************************** Check System Statistics ****************************************
select PNAME,PVAL1,PVAL2
from aux_stats$
where sname='SYSSTATS_MAIN';
*************************************** dbms_Stats package default value *******************************
SELECT sname AS parameter, nvl(spare4,sval1) AS default_value
FROM sys.optstat_hist_control$
WHERE sname IN ('CASCADE','ESTIMATE_PERCENT','DEGREE',
'METHOD_OPT','NO_INVALIDATE','GRANULARITY');
*************************************** Table analyze ***************************************************
select table_name
from dba_tables
where table_name like '%STAT%'
and table_name not like 'WRH%'
and table_name not like 'WRI%'
and owner='SYS' ;
Object Level
exec dbms_stats.create_stat_table('SYS','&stattablename');
exec dbms_stats.export_database_stats('&stattable','&statid','SYS');
exec dbms_stats.export_schema_stats('&owner','&stattablename','&statid','SYS');
exec dbms_stats.export_table_stats(OWNNAME=>'&owner',TABNAME=>'&tablename',STATTAB=>'&stattable',STATID=>'&statid',STATOWN=>'SYS');
exec dbms_Stats.IMPORT_TABLE_STATS(ownname=>'PRIMECC',tabname=>'MTRANSACTIONS',STATTAB=>'PRIME_STATS',STATID=>'PRIME_STATS_06jan10_17',CASCADE=>true,STATOWN=>'SYS');
select OWNER,TABLE_NAME,STATS_UPDATE_TIME from dba_tab_stats_history
where TABLE_NAME='&tablename'
and owner='&owner';
exec dbms_stats.RESTORE_TABLE_STATS('PIDB','GMIFTPRECOVERABLEMSGS','29-OCT-10 02.09.42.685000 PM +05:30');
Normal Tables :
select 'REM ###### Analyze of Table '||owner||'.'||table_name|| ' of size => '||mb ||'Mb'||chr(13)||chr(10)||
'exec DBMS_STATS.GATHER_TABLE_STATS('''||owner||''','''||table_name||''',DEGREE=>40,estimate_percent=>20,CASCADE=>TRUE);'
from (
select a.owner owner,a.table_name table_name,b.tablespace_name tablespace_name,b.bytes/1024/1024 as mb
from dba_tables a, dba_segments b
where a.owner=b.owner
and a.table_name=b.segment_name
and segment_type='TABLE'
and a.owner='PWYADM'
order by bytes/1024/1024 desc)
/
Partitioned Table:
select 'REM ###### Analyze of Table '||owner||'.'||segment_name||' of size '||MB||' MB'||chr(13)||chr(10)||
'exec DBMS_STATS.GATHER_TABLE_STATS('||''''||owner||''''||','||''''||segment_name||''''||', DEGREE=>2, estimate_percent=>100,CASCADE=>TRUE,granularity =>'||'''ALL'''||') ;'
from (
select a.owner owner,a.segment_name segment_name,a.segment_type segment_type,sum(a.bytes/1024/1024) as mb
from dba_segments a, dba_tables b
where a.owner = b.owner and
a.segment_name = b.table_name and
a.owner='DBOPFSRECO' and
a.segment_type = 'TABLE PARTITION' and
a.segment_name in ('TRAN_ANNEXURE_DATA_CL','TRAN_ANNEXURE_DATA_HL')
group by a.owner,a.segment_name,a.segment_type
order by mb desc)
/
spool pwyadm_analyze_180309.log
set echo on
set time on
set timing on
alter session set db_file_multiblock_read_count=128;
alter session set resumable_timeout=3600;
alter session set "_px_max_granules_per_slave"=3000;
alter session set "_px_index_sampling"=3000;
alter session set "_px_dynamic_sample_size"=3000;
exec dbms_stats.gather_table_stats('DMS','GNUDYN_CHEQUE_BOOK_HIST',estimate_percent=>20,CASCADE=>TRUE,degree=>4);
exec dbms_stats.gather_table_stats('DMS','GNUDYN_CHEQUE_BOOK',estimate_percent=>20,CASCADE=>TRUE,degree=>4);
spool off
exit
Schema Level
exec dbms_stats.export_schema_stats('SYSADM','&stattable','&statid','SYS');
exec dbms_stats.gather_schema_stats(OWNNAME=>'SYSADM',ESTIMATE_PERCENT=>20,DEGREE=>4,CASCADE=>true);
**************************************** Index Rebuild *********************************************************
set echo off;
set feed off;
set head off;
set lines 200
set pages 500
set trimspool on;
spool PROFUNDS_index_rebuild.sql
select 'spool PROFUNDS_index_rebuild.log' from dual;
set serveroutput on size 1000000;
exec dbms_output.enable(500000000);
declare
TYPE mycursor IS REF CURSOR;
c2 mycursor;
tname varchar2(50);
towner varchar2(50);
tsize number;
cursor c1 is
select owner,segment_name,bytes from dba_segments where segment_type='INDEX' and owner='PROFUNDS' order by bytes;
begin
dbms_output.put_line('set time on');
dbms_output.put_line('set timing on');
dbms_output.put_line('set echo on');
dbms_output.put_line('set lines 200');
dbms_output.put_line('alter session set db_file_multiblock_read_count=128;');
dbms_output.put_line('alter session set resumable_timeout=3600;');
dbms_output.put_line('alter session set "_px_max_granules_per_slave"=3000;');
dbms_output.put_line('alter session set "_px_index_sampling"=3000;');
dbms_output.put_line('alter session set "_px_dynamic_sample_size"=3000;');
for i in c1 loop
towner := i.owner;
tname := i.segment_name;
tsize := i.bytes;
dbms_output.put_line(chr(5));
dbms_output.put_line(' -- Rebuilding Index ' ||i.owner||'.'||i.segment_name||' of '||i.bytes/1024/1024|| ' MB.');
dbms_output.put_line('alter index ' ||i.owner ||'.'||i.segment_name||' rebuild parallel(degree 24);');
dbms_output.put_line('alter index '||i.owner||'.'||i.segment_name||' noparallel;');
end loop;
end;
/
select 'spool off' from dual;
select 'exit' from dual;
spool off
exit
********************************************* Table Analyze ******************************************************
set echo off;
set feed off;
set head off;
set lines 200
set pages 5000
set trimspool on;
spool Esearch_table_analyze.sql
select 'spool Esearch_table_analyze.log' from dual;
set serveroutput on;
declare
TYPE mycursor IS REF CURSOR;
c2 mycursor;
tname varchar2(50);
towner varchar2(50);
tsize number;
cursor c1 is
select owner,segment_name,bytes
from dba_segments
where segment_type='TABLE'
and owner in ('GOLD','KIT')
and segment_name not like '%BIN%'
order by bytes;
begin
dbms_output.put_line('set time on');
dbms_output.put_line('set timing on');
dbms_output.put_line('set echo on');
dbms_output.put_line('set lines 200');
dbms_output.put_line('alter session set sort_area_size=100000000;');
dbms_output.put_line('alter session set db_file_multiblock_read_count=64;');
dbms_output.put_line('alter session enable resumable timeout 3600;');
for i in c1 loop
towner := i.owner;
tname := i.segment_name;
tsize := i.bytes;
dbms_output.put_line('REM ###### Analyze of Table ' ||i.owner||'.'||i.segment_name||' of '||i.bytes/1024/1024|| ' MB.');
dbms_output.put_line('exec dbms_stats.gather_table_stats('''||i.owner||''','''||i.segment_name||''', estimate_percent=>100,CASCADE=>TRUE,degree=>8);');
dbms_output.put_line(' ');
end loop;
end;
/
select 'spool off' from dual;
select 'exit' from dual;
spool off
********************************** Table analyze with Multiple Columns Histograms *******************************
CREATE OR REPLACE PROCEDURE R1
as
-- VARIABLE DECARATION
str_exec_variable varchar2(32600);
l_file UTL_FILE.FILE_TYPE;
l_location VARCHAR2(100):= 'DIR_ANN';
l_filename VARCHAR2(100);
-- CURSOR DECLARATION
cursor c1 is
select distinct table_name from dba_tab_columns
where histogram != 'NONE'
and owner = 'MXG';
cursor c2(tname varchar2) is
select table_name, column_name, num_buckets, histogram
from dba_tab_columns
where table_name = tname
and histogram != 'NONE'
and owner = 'MXG';
begin
l_filename := 'MXG_HISTOGRAM_TABLES.sql';
l_file := utl_file.fopen(l_location,l_filename,'w',32600);
utl_file.new_line(l_file );
UTL_FILE.PUT_LINE(l_file,'set echo on');
UTL_FILE.PUT_LINE(l_file,'set time on');
UTL_FILE.PUT_LINE(l_file,'set timing on');
utl_file.new_line(l_file);
UTL_FILE.PUT_LINE(l_file,'SPOOL MXG_HISTOGRAM_TABLES.log');
utl_file.new_line(l_file);
UTL_FILE.PUT_LINE(l_file,'alter session set db_file_multiblock_read_count=128;');
UTL_FILE.PUT_LINE(l_file,'alter session set resumable_timeout=3600;');
UTL_FILE.PUT_LINE(l_file,'alter session set "_px_max_granules_per_slave"=3000;');
UTL_FILE.PUT_LINE(l_file,'alter session set "_px_index_sampling"=3000;');
UTL_FILE.PUT_LINE(l_file,'alter session set "_px_dynamic_sample_size"=3000;');
utl_file.new_line(l_file);
for i in c1
loop
select 'EXEC DBMS_STATS.GATHER_TABLE_STATS(' || '''' ||
'MXG' || '''' || ',' || '''' ||
i.table_name || '''' || ',' ||
'DEGREE => 28, ESTIMATE_PERCENT=> 100, CASCADE=> TRUE, METHOD_OPT=> ' || ''''
into str_exec_variable
FROM SYS.DUAL;
for j in c2(i.table_name)
loop
str_exec_variable := str_exec_variable|| 'FOR COLUMNS size ' || j.NUM_BUCKETS || ' ' || j.column_name || ' ';
end loop;
str_exec_variable := str_exec_variable || '''' || ');';
UTL_FILE.PUT_LINE(l_file,'REM --- ANALYZE OF TABLE WITH HISTOGRAM ------> '||i.table_name);
UTL_FILE.PUT_LINE(l_file,str_exec_variable);
UTL_FILE.FFLUSH(l_file);
utl_file.new_line(l_file );
--dbms_output.put_line('String is : ' || str_exec_variable);
str_exec_variable := '';
end loop;
UTL_FILE.PUT_LINE(l_file,'SPOOL OFF');
UTL_FILE.PUT_LINE(l_file,'EXIT');
utl_file.fclose(l_file);
end;
/
************************************************** Table reorganisation *****************************************
SET SERVEROUTPUT ON format WORD_WRAPPED
exec dbms_output.enable(100000000);
break on segment_name;
declare
tname varchar2(50);
towner varchar2(50);
cursor c1 is
select owner,segment_name,tablespace_name,bytes/1024/1024 bytes
from dba_segments
where segment_type='TABLE'
and owner='KIT'
and segment_name not like 'BIN$%'
order by bytes/1024/1024 desc;
cursor c2 is
select owner,segment_name as index_name,tablespace_name,bytes/1024/1024 as mb
from dba_segments
where segment_name in (select index_name from dba_indexes
where table_name=tname and
owner=towner)
and owner=towner;
begin
dbms_output.put_line('set time on');
dbms_output.put_line('set timing on');
dbms_output.put_line('set echo on');
dbms_output.put_line('set verify on');
dbms_output.put_line(chr(5));
dbms_output.put_line('alter session set db_file_multiblock_read_count=128;');
dbms_output.put_line('alter session set resumable_timeout=3600;');
dbms_output.put_line('alter session set "_px_max_granules_per_slave"=3000;');
dbms_output.put_line('alter session set "_px_index_sampling"=3000;');
dbms_output.put_line('alter session set "_px_dynamic_sample_size"=3000;');
dbms_output.put_line(chr(5));
dbms_output.put_line('spool Esearch_kit_rebuild.log');
dbms_output.put_line(chr(5));
for i in c1 loop
towner := i.owner;
tname := i.segment_name;
dbms_output.put_line('REM --- MOVING TABLE '||i.segment_name||' OF SIZE ----> '||i.bytes||' MB ') ;
dbms_output.put_line('alter table '||i.owner||'.'||i.segment_name||' move tablespace '||i.tablespace_name||';');
for j in c2 loop
dbms_output.put_line(null);
dbms_output.put_line('');
dbms_output.put_line(chr(5));
dbms_output.put_line('REM --- REBUILD INDEX '||tname||'.'||j.index_name||' OF SIZE ----> '||j.mb||' MB ') ;
dbms_output.put_line('alter index '||j.owner||'.'||j.index_name||' rebuild parallel(degree 8);');
dbms_output.put_line('alter index '||j.owner||'.'||j.index_name||' noparallel;');
end loop;
dbms_output.put_line('');
dbms_output.put_line(chr(3));
dbms_output.put_line('REM *************************************************************************');
dbms_output.put_line(chr(3));
end loop;
dbms_output.put_line('spool off');
dbms_output.put_line('exit');
end;
/
****************************************** Analyze Multiple Schemas *********************************************
CREATE OR REPLACE PROCEDURE P2
as
-- VARIABLE DECARATION
L_COUNT NUMBER;
TOWNER VARCHAR2(30);
TFILE VARCHAR2(30);
l_file UTL_FILE.FILE_TYPE;
l_location VARCHAR2(100) := 'DIR_ANN';
l_filename VARCHAR2(100) ;
-- CURSOR DECLARATION
CURSOR C1 IS
SELECT USERNAME
FROM DBA_USERS
WHERE USERNAME IN ('FINNONE_LAM','FINNBANKCPS','FINNONESEC','FINNONEEOD',
'FINNONEFA','FINNHFCFA','FINNHFCCPS','FINNONECPS','FINNLOGIN',
'FINNONELEA','FINNBANKPDC','FINNONEEAI','FINNHFCSEC',
'FINNBANKLEA','FINNHFCEOD','FINNONEPDC1','FINNONEGCD',
'FINNHFCLEA','FINNBANKFA','FINNHFCPDC','FINNBANKEOD',
'FINNBANKSEC','FINNONEPDCBAK');
CURSOR C2 IS
SELECT A.OWNER,A.SEGMENT_NAME,A.SEGMENT_TYPE,SUM(A.BYTES/1024/1024) MB
FROM DBA_SEGMENTS A, DBA_TABLES B
WHERE A.OWNER = B.OWNER AND
A.SEGMENT_NAME = B.TABLE_NAME AND
A.OWNER = TOWNER AND
A.SEGMENT_TYPE = 'TABLE'
GROUP BY A.OWNER,A.SEGMENT_NAME,A.SEGMENT_TYPE
ORDER BY MB DESC;
CURSOR C3 IS
SELECT A.OWNER,A.SEGMENT_NAME,A.SEGMENT_TYPE,SUM(A.BYTES/1024/1024) MB
FROM DBA_SEGMENTS A, DBA_TABLES B
WHERE A.OWNER = B.OWNER AND
A.SEGMENT_NAME = B.TABLE_NAME AND
A.OWNER = TOWNER AND
A.SEGMENT_TYPE = 'TABLE PARTITION'
GROUP BY A.OWNER,A.SEGMENT_NAME,A.SEGMENT_TYPE
ORDER BY MB DESC;
BEGIN
FOR i IN C1 LOOP
TOWNER := i.USERNAME;
BEGIN
SELECT COUNT(1) INTO L_COUNT
FROM DBA_SEGMENTS
WHERE OWNER=TOWNER
AND SEGMENT_TYPE='TABLE';
IF L_COUNT > 0 THEN
l_filename := TOWNER||'_NONPART_TABLES.sql';
l_file := utl_file.fopen(l_location,l_filename,'w');
utl_file.new_line(l_file );
UTL_FILE.PUT_LINE(l_file,'set echo on');
UTL_FILE.PUT_LINE(l_file,'set time on');
UTL_FILE.PUT_LINE(l_file,'set timing on');
utl_file.new_line(l_file);
UTL_FILE.PUT_LINE(l_file,'SPOOL '||TOWNER||'_NONPART_TABLES.log');
utl_file.new_line(l_file);
UTL_FILE.PUT_LINE(l_file,'alter session set db_file_multiblock_read_count=128;');
UTL_FILE.PUT_LINE(l_file,'alter session set resumable_timeout=3600;');
UTL_FILE.PUT_LINE(l_file,'alter session set "_px_max_granules_per_slave"=3000;');
UTL_FILE.PUT_LINE(l_file,'alter session set "_px_index_sampling"=3000;');
UTL_FILE.PUT_LINE(l_file,'alter session set "_px_dynamic_sample_size"=3000;');
utl_file.new_line(l_file);
FOR j IN C2 LOOP
UTL_FILE.PUT_LINE(l_file,'REM --- ANALYZE OF TABLE ------> '||j.segment_name||' OF SIZE '||j.MB||' MB' );
UTL_FILE.PUT_LINE(l_file,'exec DBMS_STATS.GATHER_TABLE_STATS('||''''||j.owner||''''||','||''''||j.segment_name||''''||', DEGREE=>40, estimate_percent=>20,CASCADE=>TRUE);');
utl_file.new_line(l_file );
END LOOP;
UTL_FILE.PUT_LINE(l_file,'SPOOL OFF');
UTL_FILE.PUT_LINE(l_file,'EXIT');
utl_file.fclose(l_file);
END IF;
END;
BEGIN
SELECT COUNT(1) INTO L_COUNT
FROM DBA_SEGMENTS
WHERE OWNER=TOWNER
AND SEGMENT_TYPE='TABLE PARTITION';
IF L_COUNT > 0 THEN
l_filename := TOWNER||'_PART_TABLES.sql';
l_file := utl_file.fopen(l_location,l_filename,'w');
utl_file.new_line(l_file );
UTL_FILE.PUT_LINE(l_file,'set echo on');
UTL_FILE.PUT_LINE(l_file,'set time on');
UTL_FILE.PUT_LINE(l_file,'set timing on');
utl_file.new_line(l_file);
UTL_FILE.PUT_LINE(l_file,'SPOOL '||TOWNER||'_PART_TABLES.log');
utl_file.new_line(l_file);
UTL_FILE.PUT_LINE(l_file,'alter session set db_file_multiblock_read_count=128;');
UTL_FILE.PUT_LINE(l_file,'alter session set resumable_timeout=3600;');
UTL_FILE.PUT_LINE(l_file,'alter session set "_px_max_granules_per_slave"=3000;');
UTL_FILE.PUT_LINE(l_file,'alter session set "_px_index_sampling"=3000;');
UTL_FILE.PUT_LINE(l_file,'alter session set "_px_dynamic_sample_size"=3000;');
utl_file.new_line(l_file);
FOR k IN C3 LOOP
UTL_FILE.PUT_LINE(l_file,'REM --- ANALYZE OF TABLE ------> '||k.segment_name||' OF SIZE '||k.MB||' MB' );
UTL_FILE.PUT_LINE(l_file,'exec DBMS_STATS.GATHER_TABLE_STATS('||''''||k.owner||''''||','||''''||k.segment_name||''''||', DEGREE=>40, estimate_percent=>20,CASCADE=>TRUE, granularity =>'||'''ALL'''||') ;');
utl_file.new_line(l_file );
END LOOP;
UTL_FILE.PUT_LINE(l_file,'SPOOL OFF');
UTL_FILE.PUT_LINE(l_file,'EXIT');
utl_file.fclose(l_file);
END IF;
END;
END LOOP;
END;
/
****************************************** Analyze Multiple Schemas without Partition ***************************
CREATE OR REPLACE PROCEDURE D2
AS
-- VARIABLE DECARATION
L_COUNT NUMBER;
TOWNER VARCHAR2(30);
TFILE VARCHAR2(30);
l_file UTL_FILE.FILE_TYPE;
l_location VARCHAR2(100) := 'ANALYZE';
l_filename VARCHAR2(100) ;
-- CURSOR DECLARATION
CURSOR C1 IS
SELECT USERNAME FROM DBA_USERS WHERE USERNAME IN
('CPCSLIAB','PSXADMIN','PSXADMLI','PSXADMAI','CPCSNEW','PMA');
CURSOR C2 IS
SELECT A.OWNER,A.SEGMENT_NAME,A.SEGMENT_TYPE,SUM(A.BYTES/1024/1024) MB
FROM DBA_SEGMENTS A, DBA_TABLES B
WHERE A.OWNER = B.OWNER AND
A.SEGMENT_NAME = B.TABLE_NAME AND
A.OWNER = TOWNER AND
A.SEGMENT_TYPE = 'TABLE'
GROUP BY A.OWNER,A.SEGMENT_NAME,A.SEGMENT_TYPE
ORDER BY MB DESC;
BEGIN
FOR i IN C1 LOOP
TOWNER := i.USERNAME;
BEGIN
SELECT COUNT(1) INTO L_COUNT FROM DBA_SEGMENTS WHERE OWNER=TOWNER AND SEGMENT_TYPE='TABLE';
IF L_COUNT > 0 THEN
l_filename := TOWNER||'_TABLES.sql';
l_file := utl_file.fopen(l_location,l_filename,'w');
utl_file.new_line(l_file );
UTL_FILE.PUT_LINE(l_file,'set echo on');
UTL_FILE.PUT_LINE(l_file,'set time on');
UTL_FILE.PUT_LINE(l_file,'set timing on');
utl_file.new_line(l_file );
UTL_FILE.PUT_LINE(l_file,'SPOOL '||TOWNER||'_TABLES.log');
utl_file.new_line(l_file );
UTL_FILE.PUT_LINE(l_file,'alter session set db_file_multiblock_read_count=128;');
UTL_FILE.PUT_LINE(l_file,'alter session set resumable_timeout=3600;');
UTL_FILE.PUT_LINE(l_file,'alter session set "_px_max_granules_per_slave"=3000;');
UTL_FILE.PUT_LINE(l_file,'alter session set "_px_index_sampling"=3000;');
UTL_FILE.PUT_LINE(l_file,'alter session set "_px_dynamic_sample_size"=3000;');
utl_file.new_line(l_file );
FOR j IN C2 LOOP
UTL_FILE.PUT_LINE(l_file,'REM --- ANALYZE OF TABLE ------> '||j.segment_name||' OF SIZE '||j.MB||' MB' );
UTL_FILE.PUT_LINE(l_file,'exec DBMS_STATS.GATHER_TABLE_STATS('||''''||j.owner||''''||','||''''||j.segment_name||''''||', DEGREE=>12, estimate_percent=>100,CASCADE=>TRUE);');
UTL_FILE.NEW_LINE(l_file );
END LOOP;
UTL_FILE.PUT_LINE(l_file,'SPOOL OFF');
UTL_FILE.PUT_LINE(l_file,'EXIT');
utl_file.fclose(l_file);
END IF;
END;
END LOOP;
END;
/
********************************************* Analyze Multiple Schemas with Partition ********************************
CREATE OR REPLACE PROCEDURE D2
AS
-- VARIABLE DECARATION
L_COUNT NUMBER;
TOWNER VARCHAR2(30);
TFILE VARCHAR2(30);
l_file UTL_FILE.FILE_TYPE;
l_location VARCHAR2(100) := 'DIR_ANN';
l_filename VARCHAR2(100) ;
-- CURSOR DECLARATION
CURSOR C1 IS
SELECT USERNAME FROM DBA_USERS WHERE USERNAME='PROFUNDS';
CURSOR C3 IS
SELECT A.OWNER,A.SEGMENT_NAME,A.SEGMENT_TYPE,SUM(A.BYTES/1024/1024) MB
FROM DBA_SEGMENTS A, DBA_TABLES B
WHERE A.OWNER = B.OWNER AND
A.SEGMENT_NAME = B.TABLE_NAME AND
A.OWNER = TOWNER AND
A.SEGMENT_TYPE = 'TABLE PARTITION'
GROUP BY A.OWNER,A.SEGMENT_NAME,A.SEGMENT_TYPE
ORDER BY MB;
BEGIN
FOR i IN C1 LOOP
TOWNER := i.USERNAME;
BEGIN
SELECT COUNT(1) INTO L_COUNT FROM DBA_SEGMENTS WHERE OWNER=TOWNER AND SEGMENT_TYPE='TABLE PARTITION';
IF L_COUNT > 0 THEN
l_filename := TOWNER||'_PART_TABLES.sql';
l_file := utl_file.fopen(l_location,l_filename,'w');
utl_file.new_line(l_file );
UTL_FILE.PUT_LINE(l_file,'set echo on');
UTL_FILE.PUT_LINE(l_file,'set time on');
UTL_FILE.PUT_LINE(l_file,'set timing on');
utl_file.new_line(l_file );
UTL_FILE.PUT_LINE(l_file,'SPOOL '||TOWNER||'_PART_TABLES.log');
utl_file.new_line(l_file );
UTL_FILE.PUT_LINE(l_file,'alter session set resumable_timeout=3600;');
UTL_FILE.PUT_LINE(l_file,'alter session set db_file_multiblock_read_count=128;');
utl_file.new_line(l_file );
FOR k IN C3 LOOP
UTL_FILE.PUT_LINE(l_file,'REM --- ANALYZE OF TABLE ------> '||k.segment_name||' OF SIZE '||k.MB||' MB' );
UTL_FILE.PUT_LINE(l_file,'exec DBMS_STATS.GATHER_TABLE_STATS('||''''||k.owner||''''||','||''''||k.segment_name||''''||', DEGREE=>12, estimate_percent=>100,CASCADE=>TRUE,granularity =>'||'''ALL'''||') ;');
utl_file.new_line(l_file );
END LOOP;
UTL_FILE.PUT_LINE(l_file,'SPOOL OFF');
-- UTL_FILE.PUT_LINE(l_file,'EXIT');
utl_file.fclose(l_file);
END IF;
END;
END LOOP;
END;
/
**************************************************** Copy Table Statistics **************************************
CREATE OR REPLACE PROCEDURE "SYS"."SETSTAT" (old varchar2, new varchar2) AS
l_tab_owner VARCHAR2(50);
l_tab_name VARCHAR2(50);
l_src_part VARCHAR2(50);
l_targ_part VARCHAR2(50);
l_col_type VARCHAR2(50);
l_srec sys.dbms_stats.statrec;
l_distcnt number;
l_density number;
l_nullcnt number;
l_avgclen number;
l_statown varchar2(80);
out varchar2(32767);
date_out varchar2(2000);
minvv DATE;
maxvv DATE;
dt DATE;
maxrv raw(32) := l_srec.maxval;
minrv raw(32) := l_srec.minval;
srec dbms_stats.statrec;
datevals dbms_stats.datearray;
-- CURSOR DECLARATION
CURSOR C1 IS
select a.table_owner,a.table_name, a.partition_name, b.column_name
from dba_tab_partitions a, dba_part_key_columns b
where a.table_name=b.name
and a.table_owner='PROFUNDS'
and regexp_like(a.partition_name, '*_'||old)
order by 1,3;
CURSOR C2 IS
select a.table_owner,a.table_name, a.partition_name, b.column_name
from dba_tab_partitions a, dba_part_key_columns b
where a.table_name=b.name
and a.table_owner='PROFUNDS'
and regexp_like(a.partition_name, '*_'||new)
order by 1,3;
begin
for i in C1 loop
for j in C2 loop
if i.table_name=j.table_name then
l_tab_owner := i.table_owner;
l_tab_name := i.table_name;
l_src_part := i.partition_name;
l_targ_part := j.partition_name;
l_col_type := i.column_name;
dbms_output.put_line('You have selected following options');
dbms_output.put_line('Table owner: '||l_tab_owner);
dbms_output.put_line('Table name: '||l_tab_name);
dbms_output.put_line('Source Partition: '||l_src_part);
dbms_output.put_line('Destination Partition: '||l_targ_part);
dbms_output.put_line('Column name for range partititioned table(date datatype): '||l_col_type);
select HIGH_VALUE into out
from dba_tab_partitions
where table_name=l_tab_name
and table_owner=l_tab_owner
and partition_name=l_src_part;
select to_char(to_date(substr(out,10,11),'yyyy-mm-dd'),'dd-mm-yyyy') into date_out
from dual;
dbms_output.put_line('Target partition for stats date: '||date_out);
dbms_stats.copy_table_stats(l_tab_owner,l_tab_name,l_src_part,l_targ_part);
dbms_stats.get_column_stats(l_tab_owner,l_tab_name,l_col_type,l_targ_part,distcnt=>l_distcnt,density=>l_density,nullcnt=>l_nullcnt,srec=>l_srec,avgclen=>l_avgclen,statown=>l_statown);
dbms_output.put_line('No of Distinct Values: '||l_distcnt);
dbms_output.put_line('Density : '||l_density);
dbms_output.put_line('NullCount : '||l_nullcnt);
dbms_output.put_line('Average Row Length : '||l_avgclen);
dbms_output.put_line('Stat Owner : '||l_statown);
maxrv := l_srec.maxval;
minrv := l_srec.minval;
dbms_stats.convert_raw_value(minrv,minvv) ;
dbms_stats.convert_raw_value(maxrv,maxvv) ;
dbms_output.put_line('PRE MAX VALUE: '||TO_CHAR(maxvv,'DD-MM-RRRR HH24:MI:SS'));
dbms_output.put_line('PRE MIN VALUE: '||TO_CHAR(minvv,'DD-MM-RRRR HH24:MI:SS'));
execute immediate 'alter session set nls_date_format=''DD-MM-YYYY''';
srec.EPC:=2;
srec.EAVS:=NULL;
dt := date_out;
dbms_output.put_line('Target partition for stats date: '||to_char(dt,'DD-MM-YYYY HH24:MI:SS'));
datevals:= SYS.DBMS_STATS.DATEARRAY(dt-28,dt-1);
dbms_stats.prepare_column_values(srec,datevals);
dbms_stats.set_column_stats(l_tab_owner,l_tab_name,l_col_type,l_targ_part,srec=>srec,NO_INVALIDATE =>false);
commit;
dbms_stats.get_column_stats(l_tab_owner,l_tab_name,l_col_type,l_targ_part,distcnt=>l_distcnt,density=>l_density,nullcnt=>l_nullcnt,srec=>l_srec,avgclen=>l_avgclen,statown=>l_statown);
maxrv := l_srec.maxval;
minrv := l_srec.minval;
dbms_stats.convert_raw_value(minrv,minvv) ;
dbms_stats.convert_raw_value(maxrv,maxvv) ;
dbms_output.put_line('POST MAX VALUE: '||TO_CHAR(maxvv,'DD-MM-RRRR HH24:MI:SS'));
dbms_output.put_line('POST MIN VALUE: '||TO_CHAR(minvv,'DD-MM-RRRR HH24:MI:SS'));
end if;
end loop;
end loop;
end;
/
exec setstat('10_10','11_10');
set lines 150
set pages 500
col high_value for a30
col TABLE_NAME for a20
col PARTITION_NAME for a25
select TABLE_NAME, PARTITION_NAME, HIGH_VALUE, NUM_ROWS, BLOCKS, AVG_SPACE, AVG_ROW_LEN, LAST_ANALYZED
from dba_tab_partitions
where table_owner='PROFUNDS'
and regexp_like(partition_name, '*_1[0-1]_10')
order by 1,2;
******************************************* Change column Stats high value ****************************************
DECLARE
srec dbms_stats.statrec;
m_distcnt number;
m_density number;
m_nullcnt number;
m_avgclen number;
days number;
DATEVALS DBMS_STATS.DATEARRAY;
--NUMVALS DBMS_STATS.NUMARRAY;
minval raw(32);
prev_high_raw raw(32);
minval_date date;
prev_high_value date;
ownername varchar(32);
tablename varchar(32);
columnname varchar(32);
BEGIN
-- Looks for min_value and max_value
select owner, table_name, column_name,low_value ,high_value
into ownername, tablename, columnname, minval,prev_high_raw
from dba_tab_columns d
where table_name = 'CNM_CNTRCT_NOTE_MSTR'
and owner='CLICKDB' and
column_name='CNM_CNTRCT_DT';
dbms_stats.get_column_stats(
ownname => ownername,
tabname => tablename,
colname => columnname,
distcnt => m_distcnt,
density => m_density,
nullcnt => m_nullcnt,
srec => srec,
avgclen => m_avgclen);
-- Converts raw values
dbms_stats.convert_raw_value(minval, minval_date);
dbms_stats.convert_raw_value(prev_high_raw, prev_high_value);
DBMS_OUTPUT.PUT_LINE ( ownername||'.'||tablename||'.'||columnname||' minval_date= ' ||minval_date ||' maxval_date = ' ||prev_high_value );
IF prev_high_value < trunc(sysdate+15) then -- Only executes if hv is less than sysdate+c_rec.days
-- Sets sysdate+15 as HV
DATEVALS := DBMS_STATS.DATEARRAY(minval_date,trunc(sysdate+15));
-- NUMVALS := DBMS_STATS.NUMARRAY(minval,maxval);
DBMS_OUTPUT.PUT_LINE ( 'Changes the max_value of '||ownername||'.'||tablename||'.'||columnname||' to ' || DATEVALS(2) );
dbms_stats.prepare_column_values(srec, DATEVALS);
dbms_stats.set_column_stats(
ownname => ownername,
tabname => tablename,
colname => columnname,
distcnt => m_distcnt,
density => m_density,
nullcnt => m_nullcnt,
srec => srec,
avgclen => m_avgclen);
ELSE
DBMS_OUTPUT.PUT_LINE ( 'No changes for '||ownername||'.'||tablename||'.'||columnname );
END IF;
COMMIT;
END;
/
==============================================================
set serveroutput on
declare
l_srec sys.dbms_stats.statrec;
l_distcnt number;
l_density number;
l_nullcnt number;
l_avgclen number;
l_statown varchar2(80);
minvv DATE ;
maxvv DATE ;
new_minvv DATE := '01-APR-2008';
new_maxvv DATE := '30-SEP-2010';
maxrv raw(32) ;--:= l_srec.maxval;
minrv raw(32) ;--:= l_srec.minval;
SREC SYS.DBMS_STATS.STATREC;
DATEVALS SYS.DBMS_STATS.DATEARRAY;
begin
sys.dbms_stats.get_column_stats('CLICKDB','CNM_CNTRCT_NOTE_MSTR','CNM_CNTRCT_DT',distcnt=>l_distcnt,density=>l_density,nullcnt=>l_nullcnt,srec=>l_srec,avgclen=>l_avgclen,statown=>l_statown);
dbms_output.put_line('No of Distinct Values::::'||l_distcnt);
dbms_output.put_line('Density ::::'||l_density);
dbms_output.put_line('NullCount ::::'||l_nullcnt);
dbms_output.put_line('Average Row Length ::::'||l_avgclen);
dbms_output.put_line('Stat Owner ::::'||l_statown);
maxrv := l_srec.maxval;
minrv := l_srec.minval;
dbms_stats.convert_raw_value(minrv,minvv) ;
dbms_stats.convert_raw_value(maxrv,maxvv) ;
dbms_output.put_line('PRE MAX VALUE ::'||TO_CHAR(maxvv,'DD-MM-RRRR HH24:MI:SS')) ;
dbms_output.put_line('PRE MIN VALUE ::'||TO_CHAR(minvv,'DD-MM-RRRR HH24:MI:SS')) ;
execute immediate 'alter session set nls_date_format=''DD-MM-YYYY''';
SREC.EPC:=2;
SREC.EAVS:=NULL;
DATEVALS:= SYS.DBMS_STATS.DATEARRAY(new_minvv,new_maxvv);
SYS.DBMS_STATS.PREPARE_COLUMN_VALUES(SREC,DATEVALS);
SYS.DBMS_STATS.SET_COLUMN_STATS('CLICKDB','CNM_CNTRCT_NOTE_MSTR','CNM_CNTRCT_DT',SREC=>SREC,NO_INVALIDATE =>false);
COMMIT;
sys.dbms_stats.get_column_stats('CLICKDB','CNM_CNTRCT_NOTE_MSTR','CNM_CNTRCT_DT',distcnt=>l_distcnt,density=>l_density,nullcnt=>l_nullcnt,srec=>l_srec,avgclen=>l_avgclen,statown=>l_statown);
maxrv := l_srec.maxval;
minrv := l_srec.minval;
dbms_stats.convert_raw_value(minrv,minvv) ;
dbms_stats.convert_raw_value(maxrv,maxvv) ;
dbms_output.put_line('POST MAX VALUE ::'||TO_CHAR(maxvv,'DD-MM-RRRR HH24:MI:SS')) ;
dbms_output.put_line('POST MIN VALUE ::'||TO_CHAR(minvv,'DD-MM-RRRR HH24:MI:SS')) ;
END;
/
******************************************* Getting Output in HTML format ***************************************
set pages 5000
SET MARKUP HTML ON SPOOL ON PREFORMAT OFF ENTMAP ON -
HEAD "<TITLE>Department Report</TITLE> -
<STYLE type='text/css'> -
<!-- BODY {background: #FFFFC6} --> -
</STYLE>" -
BODY "TEXT='#FF00Ff'" -
TABLE "WIDTH='90%' BORDER='5'"
spool report.html
Select * from dba_tables;
spool off
exit
************************************************ LOB INSERT: *****************************************************
CREATE OR REPLACE PROCEDURE "CRMUSER"."INCI_ATTACHMOVE"
AS
V_IncidentId INTEGER;
CURSOR inciattach_cursor IS
SELECT incidentid FROM INCIDMP_OPEN where processed='N';
BEGIN
OPEN inciattach_cursor;
LOOP
BEGIN
FETCH inciattach_cursor
INTO V_IncidentId ;
EXIT WHEN inciattach_cursor%NOTFOUND;
insert into Attachments (select * from attachments_old where attachmentid in (
select attachmentid from incident_attachment where incidentid=V_Incidentid));
UPDATE INCIDMP_OPEN SET Processed = 'Y' WHERE incidentid=V_Incidentid;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE('Inside Exception'||SQLERRM);
UPDATE INCIDMP_OPEN SET Processed = 'E' WHERE incidentid=V_Incidentid;
COMMIT;
END;
END LOOP;
CLOSE inciattach_cursor;
END;
*********************************************** BULK INSERT: *****************************************************
DECLARE
TYPE faaccount_prod_tab IS TABLE OF IAXISADM.STG_FAACCOUNTMOVEMENT%ROWTYPE;
faaccount_product_tab faaccount_prod_tab := faaccount_prod_tab();
start_time number;
end_time number;
BEGIN
-- Populate a collection - 100000 rows
SELECT /*+ full(a) parallel(a,10) */ * BULK COLLECT INTO faaccount_product_tab limit 100000
FROM IAXISADM.STG_FAACCOUNTMOVEMENT
where DTL__CAPXACTION = 'I';
Start_time := DBMS_UTILITY.get_time;
FORALL i in faaccount_product_tab.first .. faaccount_product_tab.last
INSERT /*+ append */ INTO LF$APP_FAACCOUNTMOVEMENT VALUES faaccount_product_tab(i);
end_time := DBMS_UTILITY.get_time;
DBMS_OUTPUT.PUT_LINE('Bulk Insert: '||to_char(end_time-start_time));
COMMIT;
END;
/
Exchange Partition :
ALTER TABLE table_name
EXCHANGE PARTITION Partition_name
WITH TABLE new_tablename
WITHOUT VALIDATION;
******************************************* Multiple AWR Report Generator ********************************************
set lines 150
set pages 500
col BEGIN_INTERVAL_TIME for a30
col END_INTERVAL_TIME for a30
select SNAP_ID, BEGIN_INTERVAL_TIME, END_INTERVAL_TIME
from dba_hist_snapshot
where trunc(BEGIN_INTERVAL_TIME)=trunc(sysdate-&no)
order by 1;
CREATE OR REPLACE DIRECTORY awr_reports_dir AS '/tmp/awrreports';
DECLARE
-- Adjust before use.
l_snap_start NUMBER := 4884; --Specify Initial Snap ID
l_snap_end NUMBER := 4892; --Specify End Snap ID
l_dir VARCHAR2(50) := 'AWR_REPORTS_DIR';
l_last_snap NUMBER := NULL;
l_dbid v$database.dbid%TYPE;
l_instance_number v$instance.instance_number%TYPE;
l_file UTL_FILE.file_type;
l_file_name VARCHAR(50);
BEGIN
SELECT dbid
INTO l_dbid
FROM v$database;
SELECT instance_number
INTO l_instance_number
FROM v$instance;
FOR cur_snap IN (SELECT snap_id
FROM dba_hist_snapshot
WHERE instance_number = l_instance_number
AND snap_id BETWEEN l_snap_start AND l_snap_end
ORDER BY snap_id)
LOOP
IF l_last_snap IS NOT NULL THEN
l_file := UTL_FILE.fopen(l_dir, 'awr_' || l_last_snap || '_' || cur_snap.snap_id || '.html', 'w', 32767);
FOR cur_rep IN (SELECT output
FROM TABLE(DBMS_WORKLOAD_REPOSITORY.awr_report_html(l_dbid, l_instance_number, l_last_snap, cur_snap.snap_id)))
LOOP
UTL_FILE.put_line(l_file, cur_rep.output);
END LOOP;
UTL_FILE.fclose(l_file);
END IF;
l_last_snap := cur_snap.snap_id;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
IF UTL_FILE.is_open(l_file) THEN
UTL_FILE.fclose(l_file);
END IF;
RAISE;
END;
/
******************************************* Multiple ADDM Report Generator ********************************************
set lines 150
set pages 500
col BEGIN_INTERVAL_TIME for a30
col END_INTERVAL_TIME for a30
select SNAP_ID, BEGIN_INTERVAL_TIME, END_INTERVAL_TIME
from dba_hist_snapshot
where trunc(BEGIN_INTERVAL_TIME)=trunc(sysdate-&no)
order by 1;
create or replace directory ADDM_DIRECTORY as '/tmp/addmreports';
create or replace procedure multiple_addm(start_id number, end_id number) AS
id number;
name varchar2(100);
descr varchar2(500);
fhandle UTL_FILE.FILE_TYPE;
fdir varchar2(50) := 'ADDM_DIRECTORY';
vclob CLOB;
len number;
amt NUMBER DEFAULT 32760;
offset NUMBER DEFAULT 1;
x varchar2(32760);
BEGIN
for i in start_id .. end_id loop
if i != end_id then
name := 'addm_report_' || to_char(i) || '_' ||to_char(i + 1);
descr := 'ADDM run: snapshots [' || to_char(i) || ', ' ||to_char(i + 1) || ']';
dbms_advisor.create_task('ADDM', id, name, descr, null);
dbms_advisor.set_task_parameter(name, 'START_SNAPSHOT', i);
dbms_advisor.set_task_parameter(name, 'END_SNAPSHOT', i + 1);
dbms_advisor.execute_task(name);
fhandle := UTL_FILE.fopen(fdir, name || '.txt', 'wb', 32760);
select dbms_advisor.get_task_report(name, 'TEXT', 'TYPICAL') into vclob from dual;
select dbms_lob.getlength(dbms_advisor.get_task_report(name,'TEXT','TYPICAL')) into len from dual;
WHILE (offset < len) LOOP
dbms_lob.read(vclob, amt, offset, x);
UTL_FILE.PUT_raw(fhandle, utl_raw.cast_to_raw(x), TRUE);
UTL_FILE.fflush(fhandle);
offset := offset + amt;
END LOOP;
UTL_FILE.fclose(fhandle);
dbms_advisor.delete_task(name);
offset := 1;
end if;
end loop;
END;
/
exec multiple_addm(23464,23465);
********************************************** To check Semaphores used by Database ******************************
Oracle11G Database not shutting down not even aborting.
Ora-7445 Occured.
Below temporary solution used.
export ORACLE_SID=name
sysresv
IPC Resources for ORACLE_SID "TEST" :
Shared Memory:
ID KEY
6923 0xe9c5c36c
Semaphores:
ID KEY
2293784 0x503689b8
Oracle Instance alive for sid "TEST"
ipcs -s | grep 2293784
ipcrm -s 2293784
ipcrm -m 6923
************************************ Get Index Creation Space Usage before creating it actually **********************
SET SERVEROUTPUT ON
DECLARE
v_used_bytes NUMBER(10);
v_Allocated_Bytes NUMBER(10);
BEGIN
DBMS_SPACE.CREATE_INDEX_COST
(
'create index EBAWEB.IDX_UPD_PROCESS_FLG on EBAWEB.UPD_PRTFLO_DTL (UPD_PROCESS_FLG,UPD_DELETE_FLG)',
v_used_Bytes,
v_Allocated_Bytes
);
DBMS_OUTPUT.PUT_LINE('Used Bytes MB: ' || round(v_used_Bytes/1024/1024));
DBMS_OUTPUT.PUT_LINE('Allocated Bytes MB: ' || round(v_Allocated_Bytes/1024/1024));
END;
/
************************************ Get Table Creation Space Usage before creating it actually **********************
CREATE TABLE SAMPLE
(
SNAP_DATE DATE
DB_NAME VARCHAR2(20)
PARAMETER VARCHAR2(60)
VALUE VARCHAR2(1000)
)
Here is the code snippet:
SET SERVEROUTPUT ON
DECLARE
v_used_bytes NUMBER(10);
v_Allocated_Bytes NUMBER(10);
v_type sys.create_table_cost_columns;
BEGIN
v_Type := sys.create_table_cost_columns
(
sys.create_table_cost_colinfo('DATE',NULL),
sys.create_table_cost_colinfo('VARCHAR2',20),
sys.create_table_cost_colinfo('VARCHAR2',60),
sys.create_table_cost_colinfo('VARCHAR2',1000)
);
DBMS_SPACE.CREATE_TABLE_COST('USERS',v_Type,4600000,7,v_used_Bytes,v_Allocated_Bytes);
DBMS_OUTPUT.PUT_LINE('Used Bytes: ' || TO_CHAR(v_used_Bytes));
DBMS_OUTPUT.PUT_LINE('Allocated Bytes: ' || TO_CHAR(v_Allocated_Bytes));
END;
/
********************************************* IO Statistics For past Day *********************************************
set lines 130
col filename for a40
select
to_char(begin_interval_time,'dd-mon-yyyy hh24:mi') "Time",
substr(filename,1,instr(filename,'/',-1,1)) filename,
sum(phyrds) reads,
sum(PHYWRTS) writes,
sum(READTIM)/sum(phyrds) read_ratio,
sum(WRITETIM)/sum(PHYWRTS) write_ratio from
dba_hist_filestatxs filestat,
dba_hist_snapshot snap
where
filestat.SNAP_ID=snap.SNAP_ID
and to_char(snap.begin_interval_time,'dd-mon-yyyy') = '18-oct-2009'
group by to_char(begin_interval_time,'dd-mon-yyyy hh24:mi'),substr(filename,1,instr(filename,'/',-1,1))
order by 1,2;
set lines 130
col filename for a40
select
to_char(begin_interval_time,'dd-mon-yyyy hh24:mi') "Time",
substr(filename,1,instr(filename,'/',-1,1)) filename,
sum(phyrds) reads,
sum(PHYWRTS) writes,
sum(READTIM)/sum(phyrds) read_ratio,
sum(WRITETIM)/sum(PHYWRTS) write_ratio from
DBA_HIST_TEMPSTATXS tempstat,
dba_hist_snapshot snap
where
tempstat.SNAP_ID=snap.SNAP_ID
and snap.snap_id in (4594,4595)
group by to_char(begin_interval_time,'dd-mon-yyyy hh24:mi'),substr(filename,1,instr(filename,'/',-1,1))
order by 1,2;
********************************************** IO Statistics For per disk *********************************************
set linesize 132
set pagesize 66
column name format a20
select substr(name, 1, 18) name,
sum(phyrds) reads,
sum(readtim) read_time,
sum(readtim)/sum(phyrds) read_ratio,
sum(phywrts) writes,
sum(writetim) write_time,
sum(writetim)/sum(phywrts) write_ratio
from v$filestat a, v$datafile b
where a.file#=b.file#
group by substr(name, 1, 18)
order by name desc
/
************************************ Archive backup logs for RMAN db **********************************
cd $ORACLE_HOME/RMAN/scripts/log
rman target / catalog rman/rman_2005@rmancat
DELETE NOPROMPT ARCHIVELOG ALL BACKED UP 2 TIMES TO 'SBT_TAPE' COMPLETED BEFORE 'SYSDATE -1';
list ARCHIVELOG ALL BACKED UP 2 TIMES TO 'SBT_TAPE' COMPLETED BEFORE 'SYSDATE -2';
************************************ Parameter-driven command file ************************************
$ORACLE_HOME/bin/rman <<EOF
@backup_ts_generic.rman "/tmp" USERS
EOF
connect target /
connect catalog rman/secretpass@rmancat
run {
allocate channel c1 type disk format ‘&1/%U’;
backup tablespace &2;
}
C:\> rman
RMAN> connect target /
RMAN> connect catalog rman/secretpass@rmancat
RMAN> create script backup_ts_users
comment ‘Tablespace Users Backup’
{
allocate channel c1 type disk format ‘c:\temp\%U’;
backup tablespace users;
}
RMAN> create script backup_ts_any
comment ‘Any Tablespace Backup’
{
allocate channel c1 type disk format ‘c:\temp\%U’;
backup tablespace &1;
Enter value for 1: users
users;
}
******************************************* List RMAN Backup *******************************************
LIST BACKUP; # lists backup sets, image copies, and proxy copies
LIST BACKUPSET; # lists only backup sets and proxy copies
LIST COPY; # lists only disk copies
LIST EXPIRED BACKUP;
LIST BACKUP BY FILE; # shows backup sets, proxy copies, and image copies
LIST COPY BY FILE; # shows only disk copies
LIST EXPIRED BACKUP BY FILE;
LIST BACKUP SUMMARY; # lists backup sets, proxy copies, and disk copies
LIST EXPIRED BACKUP SUMMARY;
LIST BACKUP OF DATABASE; # lists backups of all files in database
LIST COPY OF DATAFILE
'ora_home/oradata/trgt/system01.dbf'; # lists copy of specified datafile
LIST BACKUPSET 213; # lists specified backup set
LIST DATAFILECOPY '/tmp/tools01.dbf'; # lists datafile copy
LIST BACKUPSET
TAG 'weekly_full_db_backup'; # specify a backup set by tag
LIST COPY OF DATAFILE
'ora_home/oradata/trgt/system01.dbf'
DEVICE TYPE sbt; # specify a backup or copy by device type
LIST BACKUP LIKE '/tmp/%'; # specify a backup by directory or path
LIST COPY OF DATAFILE 2
COMPLETED BETWEEN
'10-DEC-2002' AND '17-DEC-2002'; # specify a backup or copy by a range of completion dates
LIST ARCHIVELOG ALL BACKED UP
2 TIMES TO DEVICE TYPE sbt; # specify logs backed up at least twice to tape
LIST INCARNATION;
LIST INCARNATION OF DATABASE prod3;
******************************************* RMAN Restoration *******************************************
export ORACLE_SID=AMLCA
copy pfile from 10.16.167.116 to target server
startup nomount
rman target / rcvcat rman/rman_2005@rmancat
set dbid=1728600520
RMAN>
RUN {
ALLOCATE CHANNEL ch01 TYPE 'SBT_TAPE';
ALLOCATE CHANNEL ch02 TYPE 'SBT_TAPE';
ALLOCATE CHANNEL ch03 TYPE 'SBT_TAPE';
send 'NB_ORA_SERV=icstgbk1, NB_ORA_CLIENT=MLXBANKALERT';
restore database from tag AMLCA_full_hot_15_Jun_2009;
RELEASE CHANNEL ch01;
RELEASE CHANNEL ch02;
RELEASE CHANNEL ch03;
}
********************************* Check Corruption using Database export ********************************
exp system/manager full=y log=exp_db_chk.log file=/dev/null volsize=100g
***************************************** Drop Database in 9i *******************************************
set serveroutput on size 1000000;
exec dbms_output.enable(100000000);
SET SERVEROUT ON
DECLARE
TYPE string_arr IS TABLE OF VARCHAR2(1024);
file_list string_arr;
BEGIN
SELECT t.file_path BULK COLLECT
INTO file_list
FROM (SELECT NAME file_path
FROM V$DATAFILE
UNION
SELECT MEMBER file_path
FROM V$LOGFILE
UNION
SELECT NAME file_path
FROM v$controlfile
UNION
SELECT VALUE file_path
FROM v$parameter
WHERE NAME LIKE '%dest'
UNION
SELECT VALUE file_path
FROM v$parameter2
WHERE NAME = 'utl_file_dir'
) t;
FOR i IN file_list.FIRST .. file_list.LAST LOOP
DBMS_OUTPUT.PUT_LINE('rm -rf ' || file_list(i));
END LOOP;
END;
/
*************************************** Installation Error ********************************************
[ Unable to alloc heap of requested size, perhaps the maxdata value is too small -
see README.HTML for more information. ]
[ Unable to allocate an initial java heap of 157286400 bytes. ]
[ **Out of memory, aborting** ]
[ ]
[ *** panic: JVMST016: Cannot allocate memory for initial java heap ]
/tmp/OraInstall2009-07-16_12-59-10AM/jre/1.4.2/bin/java[3]: 17230 IOT/Abort trap(coredump)
To Resolve use :
export LDR_CNTRL=MAXDATA=0x40000000@DSA
export IBM_JAVA_MMAP_JAVA_HEAP=true
*************************************** Metadata set param ********************************************
DBMS_METADATA.SET_TRANSFORM_PARAM (DBMS_METADATA.SESSION_TRANSFORM, 'PRETTY', true);
DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true);
**************************************** Using dbms_rowid *********************************************
select x.*, ts.status
from (
select t.*,
dbms_rowid.rowid_relative_fno( rowid ) rfno,
dbms_rowid.rowid_to_absolute_fno( rowid, user, ’T’ ) afno
from t
) x, dba_data_files df, dba_tablespaces ts
where df.relative_fno = rfno
and df.file_id = afno
and df.tablespace_name = ts.tablespace_name;
create or replace function ts_status( p_rowid in rowid ) return varchar2
is
l_status dba_tablespaces.status%type;
begin
select ts.status into l_status
from dba_data_files df, dba_tablespaces ts
where df.relative_fno = dbms_rowid.rowid_relative_fno( p_rowid )
and df.file_id = dbms_rowid.rowid_to_absolute_fno( p_rowid, ’OPS$TKYTE’, ’T’ )
and df.tablespace_name = ts.tablespace_name;
return l_status;
exception when NO_DATA_FOUND
then
raise program_error;
end;
/
select t.*, ts_status(t.rowid) ts_status
from t;
**************************************** Using Stored Outlines ***************************************
ALTER SYSTEM SET create_stored_outlines=TRUE;
ALTER SESSION SET create_stored_outlines=TRUE;
CONN sys/password AS SYSDBA
GRANT CREATE ANY OUTLINE TO SCOTT;
GRANT EXECUTE_CATALOG_ROLE TO SCOTT;
CREATE OUTLINE GOLD_OUTLINE_1 FOR CATEGORY GOLD
ON select sum(gbim_inv_post_sales_tax_total) USED
from GOLD.GOLD_BRANCH_INVOICE_MASTER,
GOLD.SOL_MASTER a,
GOLD.GOLD_BRANCH_DETAILS b,
GOLD.SOL_MASTER c
where a.sol_id = GBIM_INV_BRANCH_CD and
a.RPC_SRNO = c.SOL_SRNO and
a.RPC_SRNO = :"SYS_B_0" and
a.RPC_SRNO = b.gbd_branch_region_cd AND
b.gbd_branch_state_cd = :"SYS_B_1" AND
a.sol_id= b.gbd_branch_cd and
trunc(GBIM_INV_DATE) >= to_date(:"SYS_B_2",:"SYS_B_3") and
trunc(GBIM_INV_DATE) <= to_date(:"SYS_B_4",:"SYS_B_5") and
GBIM_INV_CANCELLED = :"SYS_B_6" and
GBIM_CANCEL_DATE IS NULL;
COLUMN name FORMAT A30
set long 9999
SELECT owner, name, category, sql_text, used
FROM dba_outlines
WHERE category = 'GOLD';
COLUMN hint FORMAT A50
SELECT node, stage, join_pos, hint
FROM dba_outline_hints
WHERE name = 'GOLD_OUTLINE_1';
ALTER SESSION SET query_rewrite_enabled=TRUE;
ALTER SESSION SET use_stored_outlines=GOLD;
***************************** Index Stats Validate Structure *******************************
CREATE OR REPLACE PROCEDURE PRC_GET_INDEX_STATS
AS
cursor c1 is
select a.owner,b.index_name,b.table_name,a.segment_type,a.tablespace_name,a.bytes/1024/1024 as
from dba_segments a, dba_indexes b
where a.segment_name=b.index_name
and a.owner=b.owner
and b.table_name in ('SCHNAV','SCHNAVBD','MKTPRICE','WEEKNAV','LASTNAV')
and b.owner='MFUND' order by b.table_name;
BEGIN
for i in c1 loop
execute immediate 'ANALYZE INDEX '||i.owner||'.'||i.index_name ||' VALIDATE STRUCTURE' ;
insert into view_index_stats
SELECT HEIGHT,BLOCKS,NAME,PARTITION_NAME,LF_ROWS,LF_BLKS,LF_ROWS_LEN,LF_BLK_LEN,BR_ROWS,BR_BLKS,
BR_ROWS_LEN,BR_BLK_LEN,DEL_LF_ROWS,DEL_LF_ROWS_LEN,DISTINCT_KEYS,MOST_REPEATED_KEY,BTREE_SPACE,
USED_SPACE,PCT_USED,ROWS_PER_KEY,BLKS_GETS_PER_ACCESS,PRE_ROWS,PRE_ROWS_LEN,OPT_CMPR_COUNT,
OPT_CMPR_PCTSAVE,i.table_name,i.owner,sysdate
FROM index_stats
WHERE name = i.index_name ;
commit;
end loop;
end;
/
***************** Top 10 Components to have allocated memory in Shared Pool *******************
select * From
(select name, bytes/1024/1024 MB
from v$sgastat
where pool='shared pool'
order by 2 desc)
where rownum<11;
****************************** Parsing Details in Shared Pool **********************************
select substr(d.host_name,1,8) machine_name,
substr(d.instance_name,1,8) instance_name,
rpad(nvl(program,machine),30,' ') program_name,
round(avg((a.value/b.value)*100),2) avg_parse,
round(max((a.value/b.value)*100),2) max_parse,
count(*) program_session_count
from v$session s,v$sesstat a,v$sesstat b, v$instance d
where b.value>0
and s.sid=a.sid
and a.sid=b.sid
and a.statistic#=(select statistic#
from v$statname
where name='parse count (hard)')
and b.statistic#=(select statistic#
from v$statname
where name='parse count (total)')
group by substr(d.host_name,1,8),substr(d.instance_name,1,8),rpad(nvl(program,machine),30,' ')
order by round(avg((a.value/b.value)*100),2);
select a.sid, a.program, b.value total_parse, c.value soft_parse, (b.value-c.value) hard_parse,
d.value cpu_time, e.value elapsed_time
from v$session a, v$sesstat b, v$sesstat c, v$sesstat d, v$sesstat e
where a.sid = b.sid
and a.sid = c.sid
and b.statistic# = 179
and c.statistic# = 180
and d.statistic# = 177
and e.statistic# = 178
and a.sid = d.sid
and a.sid = e.sid
order by b.value desc;
select sql_id from
(select sql_id,PARSING_SCHEMA_NAME, SHARABLE_MEM
from v$sqlarea
order by SHARABLE_MEM desc)
where rownum<10;
********************************** CPU Consuming Queries **************************************
col USERNAME for a10
col spid for a10
col sql_text for a30
set lines 200 pages 5000
SELECT /*+ ordered */ p.spid, s.sid, s.serial#, s.username, TO_CHAR(s.logon_time, 'mm-dd-yyyy hh24:mi') logon_time,
st.value, s.sql_id, sq.sql_text
FROM v$statname sn, v$sesstat st, v$process p, v$session s, v$sql sq
WHERE s.paddr=p.addr
AND s.sql_hash_value = sq.hash_value and s.sql_Address = sq.address
AND s.sid = st.sid
AND st.STATISTIC# = sn.statistic#
AND sn.NAME = 'CPU used by this session'
--AND p.spid = &osPID -- parameter to restrict for a specific PID
AND s.status = 'ACTIVE'
ORDER BY st.value desc;
****************** Largest 2 objects from each namespace in v$DB_OBJECT_CACHE ******************
select * from
(select row_number () over
(partition by namespace
order by sharable_mem desc) row_within,
namespace, sharable_mem, substr(name, 1,40) name
from v$db_object_cache
order by sharable_mem desc
)
where row_within<=2
order by namespace, row_within;
******************************* Procedure for enable trace *************************************
CREATE OR REPLACE PROCEDURE "SYS"."SET_USERPERF_TRACE" (sidin number,serialinnumber,enb varchar2) as
sprocid number;
begin
IF enb = 'Y' THEN
sys.dbms_system.set_ev(sidin,serialin,10046,12,'');
select spid
into sprocid
from sys.v_$process p, sys.v_$session s
where p.addr=s.paddr
and s.sid=sidin
and s.serial#=serialin;
dbms_output.put_line ('Trace Enabled');
dbms_output.put_line ('Session process id is '||sprocid);
end if;
if enb = 'N' THEN
sys.dbms_system.set_ev(sidin,serialin,10046,0,'');
dbms_output.put_line ('Trace Disabled');
end if;
if enb not in ('Y', 'N') THEN
dbms_output.put_line ('The 3rd parameter must me either Y or N');
end if;
end;
****************************** Estimate Index Creation size ***********************************
SET SERVEROUTPUT ON
DECLARE
v_used_bytes NUMBER(30);
v_Allocated_Bytes NUMBER(30);
BEGIN
DBMS_SPACE.CREATE_INDEX_COST
(
'CREATE INDEX finnonelea.NBFC_PMNT_DTL_IDX7 ON finnonelea.NBFC_PMNT_DTL(AGREEMENTID, PMNTDATE, BOU_TXN_DATE, TXNADVICEID) tablespace FINNINDX',
v_used_Bytes,
v_Allocated_Bytes
);
DBMS_OUTPUT.PUT_LINE(’Used Bytes: ‘ || TO_CHAR(v_used_Bytes));
DBMS_OUTPUT.PUT_LINE(’Allocated Bytes: ‘ || TO_CHAR(v_Allocated_Bytes));
END;
/
******************************** Partition Creation options ************************************
Exchange Partition :
ALTER TABLE big_table2
EXCHANGE PARTITION big_table_2007
WITH TABLE big_table
WITHOUT VALIDATION
UPDATE GLOBAL INDEXES;
Split Partition :
ALTER TABLE big_table
SPLIT PARTITION big_table_2007 AT (TO_DATE('31-DEC-2005 23:59:59', 'DD-MON-YYYY HH24:MI:SS'))
INTO (PARTITION big_table_2005,
PARTITION big_table_2007)
UPDATE GLOBAL INDEXES;
********************************** Get SQL Plan using SQL_PLAN ***********************************
SELECT '| Operation | PHV/Object Name | Rows | Bytes| Cost |'
as "Optimizer Plan:"
FROM dual
UNION ALL
SELECT * FROM (SELECT
rpad('|'||substr(lpad(' ',1*(depth-1))||operation||
decode(options, null,'',' '||options), 1, 32), 33, ' ')||'|'||
rpad(decode(id, 0, '----- '||to_char(hash_value)||' ['||to_char(child_number)||']
-----'
, substr(decode(substr(object_name, 1, 7), 'SYS_LE_', null,object_name)
||' ',1, 20)), 21, ' ')||'|'||
lpad(decode(cardinality,null,' ',
decode(sign(cardinality-1000), -1, cardinality||' ',
decode(sign(cardinality-1000000), -1, trunc(cardinality/1000)||'K',
decode(sign(cardinality-1000000000), -1, trunc(cardinality/1000000)||'M',
trunc(cardinality/1000000000)||'G')))), 7, ' ') || '|' ||
lpad(decode(bytes,null,' ',
decode(sign(bytes-1024), -1, bytes||' ',
decode(sign(bytes-1048576), -1, trunc(bytes/1024)||'K',
decode(sign(bytes-1073741824), -1, trunc(bytes/1048576)||'M',
trunc(bytes/1073741824)||'G')))), 6, ' ') || '|' ||
lpad(decode(cost,null,' ',
decode(sign(cost-10000000), -1, cost||' ',
decode(sign(cost-1000000000), -1, trunc(cost/1000000)||'M',
trunc(cost/1000000000)||'G'))), 8, ' ') || '|' as "Explain plan"
FROM v$sql_plan
WHERE address='&&addr'
AND hash_value='&&hash'
AND child_number=&&child
ORDER BY hash_value,child_number,id);
********************************** Get SQL Plan using dbms_xplan ***********************************
INSERT INTO plan_table
SELECT DISTINCT address,sysdate,'REMARKS',operation,options,object_node
,object_owner,object_name, 0,'object_type',optimizer,search_columns
,id,parent_id,position,cost,cardinality,bytes,other_tag
,partition_start,partition_stop,partition_id,other,distribution
,cpu_cost,io_cost,temp_space
FROM v$sql_plan
WHERE address='&&addr'
AND hash_value='&&hash'
AND child_number=&&child;
SELECT plan_table_output FROM TABLE(dbms_xplan.display('plan_table',null,'serial'));
***************************** Statement details and impact *****************************************
SELECT executions
,cpu_time
,DECODE(executions,0,0,ROUND(cpu_time/executions,2)) avg_cpu_time
,buffer_gets
,DECODE(executions,0,0,ROUND(buffer_gets/executions)) avg_buffer_gets
,rows_processed
,DECODE(executions,0,0,ROUND(rows_processed/executions)) avg_row_process
,disk_reads
,DECODE(executions,0,0,ROUND(disk_reads/executions)) avg_disk_read
,address||'/'||hash_value||'['||child_number||']' d
FROM v$sql
WHERE address='&&addr'
AND hash_value='&&hash';
*************************************** dbms_system package *****************************************
SET AUTOPRINT ON
VARIABLE val VARCHAR2(4000)
BEGIN
dbms_system.get_env('ORACLE_HOME', :val);
END;
/
EXEC DBMS_SYSTEM.KCFRMS(); flush max_wait, max read/write time to Zero
EXEC DBMS_SYSTEM.KSDDDT(); Added timestamp in trace file
DBMS_SYSTEM.KSDWRT(dest IN BINARY_INTEGER,tst IN VARCHAR2);
where
dest Destination file, 1=SQL trace file,
2=alert log,
3=both
e.g.
DECLARE
elapsed_time_t1 number;
elapsed_time_t2 number;
cpu_time_t1 number;
cpu_time_t2 number;
BEGIN
elapsed_time_t1:=dbms_utility.get_time;
cpu_time_t1:=dbms_utility.get_cpu_time;
dbms_stats.gather_schema_stats(user); -- do something expensive
elapsed_time_t2:=dbms_utility.get_time;
cpu_time_t2:=dbms_utility.get_cpu_time;
sys.dbms_system.ksdddt;
sys.dbms_system.ksdwrt(1, '=== Elapsed time: ' ||to_char((elapsed_time_t2 - elapsed_time_t1)/100)
||' sec CPU: ' || to_char((cpu_time_t2 - cpu_time_t1)/100) || ' sec');
END;
/
EXEC DBMS_SYSTEM.READ_EV(10046,:lev)
EXEC DBMS_SYSTEM.SET_EV(:sid, :serial, 65535, 3, 'errorstack'); Own session.
****************************** interesting facts !!!!!!!!!!!!!!!!!! ****************************
-- Dump control file contents
The event setting to disable free extent coalescing by SMON is 10269.
event = "10269 trace name context forever"
-- Dump optimizer statistics whenever a SQL statement is parsed (hint: change statement or flush pool)
alter session set events '10053 trace name context forever, level 1';
-- Dump a database block (File/ Block must be converted to DBA address)
-- Convert file and block number to a DBA (database block address). Eg:
variable x varchar2;
exec :x := dbms_utility.make_data_block_address(1,12);
print x
alter session set events 'immediate trace name blockdump level 50360894';
To know what is the Operating System Block size as also the Log Block Size use this SQL logging in as SYS user.
select max(lebsz) from sys.x$kccle;
Adjust_SCN
alter session set events 'immediate trace name adjust_scn level 1'
calculate the target wrap as the wrap in above SCN + 1.
alter session set events 'immediate trace name DUMP_SCN_CHECK level target_wrap';
Oracle Dumps
ALTER SESSION SET EVENTS 'immediate trace name global_area level level';
ORADEBUG DUMP GLOBAL_AREA level
Level Description
1 Include PGA
2 Include SGA
4 Include UGA
8 Include indirect memory dumps
ALTER SESSION SET EVENTS 'immediate trace name library_cache level level';
ORADEBUG DUMP LIBRARY_CACHE level
Level Description
1 Dump library cache statistics
2 Include hash table histogram
3 Include dump of object handles
4 Include dump of object structures (heap 0)
ALTER SESSION SET EVENTS 'immediate trace name row_cache level level';
ORADEBUG DUMP ROW_CACHE level
Level Description
1 Dump row cache statistics
2 Include hash table histogram
8 Include dump of object structures
ALTER SESSION SET EVENTS 'immediate trace name buffers level level';
Level Description
1 Buffer headers only
2 Level 1 + block headers
3 Level 2 + block contents
4 Buffer headers only + hash chain
5 Level 1 + block headers + hash chain
6 Level 2 + block contents + hash chain
8 Buffer headers only + hash chain + users/waiters
9 Level 1 + block headers + hash chain + users/waiters
10 Level 2 + block contents + hash chain + users/waiters
ALTER SESSION SET EVENTS 'immediate trace name heapdump level level';
Level Description
1 PGA summary
2 SGA summary
4 UGA summary
8 Callheap (Current)
16 Callheap (User)
32 Large pool
64 Streams pool
128 Java pool
1025 PGA with contents
2050 SGA with contents
4100 UGA with contents
8200 Callheap with contents (Current)
16400 Callheap with contents (User)
32800 Large pool with contents
65600 Streams pool with contents
131200 Java pool with contents
ALTER SESSION SET EVENTS 'immediate trace name heapdump_addr level 2, addr 2153245560';
ORADEBUG DUMP HEAPDUMP_ADDR 2 2153245560
ALTER SESSION SET EVENTS 'immediate trace name processstate level 10';
ORADEBUG DUMP PROCESSSTATE level
ALTER SESSION SET EVENTS 'immediate trace name systemstate level 10';
ORADEBUG DUMP SYSTEMSTATE level
event = "60 trace name systemstate level 10"
ALTER SESSION SET EVENTS 'immediate trace name errorstack level level';
ORADEBUG DUMP ERRORSTACK level
Level Description
0 Error stack only
1 Error stack and function call stack
2 As level 1 plus the process state
3 As level 2 plus the context area
ORADEBUG EVENT IMMEDIATE TRACE NAME ERRORSTACK level
ALTER SESSION SET EVENTS '942 trace name errorstack level 3';
ALTER SESSION SET EVENTS '604 trace name errorstack';
event = "942 trace name errorstack level 3"
ORADEBUG EVENT 942 TRACE NAME ERRORSTACK LEVEL 3
ALTER SESSION SET EVENTS 'immediate trace name hanganalyze level 5';
ORADEBUG DUMP HANGANALYZE 5
event = "60 trace name hanganalyze level 5"
ALTER SESSION SET EVENTS '60 trace name hanganalyze level 5';
ALTER SESSION SET EVENTS 'immediate trace name workareatab_dump level level';
ORADEBUG DUMP WORKAREATAB_DUMP level
Level Description
1 Global SGA Info
2 Workarea Table (Summary)
3 Workarea Table (Detail)
ALTER SESSION SET EVENTS 'immediate trace name enqueues level level';
ORADEBUG DUMP ENQUEUES level
Level Description
1 Dump a summary of active resources and locks, the resource free list and the hash table
2 Include a dump of resource structures
3 Include a dump of lock structures
ALTER SESSION SET EVENTS 'immediate trace name latches level level';
ORADEBUG DUMP LATCHES level
Level Description
1 Latches
2 Include statistics
ALTER SESSION SET EVENTS 'immediate trace name events level level';
ORADEBUG DUMP EVENTS level
Level Description
1 Session
2 Process
4 System
ALTER SYSTEM DUMP DATAFILE absolute_file_number BLOCK block_number;
ALTER SYSTEM DUMP DATAFILE absolute_file_number BLOCK MIN minimum_block_number BLOCK MAX maximum_block_number;
ALTER SYSTEM DUMP DATAFILE 'file_name' BLOCK block_number;
dd bs=8k if=filename skip=200 count=4 | od -x
ALTER SESSION SET EVENTS 'immediate trace name treedump level object_id';
ORADEBUG DUMP TREEDUMP object_id
ALTER SYSTEM DUMP UNDO_HEADER 'segment_name';
SELECT segment_id, segment_name
FROM dba_rollback_segs
ORDER BY segment_id;
ALTER SYSTEM DUMP UNDO BLOCK 'segment_name' XID xidusn xidslot xidsqn;
SELECT xidusn, xidslot, xidsqn
FROM v$transaction;
ALTER SESSION SET EVENTS 'immediate trace name file_hdrs level level';
ORADEBUG DUMP FILE_HDRS level
Level Description
1 Dump datafile entry from control file
2 Level 1 + generic file header
3 Level 2 + datafile header
10 Same as level 3
ALTER SESSION SET EVENTS 'immediate trace name controlf level level';
ORADEBUG DUMP CONTROLF level
Level Description
1 Generic file header
2 Level 1 + database information + checkpoint progress records
3 Level 2 + reuse record section
10 Same as level 3
ALTER SESSION SET EVENTS 'immediate trace name redohdr level 1';
ORADEBUG DUMP REDOHDR level
Level Description
1 Dump redo log entry from control file
2 Level 1 + generic file header
3 Level 2 + log file header
10 Same as level 3
ALTER SYSTEM DUMP LOGFILE 'FileName'
SCN MIN MinimumSCN
SCN MAX MaximumSCN
TIME MIN MinimumTime
TIME MAX MaximumTime
LAYER Layer
OPCODE Opcode
DBA MIN FileNumber . BlockNumber
DBA MAX FileNumber . BlockNumber
RBA MIN LogFileSequenceNumber . BlockNumber
RBA MAX LogFileSequenceNumber . BlockNumber;
01Jan1988
time = (((((((yyyy - 1988)) * 12 + mm - 1) * 31 + dd - 1) * 24 + hh) * 60 + mi) * 60 + ss);
********************************************* Plan change query ***************************************************
set lines 155
col execs for 999,999,999
col avg_etime for 999,999.999
col avg_lio for 999,999,999.9
col begin_interval_time for a30
col node for 99999
col node for 99999
break on plan_hash_value on startup_time skip 1
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
order by 1, 2, 3
/
********************************************* Current SQLAREA Query ***************************************************
cool begin_interval_time for a30
break on plan_hash_value on startup_time skip 1
select s.sql_id, s.plan_hash_value,
nvl(executions,0) execs,
(elapsed_time/decode(nvl(executions,0),0,1,executions))/1000000 avg_etime,
(buffer_gets/decode(nvl(buffer_gets,0),0,1,executions)) avg_lio
from gV$sqlarea S
where s.sql_id = nvl('&sql_id','4dqs2k5tynk61')
order by 1, 2, 3
/
********************************************* SYSTEMSTATE Dump ***************************************************
To get HANGANALYZE trace:
connect / as sysdba
oradebug setmypid
oradebug unlimit
oradebug hanganalyze 5
--Wait 30 seconds to give time to identify process state changes.
oradebug hanganalyze 5
exit
SECOND session:
Please make sure a separate session
- To get SYSTEM STATE DUMP:
From another SQL*Plus session:
connect / as sysdba
oradebug setmypid
oradebug unlimit
oradebug dump systemstate 255
--Wait 30 seconds
oradebug dump systemstate 10
-- this allows Oracle to show you the file name
oradebug tracefile_name
exit
Set Oracle Trace :
select spid from v$process where addr = (select paddr from v$session where sid=263);
oradebug SETOSPID 86255
oradebug UNLIMIT
oradebug EVENT 10046 trace name context forever,level 12;
oradebug TRACEFILE_NAME
oradebug CLOSE_TRACE;
oradebug event 10046 trace name context off
OR
exec dbms_system.set_ev(&sid,&serial,10046,12,'');
exec dbms_system.set_ev(&sid,&serial,10046,0,'');
exec dbms_system.set_ev(627,25756,10032,10,'');
alter session set events '10032 trace name context forever, level 10';
ORA11G Tracing
alter session set events 'sql_trace[SQL: 32cqz71gd8wy3] {pgadep: exactdepth 0}
plan_stat=all_executions,wait=true,bind=true';
alter session set events 'sql_trace[SQL: 32cqz71gd8wy3] {pgadep: exactdepth 0}
{callstack: fname opiexe} plan_stat=all_executions,
wait=true,bind=true';
alter session set events 'trace[RDBMS.SQL_Transform] [SQL: 32cqz71gd8wy3] disk=high
RDBMS.query_block_dump(1) processstate(1) callstack(1)';
alter session set events 'sql_trace wait=true, plan_stat=never';
alter session set events 'trace[sql_mon.*] memory=high,get_time=highres';
alter system set events 'trace[sql_mon | sql_optimizer.*]{process: pname = p000 | p005}';
alter system set events 'trace[sql_mon | sql_optimizer.*][sql: 7ujay4u33g337]{process: pname = p000 | p005}';
********************************************* Revoke SQLPLUS Access *************************************************
INSERT INTO SYSTEM.sqlplus_product_profile VALUES ('SQL*Plus', 'RAJESHSH', 'CONNECT', null, null, 'DISABLED', null, null);
INSERT INTO SYSTEM.sqlplus_product_profile VALUES ('SQL*Plus', 'RAJESHSH', 'INSERT', null, null, 'DISABLED', null, null);
INSERT INTO SYSTEM.sqlplus_product_profile VALUES ('SQL*Plus', 'RAJESHSH', 'UPDATE', null, null, 'DISABLED', null, null);
INSERT INTO SYSTEM.sqlplus_product_profile VALUES ('SQL*Plus', 'RAJESHSH', 'DELETE', null, null, 'DISABLED', null, null);
INSERT INTO SYSTEM.sqlplus_product_profile VALUES ('SQL*Plus', 'RAJESHSH', 'SELECT', null, null, 'DISABLED', null, null);
INSERT INTO SYSTEM.sqlplus_product_profile VALUES ('SQL*Plus', 'RAJESHSH', 'DROP', null, null, 'DISABLED', null, null);
INSERT INTO SYSTEM.sqlplus_product_profile VALUES ('SQL*Plus', 'RAJESHSH', 'CREATE', null, null, 'DISABLED', null, null);
INSERT INTO SYSTEM.sqlplus_product_profile VALUES ('SQL*Plus', 'RAJESHSH', 'SET', null, null, 'DISABLED', null, null);
*************************** Failed login details through Database Auditing *******************************************
col OS_USERNAME for a20
col TERMINAL for a25
col USERHOST for a30
col time for a15
set lines 150
select count(*),OS_USERNAME,username,USERHOST,terminal,to_char(timestamp,'DD-MON-YYYY') "Time"
from dba_audit_session
where returncode<>0
--and to_char(timestamp,'MON-YYYY')='JAN-2010'
and username='EBAPMS'
group by OS_USERNAME,username,USERHOST,terminal,to_char(timestamp,'DD-MON-YYYY');
**************************************** Get DBA jobs metadata details **********************************************
variable MYCALL VARCHAR2(1000)
exec dbms_ijob.FULL_EXPORT(3221,:MYCALL);
set lines 150
select :MYCALL from dual;
**************************************** Change Database DBTIMEZONE **********************************************
select tzname,tzabbrev
from V$TIMEZONE_NAMES
where tzabbrev = 'MST'
alter database set TIME_ZONE = '-07:00';
alter session set TIME_ZONE='-03:00';
select DBTIMEZONE from dual;
select SESSIONTIMEZONE from dual;
**************************************** Check Column USage Statistics ****************************************
select a.object_name, sum(b.EXECUTIONS) from v$sqlarea b, v$sql_plan a
where a.hash_value = b.hash_value
and object_name
in
(select index_name from dba_indexes where table_name = '&tname' )
group by a.object_name
order by 2;
OR
set lines 150
set pages 500
col table_name for a20
col column_name for a20
select a.object_name table_name, c.column_name,equality_preds, equijoin_preds, range_preds, like_preds
from dba_objects a, col_usage$ b, dba_tab_columns c
where a.object_id=b.OBJ#
and c.COLUMN_ID=b.INTCOL#
and a.object_name=c.table_name
and b.obj#=a.object_id
and a.object_name='&table_name'
and a.object_type='TABLE'
order by 3 desc,4 desc, 5 desc;
OR
set lines 150
set pages 5000
col c1 heading 'Object Name' format a30
col c2 heading 'Operation' format a15
col c3 heading 'Option' format a30
col c4 heading 'Index Usage Count' format 999,999
break on c1 skip 2
break on c2 skip 2
select p.object_name c1,p.operation c2,p.options c3,count(1) c4
from dba_hist_sql_plan p, dba_hist_sqlstat s
where p.object_owner='&&tableowner'
and p.operation like '%INDEX%'
and p.sql_id = s.sql_id
and p.object_name in (select index_name
from dba_indexes
where table_name='&tablename'
and owner='&tableowner')
group by p.object_name, p.operation, p.options
order by 4,1,2,3;
************ Index usage with disk reads and rows processed per index usage with snap time *******************
col c0 heading ‘Begin|Interval|time’ format a8
col c1 heading ‘Index|Name’ format a20
col c2 heading ‘Disk|Reads’ format 99,999,999
col c3 heading ‘Rows|Processed’ format 99,999,999
select
to_char(s.begin_interval_time,'mm-dd hh24') c0,
p.object_name c1,
sum(t.disk_reads_total) c2,
sum(t.rows_processed_total) c3
from dba_hist_sql_plan p, dba_hist_sqlstat t,dba_hist_snapshot s
where p.sql_id = t.sql_id
and t.snap_id = s.snap_id
and p.object_type like '%INDEX%'
and p.OBJECT_OWNER='DBOPFSRECO'
group by to_char(s.begin_interval_time,'mm-dd hh24'),p.object_name
order by c0,c1,c2 desc;
********************** Extract rows from a CORRUPT table creating ROWID from DBA_EXTENTS **********************
connect / as sysdba
set serveroutput on
set concat off
DECLARE
nrows number;
rid rowid;
dobj number;
ROWSPERBLOCK number;
BEGIN
ROWSPERBLOCK:=<VALUE CALCULATED IN STEP 1>;
nrows:=0;
select data_object_id into dobj
from dba_objects
where owner = '&&table_owner'
and object_name = '&&table_name'
-- and subobject_name = '<table partition>' Add this condition if table is partitioned;
for i in (select relative_fno, block_id, block_id+blocks-1 totblocks
from dba_extents
where owner = '&&owner'
and segment_name = '&&table_name'
-- and partition_name = '<table partition>' Add this condition if table is partitioned
-- and file_id != <OFFLINED DATAFILE> This condition is only used if a datafile needs to be skipped due to ORA-376
order by extent_id)
loop
for br in i.block_id..i.totblocks loop
for j in 1..ROWSPERBLOCK loop
begin
rid := dbms_rowid.ROWID_CREATE(1,dobj,i.relative_fno, br , j-1);
insert into <OWNER.NEW_TABLE> (<columns here>)
select /*+ ROWID(A) */ <columns here>
from &&table_owner.&&table_name A
where rowid = rid;
if sql%rowcount = 1 then nrows:=nrows+1; end if;
if (mod(nrows,10000)=0) then commit; end if;
exception when others then null;
end;
end loop;
end loop;
end loop;
COMMIT;
dbms_output.put_line('Total rows: '||to_char(nrows));
END;
/
******************************* Get datapump dump file header details **********************************
CREATE PROCEDURE show_dumpfile_info(
p_dir VARCHAR2 DEFAULT 'DATA_PUMP_DIR',
p_file VARCHAR2 DEFAULT 'EXPDAT.DMP')
AS
-- p_dir = directory object where dumpfile can be found
-- p_file = simple filename of export dumpfile (case-sensitive)
v_separator VARCHAR2(80) := '--------------------------------------' ||
'--------------------------------------';
v_path all_directories.directory_path%type := '?';
v_filetype NUMBER; -- 0=unknown 1=expdp 2=exp
v_fileversion VARCHAR2(15); -- 0.1=10gR1 1.1=10gR2 2.1=11g
v_info_table sys.ku$_dumpfile_info; -- PL/SQL table with file info
type valtype IS VARRAY(22) OF VARCHAR2(2048);
var_values valtype := valtype();
no_file_found EXCEPTION;
PRAGMA exception_init(no_file_found, -39211);
BEGIN
-- Show generic info:
-- ==================
dbms_output.put_line(v_separator);
dbms_output.put_line('Purpose..: Obtain details about export ' ||
'dumpfile. Version: 19-MAR-2008');
dbms_output.put_line('Required.: RDBMS version: 10.2.0.1.0 or higher');
dbms_output.put_line('. ' ||
'Export dumpfile version: 7.3.4.0.0 or higher');
dbms_output.put_line('. ' ||
'Export Data Pump dumpfile version: 10.1.0.1.0 or higher');
dbms_output.put_line('Usage....: ' ||
'execute show_dumfile_info(''DIRECTORY'', ''DUMPFILE'');');
dbms_output.put_line('Example..: ' ||
'exec show_dumfile_info(''MY_DIR'', ''expdp_s.dmp'')');
dbms_output.put_line(v_separator);
dbms_output.put_line('Filename.: ' || p_file);
dbms_output.put_line('Directory: ' || p_dir);
-- Retrieve Export dumpfile details:
-- =================================
SELECT directory_path INTO v_path FROM all_directories
WHERE directory_name = p_dir
OR directory_name = UPPER(p_dir);
dbms_datapump.get_dumpfile_info(
filename => p_file, directory => UPPER(p_dir),
info_table => v_info_table, filetype => v_filetype);
var_values.EXTEND(22);
FOR i in 1 .. 22 LOOP
BEGIN
SELECT value INTO var_values(i) FROM TABLE(v_info_table)
WHERE item_code = i;
EXCEPTION WHEN OTHERS THEN var_values(i) := '';
END;
END LOOP;
-- Show dumpfile details:
-- ======================
-- For Oracle10g Release 2 and higher:
-- dbms_datapump.KU$_DFHDR_FILE_VERSION CONSTANT NUMBER := 1;
-- dbms_datapump.KU$_DFHDR_MASTER_PRESENT CONSTANT NUMBER := 2;
-- dbms_datapump.KU$_DFHDR_GUID CONSTANT NUMBER := 3;
-- dbms_datapump.KU$_DFHDR_FILE_NUMBER CONSTANT NUMBER := 4;
-- dbms_datapump.KU$_DFHDR_CHARSET_ID CONSTANT NUMBER := 5;
-- dbms_datapump.KU$_DFHDR_CREATION_DATE CONSTANT NUMBER := 6;
-- dbms_datapump.KU$_DFHDR_FLAGS CONSTANT NUMBER := 7;
-- dbms_datapump.KU$_DFHDR_JOB_NAME CONSTANT NUMBER := 8;
-- dbms_datapump.KU$_DFHDR_PLATFORM CONSTANT NUMBER := 9;
-- dbms_datapump.KU$_DFHDR_INSTANCE CONSTANT NUMBER := 10;
-- dbms_datapump.KU$_DFHDR_LANGUAGE CONSTANT NUMBER := 11;
-- dbms_datapump.KU$_DFHDR_BLOCKSIZE CONSTANT NUMBER := 12;
-- dbms_datapump.KU$_DFHDR_DIRPATH CONSTANT NUMBER := 13;
-- dbms_datapump.KU$_DFHDR_METADATA_COMPRESSED CONSTANT NUMBER := 14;
-- dbms_datapump.KU$_DFHDR_DB_VERSION CONSTANT NUMBER := 15;
-- For Oracle11gR1:
-- dbms_datapump.KU$_DFHDR_MASTER_PIECE_COUNT CONSTANT NUMBER := 16;
-- dbms_datapump.KU$_DFHDR_MASTER_PIECE_NUMBER CONSTANT NUMBER := 17;
-- dbms_datapump.KU$_DFHDR_DATA_COMPRESSED CONSTANT NUMBER := 18;
-- dbms_datapump.KU$_DFHDR_METADATA_ENCRYPTED CONSTANT NUMBER := 19;
-- dbms_datapump.KU$_DFHDR_DATA_ENCRYPTED CONSTANT NUMBER := 20;
-- For Oracle11gR2:
-- dbms_datapump.KU$_DFHDR_COLUMNS_ENCRYPTED CONSTANT NUMBER := 21;
-- dbms_datapump.KU$_DFHDR_ENCPWD_MODE CONSTANT NUMBER := 22;
-- For Oracle10gR2: KU$_DFHDR_MAX_ITEM_CODE CONSTANT NUMBER := 15;
-- For Oracle11gR1: KU$_DFHDR_MAX_ITEM_CODE CONSTANT NUMBER := 20;
-- For Oracle11gR2: KU$_DFHDR_MAX_ITEM_CODE CONSTANT NUMBER := 22;
dbms_output.put_line('Disk Path: ' || v_path);
IF v_filetype = 1 OR v_filetype = 2 THEN
-- Get characterset name:
BEGIN
SELECT var_values(5) || ' (' || nls_charset_name(var_values(5)) ||
')' INTO var_values(5) FROM dual;
EXCEPTION WHEN OTHERS THEN null;
END;
IF v_filetype = 2 THEN
dbms_output.put_line(
'Filetype.: ' || v_filetype || ' (Original Export dumpfile)');
dbms_output.put_line(v_separator);
SELECT DECODE(var_values(13), '0', '0 (Conventional Path)',
'1', '1 (Direct Path)', var_values(13))
INTO var_values(13) FROM dual;
dbms_output.put_line('...Characterset ID.: ' || var_values(5));
dbms_output.put_line('...Direct Path.....: ' || var_values(13));
dbms_output.put_line('...Export Version..: ' || var_values(15));
ELSIF v_filetype = 1 THEN
dbms_output.put_line(
'Filetype.: ' || v_filetype || ' (Export Data Pump dumpfile)');
dbms_output.put_line(v_separator);
SELECT SUBSTR(var_values(1), 1, 15) INTO v_fileversion FROM dual;
SELECT DECODE(var_values(1),
'0.1', '0.1 (Oracle10g Release 1: 10.1.0.x)',
'1.1', '1.1 (Oracle10g Release 2: 10.2.0.x)',
'2.1', '2.1 (Oracle11g Release 1: 11.1.0.x)',
'3.1', '3.1 (Oracle11g Release 2: 11.2.0.x)',
var_values(1)) INTO var_values(1) FROM dual;
SELECT DECODE(var_values(2), '0', '0 (No)', '1', '1 (Yes)',
var_values(2)) INTO var_values(2) FROM dual;
SELECT DECODE(var_values(14), '0', '0 (No)', '1', '1 (Yes)',
var_values(14)) INTO var_values(14) FROM dual;
SELECT DECODE(var_values(18), '0', '0 (No)', '1', '1 (Yes)',
var_values(18)) INTO var_values(18) FROM dual;
SELECT DECODE(var_values(19), '0', '0 (No)', '1', '1 (Yes)',
var_values(19)) INTO var_values(19) FROM dual;
SELECT DECODE(var_values(20), '0', '0 (No)', '1', '1 (Yes)',
var_values(20)) INTO var_values(20) FROM dual;
SELECT DECODE(var_values(21), '0', '0 (No)', '1', '1 (Yes)',
var_values(21)) INTO var_values(21) FROM dual;
SELECT DECODE(var_values(22),
'1', '1 (Unknown)',
'2', '2 (None)',
'3', '3 (Password)',
'4', '4 (Dual)',
'5', '5 (Transparent)',
var_values(22)) INTO var_values(22) FROM dual;
dbms_output.put_line('...File Version....: ' || var_values(1));
dbms_output.put_line('...Master Present..: ' || var_values(2));
dbms_output.put_line('...GUID............: ' || var_values(3));
dbms_output.put_line('...File Number.....: ' || var_values(4));
dbms_output.put_line('...Characterset ID.: ' || var_values(5));
dbms_output.put_line('...Creation Date...: ' || var_values(6));
dbms_output.put_line('...Flags...........: ' || var_values(7));
dbms_output.put_line('...Job Name........: ' || var_values(8));
dbms_output.put_line('...Platform........: ' || var_values(9));
IF v_fileversion >= '2.1' THEN
dbms_output.put_line('...Instance........: ' || var_values(10));
END IF;
dbms_output.put_line('...Language........: ' || var_values(11));
dbms_output.put_line('...Block size......: ' || var_values(12));
dbms_output.put_line('...Metadata Compres: ' || var_values(14));
IF dbms_datapump.KU$_DFHDR_MAX_ITEM_CODE > 15 THEN
dbms_output.put_line('...Data Compressed.: ' || var_values(18));
dbms_output.put_line('...Metadata Encrypt: ' || var_values(19));
dbms_output.put_line('...Data Encrypted..: ' || var_values(20));
dbms_output.put_line('...Column Encrypted: ' || var_values(21));
dbms_output.put_line('...Encrypt.pwd. mod: ' || var_values(22));
IF v_fileversion = '2.1' or v_fileversion = '3.1' THEN
dbms_output.put_line('...Master Piece Cnt: ' || var_values(16));
dbms_output.put_line('...Master Piece Num: ' || var_values(17));
END IF;
END IF;
IF v_fileversion >= '1.1' THEN
dbms_output.put_line('...Job Version.....: ' || var_values(15));
END IF;
dbms_output.put_line('...Max Items Code..: ' ||
dbms_datapump.KU$_DFHDR_MAX_ITEM_CODE);
END IF;
ELSE
dbms_output.put_line('Filetype.: ' || v_filetype);
dbms_output.put_line(v_separator);
dbms_output.put_line('ERROR....: Not an export dumpfile.');
END IF;
dbms_output.put_line(v_separator);
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('Disk Path: ?');
dbms_output.put_line('Filetype.: ?');
dbms_output.put_line(v_separator);
dbms_output.put_line('ERROR....: Directory Object does not exist.');
dbms_output.put_line(v_separator);
WHEN no_file_found THEN
dbms_output.put_line('Disk Path: ' || v_path);
dbms_output.put_line('Filetype.: ?');
dbms_output.put_line(v_separator);
dbms_output.put_line('ERROR....: File does not exist.');
dbms_output.put_line(v_separator);
END;
/
SET serveroutput on SIZE 1000000
exec show_dumpfile_info('my_dir', 'exp_s.dmp')
******************************* Alternative ways to obtain dumpfile details **********************************
impdp DIRECTORY=my_dir DUMPFILE=expdp.dmp NOLOGFILE=y SQLFILE=impdp_s.sql TABLES=notexist TRACE=100300
******************************* Recover Standby database after loss of Archive log ****************************
This is the simulation based on the document. It works well (need to refresh controlfile after this -
that part is not included in this document).
The log sequence number requested prior to recovery is 60 and post recovery is 62....problem solved.
The size of the icnremental backup is also small (6M). If this is large,
then we could try with block change tracking...
Physical Standby Database Test Case for Missing/Corrupted Archived Log
Simulation :
Source
Machine 10.16.59.111
User ora10r3
DBNAME MTOH
Oracle Home /restorevol/BACKUPREST/ora10g
Archive Dest /restorevol/BACKUPREST/MTOH/arch
Destination
Machine 10.16.17.44
User ora10203
DBNAME MTOH
Oracle Home /ora10g/ora10203
Archive Dest /misuatdata/MTOH/oradata/arch
1) Synch primary and standby. Defer application of logs on standby
2) Get the current SCN of standby database
IME15K-05 (.) SQL >select current_scn from v$database;
CURRENT_SCN
--------------------------------
7765466164256
IME15K-05 (.) SQL >
3) Run a recover standby (do not actually apply), to find out the next sequence that would be required
IME15K-05 (.) SQL >recover standby database ;
ORA-00279: change 7765466164257 generated at 05/19/2010 11:55:11 needed for
thread 1
ORA-00289: suggestion : /misuatdata/MTOH/oradata/arch/MTOH_1_717173775_60.arc
ORA-00280: change 7765466164257 for thread 1 is in sequence #60
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
CANCEL
Media recovery cancelled.
IME15K-05 (.) SQL >
Archive Sequence 60 is needed for recovery. We will corrupt this archive for this demo.
4) Catalog datafiles from Physical Standby
Connect to the RMAN catalog (Created a test RMAN catalog so as to not mess with produciton RMAN database)
$ export ORACLE_SID=MTOH
{IME15K-05} /ora10g/ora10203/network/admin $ rman
Recovery Manager: Release 10.2.0.3.0 - Production on Wed May 19 12:21:09 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
RMAN>
RMAN> connect target /
connected to target database: MTOH (DBID=2326455671, not open)
RMAN> connect catalog rman/rman@rmandb
connected to recovery catalog database
RMAN> RMAN> CATALOG DATAFILECOPY
2> '/misuatdata/MTOH/oradata/data/MTOH_SYSTEM_01.dbf',
3> '/misuatdata/MTOH/oradata/data/MTOH_UNDOTBS_01.dbf',
4> '/misuatdata/MTOH/oradata/data/MTOH_SYSAUX_01.dbf',
5> '/misuatdata/MTOH/oradata/data/MTOH_TOOLS_01.dbf',
6> '/misuatdata/MTOH/oradata/data/MTOH_TS_LMTDATA_01.dbf',
7> '/misuatdata/MTOH/oradata/data/MTOH_TS_LMTDATA_02.dbf'
8> LEVEL 0 TAG 'STANDBY_ROLLFORWARD_BASE'
9> ;
cataloged datafile copy
datafile copy filename=/misuatdata/MTOH/oradata/data/MTOH_SYSTEM_01.dbf recid=22 stamp=719411454
cataloged datafile copy
datafile copy filename=/misuatdata/MTOH/oradata/data/MTOH_UNDOTBS_01.dbf recid=23 stamp=719411454
cataloged datafile copy
datafile copy filename=/misuatdata/MTOH/oradata/data/MTOH_SYSAUX_01.dbf recid=24 stamp=719411454
cataloged datafile copy
datafile copy filename=/misuatdata/MTOH/oradata/data/MTOH_TOOLS_01.dbf recid=25 stamp=719411454
cataloged datafile copy
datafile copy filename=/misuatdata/MTOH/oradata/data/MTOH_TS_LMTDATA_01.dbf recid=26 stamp=719411455
cataloged datafile copy
datafile copy filename=/misuatdata/MTOH/oradata/data/MTOH_TS_LMTDATA_02.dbf recid=27 stamp=719411455
RMAN>
4) Make changes to the primary database and switch log file
{IMASUNT54403-ZONE5} /restorevol/BACKUPREST/ora10g/backup $ sqlplus
SQL*Plus: Release 10.2.0.3.0 - Production on Wed May 19 12:37:34 2010
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Enter user-name: demo/demo
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
OBJLIST TABLE
SQL> create table object_list as select * from dba_objects
2 union select * from dba_objects;
Table created.
SQL> conn / as sysdba
Connected.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /restorevol/BACKUPREST/MTOH/arch/
Oldest online log sequence 59
Next log sequence to archive 61
Current log sequence 61
SQL> alter system switch logfile;
System altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /restorevol/BACKUPREST/MTOH/arch/
Oldest online log sequence 60
Next log sequence to archive 62
Current log sequence 62
SQL>
5) Use this backup and SCN as base
RMAN> BACKUP INCREMENTAL FROM SCN 7765466164256 DATABASE FORMAT '/restorevol/BACKUPREST/ora10g/backup/ForStandby_%U' tag 'FORSTANDBY';
Starting backup at 19-MAY-10
using channel ORA_DISK_1
RMAN-06755: WARNING: datafile 5: incremental-start SCN is too recent; using checkpoint SCN 7765465268142 instead
RMAN-06755: WARNING: datafile 6: incremental-start SCN is too recent; using checkpoint SCN 7765465268142 instead
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00003 name=/restorevol/BACKUPREST/MTOH/data/MTOH_SYSAUX_01.dbf
input datafile fno=00005 name=/restorevol/BACKUPREST/MTOH/data/MTOH_TS_LMTDATA_01
skipping datafile 00005 because it has not changed
input datafile fno=00006 name=/restorevol/BACKUPREST/MTOH/data/MTOH_TS_LMTDATA_02.dbf
skipping datafile 00006 because it has not changed
input datafile fno=00001 name=/restorevol/BACKUPREST/MTOH/data/MTOH_SYSTEM_01.dbf
input datafile fno=00002 name=/restorevol/BACKUPREST/MTOH/data/MTOH_UNDOTBS_01.dbf
input datafile fno=00004 name=/restorevol/BACKUPREST/MTOH/data/MTOH_TOOLS_01.dbf
channel ORA_DISK_1: starting piece 1 at 19-MAY-10
channel ORA_DISK_1: finished piece 1 at 19-MAY-10
piece handle=/restorevol/BACKUPREST/ora10g/backup/ForStandby_fjle2nl3_1_1 tag=FORSTANDBY comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:55
Finished backup at 19-MAY-10
6) Copy backupiece and catalog
{IME15K-05} /misuatdata/MTOH $ scp ora10r3@10.16.59.111://restorevol/BACKUPREST/ora10g/backup/ForStandby_fjle2nl3_1_1 .
{IME15K-05} /misuatdata/MTOH $ rman target / catalog rman/rman@rmandb
Recovery Manager: Release 10.2.0.3.0 - Production on Wed May 19 13:04:30 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: MTOH (DBID=2326455671, not open)
connected to recovery catalog database
RMAN> CATALOG START WITH '/misuatdata/MTOH/ForStandby';
searching for all files that match the pattern /misuatdata/MTOH/ForStandby
List of Files Unknown to the Database
=====================================
File Name: /misuatdata/MTOH/ForStandby_fjle2nl3_1_1
Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /misuatdata/MTOH/ForStandby_fjle2nl3_1_1
RMAN> exit
Recovery Manager complete.
7) Recover standby from RMAN
RMAN> RECOVER DATABASE NOREDO
2> ;
Starting recover at 19-MAY-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=148 devtype=DISK
datafile 5 not processed because file is read-only
datafile 6 not processed because file is read-only
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /misuatdata/MTOH/oradata/data/MTOH_SYSTEM_01.dbf
destination for restore of datafile 00002: /misuatdata/MTOH/oradata/data/MTOH_UNDOTBS_01.dbf
destination for restore of datafile 00003: /misuatdata/MTOH/oradata/data/MTOH_SYSAUX_01.dbf
destination for restore of datafile 00004: /misuatdata/MTOH/oradata/data/MTOH_TOOLS_01.dbf
channel ORA_DISK_1: reading from backup piece /misuatdata/MTOH/ForStandby_fjle2nl3_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/misuatdata/MTOH/ForStandby_fjle2nl3_1_1 tag=FORSTANDBY
channel ORA_DISK_1: restore complete, elapsed time: 00:00:16
Finished recover at 19-MAY-10
RMAN>
8) Refresh controlfile of standby from production
On production
SQL> alter database create standby controlfile as '/tmp/mtoh_stby.ctl';
Database altered.
SQL>
On standby
Copy the controlfile from production
{IME15K-05} /misuatdata/MTOH/oradata/cntrl $ scp ora10r3@10.16.59.111:/tmp/mtoh_stby.ctl .
Change init.ora
control_files = ('/misuatdata/MTOH/oradata/cntrl/mtoh_stby.ctl')
9) Startup and see recovery point
IME15K-05 (.) SQL >recover standby database;
ORA-00279: change 7765466166167 generated at 05/19/2010 12:54:59 needed for
thread 1
ORA-00289: suggestion : /misuatdata/MTOH/oradata/arch/MTOH_1_717173775_62.arc
ORA-00280: change 7765466166167 for thread 1 is in sequence #62
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
****************************************** Truss Command *************************************************
truss -wall -rall -fall -dall -o /ipsdata_temp/trace_fileash.out -p 504166
truss -rall -wall -fall -vall -d -o /tmp/truss.log -p 22459
********************************************** Opatch ****************************************************
opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir ./9352164
OR
opatch prereq CheckConflictAgainstOHWithDetail -ph ./9352164
********************************** Recreate Oracle Inventory *********************************************
./runInstaller -silent -invPtrLoc /oracle10/oracle/oraInst.loc -attachHome ORACLE_HOME=/oracle10/oracle ORACLE_HOME_NAME=Ora10gHome
**************************************** Statistics in ROWCACHE ******************************************
SELECT parameter
, sum(gets)
, sum(getmisses)
, 100*sum(gets - getmisses) / sum(gets) pct_succ_gets
, sum(modifications) updates
FROM V$ROWCACHE
WHERE gets > 0
GROUP BY parameter;
************************************************** Wait Type *********************************************
set lines 150
set pages 5000
col machine for a30
col username for a10
col Wait_type for a10
col program for a30
select sid,serial#,username,status,
(case when event='read by other session' then 'BBW'
when event='db file scattered read' then 'FTS'
else 'CBC' end) Wait_type,
sql_id,(select executions from v$sqlarea where sql_id=a.sql_id) executions,machine,program
from v$session a
where event in ('read by other session','db file scattered read','latch: cache buffers chains')
and state='WAITING'
order by Wait_type,executions desc
/
********************************************* SQL Refactoring ********************************************
VARIABLE name VARCHAR2(20)
exec name := 'SCOTT'
SELECT ename, hiredate, sal
FROM emp
WHERE ename = NVL (:name, ename);
TO
VARIABLE name VARCHAR2(20)
exec name := 'SCOTT'
SELECT ename, hiredate, sal
FROM emp
WHERE ename LIKE NVL (:name, '%');
-------------------------------------------------------------------------------------------------
SELECT ename, hiredate, sal
FROM emp
WHERE TRUNC (hiredate) = TRUNC (SYSDATE);
TO
SELECT ename, hiredate, sal
FROM emp
WHERE hiredate BETWEEN TRUNC (SYSDATE)
AND TRUNC (SYSDATE) + .99999;
-------------------------------------------------------------------------------------------------
and to_char(date_col,'MM/YYYY') = to_char(p_date,'MM/YYYY')
to
and date_col >= trunc(p_date,'MON')
and date_col < trunc(add_months(p_date,1),'MON')
and to_char(date_col,'DD-MM-YYYY') = to_char(sysdate,'DD-MM-YYYY')
to
and date_col >= trunc(p_date,'DD')
and date_col < trunc(p_date+1,'DD')
TRUNC(DOJ)='15-Jul-2010'
To
doj >= trunc(to_Date('15-Jul-2010','DD-Mon-YYYY')) and
doj < trunc(to_Date('15-Jul-2010','DD-Mon-YYYY')+1)
-------------------------------------------------------------------------------------------------
select cloc.cust_id,branch_id,substr(ltrim(rtrim(cloc.loc_desc)),1,30)
from retadm.cloc
where cloc.bank_id= 'ICI'
and cloc.cust_id not in (select cust_id
from retadm.brcm)
order by cloc.r_cre_time;
TO
select cloc.cust_id,branch_id,substr(ltrim(rtrim(cloc.loc_desc)),1,30)
from retadm.cloc cloc
where cloc.bank_id= 'ICI'
and not exists (select 1
from retadm.brcm brcm
where cloc.cust_id=brcm.cust_id)
order by cloc.r_cre_time;
OR
select cloc.cust_id,cloc.branch_id,substr(ltrim(rtrim(cloc.loc_desc)),1,30)
from retadm.cloc cloc
left outer
join retadm.brcm brcm
on cloc.cust_id=brcm.cust_id
where cloc.bank_id= 'ICI'
and brcm.cust_id is null
order by cloc.r_cre_time;
OR
with d_cust as
(select cloc.cust_id,branch_id,substr(ltrim(rtrim(cloc.loc_desc)),1,30)
from retadm.cloc
where cloc.bank_id= 'ICI')
select * from
d_cust
where not exists (select d_cust.cust_id
from retadm.brcm
where brcm.cust_id =d_cust.cust_id);
********************************************* SQL PROFILE *********************************************
SET SERVEROUTPUT ON
-- Tuning task created for specific a statement from the AWR.
DECLARE
l_sql_tune_task_id VARCHAR2(2000);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
SQL_TEXT => 'select distinct b.Bonds_ShortName ID ,
case when count(a.CashFlowType) > 1 then 801 else 700 end NewCashFlowType
from actuate.VIEW_BONDS_CASH_FLOW a,mxg.VIEW_BOND_FISV b
where trim(a.Bonds_ShortName) = trim(b.Bonds_ShortName)
and upper(a.CashFlowType) = ''N''
group by b.Bonds_ShortName',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 2000,
task_name => 'sqltext_tuning_task',
description => 'Tuning task for statement sqltext');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/
EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'sqltext_tuning_task');
SELECT task_name, status FROM dba_advisor_log WHERE owner = 'SYS';
SET LONG 10000;
SET PAGESIZE 1000
SET LINESIZE 200
SELECT DBMS_SQLTUNE.report_tuning_task('sqltext_tuning_task') AS recommendations FROM dual;
BEGIN
DBMS_SQLTUNE.drop_tuning_task (task_name => 'sqltext_tuning_task');
END;
/
set pages 500
set lines 150
col hint for a100
select p.name,attr#,attr_val hint
from dba_sql_profiles p, sqlprof$attr h
where p.signature = h.signature
and name like ('&profile_name')
order by attr#
/
************************ used in 10g to disable the merge join cartesian and not in 9i.******************
alter session set "_optimizer_mjc_enabled"=false ;
alter session set "_optimizer_cartesian_enabled"=false ;
alter session set "_optimizer_sortmerge_join_enabled"=false ;
************************************* Generate SQL_ID from SQL_TEXT *************************************
select kglnahsv, kglnahsh
from x$kglob
where kglnaobj ='select sysdate from dual';
KGLNAHSV KGLNAHSH
--------------------------------- ----------
b3dbd4abf1156b09780cbaeb8ba84e61 2343063137
b3dbd4abf1156b09780cbaeb8ba84e61 2343063137
select sql_id, hash_value, old_hash_value
from v$sql
where sql_text ='select sysdate from dual';
SQL_ID HASH_VALUE OLD_HASH_VALUE
------------- ---------- --------------
7h35uxf5uhmm1 2343063137 3742653144
Python script :
def sqlid_2_hash(sqlid):
sum = 0
i = 1
alphabet = '0123456789abcdfghjkmnpqrstuvwxyz'
for ch in sqlid:
sum += alphabet.index(ch) * (32**(len(sqlid) - i))
i += 1
return sum % (2 ** 32)
def stmt_2_sqlid(stmt):
h = hashlib.md5(stmt + '\x00').digest()
(d1,d2,msb,lsb) = struct.unpack('IIII', h)
sqln = msb * (2 ** 32) + lsb
stop = math.log(sqln, math.e) / math.log(32, math.e) + 1
sqlid = ''
alphabet = '0123456789abcdfghjkmnpqrstuvwxyz'
for i in range(0, stop):
sqlid = alphabet[(sqln / (32 ** i)) % 32] + sqlid
return sqlid
def stmt_2_hash(stmt):
return struct.unpack('IIII', hashlib.md5(stmt + '\x00').digest())[3]
select
lower(trim('&1')) sql_id
, trunc(mod(sum((instr('0123456789abcdfghjkmnpqrstuvwxyz',substr(lower(trim('&1')),level,1))-1)
*power(32,length(trim('&1'))-level)),power(2,32))) hash_value
from
dual
connect by
level <= length(trim('&1'))
/
Oracle 11G
select dbms_sqltune_util0.sqltext_to_sqlid('select sysdate from dual'||chr(0)) sql_id from dual;
************************************* Generate STATSPACK with Diff Level *************************************
EXECUTE STATSPACK.SNAP(i_snap_level=>7);
EXECUTE STATSPACK.SNAP(i_snap_level=>7, i_modify_parameter=>'true');
************************************* mkdir in DOS using DATE cmd ********************************************
set date_dir=%date:~7,2%_%date:~4,2%_%date:~12,2%
mkdir %date_dir%
******************************* Get File# and Block# Details from DBA ****************************************
CREATE OR REPLACE PROCEDURE cdba ( iblock VARCHAR2, imode VARCHAR2 ) AS
x NUMBER;
digits# NUMBER;
results NUMBER := 0;
file# NUMBER := 0;
block# NUMBER := 0;
cur_digit CHAR(1);
cur_digit# NUMBER;
BEGIN
IF upper(imode) = 'H' THEN
digits# := length( iblock );
FOR x IN 1..digits# LOOP
cur_digit := upper(substr( iblock, x, 1 ));
IF cur_digit IN ('A','B','C','D','E','F') THEN
cur_digit# := ascii( cur_digit ) - ascii('A') +10;
ELSE
cur_digit# := to_number(cur_digit);
END IF;
results := (results *16) + cur_digit#;
END LOOP;
ELSE
IF upper(imode) = 'D' THEN
results := to_number(iblock);
ELSE
dbms_output.put_line('H = Hex Input ... D = Decimal Input');
RETURN;
END IF;
END IF;
file# := dbms_utility.data_block_address_file(results);
block# := dbms_utility.data_block_address_block(results);
dbms_output.put_line('.');
dbms_output.put_line( 'The file is ' || file# );
dbms_output.put_line( 'The block is ' || block# );
END;
/
************************************* Background Process Details ***************************************
col ksbddidn for a15
col ksmfsnam for a20
col ksbdddsc for a60
set lines 150 pages 5000
SELECT ksbdd.ksbddidn, ksmfsv.ksmfsnam, ksbdd.ksbdddsc
FROM x$ksbdd ksbdd, x$ksbdp ksbdp, x$ksmfsv ksmfsv
WHERE ksbdd.indx = ksbdp.indx
AND ksbdp.addr = ksmfsv.ksmfsadr
ORDER BY ksbdd.ksbddidn;
***************************** Get SQL Statement details for specific OPERATION from AWR ****************
select * From SYS.AUDIT_ACTIONS;
select b.SNAP_ID,
(select BEGIN_INTERVAL_TIME from dba_hist_snapshot where snap_id= b.snap_id) as sample_time,
a.SQL_TEXT,
b.sql_id,
(select PARSING_SCHEMA_NAME from dba_hist_sqlstat where snap_id= b.snap_id and sql_id= a.sql_id) as schema_name
from dba_hist_sqltext a, dba_hist_sqlstat b
where a.sql_id=b.sql_id
and a.COMMAND_TYPE=7
and upper(a.SQL_TEXT) like '%ACMT%'
order by 1;
************************************** DB Growth shell script ******************************************
ORACLE_SID=$1; export ORACLE_SID
ORACLE_HOME=$2; export ORACLE_HOME
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/ccs/lib; export LD_LIBRARY_PATH
PATH=$PATH:$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:/usr/sbin:/usr/ccs/bin:/usr/ucb; export PATH
ORACLE_TERM=vt100; export ORACLE_TERM
TNS_ADMIN=$ORACLE_HOME/network/admin; export TNS_ADMIN
datem=`date '+%d_%b_%Y'`; export date
sqlplus -s "/ as sysdba" <<EOF
set time on
break on report
compute sum label "Total " of growth on report
set lines 120 pages 2000
TTITLE COL 40 FORMAT 9 '$1 Growth(MB)' skip 1 COL 38 FORMAT 9 =================== skip 1
set echo off
@$ORACLE_HOME/maint/db_growth/db_growth.sql;
spool $ORACLE_HOME/maint/db_growth/log/db_growth_$1_$datem.txt
select b.DT-1 DATE_V,b.NAME,b.USED_SPACE-a.USED_SPACE Growth
from space_details a,space_details b
where trunc(a.dt)=trunc(b.dt)-1
and trunc(b.dt) in (select trunc(max(dt))
from space_details)
and a.name=b.name
and (b.USED_SPACE-a.USED_SPACE)<>0
and b.name not like '%UNDO%' order by 3;
spool off
EOF
mailx -s "DB Growth of $ORACLE_SID database on $datem" dinesh.bandelkar@ext.icicibank.com <$ORACLE_HOME/maint/db_growth/log/db_growth_$1_$datem.txt
--------------------------------------
db_growth.sql
declare
sys_date date;
max_date date;
begin
select trunc(sysdate) into sys_date from dual;
select decode(trunc(max(dt)),trunc(sysdate),trunc(sysdate),null,'01-JAN-1000',trunc(max(dt))) into max_date
from space_details;
if sys_date != max_date then
insert into space_details
select sysdate DT,a.tablespace_name NAME,(b.totalspace - a.freespace) USED_SPACE,
a.freespace FREE_SPACE,b.totalspace TOTAL_SPACE,round(100 * (a.freespace / b.totalspace)) FREE_PERCNT
from
(select tablespace_name,sum(bytes)/1024/1024 TotalSpace
from dba_data_files
group by tablespace_name) b,
(select tablespace_name,sum(bytes)/1024/1024 FreeSpace
from dba_free_space
group by tablespace_name) a
where b.tablespace_name = a.tablespace_name
order by 2;
commit;
end if;
end;
/
*************************************** HP-UX Enable X11 Frowarding ******************************************
vi /opt/ssh/etc/sshd_config
and make sure "X11Forwarding yes" is uncommented. If you made a change to this file, restart the daemon:
/sbin/init.d/secsh stop
/sbin/init.d/secsh start
************************* Handling and resolving unshared cursors/large version_counts ***********************
select sql_text, hash_value,address from v$sqlarea where sql_text like 'select count(*) from emp%';
To see the CHILDREN (I expect to see 1 at this point) :-
9i - select * from v$sql_shared_cursor where kglhdpar = '0000000386BC2E58'
10G - select * from v$sql_shared_cursor where address = '0000000386BC2E58'
ADDRESS KGLHDPAR U S O O S L S E B P I S T A B D L T R I I R L I O S M U T N F
---------------- ---------------- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
0000000386BC2D08 0000000386BC2E58 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N
U - UNBOUND_CURSOR - The existing child cursor was not fully built (in other words, it was not optimized)
S - SQL_TYPE_MISMATCH - The SQL type does not match the existing child cursor
O - OPTIMIZER_MISMATCH - The optimizer environment does not match the existing child cursor
select count(*) from emp; ->> 1 PARENT, 1 CHILD
alter session set optimizer_mode=ALL_ROWS
select count(*) from emp; ->> 1 PARENT, 2 CHILDREN (The optimizer mode has changed and therefore the existing child cannot be reused)
O - OUTLINE_MISMATCH - The outlines do not match the existing child cursor
alter session set use_stored_outlines = OUTLINES1;
select count(*) from emp;
alter session set use_stored_oulines= OUTLINES2;
select count(*) from emp;
S - STATS_ROW_MISMATCH - The existing statistics do not match the existing child cursor
L - LITERAL_MISMATCH - Non-data literal values do not match the existing child cursor
S - SEC_DEPTH_MISMATCH - Security level does not match the existing child cursor
E - EXPLAIN_PLAN_CURSOR - The child cursor is an explain plan cursor and should not be shared
B - BUFFERED_DML_MISMATCH - Buffered DML does not match the existing child cursor
P - PDML_ENV_MISMATCH - PDML environment does not match the existing child cursor
I - INST_DRTLD_MISMATCH - Insert direct load does not match the existing child cursor
S - SLAVE_QC_MISMATCH -The existing child cursor is a slave cursor and the new one was issued by the coordinator
T - TYPECHECK_MISMATCH - The existing child cursor is not fully optimized
A - AUTH_CHECK_MISMATCH - Authorization/translation check failed for the existing child cursor
B - BIND_MISMATCH - The bind metadata does not match the existing child cursor
variable a varchar2(100);
select count(*) from emp where ename = :a ->> 1 PARENT, 1 CHILD
variable a varchar2(400);
select count(*) from emp where ename = :a ->> 1 PARENT, 2 CHILDREN (The bind 'a' has now changed in definition)
D - DESCRIBE_MISMATCH - The typecheck heap is not present during the describe for the child cursor
L - LANGUAGE_MISMATCH - The language handle does not match the existing child cursor
T - TRANSLATION_MISMATCH - The base objects of the existing child cursor do not match
R - ROW_LEVEL_SEC_MISMATCH - The row level security policies do not match
I - INSUFF_PRIVS - Insufficient privileges on objects referenced by the existing child cursor
I - INSUFF_PRIVS_REM - Insufficient privileges on remote objects referenced by the existing child cursor
R - REMOTE_TRANS_MISMATCH - The remote base objects of the existing child cursor do not match
USER1: select count(*) from table@remote_db
USER2: select count(*) from table@remote_db (Although the SQL is identical, the dblink pointed to by remote_db may be a private dblink which resolves
to a different object altogether)
L - LOGMINER_SESSION_MISMATCH
I - INCOMP_LTRL_MISMATCH
O - OVERLAP_TIME_MISMATCH - error_on_overlap_time mismatch
S - SQL_REDIRECT_MISMATCH - sql redirection mismatch
M - MV_QUERY_GEN_MISMATCH - materialized view query generation
U - USER_BIND_PEEK_MISMATCH - user bind peek mismatch
T - TYPCHK_DEP_MISMATCH - cursor has typecheck dependencies
N - NO_TRIGGER_MISMATCH - no trigger mismatch
F - FLASHBACK_CURSOR - No cursor sharing for flashback
-------------------------------------------------------------------------------
create or replace view SQL_SHARED_CURSOR
as select * from sys.v$sql_shared_cursor;
create or replace function version_rpt(p_sql_id varchar2 default null,p_hash number default null) return DBMS_DEBUG_VC2COLL PIPELINED is
type vc_arr is table of varchar2(32767) index by binary_integer;
type num_arr is table of number index by binary_integer;
v_version varchar2(100);
v_colname vc_arr;
v_Ycnt num_arr;
v_count number:=-1;
v_no number;
v_all_no number:=-1;
v_query varchar2(4000);
v_sql_where varchar2(4000);
v_sql_where2 varchar2(4000);
v_sql_id varchar2(15):=p_sql_id;
v_addr varchar2(100);
V_coladdr varchar2(100);
v_hash number:=p_hash;
v_mem number;
v_parses number;
theCursor number;
columnValue char(1);
status number;
v_driver varchar2(1000);
TYPE cursor_ref IS REF CURSOR;
vc cursor_ref;
v_bind_dumped boolean:=false;
v_auth_dumped boolean:=false;
BEGIN
select version into v_version from v$instance;
v_coladdr:=case when v_version like '9%' then 'KGLHDPAR' else 'ADDRESS' end;
if v_sql_id is not null then
open vc for 'select sql_text query,hash_value hash,rawtohex(ADDRESS) addr , sql_id , SHARABLE_MEM,PARSE_CALLS '
|| ' from v$sqlarea where sql_id=:v_sql_id '
using v_sql_id ;
else -- Use Hash Value
open vc for
'select sql_text query,hash_value,rawtohex(ADDRESS) addr,'||case when v_version like '9%' then ' NULL ' end
||' sql_id,SHARABLE_MEM,PARSE_CALLS '
||' from v$sqlarea where hash_value=:v_hash'
using v_hash;
end if;
PIPE ROW('Version Count Report Version 3.1 -- Today''s Date '||to_char(sysdate,'dd-mon-yy hh24:mi')) ;
/*
This loop is in the remote case there are more than 1 SQL with the same hash value or sql_id
After this loop I cannot guarantee that I can distinguish the colliding SQL from one another.
*/
loop
fetch vc into v_query,v_hash,v_addr,v_sql_id,v_mem,v_parses;
exit when vc%notfound;
v_colname.delete;
v_Ycnt.delete;
v_count:=-1;
v_no:=0;
v_all_no:=-1;
PIPE ROW('================================================================');
PIPE ROW('Addr: '||v_addr||' Hash_Value: '||v_hash||' SQL_ID '||v_sql_id);
PIPE ROW('Sharable_Mem: '||v_mem||' bytes Parses: '||v_parses);
PIPE ROW('Stmt: ');
for i in 0 .. trunc(length(v_query)/64) loop
PIPE ROW(i||' '||substr(v_query,1+i*64,64));
end loop;
if v_sql_id is not null then
v_sql_where:=' WHERE SQL_ID='''||v_sql_id||'''';
else
v_sql_where:=' WHERE hash_value='||to_char(v_hash);
end if;
v_sql_where2:=' and '||v_coladdr||'=HEXTORAW('''||V_ADDR||''')';
SELECT COLUMN_NAME,0 bulk collect into v_colname,v_Ycnt
from cols
where table_name='SQL_SHARED_CURSOR'
and CHAR_LENGTH=1
order by column_id;
v_query:='';
for i in 1 .. v_colname.count loop
v_query:= v_query ||','|| v_colname(i);
end loop;
v_query:= 'SELECT '||substr(v_query,2) || ' FROM SQL_SHARED_CURSOR ';
v_query:=v_query||v_sql_where||v_sql_where2;
begin
theCursor := dbms_sql.open_cursor;
sys.dbms_sys_sql.parse_as_user( theCursor, v_Query, dbms_sql.native );
for i in 1 .. v_colname.count loop
dbms_sql.define_column( theCursor, i, columnValue, 8000 );
end loop;
status := dbms_sql.execute(theCursor);
while (dbms_sql.fetch_rows(theCursor) >0) loop
v_no:=0;
v_count:=v_count+1;
for i in 1..v_colname.count loop
dbms_sql.column_value(theCursor, i, columnValue);
if columnValue='Y' then
v_Ycnt(i):=v_Ycnt(i)+1;
else
v_no:=v_no+1;
end if;
end loop;
if v_no=v_colname.count then
v_all_no:=v_all_no+1;
end if;
end loop;
dbms_sql.close_cursor(theCursor);
end;
PIPE ROW('');
PIPE ROW('Versions Summary');
PIPE ROW('----------------');
for i in 1 .. v_colname.count loop
if v_Ycnt(i)>0 then
PIPE ROW(v_colname(i)||' :'||v_Ycnt(i));
end if;
end loop;
If v_all_no>1 then
PIPE ROW('Versions with ALL Columns as "N" :'||v_all_no);
end if;
PIPE ROW('Total Versions:'||v_count);
PIPE ROW(' ');
declare
v_phv num_arr;
v_phvc num_arr;
begin
v_sql_where2:=' and ADDRESS=HEXTORAW('''||V_ADDR||''')';
v_query:='select plan_hash_value,count(*) from v$sql '||v_sql_where||v_sql_where2||' group by plan_hash_value';
execute immediate v_query bulk collect into v_phv,v_phvc;
PIPE ROW('Plan Hash Value Summary');
PIPE ROW('-----------------------');
PIPE ROW('Plan Hash Value Count');
PIPE ROW('=============== =====');
for i in 1 .. v_phv.count loop
PIPE ROW(to_char(v_phv(i),'99999999999999')||' '||to_char(v_phvc(i),'9999'));
end loop;
PIPE ROW(' ');
end;
end loop;
for i in 1 .. v_colname.count loop
if v_Ycnt(i)>0 then
PIPE ROW('~~~~~~~~~~~~~~'||rpad('~',length(v_colname(i)),'~'));
PIPE ROW('Details for '||v_colname(i)||' :');
PIPE ROW('');
if ( v_colname(i) in ('BIND_MISMATCH','USER_BIND_PEEK_MISMATCH','BIND_EQUIV_FAILURE','BIND_UACS_DIFF')
or (v_version like '11.1%' and v_colname(i)='ROW_LEVEL_SEC_MISMATCH')) then
if v_bind_dumped=true then -- Dump only once
PIPE ROW('Details shown already.');
else
v_bind_dumped:=true;
if v_version like '9%' then
PIPE ROW('No details for '||v_version);
else
PIPE ROW('Consolidated details for :');
PIPE ROW('BIND_MISMATCH,USER_BIND_PEEK_MISMATCH,BIND_UACS_DIFF and');
PIPE ROW('BIND_EQUIV_FAILURE (Mislabled as ROW_LEVEL_SEC_MISMATCH BY bug 6964441 in 11gR1)');
PIPE ROW('');
declare
v_position num_arr;
v_maxlen num_arr;
v_minlen num_arr;
v_dtype num_arr;
v_prec num_arr;
v_scale num_arr;
v_n num_arr;
begin
v_query:='select position,min(max_length),max(max_length),datatype,precision,scale,count(*) n'
||' from v$sql_bind_capture where sql_id=:v_sql_id'
||' group by sql_id,position,datatype,precision,scale'
||' order by sql_id,position,datatype,precision,scale';
EXECUTE IMMEDIATE v_query
bulk collect into v_position, v_minlen, v_maxlen , v_dtype ,v_prec ,v_scale , v_n
using v_sql_id;
PIPE ROW('from v$sql_bind_capture');
PIPE ROW('COUNT(*) POSITION MIN(MAX_LENGTH) MAX(MAX_LENGTH) DATATYPE (PRECISION,SCALE)');
PIPE ROW('======== ======== =============== =============== ======== ================');
for c in 1 .. v_position.count loop
PIPE ROW( to_char(v_n(c),'9999999')||' '||to_char(v_position(c),'9999999')||' '|| to_char(v_minlen(c),'99999999999999')
||' '|| to_char(v_maxlen(c),'99999999999999')
||' '|| to_char(v_dtype(c),'9999999')||' ('|| v_prec(c)||','||v_scale(c)||')' );
end loop;
if v_version like '11%' then
v_query:='select sum(decode(IS_OBSOLETE,''Y'', 1, 0)),sum(decode(IS_BIND_SENSITIVE ,''Y'',1, 0))'
||',sum(decode(IS_BIND_AWARE,''Y'',1,0)),sum(decode(IS_SHAREABLE,''Y'',1,0))'
||' from v$sql where sql_id = :v_sql_id';
EXECUTE IMMEDIATE v_query
bulk collect into v_position, v_minlen, v_maxlen , v_dtype
using v_sql_id;
PIPE ROW('');
PIPE ROW('SUM(DECODE(column,Y, 1, 0) FROM V$SQL');
PIPE ROW('IS_OBSOLETE IS_BIND_SENSITIVE IS_BIND_AWARE IS_SHAREABLE');
PIPE ROW('=========== ================= ============= ============');
for c in 1 .. v_position.count loop
PIPE ROW(to_char(v_position(c),'9999999999')||' '|| to_char(v_minlen(c),'9999999999999999')
||' '|| to_char(v_maxlen(c),'999999999999')
||' '|| to_char(v_dtype(c),'99999999999'));
end loop;
end if;
end;
end if;
end if;
elsif v_colname(i) ='OPTIMIZER_MODE_MISMATCH' then
for c in (select OPTIMIZER_MODE,count(*) n from v$sql where hash_value=v_hash group by OPTIMIZER_MODE) loop
PIPE ROW(c.n||' versions with '||c.OPTIMIZER_MODE);
end loop;
elsif v_colname(i) ='OPTIMIZER_MISMATCH' then
if v_version like '9%' then
PIPE ROW('No details available for '||v_version);
else
declare
v_param vc_arr;
v_value vc_arr;
v_n num_arr;
begin
v_query:='select o.NAME,o.VALUE ,count(*) n '
||'from V$SQL_OPTIMIZER_ENV o,sql_shared_cursor s '
||'where ISDEFAULT=''NO'' '
||' and OPTIMIZER_MISMATCH=''Y'' '
||' and s.sql_id=:v_sql_id '
||' and o.sql_id=s.sql_id '
||' and o.CHILD_ADDRESS=s.CHILD_ADDRESS '
||' group by o.NAME,o.VALUE ';
EXECUTE IMMEDIATE v_query
bulk collect into v_param,v_value,v_n using v_sql_id ;
for c in 1 .. v_n.count loop
PIPE ROW(v_n(c)||' versions with '||v_param(c)||' = '||v_value(c));
end loop;
end;
end if;
elsif v_colname(i) ='AUTH_CHECK_MISMATCH' then
declare
v_pusr num_arr;
v_pschid num_arr;
v_pschname vc_arr;
v_n num_arr;
begin
v_query:='select PARSING_USER_ID, PARSING_SCHEMA_ID, PARSING_SCHEMA_NAME ,count(*) n from v$sql '
||v_sql_where||v_sql_where2
||' group by PARSING_USER_ID, PARSING_SCHEMA_ID, PARSING_SCHEMA_NAME';
EXECUTE IMMEDIATE v_query
bulk collect into v_pusr,v_pschid,v_pschname,v_n;
PIPE ROW('# of Ver PARSING_USER_ID PARSING_SCHEMA_ID PARSING_SCHEMA_NAME');
PIPE ROW('======== =============== ================= ===================');
for c in 1 .. v_n.count loop
PIPE ROW(to_char(v_n(c),'9999999')|| TO_CHAR(v_pusr(c),'9999999999999999')|| to_char(v_pschid(c),'99999999999999999')||' '||v_pschname(c));
end loop;
end;
elsif v_colname(i) = 'TRANSLATION_MISMATCH' then
declare
v_objn num_arr;
v_objow vc_arr;
v_objnm vc_arr;
begin
v_query:='select distinct p.OBJECT#,p.OBJECT_OWNER,p.OBJECT_NAME'
||' from (select OBJECT_NAME ,count(distinct object#) n from v$sql_plan '
||v_sql_where||v_sql_where2
||' and object_name is not null group by OBJECT_NAME ) d'
||' ,v$sql_plan p where d.object_name=p.object_name and d.n>1';
EXECUTE IMMEDIATE v_query
bulk collect into v_objn,v_objow,v_objnm;
If v_objn.count>0 then
PIPE ROW('Summary of objects probably causing TRANSLATION_MISMATCH');
PIPE ROW(' ');
PIPE ROW('Object# Owner.Object_Name');
PIPE ROW('======= =================');
for c in 1 .. v_objn.count loop
PIPE ROW(to_char(v_objn(c),'999999')||' '||v_objow(c)||'.'||v_objnm(c));
end loop;
else
PIPE ROW('No objects in the plans with same name and different owner were found.');
end if;
end;
else
PIPE ROW('No details available');
end if;
end if;
end loop;
IF v_version not like '9%' then
PIPE ROW('####');
PIPE ROW('To further debug Ask Oracle Support for the appropiate level LLL.');
if v_version in ('10.2.0.1.0','10.2.0.2.0','10.2.0.3.0') THEN
PIPE ROW('and read note:457225.1 Cannot turn off Trace after setting CURSORTRACE EVENT');
end if;
PIPE ROW('alter session set events ');
PIPE ROW(' ''immediate trace name cursortrace address '||v_hash||', level LLL'';');
PIPE ROW('To turn it off do use address 1, level 2147483648');
end if;
PIPE ROW('================================================================');
exception
when others then
PIPE ROW('Error :'||sqlerrm);
PIPE ROW('for Addr: '||v_addr||' Hash_Value: '||v_hash||' SQL_ID '||v_sql_id);
for i in 0 .. trunc(length(v_query)/64) loop
PIPE ROW(i||' '||substr(v_query,1+i*64,64));
end loop;
end;
/
-------------------------------------------------------------------------------
select b.* from v$sqlarea a ,table(version_rpt(a.sql_id)) b where loaded_versions >=100;
-- Generate reports for all cursors with more than 100 versions using HASH_VALUE:
select b.* from v$sqlarea a ,table(version_rpt(null,a.hash_value)) b where loaded_versions>=100;
-- Generate the report for cursor with sql_id cyzznbykb509s:
select * from table(version_rpt('cyzznbykb509s'));
alter system set events 'immediate trace name cursortrace level 577, address hash_value';
where,
577=level 1,
578=level 2,
580=level 3
Turn Off Tracing
alter system set events 'immediate trace name cursortrace level 2147483648, address 1';
********************************************** Raw Conversion Function ****************************************
create or replace function raw_to_num(i_raw raw)
return number
as
m_n number;
begin
dbms_stats.convert_raw_value(i_raw,m_n);
return m_n;
end;
/
create or replace function raw_to_date(i_raw raw)
return date
as
m_n date;
begin
dbms_stats.convert_raw_value(i_raw,m_n);
return m_n;
end;
/
create or replace function raw_to_varchar2(i_raw raw)
return varchar2
as
m_n varchar2(20);
begin
dbms_stats.convert_raw_value(i_raw,m_n);
return m_n;
end;
/
select table_name, COLUMN_NAME, DATA_TYPE, UTL_RAW.CAST_TO_VARCHAR2(LOW_VALUE) LOW_VALUE,
UTL_RAW.CAST_TO_VARCHAR2(HIGH_VALUE) HIGH_VALUE
from dba_tab_columns
where COLUMN_NAME='&column_name'
and table_name='&table_name';
****************************************** Table Partitioning Prerequistics ************************************
set verify off
set echo off
set trims on
set linesize 1000
set heading off
set pagesize 10000
set long 500000
set feedback off
column XXXX format a1000
col segment_name for a30
exec DBMS_METADATA.SET_TRANSFORM_PARAM (DBMS_METADATA.SESSION_TRANSFORM, 'PRETTY', true);
execute DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true);
REM ########### Table size information of &&Table_name ######################################
spool table_detail_&&filename
select 'REM ########### Table size information of &&Table_name ######################################' from dual;
select owner,segment_name,segment_type,tablespace_name,bytes/1024/1024 as mb
from dba_segments
where segment_name ='&&Table_name'
and owner='&&Owner';
spool off
REM #################### Drop Referntial Contraints of table &&Table_name #############################
spool drop_ref_cons_&&filename
select 'REM #################### Drop Referntial Contraints of table &&Table_name #############################' from dual;
Select 'Alter table '||owner||'.'||table_name||' drop constraints '||CONSTRAINT_NAME||';' as "Output"
from ( select owner,constraint_name,constraint_type,table_name,r_owner,r_constraint_name
from all_constraints
where constraint_type='R'
and r_constraint_name in (select constraint_name
from all_constraints
where constraint_type in ('P','U')
and table_name= '&&Table_name'
and owner= '&&Owner')
and r_owner='&&Owner'
);
spool off
REM #################### Rename Contraints of table &&Table_name #############################
spool rename_cons_&&filename
select 'REM #################### Rename Contraints of table &&Table_name #############################' from dual;
Select 'Alter table '||owner||'.'||table_name||' rename constraint '||constraint_name||' to '||constraint_name||'_old ;'
from dba_constraints
where owner = '&&Owner'
and table_name ='&&Table_name';
spool off
REM ##################### Rename Index on Table &&Table_name ################################
spool rename_index_&&filename
select 'REM ##################### Rename Index on Table &&Table_name ################################' from dual;
select 'alter index '||owner ||'.'||index_name ||' rename to '||index_name||'_old ;'
from dba_indexes where owner = '&&Owner'
and table_name = '&&Table_name';
spool off
REM #################### Drop Triggers on Table &&Table_name #############################
spool drop_trigger_&&filename
select 'REM #################### Drop Triggers on Table &&Table_name #############################' from dual;
select 'drop trigger '||owner ||'.'||trigger_name ||';'
from dba_triggers
where owner='&&Owner'
and table_name='&&Table_name';
spool off
REM ################ Rename table &&Table_name #######################
spool rename_table_&&filename
select 'REM ################ Rename table &&Table_name #######################' from dual;
select 'alter table '||owner||'.'||table_name||' rename to '||'&&Table_name'||'_old;'
from dba_tables
where owner='&&Owner'
and table_name ='&&Table_name';
spool off
REM ################ Create Index on Table &&Table_name #################################
spool create_index_&&filename
select 'REM ################ Create Index on Table &&Table_name #################################' from dual;
Select dbms_metadata.get_ddl('INDEX',index_name,owner) XXXX
from dba_indexes
where table_name= '&&Table_name'
and owner= '&&Owner';
spool off
REM ########### Index Noparallel of Table &&Table_name #################################
spool index_noparallel_&&filename
select 'REM ########### Index Noparallel of Table &&Table_name #################################' from dual;
select 'alter index '||owner ||'.'||index_name ||' noparallel;'
from dba_indexes
where owner = '&&Owner'
and table_name = '&&Table_name';
spool off
REM ############### Create Constraint on Table &&Table_name ############################
spool create_cons_&&filename
select 'REM ############### Create Constraint on Table &&Table_name ############################' from dual;
Select
decode (constraint_type,'R',(select dbms_metadata.get_ddl('REF_CONSTRAINT',constraint_name,owner) from dual),
(select dbms_metadata.get_ddl('CONSTRAINT',constraint_name,owner) from dual)) "XXXX"
from ( select owner,constraint_name,constraint_type,table_name,r_owner,r_constraint_name
from all_constraints
where table_name= '&&Table_name' and owner= '&&Owner'
);
spool off
REM ############## Create Referential Constraint on Table &&Table_name ############################
spool create_ref_cons_&&filename
select 'REM ############## Create Referential Constraint on Table &&Table_name ############################' from dual;
Select
(select dbms_metadata.get_ddl('REF_CONSTRAINT',constraint_name,owner) from dual) "XXXX"
from
(select owner,constraint_name,constraint_type,table_name,r_owner,r_constraint_name
from all_constraints
where constraint_type='R'
and r_constraint_name in (select constraint_name from all_constraints
where constraint_type in ('P','U') and
table_name= '&&TABLE_NAME' and owner= '&&OWNER') and r_owner='&&OWNER'
) XXXX;
spool off
REM ############### Create Trigger on Table &&Table_name ###################################
spool create_trigger_&&filename
select 'REM ############### Create Trigger on Table &&Table_name ###################################' from dual;
select
(select dbms_metadata.get_ddl('TRIGGER',trigger_name,owner) from dual) "XXXX"
from ( select owner,trigger_name,table_owner,table_name
from dba_triggers
where table_name='&&Table_name'
and owner='&&Owner'
);
spool off---
Run awy process--
col "logon" for a16
col username for a12
col spid for 99999
col osuser for a10
col machine for a25
col sid for 999
col program for a30
col module for a20
select a.username,a.sid,a.serial#,a.process ,b.spid,
to_char(a.logon_time,'dd:mm:yyyy hh24:mi')
"logon", a.status,a.osuser,a.machine,a.program,a.module from v$session a, v$process b
where a.paddr=b.addr and a.username is not null and a.process='&prr'
order by logon_time;
Kill LOCAL=NO
ps -ef|grep -i ORACLE|grep FNDLIBR|awk '{print $2}'|xargs -t -I {} kill -9 {}
netstat -a | grep -i 22048
find . -mtime +2 -name 'l*.req' -exec mv {} /data/p2pu/p2pdb23/cm/log3 \;
Directory move
find . -type d -mtime +30 -exec mv {} /backup/P2PPROD/incidents
l42272982.req
find . -name "*" -mtime +20|wc -l
trace files finding for the concurrent request
SELECT 'Request id: '||request_id ,
'Trace id: '||oracle_Process_id,
'Trace Flag: '||req.enable_trace,
'Trace Name:
'||dest.value||'/'||lower(dbnm.value)||'_ora_'||oracle_process_id||'.trc',
'Prog. Name: '||prog.user_concurrent_program_name,
'File Name: '||execname.execution_file_name|| execname.subroutine_name , 'Status : '||decode(phase_code,'R','Running')
||'-'||decode(status_code,'R','Normal'),
'SID Serial: '||ses.sid||','|| ses.serial#, 'Module : '||ses.module from fnd_concurrent_requests req, v$session ses, v$process proc, v$parameter dest, v$parameter dbnm, fnd_concurrent_programs_vl prog, fnd_executables execname where req.request_id = &request and req.oracle_process_id=proc.spid(+)
and proc.addr = ses.paddr(+)
and dest.name='user_dump_dest'
and dbnm.name='db_name'
and req.concurrent_program_id = prog.concurrent_program_id and req.program_application_id = prog.application_id
Compliling Libraries:
frmcmp_batch module=XXCAPCUST.pll userid=apps/xxxxxx output_file=XXCAPCUST.plx module_type=LIBRARY
frmcmp_batch module=XXCAPCUST.pll userid=apps/Fldbf1xuat output_file=XXCAPCUST.plx module_type=LIBRARY
Compiling Forms:
export FORMS_PATH=$FORMS_PATH:$AU_TOP/forms/US
frmcmp_batch module=/s02/app/p2puat/apps/apps_st/appl/au/12.0.0/forms/US/JEITTRCC.fmb userid=apps/Fldbf1xuat output_file=/s02/app/p2puat/apps/apps_st/appl/je/12.0.0/forms/US/JEITTRCC.fmx module_type=form batch=yes compile_all=yes
frmcmp_batch Module=/s02/app/p2puat/apps/apps_st/appl/au/12.0.0/forms/US/JEITLSTS.fmb Userid=apps/Fldbf1xuat output_file=/s02/app/p2puat/apps/apps_st/appl/je/12.0.0/forms/US/JEITLSTS.fmx Module_Type=form batch=Yes Compile_All=yes
ps -fu $LOGNAME|egrep -i 'cdstatm|cdpmgr'|grep -v grep|awk '{print $2}'
Query to find whether images are attached or not.
SELECT *
FROM apps.SF_WORKITEM_PROPERTY_VALUES_V
WHERE
PROPERTY_VALUE in ('12416272')
Requests long running.
SELECT a.request_id,a.phase_code,a.status_code,b.sql_id,c.sql_fulltext from fnd_concurrent_requests a,v$session b,v$sql c
where a.oracle_session_id=b.audsid and b.sql_id=c.sql_id and a.request_id='41849933'
How to check the oracle HTTP server is responding to the database.
select utl_http.request('http://p2pap13c.nam.nsroot.net:9112') from dual;
1. select utl_http.request ('<server>:<port>') from dual;
To find the list of request running for a Applaiction
SELECT request_id, user_concurrent_program_name, phase_code, status_code
FROM fnd_conc_requests_form_v
WHERE responsibility_application_id= 20003 and phase_code='R'
gives the list of requests for CITI AP applicaiton
Joinining per_all_people,fnd_user
select fu.USER_NAME,fu.end_date "User End Date",papf.EMAIL_ADDRESS
from per_all_people_f papf,fnd_user fu where
fu.user_id in ( select user_id from fnd_user where user_name ='SV22881')
and papf.PERSON_ID=fu.EMPLOYEE_ID
order by 1
How to delete the workbook in Discover
Below steps can be used to Delete workbook:
1) Login to Discoverer Desktop tool
2) Navigate: File>>Manage Workbooks>>Delete
3) Select <<workbook name >>
4) Click delete
Please let me know if you require any Battle station for this request.
How to get the PID value for the PORT.
/usr/sbin/lsof -i :22602
How to cheke whether space is there in the text
grep -i " '," user_list.txt
How to add single quotes(') in the begging for the text
:%s/^/'/g
How too add sigle quotes in the end of the line
:%s/$/'/g
ssi_push_wf_US.wft
WFLOAD apps/FldbR3luat@P2PUAT 0 Y FORCE ssi_push_wf_US.wft
apps/FldbR3luat
chown –R p2pdb30:p2pusers /data/p2p/p2pdb30
cat /dev/null > application.log
BAW Discovere Plus issue :
From: Chavan, Mahesh [CCC-OT_IT NE]
Sent: Thursday, June 21, 2012 3:38 PM
To: Smith, Andrew1 [CCC-OT_OP NE]; *GFTS Global GPA APS P2P Support
Subject: RE: Java Error - BAW - Discoverer Plus - VT37485267
Hi Andrew,
Please complete below setting in Java and Retest issue
1- Go to Control panel -> Double click on JAVA
2- Click on Advanced tab -> security
3- Enable or check options
Use SSL 2.0 compatible
Use SSL 3.0
Use TLS 1.0
Click on apply and Re-test issue.
FND login Enabled...>>>>>
1Using System Administrator responsibility, set profile options at the user level:
FND: Debug Log Enabled : Yes
FND: Debug Log Filename for Middle-Tier : leave it as null
FND: Debug Log Level : Statement
FND: Debug Log Module : %
1.1 Bounce the apache
2.. Check the maximum log sequence from fnd_log_messages executing:
SQL> select max(log_sequence) from fnd_log_messages;. Reproduce the
SQL> issue
3.. Retrieve the log messages executing the below statement. Upload the results in a spreadsheet format.
SQL> select * from fnd_log_messages where log_sequence >
SQL> &max_log_sequence;
select * from fnd_log_messages where log_sequence >401421150
401421150
Wf mailer trouble shooting:
With the notification Ids,how to checek the Details.
select * from wf_item_activity_statuses_v WHERE NOTIFICATION_ID=52792066
To find the Concurrent request details with user details.
select request_id, user_concurrent_program_name, requestor, actual_start_date, argument_text from fnd_conc_requests_form_v where phase_code='R' and status_code='R'
To checek the AUTOSYSYS server for the aFeeds JOB....
autoping -m p2pfeedprd.citigroup.net
CAUAJM_I_50023 AutoPinging Machine [p2pfeedprd.citigroup.net]
CAUAJM_I_50025 AutoPing WAS SUCCESSFUL.
autorep -m p2pdbsrv
[rs12887@tiawccap001swq scripts]$ autoping -m corpdb107p.nam.nsroot.net
JAVA secutiry Issue:
· Navigate: Start > Control Panel > Java
· Select tab 'Advanced'
· Open 'Security' and 'Mixed code (sandbox vs. trusted) security verification'
· Select 'Enable - hide warning and run with protections'
· Select OK button to confirm the changes made
Log out URL not working fine:::
Dinesh/Irfan/Ravi,
Please change the value of PORTALURL in SSB.properties file. It will fixed the issue. If you have any confusion then call me up. I have already explained it to Ankul.
Thanks
Golak Saha
How to find the "workflow Program scheduled"
Workflow Background Process (
PHASE_CODE STATUS_CODE
P I
)
Duplicate LInes SQL:
SELECT C.ID CA_ID, F.NAME FOLDER_NAME, S.ID ITEM_ID, S.NAME, S.DISPLAY_NAME,
S.ITEMTYPE, S.DESCRIPTION, S.LANGUAGE, S.URL, S.PUBLISH_DATE,S.SEQUENCE, F.NAME
FROM XXCPORT.XXCPRT_WWSBR_ALL_ITEMS S, XXCPORT.XXCPRT_WWSBR_ALL_FOLDERS F,
XXCPORT.XXCPRT_WWSBR_ALL_CONTENT_AREAS C
WHERE C.ID = F.CAID
AND F.CAID = S.CAID
AND F.ID = S.FOLDER_ID
AND S.IS_CURRENT_VERSION = 1
AND S.ACTIVE = 1
AND F.NAME LIKE 'XXC_FOLDER_%'
AND C.NAME LIKE 'XXC_CNT_%'
AND (TRUNC (EXPIREDATE) > TRUNC (SYSDATE) OR EXPIREDATE IS NULL)
AND TRUNC (PUBLISH_DATE) <= TRUNC (SYSDATE)
AND ITEMTYPE <> 'baseportletinstance'
ORDER BY S.SEQUENCE ASC;
Select * from XXCPORT.XXCPRT_WWSBR_ALL_FOLDERS F
where F.NAME LIKE 'XXC_FOLDER_%'
and F.caid in(Select C.id from XXCPORT.XXCPRT_WWSBR_ALL_CONTENT_AREAS c where c.name LIKE 'XXC_CNT_%')
wf mailer Troiuble shooting
. Upload latest mailer log file
a. Run this query
set linesize 155;
set pagesize 200;
set verify off;
column MANAGER format a15;
column MEANING format a15;
SELECT concurrent_queue_name manager, fcp.last_update_date, fcp.concurrent_process_id pid, meaning, fcp.logfile_name
FROM fnd_concurrent_queues fcq, fnd_concurrent_processes fcp, fnd_lookups flkup
WHERE concurrent_queue_name in ('WFMLRSVC')
AND fcq.concurrent_queue_id = fcp.concurrent_queue_id
AND fcq.application_id = fcp.queue_application_id
AND flkup.lookup_code=fcp.process_status_code
AND lookup_type ='CP_PROCESS_STATUS_CODE'
AND meaning='Active';
5. Execute the following query:
select corr_id CORRID,
wfno.user_data.GET_STRING_PROPERTY('BES_EVENT_KEY') NOTIFICATION_ID, wfno.user_data.GET_STRING_PROPERTY('ROLE') ROLE,
wfno.msg_state STATE,
to_char(enq_time, 'YYYY/MM/DD HH24:MI:SS') enq_time,
to_char(deq_time, 'YYYY/MM/DD HH24:MI:SS') deq_time,
to_char(sysdate, 'YYYY/MM/DD HH24:MI:SS') sys_date,
retry_count RETRY
from applsys.aq$wf_notification_out wfno
where wfno.user_data.GET_STRING_PROPERTY('BES_EVENT_NAME') <> 'oracle.apps.wf.notification.summary.send'
and wfno.user_data.GET_STRING_PROPERTY('NOTIFICATION_ID') =
from step# 4 you can obtain the notification id (NID) to run this query
6. Execute the following query as well.
select notification_id, begin_date, end_date, mail_status, status, subject,
from wf_notifications
where notification_id =
from step# 4 you can obtain the notification id (NID) to run this query
7. Please provide the output of the $FND_TOP/sql/wfmlrdbg.sql script to review all the details about your notification and the wf mailer parameter value
Parameters:
Notification id = <from step# 4 you can obtain the notification id (NID) to run this script>
LOGin page not working (Portal)stuffs ...
SELECT * FROM
xxcprt_links_info A
WHERE
a.rowid >
ANY (
SELECT
B.rowid
FROM
xxcprt_links_info B
WHERE
A.instance_name = B.instance_name
AND
A.link_type = B.link_type
);
DELETE FROM
xxcprt_links_info A
WHERE
a.rowid >
ANY (
SELECT
B.rowid
FROM
xxcprt_links_info B
WHERE
A.instance_name = B.instance_name
AND
A.link_type = B.link_type
);
Commit;
----------------------------------------------------------------------------------
Ø XXC_CBS_UTIL
Ø XXC_PUBLIC_PGUTIL
We have issue with our production fax sever 817-837-7315
To generated RDA >>>>>Application Tier.
cd $IZU_TOP/bin
./rda.sh -vdCRP -e APPL_SHORT='PA' ACT
Paging details in GB
svmon -G -O unit=GB
lsps -s >>>Pging percentage
svmon -P -t10 -O summary=basic>>>Top 10 process.
How to Count Total Number of Users Connected to ORACLE Application [ID 295206.1]
Query to find the Concurrent program details.(starttime,end time,submitter) dsiplay the exection time in minutes
select request_id,user_concurrent_program_name,requestor,argument_text,phase_code,status_code,to_char(REQUEST_DATE, 'dd-mon-yyyy hh24:mi:ss') SUBMIT_TIME, to_char(REQUESTED_START_DATE, 'dd-mon-yyyy hh24:mi:ss') REQUESTED_TIME,
to_char(ACTUAL_START_DATE, 'dd-mon-yyyy hh24:mi:ss') START_TIME,
to_char(ACTUAL_COMPLETION_DATE, 'dd-mon-yyyy hh24:mi:ss') END_TIME
round((ACTUAL_COMPLETION_DATE-ACTUAL_START_DATE),4)*24*60 EXECUTION_TIME
from fnd_conc_requests_form_v where user_concurrent_program_name like 'Gather%'
P2PCDUAT details.
Please find the details of our UATFIX environment –
User name = p2puat
Node Name = p2pcduat
DNS Name = p2pcduat.ny.ssmb.com
Port Number = 1364
Operating System = Unix (AIX 6.1)
Exaplian Pla using the cursor value:
Hi Ahmed,
PFA the runtime plan.
select plan_table_output from table(dbms_xplan.display_cursor('7pcgpys83fjw6',0));
Thanks
Irfan
Restore issue Trouble shooting
set echo on;
set feedback on;
set numwidth 30;
set pagesize 50000;
alter session set nls_date_format = 'DD-MON-RRRR HH24:MI:SS';
column name format a10;
select dbid, name, created, resetlogs_change#, resetlogs_time, open_mode, log_mode, checkpoint_change#, controlfile_type, controlfile_change#, controlfile_time from v$database;
select status,checkpoint_change#,checkpoint_time, resetlogs_change#, resetlogs_time from v$datafile_header where file#=809;
select file#status,checkpoint_change#,checkpoint_time, resetlogs_change#, resetlogs_time, count(*), fuzzy from v$datafile_header group
Profile option Enabling
4. In the "Profile" field, enter the following profile and click the
<Find> button: 'Initialization SQL Statement - Custom'
5. In the "System Profile Values" form, enter the following in the User
Field: (This is one line and all single quotes)
BEGIN FND_CTL.FND_SESS_CTL('','', '', 'TRUE','','ALTER SESSION SET TRACEFILE_IDENTIFIER='||''''||'Redirect' ||''''||' EVENTS ='||''''||' 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12 '||''''); END;
Query to check the ASM disk files in the ASM
Query to check the Disk copy status
SET ECHO OFF
SET FEEDBACK 6
SET HEADING ON
SET LINESIZE 180
SET PAGESIZE 50000
SET TERMOUT ON
SET TIMING OFF
SET TRIMOUT ON
SET TRIMSPOOL ON
SET VERIFY OFF
CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES
COLUMN full_path FORMAT a75 HEAD 'ASM File Name / Volume Name / Device Name'
COLUMN system_created FORMAT a8 HEAD 'System|Created?'
COLUMN bytes FORMAT 9,999,999,999,999 HEAD 'Bytes'
COLUMN space FORMAT 9,999,999,999,999 HEAD 'Space'
COLUMN type FORMAT a18 HEAD 'File Type'
COLUMN redundancy FORMAT a12 HEAD 'Redundancy'
COLUMN striped FORMAT a8 HEAD 'Striped'
COLUMN creation_date FORMAT a20 HEAD 'Creation Date'
COLUMN disk_group_name noprint
BREAK ON report ON disk_group_name SKIP 1
COMPUTE sum LABEL "" OF bytes space ON disk_group_name
COMPUTE sum LABEL "Grand Total: " OF bytes space ON report
SELECT
CONCAT('+' || db_files.disk_group_name, SYS_CONNECT_BY_PATH(db_files.alias_name, '/')) full_path
, db_files.bytes
, db_files.space
, NVL(LPAD(db_files.type, 18), '<DIRECTORY>') type
, db_files.creation_date
, db_files.disk_group_name
, LPAD(db_files.system_created, 4) system_created
FROM
( SELECT
g.name disk_group_name
, a.parent_index pindex
, a.name alias_name
, a.reference_index rindex
, a.system_created system_created
, f.bytes bytes
, f.space space
, f.type type
, TO_CHAR(f.creation_date, 'DD-MON-YYYY HH24:MI:SS') creation_date
FROM
v$asm_file f RIGHT OUTER JOIN v$asm_alias a USING (group_number, file_number)
JOIN v$asm_diskgroup g USING (group_number)
) db_files
WHERE db_files.type IS NOT NULL
START WITH (MOD(db_files.pindex, POWER(2, 24))) = 0
CONNECT BY PRIOR db_files.rindex = db_files.pindex
UNION
SELECT
'+' || volume_files.disk_group_name || ' [' || volume_files.volume_name || '] ' || volume_files.volume_device full_path
, volume_files.bytes
, volume_files.space
, NVL(LPAD(volume_files.type, 18), '<DIRECTORY>') type
, volume_files.creation_date
, volume_files.disk_group_name
, null
FROM
( SELECT
g.name disk_group_name
, v.volume_name volume_name
, v.volume_device volume_device
, f.bytes bytes
, f.space space
, f.type type
, TO_CHAR(f.creation_date, 'DD-MON-YYYY HH24:MI:SS') creation_date
FROM
v$asm_file f RIGHT OUTER JOIN v$asm_volume v USING (group_number, file_number)
JOIN v$asm_diskgroup g USING (group_number)
) volume_files
WHERE volume_files.type IS NOT NULL
/
Query to check the Disk copy status
SET ECHO OFF
SET FEEDBACK 6
SET HEADING ON
SET LINESIZE 180
SET PAGESIZE 50000
SET TERMOUT ON
SET TIMING OFF
SET TRIMOUT ON
SET TRIMSPOOL ON
SET VERIFY OFF
CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES
COLUMN full_path FORMAT a75 HEAD 'ASM File Name / Volume Name / Device Name'
COLUMN system_created FORMAT a8 HEAD 'System|Created?'
COLUMN bytes FORMAT 9,999,999,999,999 HEAD 'Bytes'
COLUMN space FORMAT 9,999,999,999,999 HEAD 'Space'
COLUMN type FORMAT a18 HEAD 'File Type'
COLUMN redundancy FORMAT a12 HEAD 'Redundancy'
COLUMN striped FORMAT a8 HEAD 'Striped'
COLUMN creation_date FORMAT a20 HEAD 'Creation Date'
COLUMN disk_group_name noprint
BREAK ON report ON disk_group_name SKIP 1
COMPUTE sum LABEL "" OF bytes space ON disk_group_name
COMPUTE sum LABEL "Grand Total: " OF bytes space ON report
SELECT
CONCAT('+' || db_files.disk_group_name, SYS_CONNECT_BY_PATH(db_files.alias_name, '/')) full_path
, db_files.bytes
, db_files.space
, NVL(LPAD(db_files.type, 18), '<DIRECTORY>') type
, db_files.creation_date
, db_files.disk_group_name
, LPAD(db_files.system_created, 4) system_created
FROM
( SELECT
g.name disk_group_name
, a.parent_index pindex
, a.name alias_name
, a.reference_index rindex
, a.system_created system_created
, f.bytes bytes
, f.space space
, f.type type
, TO_CHAR(f.creation_date, 'DD-MON-YYYY HH24:MI:SS') creation_date
FROM
v$asm_file f RIGHT OUTER JOIN v$asm_alias a USING (group_number, file_number)
JOIN v$asm_diskgroup g USING (group_number)
) db_files
WHERE db_files.type IS NOT NULL
START WITH (MOD(db_files.pindex, POWER(2, 24))) = 0
CONNECT BY PRIOR db_files.rindex = db_files.pindex
UNION
SELECT
'+' || volume_files.disk_group_name || ' [' || volume_files.volume_name || '] ' || volume_files.volume_device full_path
, volume_files.bytes
, volume_files.space
, NVL(LPAD(volume_files.type, 18), '<DIRECTORY>') type
, volume_files.creation_date
, volume_files.disk_group_name
, null
FROM
( SELECT
g.name disk_group_name
, v.volume_name volume_name
, v.volume_device volume_device
, f.bytes bytes
, f.space space
, f.type type
, TO_CHAR(f.creation_date, 'DD-MON-YYYY HH24:MI:SS') creation_date
FROM
v$asm_file f RIGHT OUTER JOIN v$asm_volume v USING (group_number, file_number)
JOIN v$asm_diskgroup g USING (group_number)
) volume_files
WHERE volume_files.type IS NOT NULL
/
=================INIT parameter comaprison#########################
#################################################################################
#### Auto-Validate DB parameters ####
#### Created By Prashanth on 23-OCT-2019 ####
#### Version -1.0 ####
#################################################################################
#!/bin/bash
########################### Variable Declaration ###############################
ksppi="x\$ksppi"
ksppcv="x\$ksppcv"
BASE_PATH=/tmp
LOG_LOC=${BASE_PATH}/logs
OMCS_FILE=${BASE_PATH}/omcs_pfile.txt
OCI_FILE=${BASE_PATH}/cdb_pfile.txt
TMP_FILE=${BASE_PATH}/temp.txt
CONSISTENT=${LOG_LOC}/Consistent_value_OMCS_OCI.log
INCONSISTENT=${LOG_LOC}/Inconsistent_value_OMCS_OCI.log
mkdir -p ${BASE_PATH}/logs
rm ${CONSISTENT} ${INCONSISTENT} ${EXP_INCONSISTENT}
touch ${CONSISTENT} ${INCONSISTENT}
########################## Gather DB parameter from OCI DB #######################
sqlplus -s / as sysdba << EOF >/dev/null
set lines 200
set pages 10000
col name for a50
col value for a70
set feedback off
set heading off
spool ${OCI_FILE}
SELECT ksppinm name,TRIM(ksppstvl) value
FROM ${ksppi} x, ${ksppcv} y
WHERE (x.indx = y.indx)
AND x.inst_id=userenv('instance')
AND x.inst_id=y.inst_id
AND ksppinm LIKE '%%'
ORDER BY name;
EOF
############## Validate DB Parameter between OMCS and OCI DB #####################
sed 1d ${OCI_FILE} > ${TMP_FILE}
rm ${OCI_FILE}
mv ${TMP_FILE} ${OCI_FILE}
INPUT=${OMCS_FILE}
while IFS= read -r LINE
do
OMCS_PARAMETER=`echo "${LINE}" | awk '$1=$1'|cut -f1 -d' '`
OMCS_VALUE=`echo "${LINE}" | awk '$1=$1'|cut -f2 -d' '`
OUTPUT=`cat ${OCI_FILE} |grep "\<${OMCS_PARAMETER}\>"`
OCI_PARAMETER=`echo "${OUTPUT}" | awk '$1=$1'|cut -f1 -d' '`
OCI_VALUE=`echo "${OUTPUT}" | awk '$1=$1'|cut -f2 -d' '`
if [[ ${OCI_VALUE} == ${OMCS_VALUE} ]]
then
echo "DB Parameter values are same in OMCS and OCI"
echo "DB parameter value in OMCS instance - ${LINE}" >> ${CONSISTENT}
echo "DB Parameter value in OCI instance - ${OUTPUT}" >> ${CONSISTENT}
else
echo "DB parameter value in OMCS instance - ${LINE}" >> ${INCONSISTENT}
echo "DB Parameter value in OCI instance - ${OUTPUT}" >> ${INCONSISTENT}
echo "${LINE}" >> /tmp/logs/omcs.log
echo "${OUTPUT}" >> /tmp/logs/oci.log
fi
done < "$INPUT"
####################### Display the output ###############################
echo "Please find the inconsistant db parameters as below"
cat ${INCONSISTENT}
######################## End of the scripts ##############################
#################################################################################3-
tkprof ERPTSDB02_ora_86255.trc wfclone.txt sys=no explain=apps/easportstest@ERPTSTDB sort='(prsela,exeela,fchela)'
exec fnd_stats.gather_table_stats(ownname => 'APPLSYS',tabname => 'WF_ITEM_ATTRIBUTE_VALUES', percent => 100)
#####################################################################
Query to check the backup completion status
col dbsize_mbytes for 99,999,990.00 justify right head "DBSIZE_MB"
col input_mbytes for 99,999,990.00 justify right head "READ_MB"
col output_mbytes for 99,999,990.00 justify right head "WRITTEN_MB"
col output_device_type for a10 justify left head "DEVICE"
col complete for 990.00 justify right head "COMPLETE %"
col compression for 990.00 justify right head "COMPRESS|% ORIG"
col est_complete for a20 head "ESTIMATED COMPLETION"
col recid for 9999999 head "ID"
select recid
, output_device_type
, dbsize_mbytes
, input_bytes/1024/1024 input_mbytes
, output_bytes/1024/1024 output_mbytes
, (output_bytes/input_bytes*100) compression
, (mbytes_processed/dbsize_mbytes*100) complete
, to_char(start_time + (sysdate-start_time)/(mbytes_processed/dbsize_mbytes),'DD-MON-YYYY HH24:MI:SS') est_complete
from v$rman_status rs
, (select sum(bytes)/1024/1024 dbsize_mbytes from v$datafile)
where status='RUNNING'
and output_device_type is not null
/
#######################################################################
###########################FND PROFILE value check######################
set lines 450
set pages 450
col name for a40
col context for a38
col value for a90
SELECT po.profile_option_name "NAME",
decode(to_char(pov.level_id),
'10001', 'SITE',
'10002', 'APP',
'10003', 'RESP',
'10005', 'SERVER',
'10006', 'ORG',
'10004', 'USER', '???') "LEV",
decode(to_char(pov.level_id),
'10001','',
'10002', app.application_short_name,
'10003', rsp.responsibility_key,
'10005', svr.node_name,
'10006', org.name,
'10004', usr.user_name,
'???') "CONTEXT",
pov.profile_option_value "VALUE"
FROM apps.FND_PROFILE_OPTIONS po,
apps.FND_PROFILE_OPTION_VALUES pov,
apps.fnd_user usr,
apps.fnd_application app,
apps.fnd_responsibility rsp,
apps.fnd_nodes svr,
apps.hr_operating_units org
WHERE 1 =1
AND (lower(pov.profile_option_value) like '%sso%' )
AND pov.application_id = po.application_id
AND pov.profile_option_id = po.profile_option_id
AND usr.user_id (+) = pov.level_value
AND rsp.application_id (+) = pov.level_value_application_id
AND rsp.responsibility_id (+) = pov.level_value
AND app.application_id (+) = pov.level_value
AND svr.node_id (+) = pov.level_value
AND org.organization_id (+) = pov.level_value
ORDER BY "NAME", pov.level_id, "VALUE";
##########################Enqueueu RAC locks##########33
SELECT lh.inst_id Locking_Inst, lh.sid Locking_Sid,lh.ctime/60 "Lock_Held (In Mins)", lw.inst_id Waiting_Inst, lw.sid Waiter_Sid, decode ( lh.type, 'MR',
'Media_recovery', 'RT', 'Redo_thread', 'UN', 'User_name', 'TX', 'Transaction','TM', 'Dml', 'UL', 'PLSQL User_lock', 'DX', 'Distrted_Transaxion', 'CF', 'Control_file',
'IS','Instance_state', 'FS', 'File_set', 'IR', 'Instance_recovery', 'ST', 'Diskspace Transaction', 'IV', 'Libcache_invalidation', 'LS', 'LogStaartORswitch', 'RW',
'Row_wait', 'SQ', 'Sequence_no', 'TE', 'Extend_table', 'TT', 'Temp_table', 'Nothing-' ) Waiter_Lock_Type,decode ( lw.request, 0, 'None', 1, 'NoLock', 2, 'Row-Share',
3, 'Row-Exclusive', 4, 'Share-Table', 5, 'Share-Row-Exclusive', 6, 'Exclusive', 'Nothing-' ) Waiter_Mode_Req
FROM gv$lock lw, gv$lock lh
WHERE lh.id1=lw.id1 AND lh.id2=lw.id2
AND lh.request=0 AND lw.lmode=0
AND (lh.id1,lh.id2) in
( SELECT id1,id2 FROM gv$lock WHERE request=0 INTERSECT SELECT id1,id2 FROM gv$lock WHERE lmode=0 )
/
########################################################################################
Which program is assigned to Responsibility
SELECT DISTINCT
FCPL.USER_CONCURRENT_PROGRAM_NAME
, FCP.CONCURRENT_PROGRAM_NAME
, FAPP.APPLICATION_NAME
, FRG.REQUEST_GROUP_NAME
, FNRTL.RESPONSIBILITY_NAME
FROM
APPS.FND_REQUEST_GROUPS FRG
, APPS.FND_APPLICATION_TL FAPP
, APPS.FND_REQUEST_GROUP_UNITS FRGU
, APPS.FND_CONCURRENT_PROGRAMS FCP
, APPS.FND_CONCURRENT_PROGRAMS_TL FCPL
, APPS.FND_RESPONSIBILITY FNR
, APPS.FND_RESPONSIBILITY_TL FNRTL
WHERE
FRG.APPLICATION_ID =FAPP.APPLICATION_ID
AND FRG.APPLICATION_ID = FRGU.APPLICATION_ID
AND FRG.REQUEST_GROUP_ID = FRGU.REQUEST_GROUP_ID
AND FRG.REQUEST_GROUP_ID = FNR.REQUEST_GROUP_ID
AND FRG.APPLICATION_ID = FNR.APPLICATION_ID
AND FNR.RESPONSIBILITY_ID = FNRTL.RESPONSIBILITY_ID
AND FRGU.REQUEST_UNIT_ID = FCP.CONCURRENT_PROGRAM_ID
AND FRGU.UNIT_APPLICATION_ID = FCP.APPLICATION_ID
AND FCP.CONCURRENT_PROGRAM_ID = FCPL.CONCURRENT_PROGRAM_ID
AND FCPL.USER_CONCURRENT_PROGRAM_NAME LIKE 'EAAR: Custom Credit%'
AND FNRTL.LANGUAGE = 'US'
AND FAPP.LANGUAGE = 'US'
;
=================##############################################
WITH
p AS (
SELECT plan_hash_value
FROM gv$sql_plan
WHERE sql_id = TRIM('&&sql_id.')
AND other_xml IS NOT NULL
UNION
SELECT plan_hash_value
FROM dba_hist_sql_plan
WHERE sql_id = TRIM('&&sql_id.')
AND other_xml IS NOT NULL ),
m AS (
SELECT plan_hash_value,
SUM(elapsed_time)/SUM(executions) avg_et_secs
FROM gv$sql
WHERE sql_id = TRIM('&&sql_id.')
AND executions > 0
GROUP BY
plan_hash_value ),
a AS (
SELECT plan_hash_value,
SUM(elapsed_time_total)/SUM(executions_total) avg_et_secs
FROM dba_hist_sqlstat
WHERE sql_id = TRIM('&&sql_id.')
AND executions_total > 0
GROUP BY
plan_hash_value )
SELECT p.plan_hash_value,
ROUND(NVL(m.avg_et_secs, a.avg_et_secs)/1e6, 3) avg_et_secs
FROM p, m, a
WHERE p.plan_hash_value = m.plan_hash_value(+)
AND p.plan_hash_value = a.plan_hash_value(+)
ORDER BY
avg_et_secs NULLS LAST;
========###############################################################################3-
select b.concurrent_program_id CP_ID,user_concurrent_program_name USER_CP_NAME, request_id, phase_code,status_code,
to_char(actual_start_date,'mm/dd/yy hh24:mi:ss') start_time,
to_char(actual_completion_date,'mm/dd/yy hh24:mi:ss') end_time,
round((actual_completion_date-actual_start_date)*24*60,2) as duration_min,
argument_text as arguments
from apps.fnd_concurrent_requests a, apps.fnd_concurrent_programs_tl b
where a.concurrent_program_id=b.concurrent_program_id
and LANGUAGE='US'
--and (user_concurrent_program_name) like '%Print Selected Invoices%'
and a.request_id='131150736'
order by end_time desc;
##############################################################################################
select
s.sql_id,plan_hash_value,sql_profile,begin_interval_time,STARTUP_TIME, END_INTERVAL_TIME,s.optimizer_cost,s.iowait_total,s.cpu_time_total,
s.disk_reads_total
from
dba_hist_sqlstat s,
dba_hist_snapshot sn
where
s.snap_id = sn.snap_id and
s.sql_id = '2w817vcr68r16'
order by
to_char(begin_interval_time, 'YYYYMMDDHH24MMSS') desc;
#########################################################################################3-
How to add EBS user from the Back end
DECLARE
v_user_name VARCHAR2 (100) := upper('&Enter_User_Name');
v_description VARCHAR2 (100) := 'NEW Test User';
BEGIN
fnd_user_pkg.createuser
(x_user_name => v_user_name,
x_owner => NULL,
x_unencrypted_password => '&input_password',
x_session_number => 0,
x_start_date => SYSDATE,
x_end_date => NULL,
x_last_logon_date => NULL,
x_description => v_description,
x_password_date => NULL,
x_password_accesses_left => NULL,
x_password_lifespan_accesses => NULL,
x_password_lifespan_days => NULL,
x_employee_id => NULL,
x_email_address => NULL,
x_fax => NULL,
x_customer_id => NULL,
x_supplier_id => NULL,
x_user_guid => NULL,
x_change_source => NULL
);
COMMIT;
END;
/
#####################
PDB violations
set pages 300
set lines 300
col cause for a20
col name for a20
col message for a35 word_wrapped
select name,cause,type,message,status from PDB_PLUG_IN_VIOLATIONS where STATUS='PENDING' and type='ERROR'
#######################33
Xplain plan using SQL_ID
Prashant Boopathy 11:46 AM
set linesize 130
set pagesize 0
SELECT * FROM TABLE(dbms_xplan.display_cursor('69swvzcd7uddq'));
##################3-Terminating Request from back end
update apps.fnd_concurrent_requests set phase_code='C' , status_code='X' where request_id='&REQUEST_ID';
##########################
Script to check the Patches inside the DATABASE
SET LINESIZE 500
SET PAGESIZE 1000
SET SERVEROUT ON
SET LONG 2000000
COLUMN action_time FORMAT A12
COLUMN action FORMAT A10
COLUMN bundle_series FORMAT A4
COLUMN comments FORMAT A30
COLUMN description FORMAT A40
COLUMN namespace FORMAT A20
COLUMN status FORMAT A10
COLUMN version FORMAT A10
spool check_patches.txt
SELECT TO_CHAR(action_time, 'YYYY-MM-DD') AS action_time,
action,
status,
description,
version,
patch_id,
bundle_series
FROM sys.dba_registry_sqlpatch
ORDER by action_time;
spool off
#########ASM diskgroup space utilization############
SET LINESIZE 145
SET PAGESIZE 9999
SET VERIFY off
COLUMN group_name FORMAT a20 HEAD 'Disk Group|Name'
COLUMN sector_size FORMAT 99,999 HEAD 'Sector|Size'
COLUMN block_size FORMAT 99,999 HEAD 'Block|Size'
COLUMN allocation_unit_size FORMAT 999,999,999 HEAD 'Allocation|Unit Size'
COLUMN state FORMAT a11 HEAD 'State'
COLUMN type FORMAT a6 HEAD 'Type'
COLUMN total_mb FORMAT 999,999,999 HEAD 'Total Size (MB)'
COLUMN used_mb FORMAT 999,999,999 HEAD 'Used Size (MB)'
COLUMN pct_used FORMAT 999.99 HEAD 'Pct. Used'
break on report on disk_group_name skip 1
compute sum label "Grand Total: " of total_mb used_mb on report
SELECT
name group_name
, sector_size sector_size
, block_size block_size
, allocation_unit_size allocation_unit_size
, state state
, type type
, total_mb total_mb
, (total_mb - free_mb) used_mb
, ROUND((1- (free_mb / total_mb))*100, 2) pct_used
FROM
v$asm_diskgroup
ORDER BY
name
/
###########
How to mount the backup partition .
mount -o rw,bg,hard,intr,tcp,actimeo=0,vers=3,actimeo=0,nodev,nosuid -t nfs 10.44.2.107:/ERPPDSTAGE/DB_BKP /PREPROD_BUILD
####PL/SQL object ID##
SELECT sid
, serial#
, username
, ( SELECT max( substr( sql_text , 1, 40 )) FROM v$sql sq WHERE sq.sql_id = se.sql_id ) AS sql_text
, ( SELECT object_name FROM dba_procedures WHERE object_id = plsql_entry_object_id AND subprogram_id = 0) AS plsql_entry_object
, ( SELECT procedure_name FROM dba_procedures WHERE object_id = plsql_entry_object_id AND subprogram_id = plsql_entry_subprogram_id) AS plsql_entry_subprogram
, ( SELECT object_name FROM dba_procedures WHERE object_id = plsql_object_id AND subprogram_id = 0) AS plsql_entry_object
, ( SELECT procedure_name FROM dba_procedures WHERE object_id = plsql_object_id AND subprogram_id = PLSQL_SUBPROGRAM_ID) AS plsql_entry_subprogram
, se.*
FROM gv$session se
WHERE 1=1
-- AND se.status = 'ACTIVE'
-- AND sid = 3929
-- AND plsql_entry_object_id IS NOT NULL
ORDER BY se.sid
####Modify the Preferred the available instance ###
srvctl modify service -db ERPTSCDB -service ERPTSCDB1 -modifyconfig -preferred "ERPTSCDB1"
######Concurrent Request Run time details ########
SELECT /*+ rule */
rq.parent_request_id "Parent Req. ID",
rq.request_id "Req. ID",
tl.user_concurrent_program_name "Program Name",
rq.actual_start_date "Start Date",
rq.actual_completion_date "Completion Date",
ROUND((rq.actual_completion_date -
rq.actual_start_date) * 1440, 2) "Runtime (in Minutes)"
FROM applsys.fnd_concurrent_programs_tl tl,
applsys.fnd_concurrent_requests rq
WHERE tl.application_id = rq.program_application_id
AND tl.concurrent_program_id = rq.concurrent_program_id
AND tl.LANGUAGE = USERENV('LANG')
AND rq.actual_start_date IS NOT NULL
AND rq.actual_completion_date IS NOT NULL
AND tl.user_concurrent_program_name like 'Gather%'
-- AND TRUNC(rq.actual_start_date) = '&start_date' -- uncomment this for a specific
#########Forms run-away session ##########
select a.username,a.sid,a.serial#,a.process,b.spid,to_char(a.logon_time,'dd:mm:yyyy hh24:mi') "logon",a.status,a.osuser,
a.machine,a.program,a.module from gv$session a,gv$process b
where a.paddr=b.addr and a.username is not null and a.process='&pid'
order by logon_time;
#######Different types of Lock alerts############
Find all blocked sessions and who is blocking them
select sid,blocking_session,username,sql_id,event,machine,osuser,program,last_call_et from v$session where blocking_session > 0;
select * from dba_blockers
select * from dba_waiters
-- Find what the blocking session is doing
select sid,blocking_session,username,sql_id,event,state,machine,osuser,program,last_call_et from v$session where sid=746 ;
-- Find the blocked objects
select owner,object_name,object_type from dba_objects where object_id in (select object_id from v$locked_object where session_id=271 and locked_mode =3);
-- Friendly query for who is blocking who
-- Mostly for versions before v$session had blocking_session column
select s1.inst_id,s2.inst_id,s1.username || '@' || s1.machine
|| ' ( SID=' || s1.sid || ' ) is blocking '
|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from gv$lock l1, gv$session s1, gv$lock l2, gv$session s2
where s1.sid=l1.sid and s2.sid=l2.sid and s1.inst_id=l1.inst_id and s2.inst_id=l2.inst_id
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2
order by s1.inst_id;
-- find blocking sessions that were blocking for more than 15 minutes + objects and sql
select s.SID,p.SPID,s.machine,s.username,CTIME/60 as minutes_locking, do.object_name as locked_object, q.sql_text
from v$lock l
join v$session s on l.sid=s.sid
join v$process p on p.addr = s.paddr
join v$locked_object lo on l.SID = lo.SESSION_ID
join dba_objects do on lo.OBJECT_ID = do.OBJECT_ID
join v$sqlarea q on s.sql_hash_value = q.hash_value and s.sql_address = q.address
where block=1 and ctime/60>15
-- Check who is blocking who in RAC
SELECT DECODE(request,0,'Holder: ','Waiter: ') || sid sess, id1, id2, lmode, request, type
FROM gv$lock
WHERE (id1, id2, type) IN (
SELECT id1, id2, type FROM gv$lock WHERE request>0)
ORDER BY id1, request;
-- Check who is blocking who in RAC, including objects
SELECT DECODE(request,0,'Holder: ','Waiter: ') || gv$lock.sid sess, machine, do.object_name as locked_object,id1, id2, lmode, request, gv$lock.type
FROM gv$lock join gv$session on gv$lock.sid=gv$session.sid and gv$lock.inst_id=gv$session.inst_id
join gv$locked_object lo on gv$lock.SID = lo.SESSION_ID and gv$lock.inst_id=lo.inst_id
join dba_objects do on lo.OBJECT_ID = do.OBJECT_ID
WHERE (id1, id2, gv$lock.type) IN (
SELECT id1, id2, type FROM gv$lock WHERE request>0)
ORDER BY id1, request;
-- Who is blocking who, with some decoding
select sn.USERNAME,
m.SID,
sn.SERIAL#,
m.TYPE,
decode(LMODE,
0, 'None',
1, 'Null',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share',
5, 'S/Row-X (SSX)',
6, 'Exclusive') lock_type,
decode(REQUEST,
0, 'None',
1, 'Null',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share',
5, 'S/Row-X (SSX)',
6, 'Exclusive') lock_requested,
m.ID1,
m.ID2,
t.SQL_TEXT
from v$session sn,
v$lock m ,
v$sqltext t
where t.ADDRESS = sn.SQL_ADDRESS
and t.HASH_VALUE = sn.SQL_HASH_VALUE
and ((sn.SID = m.SID and m.REQUEST != 0)
or (sn.SID = m.SID and m.REQUEST = 0 and LMODE != 4 and (ID1, ID2) in
(select s.ID1, s.ID2
from v$lock S
where REQUEST != 0
and s.ID1 = m.ID1
and s.ID2 = m.ID2)))
order by sn.USERNAME, sn.SID, t.PIECE
-- Who is blocking who, with some decoding
select OS_USER_NAME os_user,
PROCESS os_pid,
ORACLE_USERNAME oracle_user,
l.SID oracle_id,
decode(TYPE,
'MR', 'Media Recovery',
'RT', 'Redo Thread',
'UN', 'User Name',
'TX', 'Transaction',
'TM', 'DML',
'UL', 'PL/SQL User Lock',
'DX', 'Distributed Xaction',
'CF', 'Control File',
'IS', 'Instance State',
'FS', 'File Set',
'IR', 'Instance Recovery',
'ST', 'Disk Space Transaction',
'TS', 'Temp Segment',
'IV', 'Library Cache Invalidation',
'LS', 'Log Start or Switch',
'RW', 'Row Wait',
'SQ', 'Sequence Number',
'TE', 'Extend Table',
'TT', 'Temp Table', type) lock_type,
decode(LMODE,
0, 'None',
1, 'Null',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share',
5, 'S/Row-X (SSX)',
6, 'Exclusive', lmode) lock_held,
decode(REQUEST,
0, 'None',
1, 'Null',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share',
5, 'S/Row-X (SSX)',
6, 'Exclusive', request) lock_requested,
decode(BLOCK,
0, 'Not Blocking',
1, 'Blocking',
2, 'Global', block) status,
OWNER,
OBJECT_NAME
from v$locked_object lo,
dba_objects do,
v$lock l
where lo.OBJECT_ID = do.OBJECT_ID
AND l.SID = lo.SESSION_ID
and block=1
################
https://dbaclass.com/monitor-your-db/
########
http://www.br8dba.com/troubleshooting-long-running-queries/
#######Concurrent Request Scheduled ######
SELECT fcr.request_id,
DECODE(fcpt.user_concurrent_program_name,
'Report Set',
'Report Set:' || fcr.description,
fcpt.user_concurrent_program_name) CONC_PROG_NAME,
argument_text PARAMETERS,
NVL2(fcr.resubmit_interval,
'PERIODICALLY',
NVL2(fcr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE')) PROG_SCHEDULE_TYPE,
DECODE(NVL2(fcr.resubmit_interval,
'PERIODICALLY',
NVL2(fcr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE')),
'PERIODICALLY',
'EVERY ' || fcr.resubmit_interval || ' ' ||
fcr.resubmit_interval_unit_code || ' FROM ' ||
fcr.resubmit_interval_type_code || ' OF PREV RUN',
'ONCE',
'AT :' ||
TO_CHAR(fcr.requested_start_date, 'DD-MON-RR HH24:MI'),
'EVERY: ' || fcrc.class_info) PROG_SCHEDULE,
fu.user_name USER_NAME,
requested_start_date START_DATE
FROM apps.fnd_concurrent_programs_tl fcpt,
apps.fnd_concurrent_requests fcr,
apps.fnd_user fu,
apps.fnd_conc_release_classes fcrc
WHERE fcpt.application_id = fcr.program_application_id
AND fcpt.concurrent_program_id = fcr.concurrent_program_id
AND fcr.requested_by = fu.user_id
AND fcr.phase_code = 'P'
AND fcr.requested_start_date > SYSDATE
AND fcpt.LANGUAGE = 'US'
AND fcrc.release_class_id(+) = fcr.release_class_id
AND fcrc.application_id(+) = fcr.release_class_app_id
and DECODE(fcpt.user_concurrent_program_name,
'Report Set',
'Report Set:' || fcr.description,
fcpt.user_concurrent_program_name) like '%OOD%
######stat migration details ####
bi-statpdap1.rws.ad.ea.com
OCI_MIGRATE/Y8uNA9lg
OCI_MIGRAT/EAStat4u
#####Catalog/catproc execution in CDB/PDB########
SET VERIFY OFF
connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
spool /tmp/validate_catalog.log append
alter session set "_oracle_script"=true;
alter pluggable database pdb$seed close;
alter pluggable database pdb$seed open;
host perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -l /tmp -b catalog $ORACLE_HOME/rdbms/admin/catalog.sql;
host perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -l /tmp -b catproc $ORACLE_HOME/rdbms/admin/catproc.sql;
host perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -l /tmp -b utlrp1.log $ORACLE_HOME/rdbms/admin/utlrp.sql;
host perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -l /tmp -b name_for_logs $ORACLE_HOME/rdbms/admin/utlrp.sql;
host perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -l /tmp -b name_for_logs $ORACLE_HOME/rdbms/admin/utlrp.sql;
spool off
