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
/

How to check the current value of the sequence, without advancing it?

SQL> select MY_SEQ.currval from dual;

   CURRVAL
----------
         1

currval, as opposed to nextval, is not advancing the sequence.

How to enable tracing for another session, for the event 10046?

EXEC DBMS_SYSTEM.set_ev(si=>123, se=>1234, ev=>10046, le=>8, nm=>'');

To disable the 10046 trace:

EXECUTE dbms_system.set_ev (123,1234,10046, 0, '');

How to create fast a table with 1 million rows

  create table test1
  as
  select rownum rn, mod(rownum,1000) mod1 ,  mod(rownum,90) mod2
 from dual
 connect by level <=1000000;

PGA usage

To find the total PGA memory used by processes:

SELECT ROUND(SUM(pga_used_mem)/(1024*1024),2) PGA_USED_MB FROM v$process;

To find PGA usage for a specific session

SELECT SID, b.NAME, ROUND(a.VALUE/(1024*1024),2) MB FROM 
v$sesstat a,  v$statname b
WHERE (NAME LIKE '%session uga memory%' OR NAME LIKE '%session pga memory%')
AND a.statistic# = b.statistic# 
AND SID = 80;

To calculate the amount of memory that you gone need for PGA, estimate the number of maximum connected sessions and run:

SELECT :MAX_CONNECTED_SESSIONS*(2048576+P1.VALUE+P2.VALUE)/(1024*1024) YOU_NEED_PGA_MB 
FROM V$PARAMETER P1, V$PARAMETER P2
WHERE P1.NAME = 'sort_area_size'
AND P2.NAME = 'hash_area_size';

To change PGA memory parameter

ALTER SYSTEM SET pga_aggregate_target = 3500M SCOPE=BOTH;