The Limits of Data Redaction

Data Security is becoming more and more important nowadays.
In fact it was always important, just as the expected problems increase (by count or value) management seems to be more aware now. Due to many discussions I started to have a look at DBMS_REDACT - which is an implementation to show only those data to users they are allowed to use.
One of my first places to go was Tim Halls Data Redaction (DBMS_REDACT) in (more...)

The Slow Tires

Once there was a man with a car. On this car he had 4 tires.
As the car is a modern one it has a nice board computer which collects many measurements. One is the rotation per minute of the tires.
One day it took more time for the man to get home than usually. So he decided to check his car's computer for any values which could lead to that delay.
After some minutes (more...)

Hints, up and down

Last week was AOUG conference 2017. There I attended Lothar Flatz' Any Hint, anywhere. There Lothar mentioned it's not required to have hints in the first / topmost SELECT. Even the sentence itself was clear for me, the consequences and possibilities were not at that time.
During the presentation and some discussion with Lothar afterwards, some possibilities were shown.


drop table T1;
create table T1 as
select rownum as rn, 'A' as const (more...)

Method R Profiler 7.0 available

I'm seldom doing product announcements on my blog. So there must be a reason why I do not follow this rule.

Method-R recently released a new version of it's powerful SQL-Trace profiler.
Of course there are many improvements on the profiler engine itself, but as the prodict was very stable already they will only help in rare edge cases, most people would not benefit dramatically.

But there is a new featuere which brings a lot (more...)

search for the missing ORA-04068

Oracle Active Dataguard is a great software for offloading read only activities from the primary nodes to nodes which is not utilized anyhow (at least during non-disaster times).
A very good example for doing so is regular extraction of data for processing in a data warehouse. That's also the case which led to this post.

The setting is just slightly more complex: both, primary and standby site are RACs - of course when you plan (more...)

Can I flush the shared pool of all RAC Instances in an Active DataGuard?

That's a good question.

And the answer is simple: Yes, but NO!

Well, ok; that's not very helpful. But whenever is anything simple in modern IT?

So I'll show why Yes, it's possible:

first as SYS create a dummy user without special permissions and a function to drop the shared pool:

create user berx identified by xxx;
grant connect to berx;
grant select on v_$instance to berx;

create or replace function sys.flush_shared_pool_bx (more...)

force connections to use SERVICE_NAME

During the setup of a project I had a small observation: The Oracle Database is running on a 4 node RAC, but we (DBAs) wanted to provide only 2 instances to the application team.

So we created a dedicated service with Preferred instances: inst1,inst2 and Available instances: inst3.inst4

But after some minutes I saw sessions running on all 4 instances. Those sessions used the Service SYS$USERS - so they managed to "guess" the SID and created a connetionstring (more...)

interconnect fragmentation kills the cluster

On a particular Oracle 2 node cluster (12.1) we faced random instances failing. Servicerequests at Oracle were open with limited result, as it was quite random and we could not link it to any trigger.
As it looked somehow like a communication problem between the 2 nodes, network team has checked the switches involved - without any outcome.
Even crashing instances were a problem already, it get worse one day when one node rebooted (more...)

cost vs. gets

Last week I hit an interesting performance issue:
A Table (T) has 2 index (of interest). One (IX_1) is only on column S, the other (IX_2) on (C, S).
The optimizer calculates the plan with IX_1 more expensive than the plan with IX_2 - as this should be more selective.
But the gets for the plan with IX_1 were less than those the optimizer preferred.

Here the information about the statement, index and plans.
As (more...)

no peek in PL/SQL

Connor McDonald wrote a blog about differences between bind peeking and SYS_CONTEXT in SQL queries. This even led to an proposal in Database Ideas: CBO should peek at SYS_CONTEXT values just like bind values (feel free to vote and comment, if you like it)
As I have a friend who really loves SYS_CONTEXT; I showed him the blog.

