BTB – Aggregation

It is time for aggregating data, using the GROUP BY keyword.

Hint: I will be using the emp/dept tables used by Oracle for demo in all demonstrations. I recommend Live SQL for playing, it is a great place to test SQL and learn from others. You can also sign up for a database for free (for a short time) on Oracle’s cloud. I recommend beginning with Live SQL and moving to paid services when you are (more...)

Back to basics

Let’s start with baby steps

We’re all very excited to read a new blog with a neat trick or a cool and complex feature or even a very useful undocumented function. However that is preaching to the choir. We do that and then we get together and complain that there is no new fresh talent in the database business. Well, it may be a bit hard to get into it if all we do are (more...)

Getting t1001 in interactive grid PK

Have you been plagued with the issue of getting values like t1001 show up in columns you marked as primary key in your interactive grid? In case you do not feel it is a good interface for your end users you may have found yourself spending quality time with Google looking for clues. I have had a couple of people asking me about this in just the last week. Googling turns up very little and mostly (more...)

Quicktip: Don’t grant access to a synonym

I’ve seen this in a few places lately. When I ask the database gays there about it, they think this DDL would never work or “it does nothing but does not return an error”.

Here is an example, All of the following is performed by a DBA user. We create a table, let’s call it TB. It is owned by user A. User B is granted select privilege to A.TB. A synonym is created for user (more...)

When views causes havoc

Views are great. They simplify design, makes code look more elegant and hides complexity. They also enables reuse by putting complex code in just one place instead of in every accessing piece of code.

But…

There once was a large project that has been churning away for a long time. The performance of having more than one user on the system was horrendous. But it was chalked up to misconfiguration.

I am asked to spend (more...)

Quick note on MT and applications II

Talking about Multi Tenant and applications, one has to talk about the different options for sharing. It is one of the great features of multi tenant.

It is a way to let one PDB – the application root –  have data that can be used from other PDBs.

There are three kinds that allow you to get different features of the sharing.

  1. Metadata
  2. Data
  3. Extended Data

Lets review each kind and why and how you’d use (more...)

Quick note on MT and applications

Are you using multi tenant with multiple containers and you’re not familiar with application containers?

It allows you to set up PDBs that belong to a CDB-like PDB that “owns” them. With that you can install applications (database objects) in the container, and then decide into which PDB to install, upgrade or patch that particular application,

So you could have an application that is a set of schemas with objects that belong together in a logical (more...)

Does recreating a view remove the privileges?

Short answer: No

This blog post is pretty much just to document what seems to have been hard to find online.

At work there was a big rework of a liquibase project due to the perception that “create or replace view” on an existing view would make the granted privileges be dropped. That was of course not what happened.

The issue was that using liquibase the new version of the view was installed. Then the (more...)

Your very own development environment

Everyone should have their own local development environment. No matter if you write Java and that just means IDE and JDG, or if you work with databases. Not having it reduces your learning a lot. I have yet to meet a really sharp database expert who does not have a local personal environment. It also goes the other way around, you can usually guess if a person has it or not.

Yes, you can get (more...)

New Year, New You

Well maybe not so much a new you. More like getting your personal “play” environments refreshed.

If you are anything like me, you want the latest and greatest in the setup where you play with great tech.

I have always had database, ORDS, APEX, SQLPLUS and so on installed in a VirtualBox VM. It has worked great and while I was hesitant to jump on the Docker train it is clear that not taking advantage (more...)