PGA_AGGREGATE_TARGET

 PGA_AGGREGATE_LIMIT can be set to 0 either in a parameter file or dynamically after startup. If a value of 0 is specified, it means there is no limit to the aggregate PGA memory consumed by the instance.

Oracle GG, how to skip a failing transactions? The Replicat is in ABBEND mode

 >START REPLICAT replicate_name SKIPTRANSACTION

How to install oracle 19c in silent mode? This is useful to overcome the Xterm issue

 ./runInstaller -silent -ignorePrereqFailure -waitforcompletion -responseFile /u01/app/oracle/soft/install/response/db_install.rsp -debug

How to shrink datafiles, when you need to rebuild tables/indexes, or move LOBs around

 Below are 2 links with very good explanations, thanks to Jonathan Lewis!!!

https://jonathanlewis.wordpress.com/tablespace-hwm/

https://jonathanlewis.wordpress.com/2010/02/06/shrink-tablespace/

These help when you get:

ORA-03297: file contains used data beyond requested RESIZE value

but there is a lot of free space in the datafile, just not compacted.

Using telnet/nc to check if a specific port is open

 Example:

curl -v telnet://172.33.542.199:1521

nc -v 172.23.242.199 5696

How to turn subquery into JOIN?

 

Current long running query:

 

 

UPDATE /*+ full(b) parallel(b,8) */ SRV B SET SRV_STS_CD =

'CE' WHERE PARTITION_ID = 8 AND (SRV_STS_CD = 'CS' OR SRV_STS_CD =

'HA') AND SYS_CREATION_DATE > SYSDATE - 730 AND SRV_TRX_S_NO IN (SELECT

SRV_TRX_S_NO FROM DVC WHERE PARTITION_ID = 8 AND

DVC_TRX_STS_CD != 'CS');

 

 

Modified query:

 

UPDATE /*+ full(b) parallel(b,4) use_hash(DVC) leading(DVC_) unnest */ SRV B SET SRV_STS_CD =

'CE' WHERE PARTITION_ID = 8 AND (SRV_STS_CD = 'CS' OR SRV_STS_CD =

'HA') AND SYS_CREATION_DATE > SYSDATE - 730 AND SRV_TRX_S_NO IN (SELECT /*+ use_hash(DVC) full(DVC) parallel(DVC,4) */

SRV_TRX_S_NO FROM DVC_TRX_REPOS WHERE PARTITION_ID = 8 AND

DVC_TRX_STS_CD != 'CS')

;

How to run sqlplus as user root?

 [root@o71 ~]# sudo -u oracle bash -c '. ~/.bash_profile; sqlplus / as sysdba'

SQL*Plus: Release 18.0.0.0.0 - Production on Sun Oct 28 10:52:43 2018
Version 18.4.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.


Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0

SQL>