I’m currently listing all interfaces – database links and cron jobs – into excel, and used these two scripts to automate the process. Shell script to list all crons (note that this assumes crons in /etc/cron.d run as root): for fle in `ls /var/spool/cron/* | grep -v tmp` do echo $fle =============== grep -v ‘^#’ […]
Read More List all crons and database links with scriptsShell script to check if orcladmin account is locked, and unlock it if required # Check to see if orcladmin account is locked, and unlock it if it is. if [ “`ldapbind -p <myport> -D cn=orcladmin -w <myorcladminpassword>`” = “bind successful” ] then echo orcladmin account is ok, is not locked. else echo unlocking orcladmin […]
Read More unlock orcladmin password in shell scriptIndexed views (the SQL Server equivalent of Oracle’s materialized views) are a good performance fix, especially for reporting and data warehouse systems. However they only work with inner joins. Attempts to create an indexed view with outer joins fail with: Cannot create index on view because it uses a LEFT, RIGHT, or FULL OUTER join, […]
Read More Indexed views – workaround for “Cannot create index on view because it uses a LEFT, RIGHT, or FULL OUTER join, and no OUTER joins are allowed in indexed views. Consider using an INNER join instead.”This is the extreme limit for oracle dates: alter session set nls_date_format = ‘Dy DD-Mon-YYYY AD’ ; select sysdate, to_date(’01-JAN-4712 BC’,’DD-MON-YYYY AD’), to_date(’30-DEC-9999 AD’,’DD-MON-YYYY AD’) from dual ; Without encountering this error: ORA-01841: (full) year must be between -4713 and +9999, and not be 0 01841. 00000 – “(full) year must be between -4713 and […]
Read More Oracle range limit AD BC datesTo view code for procedures and package bodies in SQL Developer that are owned by other users, you need the following privilege: grant select_catalog_role to <user> ; This privilege does not give access to the sys.link$ view, so should be safe to grant out. You also need to use version 3 or above of Oracle […]
Read More Oracle SQL Developer privileges for Other Users procedures and package bodiesOracle Data Guard is Oracle’s main solution for high availability in the event of a disaster. This overview is for Oracle 11gR2. A pdf version of this page is available here.
Read More Oracle Data Guard OverviewIn e.g. post-query block level trigger: IF block.check_field = ‘F’ THEN — switch on Set_Item_Property( ‘block.field’ , LOV_NAME, ‘your lov’) ; Set_Item_Property( ‘block.field’, VALIDATE_FROM_LIST, PROPERTY_TRUE); ELSE — switch off Set_Item_Property( ‘block.field’ , LOV_NAME, ”) ; Set_Item_Property( ‘block.field’, VALIDATE_FROM_LIST, PROPERTY_FALSE); END IF ; H/t François Degrelle
Read More Switch Oracle Forms List of Values on and off dynamically at run timeOracle Developer Suite 10g 9.0.4.0.1 for Windows (forms developer) is not easy to obtain now, out of support by Oracle for a long time, but that version is still in use out there. So I have zipped both installation CDs here.
Read More Oracle Developer Suite 10g 9.0.4.0.1 windows software download mediaWerner Puschitz has details of how to use ulimit and limits.conf to change maximum processes and open file descriptors. But for processes running from cron, I found I had to additionally make the below changes (this is on old version: 32 bit Linux 2.4.21-27.ELsmp Red Hat Enterprise Linux AS release 3 (Taroon Update 4) – […]
Read More Cron fix for ulimit: max user processes: cannot modify limit:operation not permittedSplit up a delimited string with: SELECT REGEXP_SUBSTR ( ‘Hello world !’ , ‘[^ ]+’ , 1 , 1 ) , REGEXP_SUBSTR ( ‘Hello world !’ , ‘[^ ]+’ , 1 , 2 ) , REGEXP_SUBSTR ( ‘Hello world !’ , ‘[^ ]+’ , 1 , 3 ) FROM DUAL ; Output: REGEX REGEX R […]
Read More Split space delimited string with regexp SQL