This is a documented feature, but I wanted to post my notes so I could reference them if needed. All you basically have to do to set this up is: install XML DB (package dbms_xdb). This is installed if you…
Author: Steve
Automatically switching connections over with Dataguard and zero code changes
In 10.2.0.4, I tested the ability of off the shelf code to automatically connect to a database that has been “switched over to”. A switchover is a graceful exchange of database responsibilities that prevents a split brain scenario where two…
Format output of crsctl status for RAC 11gR2 (and now 12c)
crs_stat -t in 10g was great because you could just print it out and grep for OFFLINE or UNKNOWN to determine if there was a problem. 11g deprecated the use of crs_stat -t. It’s not just that you shouldn’t use…
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…
Why MERGE should be used for even single row inserts
We recently had an issue where a particular insert was generating a large amount of CPU time requirements. When we examined the PL/SQL code, we found the developer had coded the following… begin insert into table values(pk); exception when dup_val_on_index…
Why PGA_AGGREGATE_TARGET is not a hard limit
PGA_AGGREGATE_TARGET is not a hard limit. Lather, rinse, and repeat 🙂 I like to think of it as a tuning suggestion to Oracle as to how much it can allocate for things such as sorts and hash tables. Below is…
bbed password
It never ceases to amaze me how difficult some responders on message boards make it to use certain tools. Requests for information are usually denied with something similar to “If you have to ask that, you shouldn’t be _fill_in_the_blank_”. How…
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…
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…
Handling constraints when truncating a table
Oracle doesn’t supply a TRUNCATE TABLE TABLE_NAME CASCADE CONSTRAINTS syntax structure as it does when dropping a table. The anonymous block below will truncate all tables in the current schema and handle the constraints violations (foreign keys) that may be…