Search

Top 60 Oracle Blogs

Recent comments

DBA

Nulls are not stored in indexes … most of the time

This question got posed on Twitter today

image

I answered briefly on Twitter, but I thought I should give it some “meat” with a blog post.

Firstly, lets explore the commonly understood mechanism where in a conventional index, nulls are not stored and hence you can have multiple index entries (so to speak) where the value is null.

Here is my simple table T with a standard (ascending) unique index, and we can see immediately that there is no limit to the number of null values, where by “null” I mean that all indexed columns are null, that you can have in the table.

Databases are slow right?

Man, it gets my goat when people start pontificating nonsense on Twitter.

Yeah, I know, I know, I shouldn’t get upset, because 99% of everything on social media is nonsense anyway, but it is when people roll out claims about database tech that I tend to get tetchy Smile

Today it’s that familiar old chestnut: “Constraints in the database make it slow“. And…this one even came with some numbers add some apparent weight!

18c versus 19c

I had someone say to me at an event recently: “We’re are going to upgrade to 18c, because 19c is new and is probably less stable”.

Let me sum up that sentiment simply: It’s Wrong Smile

Now, don’t get me wrong. I am not claiming that every Oracle release is perfect, contains zero bugs, never has a regression, will mow your lawn, take your kids to school, clean your house and sort out all the climate change issues in the world.

SQLPlus goodies with linesize

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

image

Has my scheduler been turned off?

When the scheduler came into existence in Oracle 10g, there was a cool API call that could be used to temporarily turn the entire scheduler off.  That command was:


SQL> exec dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED','TRUE')

PL/SQL procedure successfully completed.

However, a quick check of the documentation in later releases, no longer makes any reference to this attribute:

image

Unique all the things … including your pluggables

A quick tip just in time for Christmas Smile

I logged on to my database this morning, and things just didn’t look right. In fact, they looked down right alarming. All my objects were gone, my user account had the wrong password..It was almost as if I was connecting to a totally different database!

That’s because I was! Smile Here is how it happened:

Listener log data mining with SQL

If you take a look at the log files created by the listener, there is obviously a nice wealth of information in there. We get service updates, connections etc, all of which might be useful particularly in terms of auditing security

However, it also is in a fairly loose text format, which means ideally I’d like to utilise the power of SQL to mine the data.

Plugzilla!

Cloning a pluggable database takes time, and for environments where you’d like to use clones as part of unit testing, or other elements of Agile development, it would be nice to be able to bring a clone into operation in the smallest time possible. One mechanism for that is sparse storage clones aka snapshot copy, but depending on your database version and your storage infrastructure, you might hit some limitations.

Enter …. Plugzilla! This PL/SQL package allows you clone pluggable databases extremely quickly by having pluggable database pre-cloned in advance.

Example

Lets say you have a development pluggable database called PDB1. You want to let developers take clones of this as quickly and as often as they like and at various stages in its life cycle. Here is how we might do it with plugzilla.

SMON_SCN_TIME and ORA-8161? Digging deeper

In the recent versions of the Oracle database, we’ve had the ability to convert between a System Change Number (SCN) and the approximate time to which that SCN pertains. These functions are unsurprisingly called SCN_TO_TIMESTAMP and TIMESTAMP_TO_SCN. The only potential misnomer here is that even though the functions are called “timestamp” and return a datatype of timestamp, on most platforms you are going to notice that the granularity doesn’t run down into fractions of seconds


SQL> select scn_to_timestamp(14816563713652) from dual;

SCN_TO_TIMESTAMP(14816563713652)
---------------------------------------------------------
08-JUN-19 02.30.59.000000000 AM

This all looks great until you start poking around too far into the past, and you end up in territory like this:

Hibernate for Oracle DBAs

Warning: any smart developer may feel sick when reading this ;)

I am not a developer, but I like to discuss with developers: share my side of the IT (the database that we want rock stable and durable) and listen to their side (the application that they want easy to maintain and evolve). And, as I like to understand what I’m talking about, I often need to test some snippets.

Many DBAs complain about Hibernate when they come upon the queries generated by a wrong mapping. They think it was designed to be bad (who would do that?). And they are convinced that JDBC and SQL are sufficient to build applications. Actually, many DBAs I have seen are persuaded that they understand everything about coding because they have written some ugly PERL scripts to automate their job. And that anything going beyond has the only goal to break the database.