Transactions per second ( in a RAC DB setup)

  select round(avg(a.tps))  from (

WITH hist_snaps

AS (SELECT instance_number,

snap_id,

round(begin_interval_time,'MI') datetime,

(  begin_interval_time + 0 - LAG (begin_interval_time + 0)

OVER (PARTITION BY dbid, instance_number ORDER BY snap_id)) * 86400 diff_time

FROM dba_hist_snapshot where instance_number=&&1), hist_stats

AS (SELECT dbid,

instance_number,

snap_id,

stat_name,

VALUE - LAG (VALUE) OVER (PARTITION BY dbid,instance_number,stat_name ORDER BY snap_id)

delta_value

FROM dba_hist_sysstat

WHERE stat_name IN ('user commits', 'user rollbacks') and instance_number=&&1)

SELECT datetime,

ROUND (SUM (delta_value) / 3600, 2) TPS

FROM hist_snaps sn, hist_stats st

WHERE     st.instance_number = sn.instance_number

AND st.snap_id = sn.snap_id

AND diff_time IS NOT NULL

and st.instance_number=&&1

GROUP BY datetime

ORDER BY 1 desc

) a

where rownum < 61

/

Note: the input will be instance number, like 1, 2 etc

Using grep to display lines around the match also

 Example:

>grep -i  -C 2 "Module: my_program" report20201129*

In this case, we want to display the line above the ones that matches the pattern, since it contains important info.

Having fun with "for" loops in Linux

  >for i in {1..61}

> do

> for j in {01..12}

> do

> echo "create synonym us$i$j for usage_dummy;" >> 1.sql

> echo "create synonym au$i$j for accumulates_usage_dummy;" >> 1.sql

> done

> done

How to find special characters in the DB

  Match nth character

SQL> select case when regexp_like('ter*minator' ,'^...[^[:alnum:]]') then 'Match Found' else 'No Match Found' end as output from dual;

Output: Match Found

In the above example we tried to search for a special character at the 4th position of the input string “ter*minator”

Let’s now try to understand the pattern '^...[^[:alnum:]]'

^ marks the start of the string
. a dot signifies any character (… 3 dots specify any three characters)
[^[:alnum:]] Non alpha-numeric characters (^ inside brackets specifies negation)

Note: The $ is missing in the pattern. It’s because we are not concerned beyond the 4^th character and hence we need not mark the end of the string. (...[^[:alnum:]]$ would mean any three characters followed by a special character and no characters beyond the special character)

https://www.orafaq.com/node/2404

How to zip/unzip files on the fly, when we have space constraints

 To unpack on the fly:

gunzip < FILE.tar.gz | tar xvf -

To pack on the fly:
tar cvf - FILE-LIST | gzip -c > FILE.tar.gz

Another method, without creating a tar on the local server at all:

server1> tar cvf - 19.3.0 | ssh oracle@server2 "tar xvf - -C /u01/app/oracle/product"

Oracle 12c and pre-12c, how to check the latest PSU applied in the database?

To find the latest PSU/RU:

-- For 12c and 18c


set line 1000
col action form a12
col version  form a40
col description form a85
col action_date form a20

select description, action, to_char(action_time,'DD/MM/YYYY HH24:MI:SS') action_date, ' ' version

from dba_registry_sqlpatch

order by action_time desc

fetch first 1  rows only

/

How to find out a query which used a lot of TEMP space in the past? dba_hist_active_sess_history to the rescue

select SQL_ID,TEMP_SPACE_ALLOCATED
from dba_hist_active_sess_history
where SAMPLE_TIME between to_date('2019-06-16 15:14:00','yyyy-mm-dd hh24:mi:ss') and to_date('2019-06-16 15:17:00','yyyy-mm-dd hh24:mi:ss')
and TEMP_SPACE_ALLOCATED is NOT null
order by TEMP_SPACE_ALLOCATED
/