Script to hunt and lock/drop user accounts, can call from a central script connecting to multiple databases: BEGIN FOR d1 IN ( SELECT username FROM dba_users WHERE username LIKE ‘AGXDL%’ AND account_status != ‘LOCKED’ ORDER BY 1 ) LOOP dbms_output.put_line ( ‘alter user ‘ || d1.username || ‘ account lock ‘ ) ; execute immediate […]
Read More PL/SQL to find and lock or drop user accountsOracle Forms on iPad, Android and PCs without thick client Java Runtime Environment download. www.talkingbyte.com/?p=2
Read More TalkingByte Thinforms: Oracle Forms on iPad, Android and PCs without JRESimple bash command to check if processes are running: if [ $(pgrep -f pmon) ] then echo One or more databases running else echo No databases running fi Syntax gets more complex if you want to check for specific counts of processes: case $(pgrep -f tnslsnr | wc -l | awk ‘{print $1}’) in 0) […]
Read More bash check count processesSimple script to calculate optimal pga_aggregate_target size (in gb): col avg_tpi form 999.99 col max_tpi form 999.99 select instance_number, avg(value)/1024/1024/1024 avg_tpi , max(value)/1024/1024/1024 max_tpi from dba_hist_pgastat where name = ‘total PGA inuse’ group by instance_number ; dba_hist_pgastat has 1 weeks worth of data by default, snapshots taken at 1 hour intervals. Memory not assigned to […]
Read More Calculate optimal PGA sizeYum update is used to patch red hat/oracle enterprise linux. Can take a while, so best run nohup: rm -f nohup.out nohup yum -y update & By default yum stores its temporary downloads into /var, if that is low in space a symbolic link will workaround: rm -fr /var/cache/yum/updates/packages mkdir /bigfs/packages ln -s /bigfs/packages /var/cache/yum/updates/packages […]
Read More Yum update nohup Insufficient space in download directoryI wanted to give non-DBA users (Servicedesk, Operations) the ability to unlock database user accounts. The ‘alter user’ system privilege allows that, but it also gives out a lot more privileges (change password, kill session, alter DBA accounts) than I thought safe. So instead I created a pl/sql procedure to handle this securely. Usage for […]
Read More Unlock oracle accounts without alter user system privilegeOracle 12c was released yesterday (25-Jun-2013). I’m a little bit underwhelmed by a first look at the new features guide – if that’s all there is, Oracle is going to continue to struggle against Microsoft SQL Server. Main change with 12c is concept of container databases and pluggable databases: Container databases by default hold SGA, […]
Read More Oracle 12c new featuresMaterialized views work ok even when their dependent database link is dropped from under them. This can be used as a security improvement if you don’t want database links lying around during the day outside the refresh window. Script used to test (with complete refresh) was: create database link temp_link connect to user identified by […]
Read More Drop and create database links at materialized view refresh timeAdd a quote in front of a field (useful for excel to mark cell as text datatype) with any of: SELECT CHR(39) || address1 FROM customers ; SELECT q'[‘]’ || address1 FROM customers ; SELECT ”” || address1 FROM customers ;
Read More Oracle single quote marks for excel text datatypeMain highlight Oracle User Group Scotland Conference 2013 was a presentation by Dean Richards of Confio Software on VMWare performance for Oracle. Confio’s servers were at 1-5% CPU, so underutilised, but databases use memory rather than CPU. They virtualised 50 physical servers > 1 esx host – was at 40-50% CPU. Later moved to two […]
Read More VMWare performance for Oracle at Oracle User Group Scotland Conference 2013