Uncategorized

connor_mc_d's picture

Those pesky LONG columns

There was a time, many moons ago Smile when CLOB, BLOB and BFILE did not exist as data types. So if you had anything longer than a few kilobytes of data to store, you had to use a LONG or a LONG RAW.  But those data types came with all sorts of restrictions and frustrations, and we all embraced the improvements that the LOB data types brought in Oracle 8.  But of course, we carry a lot of that historical “baggage” in the data dictionary.

Jonathan Lewis's picture

gc buffer busy

I had to write this post because I can never remember which way round Oracle named the two versions of gc  buffer busy when it split them. There are two scenarios to cover when my session wants my instance to acquire a global cache lock on a block and some other session is already trying to acquire that lock (or is holding it in an incompatible fashion):

  • The other session is in my instance
  • The other session is in a remote instance

One of these cases is reported as “gc buffer busy acquire”, the other as a “gc buffer busy release” – and I always have to check which is which. I think I usually get it right first time when I see it, but I always manage to convince myself that I might have got it wrong and end up searching the internet for Riyaj Shamsudeen’s blog posting about it.

connor_mc_d's picture

Identity columns in 12c … just a sequence ?

This question came to me over Twitter, so I thought I’d whip out a quick post on it

 

image

Yes, we do implement the IDENTITY column via a sequence.  (Digression – I think this is a smart thing to do.  After all, we’ve had sequences for 20 years, so we know how they work, they are mature, tested, and rock solid, so why invent something new?)

connor_mc_d's picture

Subtle changes in XML, 11g vs 12c

An AskTOM reader brought this to our attention.  It is unlikely to cause you any issues, but perhaps is good to know when it comes times to upgrade from 11g to 12c.

If you are taking an user defined object type and transposing that to XML, you will see a slightly different handling of NULLs in the object attributes.  Here’s a quick example which demonstrates the difference.

11g

Nulls attributes do not appear within the XML output

connor_mc_d's picture

Dealing with IP addresses

Kris Rice made mention on Twitter about the free (but limited lifetime) IP address to Country mappings that you can get from Maxmind.

image

connor_mc_d's picture

Brand new year ? Brand new to installing Oracle ?

If you’ve stumbled across this blog trying to get started with a local installation of Oracle database for some personal development, then firstly, welcome!

Secondly, it can be a little daunting to get up to speed, so here’s a little video that will walk though the process of download, installing and getting up and running with Oracle database on a Windows laptop/desktop. It’s a lot easier than you think.

connor_mc_d's picture

Clone a table

Sometimes doing a CREATE TABLE AS SELECT is all we need to copy the data from an existing table.  But what if we want more than that ?  What if we really want to clone that table to match the original as closely as possible.  We had a question along these lines on AskTOM today.  A standard CTAS copies the NOT NULL attributes and the data types, but not really much else.  We know that Data Pump will take care of it, but that is more complex than a simple CTAS.

So here is a simple routine to wrap the Data Pump calls so that the CTAS can be achieved with just as simple a command.  A database link pointing back to the same database is all we need.

connor_mc_d's picture

Licensed for Advanced Compression? Don’t forget the network

We often think of Advanced Compression being exclusively about compressing data “at rest”, ie, on some sort of storage device.  And don’t get me wrong, if we consider just that part of Advanced Compression, that still covers a myriad of opportunities that could yield benefits for your databases and database applications:

  • Heat maps
  • Automatic Data Optimization
  • XML, JSON and LOB compression (including de-duplication)
  • Compression on backups
  • Compression on Data Pump files
  • Additional compression options on indexes and tables
  • Compressed Flashback Data Archive storage
  • Storage snapshot compression

However, if you are licensed for the option, there are other things that you can also take advantage of when it comes to compression of data on the network.

connor_mc_d's picture

It’s not about ego … it’s about knowledge

Take a quick look at this blog post by Jonathan Lewis

https://jonathanlewis.wordpress.com/2017/12/30/nvarchar2/

Anyone that has been working with Oracle for any length of time probably knows that Jonathan has a great depth of knowledge in the Oracle database, and is a regular blogger.  But this post is a good example to inspire anyone that is working with Oracle (or any technology for that matter) to start blogging and sharing their experiences with the community, no matter what their level of experience is.

If you read the post, you’ll see that Jonathan presented a well-crafted test case, and presented a hypothesis about NVARCHAR2 and potential side effects of adding columns of this data type to an existing table.

connor_mc_d's picture

2017–what grabbed your attention

Here are the blog posts that you hit on most this year.  Thanks for supporting the blog, and always, there will be more content next year !

To prevent automated spam submissions leave this field empty.
Syndicate content