Video : Online Statistics Gathering for Bulk Loads

Today’s video gives a demonstration of Online Statistics Gathering for Bulk Loads.

This is based on this article.

The star of today’s video is my sister-in-law Maria Colgan, otherwise know as SQLMaria. You may know her by her former names of “the in-memory lady” and “the optimizer lady”. Maria sent me three different clips, with one looking like she’s running for president. I’m sure these will appear in future videos… πŸ™‚

Cheers

Tim…

The Problem With Oracle : If a developer/user can’t do it, it doesn’t exist.

This post was inspired by two separate events that happened back to back.

Event 1

One of my colleagues sent me a list of cool features that were available in a cloud database service we may be purchasing. The vast majority of those features have been available in Oracle for over a decade, and we are currently licensed to use them. I would suggest the Oracle features were in fact the inspiration for their inclusion in this cloud database product.

I got a little on the defensive and explained this situation, and their reply was along the lines of, “Most of those features are DBA-focused, not user-focused”. That was also not 100% true, but I can understand where that assumption came from.

Now you could raise several arguments, including these.

  • The person in question should have known about this functionality.
  • I should have done a better job about promoting this functionality.
  • I could have done a better job about making those features that were DBA-focused available to a wider audience.

Regardless of all that, we are left in the position where Oracle and several other database engines are seen as DBA-focused tools, and not really inclusive for developers and users.

FYI: I think this cloud database product is probably the right choice for this specific use case, so this is not {only} about me being an Oracle fan-boy. It is my frustration at the reinvention of the wheel being touted as new. Happens all the time… πŸ™‚

Event 2

Jacob Duval posted these two tweets.

#mysql is better than #PostgreSQL

Jacob Duval

simply put: mysql has a superior developer experience. postgres has a superior DBA experience. DBA is not really a job anymore, so I pick the developer experience every time.

Jacob Duval

Then think(x) replied.

We are here to learn… #PostgreSQL can sometimes seem rigid and inflexible.. but then our customers tell us that data integrity needs to be a rigid and inflexible goal

think(x)

My reply to Jacob’s second tweet was.

Being a DBA I am supposed to disagree with you, but I actually agree. If products lose sight of their users they are doomed to failure. I think some of the “rigid” stuff is necessary, but the user experience should always feel as effortless as possible. Combining the two is key!

oraclebase

Thoughts

This post is not meant to be damning of any specific company or product. It’s just meant to highlight what I believe is (or should be) a significant change in mindset.

Cloud databases, including Oracle Autonomous Database, reduce the dependency on DBAs, which is a good thing. You can argue that you still need “power users” of any product, but I think there must be a greater emphasis on making services that are developer focused, or even user focused. If companies persist on delivering products that have rigid dividing lines they will lose the fight.

The Oracle database is filled with amazing tech, but so much of it is still dependent on the DBA. I would argue that if you need a DBA to use a feature, that feature doesn’t exist. Cloud interfaces can help somewhat, but even then I feel like they track the DBA’s thought process, not how a user or developer would approach them.

“But what about finance systems and ERPs etc.”, I hear you cry. Yes, there will always be systems that need tighter control and removal of rights from the developers and users. These should be cloud-based and that is where the “power users” come in, but let’s not act like all systems are the same. As people have said before, the rise of NoSQL was in part due to putting the power in the hands of the developers. You can argue the rights and wrongs of this, but it’s clear to me the days of the DBA are numbered, and there has to be a change of focus. This is going to be a lot harder for long established companies/products as they come with a lot of baggage.

You either milk the existing cash-cow until it dies, or you take a step back and ask yourself where you want to be in the future…

I don’t claim to have any answers. I’m just a generalist that uses a whole bunch of products from a whole bunch of companies, and most of them are too damn hard to use, even with the assistance of Uncle Google. πŸ™‚

Cheers

Tim…

PS. I’m reminded of this post from the past. Infrastructure is dead. It’s all about the platforms baby!

Video : Multitenant : Running Scripts Against Multiple Pluggable Databases Using catcon.pl

In today’s video we give a quick demonstration of using catcon.pl to run scripts against multiple pluggable databases (PDBs) in a container database (CDB).

The video is based on one section of this article.

You can find loads of information about living with CDBs and PDBs in the following articles and the YouTube playlist linked below.

The star of today’s video is my long suffering wife Debra Lilley. Clearly suffering because of her social isolation, which of course means not seeing me. πŸ™‚

Cheers

Tim…

Video : APEXExport : Export APEX Applications and Workspaces From the Command Line

In today’s video we’ll give a quick demonstration of using the APEX command-line export utility.

The video is based on this article, which includes more examples, and Windows-based examples also.

The star of today’s video is my daughter Heli “Hell-Squirel” Helskyaho. Make sure you check out the cloud forming a Pikachu tail above her head! πŸ™‚

Cheers

Tim…

Video : Resource Manager : SQL Quarantine in Oracle Database 19c Onward

In today’s video we give a quick demonstration of using the SQL Quarantine features of Oracle Database 19c.

It’s a follow-on to last week’s video.

Today’s video is a cut down of this article.

These might come in handy too.

The star of today’s video is Martin Widlake. I put out a message to “The Family” as I had run out of “.com” clips, and Martin was the first one back. He sent three, but this one had me laughing, and suited the quarantine theme. πŸ™‚

Cheers

Tim…

PS. If you want to send me a clip with you saying “.com”, you know where I am.

