Development
Which service is executing a given SQL statement
We have a shared PLSQL code base between several pieces of application services. Some of them use all of it, some only a few pieces. We also use database services for accounting reasons. I needed to determine which database service was executing a given SQL statement while we plan a migration that may impact... »
Gzip in python
We have a large text file containing 325 million integer values that we need to load into a database. The file uncompressed is about 4.5GB. Even though disk is cheap, we didn’t have enough readily available to quickly decompress the file. As such, I used the python gzip module to decompress the source file... »
Python multiple producer/consumer queue
Below is a simple skeleton for a python queue that is both fed by and consumed by multiple different threads. We run five threads, each of which populate a queue with random integers. Lastly, we sleep a couple of seconds between each put, to simulate an ongoing workload. Our popper thread, which reads items... »
Dumping Oracle DDL part two
Below is a python script that strip the double quotes from the output of dbms_metadata.get_ddl, as well as the storage clauses. The initial clause is often far too high, so this allows you to extract the DDL and run it wherever you like. You can call it as follows: 1 ./getDdl.py case_insensitive_user_for_which_to_get_tables_and_indexes 1 2... »
Running log miner from python
Python is so flexible for quick and dirty toolsets, that I built one this morning that invokes the Oracle log miner software to read archived redo logs. It queries the logs for changes to a given table, and prints any records it finds. The version below needs to be enhanced to have search filters... »
Quick way to compare two python lists
***Please read the entire post if you are looking for a fast way to compare two lists*** If you need to compare two lists, you can use a list comprehension such as the following: 1 2 3 4 5 >>> a=[1,3,5] >>> b=[1,2,3,4,5,6] >>> print [c for c in b if c not in... »
Python script to print out table data in name/value pairs
Simple today. I just wanted to ensure I had this in case I need to cut and paste it later 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 #!/home/oracle/local/bin/python import cx_Oracle, string connection = cx_Oracle.connect(mode = cx_Oracle.SYSDBA) cursor = connection.cursor()... »
Parsing hanganalyze output for blocking sessions
The hanganalyze command was introduced in Oracle 8i. It is very useful, especially to mere mortals like us. It is formatted in a human readable format. In contrast, a systemstate dump normally contains a lot of memory addresses and offsets, which is useful to Oracle support, but not so much to us. hanganalyze is... »
Yet another reason to use Oracle’s JDBC connection manager
Over the weekend we had an odd issue. We have four nodes in a cluster, with two core services. SERVICE_A runs on servers 1 and 2, while SERVICE_B runs on server 4. Server 3 is effectively idle most of the time unless we need to allow for additional throughput. Server 3 died on Friday... »
Troubleshooting “library cache lock”
If you have several users waiting on a library cache lock, they are normally blocked by one user that is waiting on a library cache pin. You have to find out who is blocking the user waiting on the library cache pin and find out what they are doing. Our test case performs the... »