Standard decimal > hexadecimal is done with: select to_char ( 10 , ‘x’ ) from dual ; But I wanted a kind of extended hexadecimal that would keep single letters going up beyond 15/f, so would return ‘g ‘for decimal 16 instead of returning hexadecimal ’10’. Why did I want that? I needed to squeeze […]
Read More Extended Hexadecimal in OracleYou can denormalise data using the listagg function, so to display users with their roles on a single line comma separated: SELECT grantee , LISTAGG ( granted_role , ‘ , ‘ ) WITHIN GROUP ( ORDER BY granted_role ) AS granted_roles FROM dba_role_privs GROUP BY grantee ORDER BY 1 ; H/t Adrian Billington.
Read More Denormalise listagg children on single row comma separatedDe-duplicating parent – child table sets of data here using the listagg function to merge all the child rows into a single denormalised column. set serverout on BEGIN FOR d1 IN ( WITH denorm AS ( SELECT parent.aos_code , parent.pre_group_id , LISTAGG ( child.aos_code || child.stud_pass ) WITHIN GROUP ( ORDER BY child.aos_code || child.stud_pass […]
Read More Oracle remove duplicates parent child with listaggThe best utilities to use when copying/mirroring/backing up files are: robocopy – Windows rsync – Linux lftp – Linux without setting up password-less ssh Scripts can supply a password to sftp using lftp like this: lftp -u user:password sftp://server.domain.com << END_LFTP put /directory/file.txt quit END_LFTP You can set up password-less ssh this way.
Read More Copy mirror files with robocopy rsync lftpSimple 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 processesI 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 datatypeIt is better to use scp/sftp rather than ftp – because ftp transmits passwords in plain text and also requires extra firewall openings to work. 1) Simplest way – use sftp specfiying the password in the script, like this: lftp -u user:password sftp://server.domain.com << END_LFTP put /directory/file.txt quit END_LFTP Use the -O option to put […]
Read More scp and sftp either with or without a password using authorized keys