Vagrant and Docker Builds : ORDS 20.2 and SQLcl 20.2 Updates

The recent Oracle REST Data Services (ORDS) 20.2 release prompted my usual reaction. I’ve gone through my Vagrant and Docker builds, and updated them to use ORDS 20.2 and SQLcl 20.2.

The Vagrant database builds, which include ORDS, can be found here.

The Docker ORDS builds can be found here.

There were also some small Tomcat mods.

  • Tomcat upgraded to 9.0.37.
  • HTTP/2 enabled.
  • Compression enabled.
  • Cache-Control enabled for images, CSS and Javascript.

All that went pretty well so as soon as I got to work yesterday I rolled ORDS 20.2 to all non-production environments, and a few “not yet production” environments. If you follow the blog you will know we use Docker for ORDS (similar to my Github builds). It makes rolling out a new version really simple. Just throw away all the containers and replace them with the spangly new ones.

If it’s looking OK after a few days we’ll push it out to the remaining production installations.

Cheers

Tim…

Video : Resource Manager : Runaway Query Management

In today’s video we give a quick demonstration of using Resource Manager to cancel a long running SQL statement.

This video is based on a series of articles from Oracle 8i to the present day. There is an example here.

Although there are aspects of it throughout most of the Resource Manager articles on the website.

The horror show was brought to you by Jeff Smith. Notice his proximity to assorted torture devices he probably describes as “tools” when questioned by the authorities.

Cheers

Tim…

When Implicit Date Conversions Attack

Yesterday, one of the developers was having a problem and emailed to ask what was going on. They sent me a section of code from an old trigger that included some date handling that looked “interesting”.

TO_DATE(SYSDATE,'DD/MON/RRRR')

Some bright spark had decided this was the best way to trim the time component off a date, and unfortunately for us it worked for a very, very long time. Many years in fact.

Why was this causing a problem now? They were trying to do an update of a table that caused this to trigger to fire. The update was working fine from SQLcl, but failed when it was called from APEX, giving this error.

ORA-01843: not a valid month

Let’s take a look at what was happening here.

The SYSDATE function returns a date. The TO_DATE function expects a string as input, so Oracle does you a “favour” and does an implicit conversion from date to a string. How does it know how to do this conversion? It uses the NLS_DATE_FORMAT value for the session. What is the default value in our database?

SQL> SELECT SYS_CONTEXT('USERENV','NLS_DATE_FORMAT') FROM dual;

SYS_CONTEXT('USERENV','NLS_DATE_FORMAT')
----------------------------------------
DD-MON-RR

SQL>

OK. So this is what is really happening, thanks to the implicit conversion.

TO_DATE(TO_CHAR(SYSDATE,'DD-MON-RR'),'DD/MON/RRRR')

That’s lucky. Those formats will work with no errors. Phew.

SQL> SELECT TO_DATE(TO_CHAR(SYSDATE,'DD-MON-RR'),'DD/MON/RRRR') FROM dual;

TO_DATE(T
---------
08-JUL-20

SQL>

So what’s the problem with APEX?

If I print out the NLS_DATE_FORMAT value from my APEX session it defaults to ‘DS’. What does that look like?

SQL> SELECT TO_CHAR(SYSDATE,'DS') FROM dual;

TO_CHAR(SY
----------
08/07/2020

SQL>

This is ‘DD/MM/YYYY’. Now I’m hoping you see the problem.

MON = MON
MM <> MON

We can see the result here.

SQL> ALTER SESSION SET nls_date_format = 'DS';

Session altered.

SQL> SELECT TO_DATE(SYSDATE,'DD/MON/RRRR') FROM dual;

Error starting at line : 1 in command -
SELECT TO_DATE(SYSDATE,'DD/MON/RRRR') FROM dual
Error report -
ORA-01843: not a valid month

SQL>

The implicit conversion used a different format mask that happened to be incompatible with the format mask used by the TO_DATE call.

In this case, they should have removed the time component using TRUNC(SYSDATE), but that’s not really the point of this post. Relying on an implicit conversion is *ALWAYS* a bug waiting to happen. In this case it took many years to surface, but the bug was always there. Waiting! APEX didn’t break the code. The code was already broken.

Date handling seems to mystify a lot of people, but it’s not that hard. You just have to pay attention and understand the functions you are using, rather than randomly combining things together until they appear to work.

Cheers

Tim…

PS. If someone says Oracle stores dates as strings, punch them in the face. I take no responsibility for the outcome of this action.

PPS. If you want to know more about Oracle dates, timestamps and intervals, you might want to look at this article.

Video : SQLCL and Liquibase : Deploying Oracle Application Express (APEX) Applications

In today’s video we’ll give a quick demonstration of deploying an APEX application using the SQLcl implementation of Liquibase.

I Know what you’re thinking. Didn’t I do this video two weeks ago? The answer is yes and no. This video is very similar to the Liquibase video I did two weeks ago, but that was using the Liquibase Pro client. This video uses the SQLcl implementation of Liquibase, and more specifically the runOracleScript tag to achieve the same thing.

The video is based on this article, which has an example of deploying an APEX workspace and an APEX application.

If you are new to Liquibase and SQLcl, you might find it easier to start with these.

The stars of today’s video are the offspring of Jeff Smith. I had been annoying Jeff on Twitter DMs while he was meant to be on holiday, so I agreed to pay him back by turning his children into international megastars. I take no responsibility for how they handle the fame! πŸ˜‰

Cheers

Tim…