Andrew Fraser DBA

Call Sqlplus from Jenkins

For Jenkins running on Microsoft Windows, to run a sqlplus .sql file, can specify any of these in the Build Command: 1) PowerShell 2) Execute Windows Batch Command 3) Execute shell Or to run some sqlplus statements directly without using a separate .sql file: 4) PowerShell 5) Execute shell

Move LOB Partitions

Outer Join (+) and ANSI Syntax

These two are functionally identical: 1) Old style (+) syntax: 2) Newer ANSI join syntax:

Oracle Orphaned Processes

Error reported to application attempting to connect to database: ORA-12516, TNS:listener could not find available handler with matching protocol stack Underlying cause (this reported in database alert log rather than at application side) was: ORA-00020: maximum number of processes (nnn) exceeded Fix was to kill a large number of orphaned processes, with no associated session, […]

ORA-00600 17287 fix

There are some MoS docs for ORA-00600 [17287] suggesting to rerun various dictionary install scripts. Sometimes that might be needed – but in my case would have been overkill, because just recompiling the invalid objects to get them to status invalid was all that was needed – with a few extra grants needed applied back […]

Oracle hints profiles baselines explained

Ideally fix performance issues globally: that is, with database parameter changes and/or dbms_stats.set_prefs. But if you need to fix locally one specific sql, Oracle comes with 6 (count them, 6!) methods for doing that. In order of preference: 1) Optimizer Hint Written into sql code. Keeps the fix in place even through future application sql […]

Space usage lob sub partitions

Querying partition space usage is a little awkward with lobs and sub partitions, but can be done with sql like this: WITH base AS ( SELECT lp.table_owner , lp.table_name , lp.partition_name , SUM ( s.bytes ) / 1024 / 1024 AS lob_mb , COUNT(*) AS num_subpartitions FROM dba_lob_partitions lp JOIN dba_lob_subpartitions lsp ON lsp.table_owner = […]

Oracle grants generate commands

Script to generate commands for object grants: SQL: SELECT ‘GRANT ‘ || privilege || ‘ ON ‘ || LOWER ( owner || ‘.’ || table_name ) || ‘ TO ‘ || LOWER ( grantee ) || CASE WHEN grantable = ‘YES’ THEN ‘ WITH GRANT OPTION’ END || ‘ ;’ AS cmd FROM dba_tab_privs WHERE […]

listagg xml path Oracle Microsoft Sql Server

If you have a table with rows like this: typeName custName ——– ——– typeA cust1 typeA cust2 typeA cust3 typeB cust1 typeB cust4 typeB cust5 typeB cust6 And you want to display it’s data aggregated like this: typeName COUNT(*) fullListing ——– ——– —————————– typeA 3 cust1 , cust2 , cust3 typeB 4 cust1 , cust4 […]