I inherited my dad’s penchant for tools. He frequently came home after work with a new screw driver or ratchet. He used baby food jars to organize excess nuts and bolts left over from projects past. I too, buy tools,…
Author: Steve
Fire! Fire! Fire!
If you find yourself in the middle of a hung database, you can do what follows (see Metalink for additional information) Can login to the database alter session set events ‘immediate trace name SYSTEMSTATE level 10’; –three time waiting five…
SQL Bind Capture – Part 3
I needed to find out which variables were producing a poor execution plan. Short of enabling 10046 trace with binds enabled, I found the following is a good approximation. Basically: 1. Find the PLAN_HASH_VALUE for the SQL_ID with the poor…
Using a cursor variable in SQL*Plus
Below is a simple example of opening a cursor variable in SQL*Plus and then printing its contents. DBA’s can use this if they are not PL/SQL proficient to do some simple test casing with developer cursor code. SQL> variable b…
Shell scripting stats from a 10046 trace
This morning, I enabled a 10046 trace at level 12 for a slow session. I did not scope the trace interval (ala Cary Milsap), so I didn’t even have the query text in the trace file (although I knew what…
Which events are set in my instance?
Quick and dirty today… For 10g… connect / as sysdba oradebug setmypid oradebug dump events 4 …and for 11g… connect / as sysdba oradebug setmypid oradebug eventdump session
Running RMAN commands from PLSQL
I still haven’t been able to determine if this is 100% supported, but it works. If you wanted to delete archived redo logs from within a database session (for some unknown reason), you can run something similar to the following:…
Library cache lock during grant/revoke
This is interesting. I have always thought oracle latches were kind of mysterious, for lack of a technical term. Don’t get me wrong, I understand semaphores (I have programmed with these before), and “test and set” operations on certain types…
sudo and LD_LIBRARY_PATH … never the twain shall meet
While writing something for our operations support team to use when running a particular process, I found that sudo does not recognize a previously exported value for LD_LIBRARY_PATH. It also won’t use what is in the .bashrc or .profile for…
JDBC connection timeout
While testing a remote data center today, we had some network connectivity issues. We twiddled our thumbs while waiting for connections to throw an exception due to a network block. I found the setLoginTimeout() method on the DriverManager class. You…