Top 60 Oracle Blogs

Recent comments



A recent question on the OTN database forum asked: “What’s the difference between index rebuild unrecoverable and nologging?” The most important difference, of course, is that unrecoverable is a deprecated option so you shouldn’t be using it even though it still works. Another important difference is that unrecoverable tells you exactly the risk you are [...]

Index branches

Some time ago I published a little script that showed you how to read an index treedump and summarise the leaf block usage in the logical order of the index leaf blocks – allowing you to see fairly easily if the index had any areas where the blocks were poorly filled. Here’s another  way of looking [...]


The following question came up in an email conversation a little while ago: Are you aware of any problems a large oltp site might have with running index coalesce during production hours, as opposed to doing index rebuilds in a maintenance window? The main overhead with index coalesce is that it generates a lot of [...]


A client recently upgraded from 32-bit Oracle to 64-bit Oracle because this would allow a larger SGA. At the same time they increased their SGA from about 2GB to 3GB hoping to take more advantage of their 8GB of RAM. The performance of their system did not get better – in fact it got worse. [...]


I usually think about locks (and the meaning of the lmode and request columns in v$lock) in terms of numbers, and never seem to remember which number goes with which name – apart from mode 6 = exclusive. So I’ve finally put up a little list somewhere I’ll always be able to find it if [...]


In a recent discussion in the comments of a blog item by Charles Hooper, I made a comment about how hard it is to be accurate (and unambiguous) when talking about the space needed for a rowid. So I thought I’d try to list all the possible options for rowids that you might find in [...]

SQL*Net compression

Here’s a little demonstration I’ve been meaning to write about for the last few years – it’s very simple: create a table, then query it a few times. execute dbms_random.seed(0) create table t1 as with generator as ( select --+ materialize rownum id from all_objects where rownum <= 3000 ) select lpad(dbms_random.string('U',2),40,'X') v1 from generator [...]

Row count 2

Following my notes about counting rows in blocks, someone emailed me to ask how the query I’d published would deal with migrated rows. Remember, a migrated row is one that has been moved in its entirety to a different block because an update to the row resulted in the row needing more space than was [...]


For anyone doing complicated things with RAC, there’s a useful little note on Fairlie Rego’s blog about the improved options for handling and reporting dynamic remastering of objects.

Failed Login

Here’s a piece of code I found recently running every half hour on a client site:

SQL_ID = 2trtpvb5jtr53
TO_CHAR(current_timestamp AT TIME ZONE :"SYS_B_0", :"SYS_B_1") AS curr_timestamp,
COUNT(username) AS failed_count