Search

Top 60 Oracle Blogs

Recent comments

April 2015

Not In CTAS

Everyone gets caught out some of the time with NOT IN.

NOT IN is not the opposite of IN.

This came up in a (fairly typical) question on OTN recently where someone had the task of “deleting 6M rows from a table of 18M”. A common, and perfectly reasonable, suggestion for dealing with a delete on this scale is to consider creating a replacement table holding the data you do want rather than deleting the data you don’t want.  In this case, however, the query for deleting the data looked like this:


DELETE FROM EI.CASESTATUS
     WHERE CASEID NOT IN (SELECT CASEID FROM DO.STG_CASEHEADER);

The suggested code for creating the kept data was this:

(OT) an idea for Easter (and well…every day)

I don’t travel as frequently as some people do, but with OpenWorld and various Oracle conferences each year, I definitely see my fair share of the inside of a plane and a hotel room.  To pass the time on flights, I try to read, but when you’re reading on a work-based trip, it’s a case of reading a page or two here and there, rather than a true extended session of relaxed reading.

The net result ?  I generally buy what I call "airport crap" when it comes to books.  That is, cheap, easy to read, crime thrillers that are totally formulaic in nature.  Its pretty simple:

Hey dude, where’s my memory?

This blogpost is about finding the actual amount of memory a process is taking. In order to do so, this post dives into the memory mechanisms of Linux. The examples in this article are taken from an Oracle Linux version 6.6 server, with kernel 2.6.39-400.243.1 (UEK2). This is written with the Oracle database processes in mind, but actually uses examples of a processes running ‘cat’, which means the contents of this post are absolutely not limited to Oracle database processes.

Let’s start off with a simple example. Let’s look at our own memory map. In order to do so, I use the ‘cat’ executable and the ‘maps’ entry in the proc pseudo-filesystem. This is how that is done, including the result:

Edition based redefinition – an apology

In April 2008 (wow, does time fly!) I used the following picture in my "11g features for Developers" presentation at the Australian Oracle User Group conference.

image

I think the picture is from the movie "Indiana Jones and the Last Crusade"… where they sought the Holy Grail.

OakTable World at IOUG COLLABORATE15

class="l-submain">
class="l-submain-h g-html i-cf">

size="-2">Update history: /> 5-Apr: WIT panel added, Alex removed, Gwen and Pete schedule shifted. /> 11-Apr: Gwen and Pete swapped sessions. /> 13-Apr: Jonathan off lightning talks. />

Guess what? OakTable World at IOUG C15 is happening again! Last year, we had awesome sessions and wonderful attendees. The sessions were so successful, in fact, that we needed a bigger room this year (there were other reasons too, but hey we can fit more people now!).

What: OakTable World C15 /> When: Wednesday, April 15, 2015, 8:00am – 5:30pm /> Where: Mandalay Ballroom K /> id="more-72835">

Oracle 12c SQL – Expanded VARCHAR2 (32k)

Extended VARCHAR2

Oracle 12c now allows an expanded maximum size for VARCHAR2, NVARCHAR2, and RAW columns; CHAR and NCHAR are unchanged.
Three varying character datatypes may now grow to a maximum size of 32,767 bytes (4,000 was the previous limit):

  • VARCHAR2       32767
  • NVARCHAR2    16383
  • RAW                    32767

Expanded VARCHAR is not the default; it must be enabled by DBAs (see following).

Extended VARCHAR Setup

DBA activities to setup extended VARCHAR include:

  • Set init parameter:  max_sql_string_size = EXTENDED
  • May be set when creating database
  • To change an existing database
  • Bring database to UPGRADE mode first
  • Execute upgrade script (utl32k.sql)

Once made, the change to extended is irreversible!

Using Extended VARCHAR

Extending VARCHAR maximum size should be researched carefully:

OT: New Oracle Database features spell doom and gloom for NoSQL

Oracle Corporation has released a slew of new features that allow Oracle Database implementations to catch up to, match up to, and leave in the dust NoSQL implementations in the areas of performance, scalability, and reliability (PSR).(read more)

OT: Oracle wants your votes

It used to be that if you had an enhancement suggestion for Oracle Database, you had to file an enhancement request in MOS and pray that the Oracle product management gods would hear your request someday. It is now possible to make your enhancement request in a public forum and let the community vote on your idea.(read more)