DBA

connor_mc_d's picture

Level up your audit trigger game

A weekend audit

Often if you see the words “weekend audit” in a non-technical sense, it means that awkward moment where you look at the state of your house/bedroom/bathroom/bookshelf/shed etc and say to yourself “Yup, it is time we had a weekend audit of all this crap” Smile

connor_mc_d's picture

DDL gets my goat

First things first. If you are not familiar with the term “gets my goat” then you can find a definition here Smile. But put simply, it means things that you find frustrating or bothersome.

What is getting my goat today is the time it takes to get the DDL for a database object using the DBMS_METADATA package. It always seem slow to me. Now I must admit, this is very much me exhibiting “never happy” syndrome, because before DBMS_METADATA came along, generating DDL from the database was an absolute pain in the proverbial. Your choices were either:

connor_mc_d's picture

Upgrading…Its Time!

Gough Whitlam was an Australian politician who rose to power in the 1970s with the campaign slogan “It’s Time!”. Politics aside, it loosely ran on the premise that not to have the occasional dramatic change ultimately leads to stagnation in social and economic progress.

428px-Gough_Whitlam_1973

connor_mc_d's picture

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.

connor_mc_d's picture

Database space usage in layman’s terms

It seems easy enough. An email pops through from your manager and he’s concerned about the space utilization in the database. Then comes the question:

“So what exactly is using up most of the space?”

For the sake of simplicity, lets assume that you already know that most of the space comes from a single database schema, so you connect to that schema and throw a query against USER_SEGMENTS. And that is when the frustration starts..

connor_mc_d's picture

MAX_STRING_SIZE – stretching the dictionary

This post cycles back too some other historical posts of mine related to max_string_size and the fact that the data dictionary uses LONG data type columns because of historical reasons related to backward compatibility. Most of us probably have existing databases that have gone through the standard upgrade through various versions of the Oracle Database, and as such, all of our existing database have a max_string_size of STANDARD. Thus to convert to the larger string size, we start by setting max_string_size to EXTENDED, run the appropriate scripts after shutting out database down and re-opening it in upgrade mode, setting the appropriate initialization parameters for the instance in our SPFILE. But what if you are creating a new database?

connor_mc_d's picture

Read only partitions

The ability for part of a table to be read-only and other parts of the same table to allow full DML is a cool feature in the Oracle Partitioning option.  Perhaps the most common example you will typically see for this is range-based partitioning on a date/timestamp column.  As data “ages”, setting older partitions to read-only can yield benefits such as:

  • moving the older partitions to cheaper, or write-once storage
  • guaranteeing that older data cannot be tampered with
  • shrinking backup times because read-only data only needs to be backed up once (or twice to be sure)

But if you try this in 18c, you might get a surprise:

connor_mc_d's picture

From 19.6 to 19.7 on Windows

I must say this Release Update (RU) was probably the smoothest I’ve ever done. Obviously you should always read the patch notes carefully before proceeding on your own systems, but for me, it was a simple exercise. I’m posting this just to cover a couple of things that the patch notes “assume” and don’t explicitly state.

  • Shutdown everything Oracle related. I just go to “Services” and look for anything with Oracle. Also shutdown the “Distributed Transaction Coordinator service”.

This next one is key … I’ve made this mistake so many times. Open a command prompt window as administrator. If you don’t, things will progress OK for a tiny bit and then OPatch is going to throw a wobbly.

I did both the 19.7 RU and the 19.7 OJVM with OPatch, and both went through without incident.

connor_mc_d's picture

The lunchtime nuisance…

We’ve all been there … You’re trying to save some changes to the database, and no matter what you try, your application just hangs. After some to-and-fro with the DBA, or perhaps you are the DBA, you stumble across that uncommitted transaction that “Joe from marketing” commenced at 11:55am.  You pop around to Joe’s desk, and there it is on screen….and Joe has popped out to lunch for an hour ….. grrrrr!

image

connor_mc_d's picture

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.

To prevent automated spam submissions leave this field empty.
Syndicate content