Find files older than 30 days, on Linux

 $ find . -mtime +30

How to find out on which object my query is "stuck" right now?

 So you have a query that seems to run forever..

v$session_wait shows "db file sequential read" , but since we have a multi join, the questions is on which object we are spending time.

Answer: P1, P2 and P3 reveal the object in case:

SQL>select wait_class,p1,p2,p3 from v$session where sid=425:

WAIT_CLASS                 P1         P2         P3

 -----------------------       ---------- ---------- ----------

  db file sequential read       5    2046999          1

 SQL> SELECT owner , segment_name , segment_type 

            FROM dba_extents WHERE file_id = 5 AND 

            (select p2 from v$session_wait where sid=425) BETWEEN block_id AND block_id + blocks -1;

  

OWNER

--------------------------------------------------------------------------------

SEGMENT_NAME

--------------------------------------------------------------------------------

SEGMENT_TYPE

------------------------------------------------------

SP

SP_AUDIT

TABLE

Oracle: how to turn an IN subquery into hash join

 The goal is to replace the nested loop, that is taking long, by a hash join.

Original query:

SELECT /*+ PARALLEL(M,8) */ 

my_tab_BAN,'CR019',(SELECT LOGICALY_DATE FROM

LOGICALY_DATE WHERE LOGICAL_DATE_TYPE='B'),SYSDATE,NULL,NULL,'CHHEXT','DL

019',NULL,'my_tab','my_tab_SYS_ALT_TXT','DL',my_tab_ID,NULL,NULL,my_tab_SYS_ALT_

TXT,'R'  

FROM my_tab M  WHERE my_tab_BAN IN 

(SELECT /*+ PARALLEL(HS,4) */

BAN FROM HSI_CLEANUP HS WHERE CATEGORY ='CR019' AND

CONDITION_DESC IS NOT NULL AND NVL(PURGE_STATUS,' ') <>'S')  AND

my_tab_TYPE='7770' AND my_tab_SYS_ALT_TXT IS NOT NULL;

Modified query:

SELECT /*+ PARALLEL(M,8) FULL(M) use_hash(M)  */ 

my_tab_BAN,'CR019',(SELECT LOGICALY_DATE FROM

LOGICALY_DATE WHERE LOGICAL_DATE_TYPE='B'),SYSDATE,NULL,NULL,'CHHEXT','DL

019',NULL,'my_tab','my_tab_SYS_ALT_TXT','DL',my_tab_ID,NULL,NULL,my_tab_SYS_ALT_

TXT,'R'  

FROM my_tab M  WHERE my_tab_BAN IN 

(SELECT /*+ PARALLEL(HS,4) */

BAN FROM HSI_CLEANUP HS WHERE CATEGORY ='CR019' AND

CONDITION_DESC IS NOT NULL AND NVL(PURGE_STATUS,' ') <>'S')  AND

my_tab_TYPE='7770' AND my_tab_SYS_ALT_TXT IS NOT NULL;

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