don’t look to close, it might change the results

We all know, if we try to look to close on some details, the results we get may change from the original observation.
This is also sometimes true for Oracle Trace events:
During analyses of a query with XMLTYPE which fails with ORA-12850 (in a 4 node RAC DB), Oracle Support asked for some trace files. the query itself is something like
FROM gv$lock l
JOIN gv$session s
... ) )

and (more...)

oratop with TFA

We are facing an issue where Oracles Trace File Analyzer creates high load on some servers.
During the discussion with Oracle Support I installed the latest version of TFA which is right now.
With this version, oratop - which oracle calls
oratop is a utility similar to the unix OS utility top which gives a overview of database performance and can be used in combination with top to get a (more...)

manage ORACLE_HOMEs and patches in a centralised way

Managing many ORACLE_HOMEs in a complex environment can be tricky, especially when questions about their patches & bugs or comparisons should be done.

It's required to understand the different types of patches available. This can be (according to the documentation & my interpretation). Especially the differences between SYSTEM PATCH (in our case only Bundle Patches), COMPOSITE and SINGLETON patches, and BUGs - of course.

A way to handle all the information is to (more...)

I went to a conference – and all I can post about is this picture

I was on DOAG and UKOUG conferences this year and hold some presentations there.
Of course I attended even more presentations and learned a lot of new things.
But I did not blog about it for some good reasons:
The presentations I attended are done already. Which means the presenters did a lot of research and put it all into great presentations and abstracts - so why should I create a weak copy of their (more...)

Backup stalled due to ASM rebalance stuck

I hit an issue where a full backup took much longer than normal.
In this case there was no alarm yet as no threshold was reached. But I worked on the DB for some other reason and out of a habit I most often start a ASH viewer whenever I work on a system - even if I only check data, it's worth to have an eye on the system.
In this case I saw (more...)

prelink makes instance unusable

I spent a recent night hunting an issue which was non trivial to detect.
On a 4 node cluster 2 nodes got Linux patches and were rebooted. This was done after 10pm for minimal business impact. (The application is written by Oracle - so it's not fully TAF aware).
The DBs started fine, all services were available.
At 3:50 in the morning I got a call some processes show strange errors.
As I connected to (more...)

Oracle Bundle Patches – release timing

I have to invest much of my time (at work) into Oracle and managing of patches, merge patches, patch requests and similar things.
At this task I identified an interesting (but probably worthless) information:
It seems a Bundle Patch (for Version 12.1) is defined approximately (at least) 40 days before it's released.
At least the BP follows this rule.

I have requested a lot of one-off and merge patches (more...)

digging into mrskew internals

I'm spending some time working with Method-Rs trace file analyzer mrskew.
As the requirements are beyond simple analyses (here an example about the uncommon things to do) I'm consulting the documentation and all available information in detail. After many readings I started to take this part of the documentation serious:

Wherever mrskew requires an expression (such as in --group and --where option arguments), you may use any valid Perl expression consisting of:

wrong permission on shm kills JAVA_JIT

We found a lot of trace files from several DBs on one of our DB Servers.
They look like:

*********_ora_26444.trc or *********_m000_5598.trc
*** 2017-09-08 15:11:29.181
*** SESSION ID:(632.5995) 2017-09-08 15:11:29.181
*** CLIENT ID:(SYSADMIN) 2017-09-08 15:11:29.181
*** SERVICE NAME:(****_****) 2017-09-08 15:11:29.181
*** MODULE NAME:(*::***:******.****.***.****.******) 2017-09-08 15:11:29.181
*** ACTION NAME:(/) 2017-09-08 15:11:29.181

Map_Length = 4096
Map_Protection = 7
Flags = 1
File_Offset = (more...)

SecureFile LOB – managing free blocks

This blog post continues with all the objects and settings from SecureFile LOB - the empty tableSecureFile LOB - the 1st insert SecureFile LOB - the 1st update and SecureFile LOB - more inserts.

It starts with some inserts:
insert into berx.TEST_BLOBS
select 4, comments, file_content
from berx.source_blobs
where id=4;


insert into berx.TEST_BLOBS
select 5, comments, file_content
from berx.source_blobs
where id=1;

insert into berx.TEST_BLOBS
select 6, comments, file_content
from berx. (more...)

SecureFile LOB – more inserts

This blog post continues with all the objects and settings from SecureFile LOB - the empty tableSecureFile LOB - the 1st insert and SecureFile LOB - the 1st update

The next insert is a small LOB with 2 bytes again:
insert into berx.TEST_BLOBS
select 2, comments, file_content
from berx.source_blobs
where id=2;
--2 byte
The table row points to a new block in the LOB segment:
LOBID = 00000001000099BF67D1

SecureFile LOB – the 1st update

This blog post continues with all the objects and settings from SecureFile LOB - the empty table and SecureFile LOB - the 1st insert

the statement is quite easy:
update berx.TEST_BLOBS
set (comments, file_content) =
(select comments, file_content
from berx.source_blobs
where id=4)

The new CLOB is about 11.1 kb in size - so it will require more than 1 block.

Fortunately the number of extents did not change.

New information needs to be gathered (more...)

SecureFile LOB – move online in 12.2

Today we had a "new features in 12.2" workshop. Thank you Mathias Zarick for this great event.
One of the new features was Online Table Move. But the doc was quite unspecific if this also includes LOBs:
Nonpartitioned tables can be moved as an online operation without blocking any concurrent DML operations. A table move operation now also supports automatic index maintenance as part of the move.
Data maintenance on nonpartitioned tables does not (more...)

SecureFile LOB – the 1st insert

This blog post continues with all the objects and settings from SecureFile LOB - the empty table.

The insert of one row with a small (1byte) LOB increases the complexity.
berx.source_blobs contains some BLOBs of different size.
insert into berx.TEST_BLOBS
select 1, comments, file_content
from berx.source_blobs
where id=1; --1 byte


we have a new extent now

col owner for A10  
col segment_name for A30
select owner, segment_name, file_id, block_id, blocks
from (more...)

SecureFile LOB – spfile parameters

Here is a list of all the spfile parameters I found (and guess are) related to SecureFile LOBs.

This Post will be updated with additional information whenever I collect them.


Default value




DBFS Link allows implicit fetch on modify - only on SecureFiles



Enable securefile flashback optimization



undocumented parameter for internal use only


0 12. (more...)

SecureFile LOB – the empty table

I have to dig into structures of SecureFile LOBs right now. Unfortunately I do not find a lot of resources available, so I have to investigate at my own.
This post is to document my findings. There is a lot of guesswork and interpretation here, so be careful if you use or refer to any information here.

The whole testcase is on BundlePatch EE, Linux, 2 node RAC.

I start (more...)

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...)