Search

Top 60 Oracle Blogs

Recent comments

Uncategorized

Re-partitioning 2

Last week I wrote a note about turning a range-partitioned table into a range/list composite partitioned table using features included in 12.2 of Oracle. But my example was really just an outline of the method and bypassed a number of the little extra problems you’re likely to see in a real-world system, so in this note I’m going to bring in an issue that you might run into – and which I’ve seen appearing a number of times: ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION.

DBMS_JOB – watching for failures

I had a friend point this one out to me recently. They use DBMS_JOB to allow some “fire and forget” style functionality for user, and in their case, the jobs are “best efforts” in that if they fail, it is not a big deal.

So whilst this may sound counter-intuitive, but if you rely on jobs submitted via DBMS_JOB to fail, then please read on.

By default, if a job fails 16 times in a row, it will marked as broken by the the database. Here’s a simple of example that, where the anonymous block will obviously fail because we are dividing by zero each time. I’ll set the job to run every 5 seconds, so that within a couple of minutes we’ll have 16 failures. First I’ll run this on 11.2.0.4

Being generous to the optimizer

In a perfect world, the optimizer would reach out from the server room, say to us: “Hey, lets grab a coffee and have a chat about that query of yours”. Because ultimately, that is the task we are bestowing on the optimizer – to know what our intent was in terms of running a query in a way that meets the performance needs of our applications. It generally does a pretty good job even without the coffee Smile, but if we can keep that caffeine hit in mind, we can do our bit as SQL developers to give the optimizer as much assistance as we can.

Hacking together faster INSERTs

Most developers tools out there have some mechanism to unload a table into a flat file, either as CSV, or Excel, and some even allow you to unload the data as INSERT statements. The latter is pretty cool because it’s a nice way of having a self-contained file that does not need Excel or DataPump or any tool additional to the one you’re probably using to unload the data.

SQLcl and SQL Developer are perhaps the easiest to utilize for such an extract. You simply add the pseudo-hint INSERT to get the output as insert statements. For example:

Nostalgia and choosing your in-flight movie

First thing to note on this post. No tech content in this one. Just some nostalgia.

Couple of days ago, I was flying from Perth to Dubai on my way to APEX Connect in Bonn. Because this is an 11hour hell in a death tube flight I settled in to my standard sleepless task of watching movies to pass the time. I try to steer clear of going exclusively with new releases because I know I’ll always be travelling again soon, so I try not to exhaust all my options too soon Smile

I was browsing through the “Movie Classics” section and found a movie from my childhood: The Right Stuff.

Partition loading in direct mode

Direct mode insert using the APPEND hint is a cool piece of technology that lets you load bulk data into a table very quickly and efficiently. Obviously there are a number of implications of doing so which you can read about here, but the one that catches most people out is the that you are locking the table during the load and once the load is completed, the table is “disabled” until the transaction ends. Here’s a quick example of that in action:

Raw partitions?

Here’s a quirky one for you. It can happen when you are dealing with a partitioned table where the partition key is defined as RAW. To be honest, I really can’t think of a reason why you ever want a table with a raw partition key. (If you have one, please let me know in the comments). Anyway, here’s the demo. I’ll start with a table using the cool automatic list partition facility and load a single row into it.

Oracle: compare DB Time and CPU time to ASH

Screen Shot 2019-04-26 at 1.31.49 PM

Below I’m comparing AAS from ASH verses SYSMETRICs specificially

  • ASH CPU in AAS vs SYSMETRIC CPU in AAS
  • ASH total AAS vs SYSMETRIC  total AAS derived from DB TIME

ASH CPU is consistently lower that SYSMETRIC  CPU and that could be because waits burn CPU but show up as waits and not CPU in ASH.

On the other hand, not sure why total AAS from SYSMETRIC  is consistently smaller than total AAS from ASH.

Preparing A Device for Someone New To I.T.

In my previous post I covered what I felt were the main considerations on deciding how to get someone with no real experience of I.T. online, for an example an elderly relative like my mum. In this post I’ll cover setting up the device.  Set-up is actually quite complex and there is a lot of assumed knowledge, like how the keyboard works, what spyware you leave turned on (none!) etc. So I am setting it up for her.  First I’ll just recap the main points on why I decided to use an Apple iPad for my Mum:

Take care with automatic indexes

This one came in via an AskTOM question. You need to be careful when disabling constraints that are underpinned by an index, especially when it comes to the storage for that index.

Let’s take a look at a simple example. I’ll explicitly nominate the LARGETS tablespace for my index that will support the primary key.