Search

Top 60 Oracle Blogs

Recent comments

Uncategorized

2018-what grabbed your attention

Here are the blog posts that you hit on most this year, with the most viewed entry on top. Unsurprisingly it is about the release of 18c, but interestingly the ORA-14758 and the Active Sessions post have come up again from last years list, so it appears they are still common issues for the modern Oracle professional. And of course, it is nice to see that my Openworld Mega-download is helping the community.

Thanks for supporting the blog, and as always, there will be more content next year !

Your New Years Resolution

Aligning roughly with the calendar year, based on the Chinese zodiak we’re about to go from the year of the dog to the year of the pig. But for me, in the “Information Technology Zodiak” Smile , 2018 was the year of the hack, just as it was in 2017 and just as it will be for 2019.

I’ve not dedicated much time to keeping a record of all of the high profile breaches this year, but just off the top of my head I can think of:

Another day…another "use the right datatype" post

Here’s an interesting little oddity (aka bug) with scalar queries.

We’ll start with a simple working example


SQL> create table t1 ( c1 number );

Table created.

SQL> insert into t1 values (1);

1 row created.

SQL> create table t2 ( c1 int, c2 varchar2(10));

Table created.

SQL>
SQL> insert into t2 values(1,'t1');

1 row created.

SQL> insert into t2 values(1,'t01');

1 row created.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats('','T1')

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats('','T2')

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> select a.c1,
  2    ( select max(b.c2) from t2 b where a.c1 = b.c1 )
  3  from t1 a;

        C1 (SELECTMAX
---------- ----------
         1 t1

1 row selected.

That all seems straightforward:

A Christmas Carol

You better watch out,
Let me tell you a fact.
If your SQL has literals,
You’re gonna be hacked.

SQL Injection is comin’ to town

We’ve got a library cache,
It’s memory all linked twice.
But it only works well,
When you’re binding all nice.

SQL Injection is comin’ to town

We know when you’ve been lazy,
And concatenated simple strings.
So just make sure you bind your stuff,
And don’t let the bad guys in.

So… you better watch out,
Let me tell you a fact.
If your SQL has literals,
You’re gonna be hacked.

SQL Injection is comin’ to town

 

Merry Christmas everyone! Smile

Your AskTOM Top 10 for 2018

Here we go folks…here are the top 10 most viewed questions in AskTOM this year!

We’ll count them down from 10 to 1

10) Inserting values into a table with ‘&’

Viewed 80,000 times.

First asked in 2012, this actually is not a database issue but a client issue. Many client tools view ampersand as a substitution variable, and this they intercept the execution before the command is sent to the database. Full details here

9) What is a SID, how to change it, how to find out what it is

Viewed 95,000 times.

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.

CLI for Amazon RDS Performance Insights

Installing CLI on LINUX

1. install PIP

https://docs.aws.amazon.com/cli/latest/userguide/awscli-install-linux.html#awscli-install-linux-pip

curl -O https://bootstrap.pypa.io/get-pip.py
python get-pip.py --user

2. install AWS CLI

https://docs.aws.amazon.com/cli/latest/userguide/installing.html

pip install awscli --upgrade --user

3. configure

aws configure

For “aws configure” you will need

  • AWS Access Key ID:
  • AWS Secret Access Key:

Which you can get by going to the AWS console, going to IMS and creating access key.

Running example

Once “aws” is configured you can run the CLI like

Misdirection

A recent post on the ODC database forum prompted me to write a short note about a trap that catches everyone from time to time. The trap is following the obvious; and it’s a trap because it’s only previous experience that lets you decide what’s obvious and the similarity between what you’re looking and your previous experience may be purely coincidental.

The question on OTN (paraphrased) was as follows:

When I run the first query below Oracle doesn’t use the index on column AF and is slow, but when I run the second query the Oracle uses the index and it’s fast. So when the input starts with ‘\\’ the indexes are not used. What’s going on ?

Automatic sequences not being dropped

One of the nice new things in 12c was the concept of identity columns. In terms of the functionality they provide (an automatic number default) it is really no different from anything we’ve had for years in the database via sequences, but native support for the declarative syntax makes migration from other database platforms a lot easier.

Under the covers, identity columns are implemented as sequences. This makes a lot of sense – why invent a new piece of functionality when you can exploit something that already has been tried and tested exhaustively for 20 years? So when you create a table with an identity column, you’ll see the appearance of a system named sequence to support it.

The phantom tablespace

(Cueing my deep baritone Morpheus voice…) What if I told you that you can reference non-existent tablespaces in your DDL?

OK, it sounds like a gimmick but there is a real issue that I’ll get to shortly. But first the gimmick Smile

I’ve created a partitioned table called “T” (I’ll pause here for your applause at my incredible imagination skills for table naming Smile) and to show you the complete DDL, I’ll extract it using the familiar DBMS_METADATA package.