Search

Top 60 Oracle Blogs

Recent comments

developer

Developer Live wrap up

The Developer Live event for database has concluded. Thank you to the (almost) 2000 people that attended my talk across the USA, Europe and APAC timezones! I very much appreciate you giving up your time to attend the session.

Whilst we were doing some Q&A I tried to add as many useful links into the chat line as I could, to help people with onward learning on SQL performance and database performance in general. A few of you then asked if I could publish that chat as to not lose that information, so here it is below

From Me to Everyone:  04:06 PM
Hi everyone,

Its Connor here. Chris Saxon and I are monitoring the Q&A channel, so fire off your questions and we’ll answer them right here.  Anything we can’t answer, we’ll tackle at a later date.

A guided path to database expertise

At college/university, the learning path for a computing-related course often includes several coding languages, and occasionally some treatment of database technology as well. But often, it is only once you enter the business world as you embark upon your IT career that you truly start to face the demands of enterprise-level data requirements. The small amount of education on databases, or the small databases themselves sometimes do not prepare you for the realisation that data drives everything in the business world.

And where does one start? The amount of content out there about databases is overwhelming, and often assumes a base level of knowledge or is targeted at solving specific business problems.

If you have faced this obstacle, then I’m pleased to offer a new resource for you. One simple, consolidated, FREE training guide to kick start your database knowledge on developing applications on the Oracle Database.

Enjoy!

Say NO to default DATE formats

Today’s quick but important message is…don’t be lazy!

Look….I get it Smile

If given the choice of typing:


'01-JUL-20'

or typing this:


to_date('01-JUL-20','DD-MON-RR')

then more often than not, we all are going to opt for the first alternative because we all can get lazy. But that reliance on the default date format mask in a session can be fraught with danger. Because a script that worked in one circumstance, can silently fail in another.

GROUP BY might be distinctly better than DISTINCT

One of the cool things with materialised* views in Oracle is their ability to be kept in sync with the source table(s) from which they are derived from, in real time or near real time. To achieve this, we typically employ mechanisms such as materialised view logs to capture modifications to the source tables, and occasionally we need to change the definition of the materialised view itself when dealing with aggregations and joins. However, some times we know that if DML on the source is incredibly rare and/or the cost of updating the materialised view is very small, we can avoid all that and simply perform a REFRESH COMPLETE whenever a transaction is committed on the source tables. This avoids any issue around the materialised view becoming stale, and also avoids the need for scheduler jobs to keep the materialised view refreshed.

SQLPlus goodies with linesize

Anyone that has used SQL Plus since its inception will be familiar with this frustration:

image

Cloning a schema with one line

In the world of DevOps, continuous integration and repeatable test cases, the demand for being able to

  • quickly build a suite of database objects,
  • utilise it for a series of tests,
  • then throw the objects away

has become far more common. This is one of the many great use cases for pluggable databases with all of the powerful cloning facilities available. In particular, now that you can take advantage of pluggable databases without* incurring additional license fees, there are some great opportunities there…but that is the topic for another post.

The death of UTL_FILE – part 2

I wrote a post a while back call “The Death of UTL_FILE”, and probably because of it’s click-bait title I got lots of feedback, so I’m back to flog that horse Smile. Seriously though, I stand behind my assertion in that post, that the majority of usages of UTL_FILE I’ve seen my career are mimicking the spooling behaviour of a SQL*Plus script. And as that post pointed out, you can now achieve that functionality directly with the scheduler.

That is well and good for writing files from the database, and I added:

It’s all downhill past 30

Yes, it sounds like a lamentation of the aging process, and from my own experience, it is definitely true that the wheels started falling off for my knees, back and plenty of other body parts once I turned 30. But that is perhaps a legacy of too much alcohol, not eating well in my youth and failing to stretch rather than any particular chronological milestone Smile.

But this post is not about that. This one is about the magical 30 character limit on identifiers that we finally lifted in version 12c. For static SQL, this is obviously a no impact change – you either define your tables and columns with longer names or you don’t, and the static SQL you write reflects that position.

Juicing up UTL_FILE

Think about your rubbish bin for a second. Because, clearly this is going to be an oh so obvious metaphor leading into UTL_FILE right?  OK, maybe a little explanation is needed. I have a basket next to my desk into which I throw any waste paper. It is where I throw my stupid ideas and broken dreams Smile

The simplest things….can be risky

Java and Oracle expert Lukas Eder tweeted yesterday about a potential optimization that could be done when reviewing database SQL code.

image