Top 60 Oracle Blogs

Recent comments

July 2019

Hi Tuyen, as this removes lot of files from the Oracle Home, many features will not work.

Hi Tuyen, as this removes lot of files from the Oracle Home, many features will not work. Only what requires only the basic binaries and procedures will work.


Over the last few days I’ve been tweeting little extracts from Practical Oracle 8i, and one of the tweets contained the following quote:

This lead to the question:

HIGH_VALUE (and other LONG columns) revisited

Just a quick post to ensure people don’t get caught out by a recent “trick” I saw on an AskTOM question for extracting the HIGH_VALUE out of it’s LONG storage in the dictionary to a more appropriate datatype. A reviewer (I’m sure in good faith) posted the following mechanism to extract the HIGH_VALUE

Sampling pg_stat_statements based on the active sessions and their associated queryid


Now that we have the ability to sample and record the active sessions and their associated queryid with the pg_active_session_history view (see this blog post), it would be interesting to have insights about the queries statistics at the time the sessions were active.

PostgreSQL provides the queries statistics with the pg_stat_statements view. We could query the pg_active_session_history and the pg_stat_statements views and join them on the queryid field, but the queries statistics would be:

strace -k (build with libunwind)

strace -k (built with libunwind)

prints the stack trace with the system calls

PostgreSQL is Open Source and you may think that it is not necessary to trace the system calls as we can read the source code. But even there, strace is a really nice tool for troubleshooting a running process.

Little disclaimer here: attaching strace to a running process may hang. Do not use it in production, except when this (small) risk is an acceptable way to troubleshoot a critical problem.

Getting rid of annoying, repetitive messages in /var/log/messages

The primary source of information regarding any change or issue on a linux system is the /var/log/messages file. I am often annoyed when a linux system is setup in such a way that certain messages are written to syslog with a high frequency swamping the messages file with information that is not important. The reason for my annoyance is that this makes it very hard to actually spot important information because you have to skip through a lot of lines before you find the important information, especially if you do not know for sure if there a message in the first place.

Please mind this blogpost is created on a Centos 7 server which uses rsyslog.

There are a couple of ways to manage this. The standard syslog way of managing this is the following, which can be found in /etc/rsyslog.conf:

DB links

A recent thread on the ODC SQL & PL/SQL forum raised the question of how data moves across a database link – is it row by row or array based (or other)? It’s a good question as it’s fairly common knowledge that distributed queries can be very much slower than the same query when executed on an equivalent set of local tables, so it’s worth having a little poke around to see if there’s anything in the way the data moves across the link that could be tweaked to improve performance.

Same dog, different leash – functions in SQL

Let’s start with this somewhat odd looking result. I have an inline function that returns a random number between 0 and 20, and I call that for each row in ALL_OBJECTS and then I extract just those rows for which the generated random number is 10. Seems simple enough….but why do I get results for which the value of the second column is most certainly not 10?

Power BI Reporting with Oracle Essbase

This last week, I presented a few sessions at ODTUG’s KSCOPE 2019 conference in Seattle.  One of these sessions was with my wonderful co-presenter and Oracle Application Cloud expert, Opal Alapat.  I connected with her when the call for papers opened and asked her if there would be interest in doing a session with Power BI connect to the Essbase in the Oracle Applications cloud, (OAC).  There was no desire to do a bake-off or competitive session, just a morbid curiosity about what options there were to connect the two and the outcome of building reports out of valuable data with such a powerful BI tool.

Oracle Refresh Group consistency with nested materialized views.

A Refresh Group can be used to refresh a list of materialized views and ensures that all data is read with transaction consistency. This post is about the meaning of consistency when one materialized view depends on the other one. That’s just a quick test to understand the behavior. Any comment is welcome (preferably on twitter — @FranckPachot)


The documentation mentions:

To preserve referential integrity and transactional (read) consistency among multiple materialized views, Oracle Database can refresh individual materialized views as part of a refresh group. After refreshing all of the materialized views in a refresh group, the data of all materialized views in the group correspond to the same transactionally consistent point in time.

Materialized View Concepts and Architecture