PL/SQL
Poor man’s partitioning with instead of triggers – PART 1
The Enterprise Edition of Oracle Corp’s. flagship database has many features. One of these is partitioning. Partitioning allows a user to segment table rows into separate physical storage structures. This can improve things such as performance and manageability. Performance can be improved by utilizing what Oracle terms “partition pruning”. For example, let’s assume you... »
Standard exception pragmas
Oracle provides several standard exception pragmas (essentially, a compiler directive) for exceptions found on a fairly frequent basis. These are documented in the PL/SQL User Guide and Reference, but you can also get them at runtime using what is below: 1 2 3 4 5 6 7 8 9 10 11 12 13 14... »
Posting GV$SESSION_LONGOPS from a user procedure
The GV$SESSION_LONGOPS view can save you if you are nervous about how much time a long running operation has remaining, and are asked for an update from someone in a higher “zip code”. Oracle posts an entry to this memory structure whenever an operation is expected to last more than six seconds. Note the... »
11g compiler warnings
The PL/SQL compiler warnings have been enhanced in 11.2.0.1 and up to include a warning for the dreaded “when others then null” exception handler. Actually, the warning is raised any time an “others” is handled (even if it is logged somewhere), and not re-raised. See below for an example. 1 2 3 4 5... »
Useful script to print session gv$sesstat changes over time
Oracle provides a plethora of tools for monitoring user activity, including tracing, monitoring wait events, as well as ASH. However, sometimes I want a quick (literally) overview of a session. To do this, I query the gv$sesstat view. However, the data in this view is cumulative, so you don’t what happened during a single... »
MySQL mysqlbinlog vs. Oracle logmnr
MySQL provides the mysqlbinlog utility to parse binary transaction logs for any number of purposes which may include replication, auditing, or troubleshooting. Oracle also provides this functionality through a PL/SQL package called dbms_logmnr, more commonly referred to as “Log Miner”. MySQL is very convenient in that all you need to enter at the command... »