In the following discussion he stated (more/less
I should not have this problem at all, as (more...)

OTN Appreciation Day : ONLINE

This post is inspired by Tim Hall.

Thinking about "what is my single favourite feature of Oracle", when all the products of Oracle (or at least all I know about) can be covered sounds stupid or impossible. There are far to many features which could be interesting, worth or made my life easier over my years in IT.
So I decided to pick something more generic.

The "feature" I like most is ONLINE

With (more...)

Oracle DIRECTORY access on OS layer

Oracle DIRECTORY objects are very nice to handle access for external tables or other procedures.
It just can be challenging to enable proper access to those files for other users than the database user (I call it oracle here).

If there are any concerns to allow any access to the databases host (except for DBAs and OS admins) it get's tricky fast.

One possibility is to use a NFS mount and make the directory available (more...)

ORA-08176 with DB-link, create table and isolation level SERIALIZABLE

Today I had some fun identifying how a ORA-08176 can happen.
It started with a ticket similar to "we get an ORA-08176 during a select, please fix the database."
After some questions it refined to "we do a CREATE TABLE x AS SELECT in one session, and a SELECT * FROM X@db_link throws the ORA-08176.

That's enough for me to do a test-case, but I needed to change the isolation level to produce the (more...)

if you write SQL, be specific!

Today  I got a ticket from a developer where he claimed Oracle has a bug somewhere. The reason for this claim was a statement similar to
FROM   table1
WHERE  tab1col1 IN
       (      SELECT 
              FROM   table2)

This query returned rows.

But when he run the inner query on it's own, he received
ORA-00904: "TAB1COL1": invalid identifier
00904. 00000 -  "%s: invalid (more...)

access to CHM raw data – without manipulating the -MGMTDB

In Version 12.1 Oracle introduced the Grid Infrastructure Management Repository (GIMR) called ‑MGMTDB.
This self managed pluggable database is a required component of Grid Infrastructure and should never require direct interactions. (there can be some interactions when you want to migrate to different diskgroups, but also those activities are covered within wrapper scripts provided by Oracle).
Every interaction with the data stored in this DB is done through applications - oclumon might be (more...)

Instance parameters derived from cpu_count –

About 6 years ago I wanted to know which instance parameters are derived from cpu_count. So it tested a DB - in that version there 21 parameters changed based on the value of cpu_count.
Some DB versions passed by so I decided it's time for another check. This time it's without any PSUs/patches. The machine is the same class as previous, so it took some time.

the (more...)

handling disks for ASM – when DB, Linux and Storage admins work together

A proper management of ASM Disks can be a complicated task.

On DOAG2015 I discussed with Martin Bach about the concept in my current company, where we implemented a setting which is consistent, robust and enables Storage, Linux and DB admins to work together easily.

As we started to think about ASM when 10.1 was out we tried to evaluate our possibility. asmlib was discarded quite early as it only increased complexity without additional (more...)


In Oracle database the vie DBA_FEATURE_USAGE_STATISTICS is given to
display information about database feature usage statistics.
That's all I can find in The (12.1) Docu.

Just in case anyone asks - DBA_FEATURE_USAGE_STATISTICS is not mentioned in the license guide at all:

Still for me it's interesting what's behind the view, and how it's content is populated.

So I started to analyze it's anatomy.
Starting with the view definition (slightly rewritten for better readability):
SELECT  (more...)

12c datapatch can fail with manual out of place patching

datapatch can fail if doing manual out of place patching, when the new ORACLE_HOME is not cloned from previous one but a fresh installation. The problem is a one-of patch in the old ORACLE_HOME, which is included in something else (like a PSU) in the new ORACLE_HOME.
In my specific situation the old O_H had PSU + some one-off + Patch:21355879.
This Patch is not needed anymore in my new O_H (more...)

12c datapatch – take care of parallel patching

datapatch is a nice new feature in recent Oracle database installations. It helps to ensure the databases objects match the binaries after any kind of patching and so avoid situations which can be an operational and support nightmare - and very hard to identify.


Unfortunately it has some drawbacks as well.
One of those I hit recently when running datapatch on 2 instances which uses the same ORACLE_HOME.
At some time in it's progress (more...)