What to do when you hit a problem (possibly after an incomplete recovery) that reports a “unique key violation” on sys.wrm$_snapshot_pk. (As reported recently in this thread on the Oracle database forum.) Snapshot ids are carefully sequenced, without gaps, so somehow the thing that controls the “current” sequence number has gone backwards and is trying to generate a value that is lower than the current highest value in wrm$snapshot. The thread I referenced above does point to an article dated 2017 on Alibaba discussing methods of checking for corruption and clearing up messes; but as an extra ..read more

Here’s a detail about dbms_output that is probably overlooked because (in most cases) it’s ignorable, except that it can lead to unexpected response times when you try using it to debug “busy” operations. A question on the Oracle SQL and PL/SQL forum asked: “Why is a PL/SQL ‘for loop’ so slow on Oracle Autonomous DB?” and provided the following code to demonstrate the issue. begin for i in 1..36000 loop dbms_output.put_line ('i value: '|| i); end loop; end; / The OP reported the elapsed time for this block as 1 minute 40 seconds (compared to Java taking only ..read more

In the second part of this series I described some of the technicalities of Index Usage Tracking and showed an example of what I was doing to test the feature. In this episode I’ll describe some of the index access methods I’ve tested and report the results. I’ve listed the tests I’ve planned so far and will show results as I find time to run the tests – if you can think of more cases add them in the comments and I’ll extend the list. (If you think a test is a very good idea, “upvote” it in the comments and I’ll try to run it sooner. Before I’ve done all the tests I’ll add a section on Conclus ..read more

In the first part of this series I introduced Index Usage Tracking and the view dba_index_usage – a feature that appeared in 12.2 as a replacement for index monitoring and the view dba_object_usage. In this note I’ll give a quick sketch of the technicalities of the implementation and comments on how to test and use the feature. Actual tests, results and observations will have to wait until part 3. A not very deep dive There are three parameters relating to Index Usage Tracking (iut), shown below with their default values: _iut_enable [TRUE] _iut_max_entries [30000] _iut_stat_collection_type ..read more

In 12.2 Oracle introduced Index Usage Tracking to replace the previous option for “alter index xxx monitoring usage”. A recent post on the Oracle database discussion forum prompted me to look for articles about this “new” feature and what people had to say about it. There didn’t seem to be much information online – just a handful of articles starting with Tim Hall a few years ago and ending with Maria Colgan a few months ago – so I thought I’d update my notes a little and publish them. Unfortunately, by the time I’d written the first 6 pages it was starting to feel like very heavy going, so I ..read more

Despite their continued presence in the Oracle data dictionary, LONG columns are not an option that anyone should choose; you can’t do much with them and they introduce a number of strange space management problems. Nevertheless a recent thread on the Oracle database forum started with the following question: “How do you use LONG columns in a WHERE clause?”. The basic answer is: “You don’t”. This user wanted to query all_tab_partitions for a given table_name and high_value, and the high_value is (still) a LONG, so attempts to use it resulted in Oracle error “ORA-00997: illegal use of LONG data ..read more

Here’s a silly little detail about execution plans on (interval) partitioned tables that I hadn’t noticed until it showed up on this thread on a public Oracle forum: it’s an execution plan that claims that Oracle will be visiting a partition that clearly won’t be holding the data requested. Here’s the starting section of a demonstration script – mostly by Solomon Yakobson with minor tweaks and additions from me: rem rem Script: non_existent_partition.sql rem Author: Solomon Yakobson / Jonathan Lewis rem Dated: Mar 2024 rem rem Last tested rem ..read more

Here’s a little detail that I discovered recently when I wanted to do something a little exotic in 23c on the official Virtualbox VM. There’s been a hidden parameter to disable oradebug since (at least) 12.2.0.1. The clue is in the name: _disable_oradebug_commands The valid values for this parameter are none, restricted, and all. The default value in 12c, 18c, and 19c is none; the default value in 21.3 and 23.3 is restricted. This means some of the investigative code you may have used in the earlier versions may produce an error in the newer versions. To change the value you have to restart ..read more

A recent (Mar 2024) question on the MOSC DB Admin forum (needs an account) asks why a query which runs every few minutes and executes 25 times in 2 hours according to an AWR report never shows up in v$session when you query for the SQL_ID. SQL> select * from V$session where sql_id = '0qsvkwru0agwg'; no rows selected There are a few reasons why you might be unlucky with the timing but it seems likely that this query, if repeated very frequently for a couple of minutes, ought to be able to capture details of the session executing it, especially since the (edited) line supplied from the ..read more

This is a note that will probably be of no practical use to anyone – but someone presented me with the question so I thought I’d publish the answer I gave: Do you know how object ID is assigned? It doesn’t look as if a sequence is used I’m fairly sure the mechanism has changed over versions. (Including an enhancement in 23c where the object number of a dropped (and purged) object can be reused.) I don’t really know what Oracle does, but I do know that there is an object in obj$ called “_NEXT_OBJECT” and Oracle uses that as a control mechanism for the dataobj# and obj# (data_object_id and objec ..read more