Author Archives: Andrew Fraser

PL/SQL bulk collect union

PL/SQL bulk collect will work with UNION ALL when written with an inline view: DECLARE TYPE l_type_assoc IS TABLE OF VARCHAR2(14) INDEX BY BINARY_INTEGER ; l_big l_type_assoc ; BEGIN WITH q AS ( SELECT ename AS name FROM emp UNION … Continue reading

Posted in Uncategorized | Leave a comment

Extended Hexadecimal in Oracle

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 … Continue reading

Posted in Uncategorized | Leave a comment

srvctl start listener -n nodename

Preferred method to stop and start oracle listener in RAC enivronment: srvctl config listener -n nodename srvctl stop listener -n nodename srvctl start listener -n nodename

Posted in RAC | Leave a comment

Denormalise listagg children on single row comma separated

You 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 … Continue reading

Posted in Uncategorized | Leave a comment

Oracle remove duplicates parent child with listagg

De-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 … Continue reading

Posted in Uncategorized | Leave a comment

Create output XML data files from oracle with dbms_xmlgen

The below code will generate XML format output, just plug in any SQL and it will work without further development. This is based on three articles by other authors: Tim Hall – but Tim’s code has a bug that stops … Continue reading

Posted in XML | Leave a comment

Replace all tnsnames.ora files with a single master file using TNS_ADMIN

Maintaining large numbers of client tnsnames.ora files is a pain.  Oracle Internet Directory is the recommended solution to this, but it is not an easy exercise to set up or migrate to. An easier alternative is to set TNS_ADMIN in all … Continue reading

Posted in SQL*Net, Windows | Leave a comment

Copy mirror files with robocopy rsync lftp

The 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 << … Continue reading

Posted in Uncategorized | Leave a comment

Shrink Transaction Logs in all every database SQL Server

SQL Server transaction logs need backed up regularly, or they will grow and grow forever, eventually filling up all available disk space. That is best done in a maintenance plan. Unfortunately you have to remember to set that up post … Continue reading

Posted in SQL server | Leave a comment

Remote Desktop Connection Manager

Remote Desktop Connection Manager, a free download from http://www.microsoft.com/en-gb/download/details.aspx?id=21101, lets you switch quickly between windows servers, saves the servers and your login details in a list.

Posted in SQL server, Windows | Leave a comment