At last, here is the first complete solution to the problem of dividing a table into 12 chunks with equal numbers of blocks. James Su and I proposed this solution on OTN, but without the "filtered_extents" optimisation.
If you are about to embark on a Data Vault project this year, and not quite sure the best way to load your data into a Data Vault efficiently, I have a great opportunity for you. Dan and Sanjay have been hard at work re-vamping the Learn Data Vault site and are now ready to […]
Oracle SQL Developer has 2 client-side logging mechanisms for capturing SQL that is executed:
- SQL History
I tend to keep both panels hidden or closed until I need them. The recording happens whether they are visible or not on the desktop.
This is the SQL that you have executed in a SQL Worksheet. It’s also any scripts you have executed in a SQL Worksheet. Ran something the other day and forgot to (more...)
We still want to split a table into 12 chunks with equal numbers of blocks, so we can access each chunk using a rowid range. We have the extents we need and now we need to calculate the exact chunk boundaries.
After "only" 3 posts on chunking tables, we now have the raw materials for breaking up a table into 12 chunks with equal numbers of blocks, and for getting ROWID ranges that let us access the data. Now let's chunk!
So far we've decided to access a table chunk by chunk, each chunk containing one twelfth of the tables's blocks. But first, how do we access data in a chunk of blocks? How do we even know what blocks a table has?
Seems to be a simple problem and easy to fix. Actually it is not because if you try one of these examples then you will fail: Mehr »
As I mentioned in my previous post
, the basic idea is to divide a table into 12 "equal chunks", each "chunk" being a rowid range that covers the same number of blocks (give or take 1). What does this mean? Why do this? Why divide by blocks and not rows? Here are my thoughts.
Sometimes things just happen which makes you realise that stuff you thought all experienced DBA’s knew about isn’t common knowledge. I suppose it’s a side effect of working with the same evolving product for over 25 years at dozens of clients, when the colleague you are talking to has a mere decade of experience at fewer companies (and therefore less diversity of challenge).
Today I noticed that a release was creating a table, then an (more...)
Im heutigen Blogpost möchte ich kurz anhand eines Beispiels aufzeigen, wie Sie die Entfernung von SDO_GEOMTRY Punkten berechnen können. Hierbei gehe ich auch konkret auf das Problem des Verbindens von zwei Datenmengen anhand einer definierten Entfernung ein.Mehr »
I was going to do a follow-up post to my post on USERENV(‘COMMITSCN’) just to describe the slightly better known but still overlooked (and possibly more useful) ORA_ROWSCN – but I don’t need to!
As Neil Chandler has done this excellent post on it
Go and look at that post, it has all the information and detail you were actually looking for.
However, for any of you search-engine-seekers who can’t face the pain of following (more...)
I had a requirement the other day to understand when some data had been changed, but there was no column on the table which showed this.
So how do I find out? Well I could go off mining redo and lots of other time consuming and exotic things, but you can use the Oracle Pseudocolumn ORA_ROWSCN. This gives the SCN assocaited with each row. Well, actually it usually doesn’t. It does not show when the (more...)
If you have temporal tables with date ranges, and you are sure those date ranges cannot overlap, then you can join them using the technique in my previous post
- with a few important changes!
A few days ago I was introduced (or re-introduced) to USERENV(‘COMMITSCN’) by Jonathan Lewis. This is an internal function that allows limited access to the SCN of your transaction.
I was trying to find a way to get the actual commit SCN easily as it struck me that Oracle would have it to hand somewhere and it would be unique to the change and generated very efficiently. I could not find anything to do it (more...)
This is my third post about temporal tables that don't allow overlaps. I'm going to add support for temporal gaps, which will also allow objects to stop existing at some point.
I have to import a set of XML files from time to time. Most of those XML files can be imported with out any problems. But at least one file includes a special character U+0016 which occurs randomly some where inside the file.
When I try to import that file I get this ORA- error message:ORA-31011: XML-Parsing nicht erfolgreichORA-19202: Fehler bei XML-VerarbeitungLPX-00217: Ungültiges Zeichen 22 (U+0016)Error at line 39409 aufgetretenMehr »
In my previous post
, I showed a way to implement and join temporal tables when there are no gaps, overlaps or null values. Let's see what happens when I allow null values.
n this post I will show you how to access an array of data within the JSON document and how we handle nested arrays. Then finally I will give you an example of doing an aggregation using data in the JSON structure and how simple it is to filter your query results by referring to values within an array.
Previous installment: Day Three: Just a Mess Without a Clue
I know a funny little man,
As quiet as a mouse,
Who does the mischief that is done
In everybody’s house!
There’s no one ever sees his face,
And yet we all agree
That every plate we break was cracked
By Mr. Nobody.
The purpose of a SQLT report is to collect all the information that could possibly shed light on a problem (more...)