[florinm]$ sudo printf 'y\ny' | sudo /u01/app/oracle/product/19.3.0/root.sh
Oracle DB and beyond: practical tips, Q&A
Oracle DBA and beyond; these are practical tips for day to day DBA operation and maintenance; a place where you would come to look for a quick fix for a burning situation. I hope that by sharing all these, we all will become better in what we do. And on the way, I hope to save you some sweat :-)
Tuesday 11 June 2024
Monday 29 May 2023
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.
Monday 27 February 2023
Oracle GG, how to skip a failing transactions? The Replicat is in ABBEND mode
>START REPLICAT replicate_name SKIPTRANSACTION
Thursday 23 February 2023
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
Wednesday 11 January 2023
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.
Tuesday 13 December 2022
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
Wednesday 9 November 2022
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')
;