Python
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 #!/home/oracle/local/bin/python import cx_Oracle, string connection = cx_Oracle.connect(mode = cx_Oracle.SYSDBA) cursor = connection.cursor() cnt=cursor.execute("select * from v$database") colcount=len(cnt.description) for row in cursor.fetchall(): print "-----------------------------------------------" for col in range(colcount): print string.ljust(cursor.description,40) + str(row) print... »
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... »
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 following... »
cx_Oracle cursor fetching behaviour
I have noticed this over the past few months, and think I still may be missing something. Given the following code… #!/home/oracle/local/bin/python import cx_Oracle import sys con = cx_Oracle.connect("howards","******","perfrac") con.cursor().execute("alter session set events '10046 trace name context forever, level 12'"); cursor = con.cursor() cursor.arraysize = 1000 try: cursor.execute("drop table t") except: pass cursor.execute("create table t(c number)") cursor.execute("insert into t select rownum from... »
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 the user that owns the script being sudo’d. You also can’t use... »
How long will my flashback database command take?!!
Oracle 10g introduced the wonderful (and I mean wonderful) ability to “rewind” a database back to a point in time, rather than restoring a backup and rolling it forward in time using transaction logs. This technology is called flashback database, and googling it will provide far more useful information than I can in... »
How close am I to realtime with dataguard and maximum performance (asynchronous) mode?
Oracle supplies two views to monitor how up to date your standby database is when compared to your primary database. One view is v$dataguard_stats, and the other is v$standby_apply_snapshot. Each of these views is only updated every 30 seconds, or so. Even then, I wonder if they are not “behind”. ... »
Manually subscribing to Oracle FAN events
JDBC and dot Net clients are the recommended way to automatically subscribe to high availability events in an Oracle clustered environment, AKA, a RAC. I was curious as to how this could be implemented using your own code behind the scenes. To show this, we will use a combination of PL/SQL and python. First, we... »
Poor man’s parallel DML
We were recently required to get rid of 250 million rows in a partitioned audit table. While the table is partitioned, we have global indexes that would be rendered unusable after dropping or truncating a partition. The UPDATE GLOBAL INDEXES clauses I have never found to be immensely useful, as all it... »
Port checker
This was useful when we testing a migration between database servers. import socket import sys for i in range(1,5): try: HOST = "foo" + str(i) PORT = 2484 print HOST + " " + str(PORT) sock = socket.socket() sock.connect((HOST, int(PORT))) ... »