I tend to compile with gcc, in a bash session, on Windows 7. I use Code::Blocks as my IDE of choice and one of my projects, well, quite a few, use the excellent OCILIB library for accessing Oracle databases, by Vincent Rogier. I can’t recommend this library highly enough. However, it comes with a Code::Blocks project file to build 32 bit libraries, but I need 64 bits. Here’s how I do it.

There’s a new utility to assist in diagnosing the underlying cause of Oracle deadlocks. Interested? You can download source code as well as binaries for Linux and Windows at https://github.com/NormanDunbar/DeadlockAnalysys/releases. All you have to do is execute the DeadlockAnalysis utility with a list of Oracle trace files on the command line. Each trace file will get its own report, in HTML format, in the same directory as the trace file.

Thanks to http://www.dbaglobe.com/2010/08/drop-temporary-tablespace-hang-with-enq.html it was a simple matter to resolve the above enqueue wait on an attempt to drop a previously default temporary tablespace. The session causing the problem was a DBSNMP session being run by the OEM agent on the server. The following script, from the above blog, allowed me to identify the session and sort out getting it ‘removed’ to allow the drop to continue. 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 SELECT se.

Have you ever seen the error RMAN-20033: control file SEQUENCE# too low and wondered what could be causing it? If you look on MOS, you will probably see that the error is caused by the control file in use is older than the one that was most recently used to synchronise the RMAN catalogue and that you should either recreate the database control file(s), or, delete the database from the catalog and add it in again.

Sometimes, just occasionally, you find yourself as a DBA on a site where, for some strange and unknown reason, you don’t have an Entity Relationship Diagram (ERD) for the database that you are working on. You could use a tool such as Toad, or SQL*Plus (or even, SQL Developer - if you must) to generate a list of referential integrity constraints. There has to be a better way. The problem with lists is, they are just words.

(Snorkelling is not quite as in depth as a “deep dive”!) Attempting to parse a listener.log will probably bend your brain, but I needed to do it recently to determine which unique servers and/or desktops and/or application servers were still connecting to a database prior to that database going down for maintenance. This was an exercise in confirming that the documentation we have, is correct. According to the Net Services Administrator’s Guide, there are a number of different message types that can appear in a listener.

You know the score, you are running an impdp and it looks to have hung up. You’ve watched the log file (or on screen messages) and it’s sitting at something like: Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX But hasn’t moved from there for what seems hours. The alert log for the database is of no help, as there are no errors or warnings logged there. What’s going on? Is the import actually running?

Trace Collier has been updated and rewritten. Numerous bugs and foibles have been fixed. TraceAdjust is a new, useful, utility to carry out some pre-processing on a trace file before you have to use your own weary eyes to work thorough potential Oracle performance problems! That’s why I wrote it! Trace Collier My old utility has been totally rewritten in C++ rather than vanilla C, which has had the bonus of allowing me to fix some bugs, and do away with the need to recompile whenever you hit a system limit of some kind.

Trace Collier has been updated again. Mostly bug fixes, but there’s a little enhancement too. The current release is 0.21. Trace Collier is a utility that parses an Oracle trace file, with binds listed (event 10046 level 4 or 12, etc) and extracts all the user submitted SQL statements and writes them to an output file with the bind variables replaced by the actual literals used when the statement was executed.

Trace Collier has been updated after a couple of foibles were found during the processing of a trace file. The version has been bumped to 0.19 as of today, 2nd December 2016. The bugs fixed were: The utility now notices exec ERROR lines as well as PARSE ERRORs. Just because it’s nice to know where things might have gone wrong. These are in addition to the PARSE ERRORs that it has been processing up until now.

I have a table with dates in, and some NULLs. Two people, on the same database, running the same SELECT query, in the same schema, with the same privileges, get vastly differing results. Why? Fine Grained Auditing is not at play here. Table names, column names etc have been changed to protect the guilty. In the table in question, the A_DATE column is correctly defined as DATE, rather than anything else unsuitable.

I recently posted a useful oraenv for Windows utility. This has been updated so that you can run it in batch files by passing the desired Oracle SID on the command line. Details at http://qdosmsq.dunbar-it.co.uk/blog/2016/08/oraenv-for-windows/

Having recently had to learn a whole new way of working when I took on a contract migrating a database to the Windows “cloud”, I realised that there’s no equivalent to the useful Unix oraenv utility. I had to write my own. Give me a bash shell any day! The utility is oraenv.cmd and executes like this: set ORATAB=c:\\users\\ndunbar\\oratab ... oraenv Update 30/08/2016: You can now pass the desired SID on the command line and avoid all that prompting stuff!