Video : NTH_VALUE : Problem Solving using Analytic Functions

| May 18, 2020

Today’s video is a demonstration of the NTH_VALUE analytic function.

This is based on this article.

You might find these useful also.

The star of today’s video is Connor McDonald. This is his second visit to the channel. I’m sure most of you must know Connor from his blog and AskTom. He also has a YouTube channel here.


How To Resize the Online Redo Log files

| Apr 16, 2020

Today we are going to work on the steps used for Resize the online Redo Logfiles.  Let’s follow the steps:- Single Instance:

1. First, see the size of the current logs:

sqlplus /nolog
SQL> connect / as sysdba

SQL> select group#, bytes, status from v$log;
———- ———- —————-
1 1048576 INACTIVE
2 1048576 CURRENT
3 1048576 INACTIVE

2. Logs are 1MB from above, let’s size them to 10MB. Retrieve all the (more...)

How to clone Oracle Binaries

| Apr 16, 2020

Today we are going to work on the steps which are used to clone Oracle Binaries.  Sometimes we install Oracle software by using a utility such as tar to copy an existing installation of the Oracle binaries to a different server.

Installing Oracle with an existing copy of the binaries is a two-part process

  1. Copy the binaries
  2. Attach the Oracle home

Step 1. Copy the Binaries You can use any OS copy utility to (more...)

How to set IDLE TIME in Oracle Database

| Apr 16, 2020

Today we are going to explore the steps which we use for set IDLE TIME in Oracle Database.  Let’s follow the steps.

Step1:- connect as sysdba user

SQL> conn /as sysdba
SQL> sho parameter resource_limit
———————————— ———– ——————————
resource_limit boolean FALSE

Step2:- Change the resource parameter to true

SQL> alter system set resource_limit=TRUE scope=both;
System altered.
SQL> select * from dba_profiles where profile='DEFAULT' and resource_name in (‘IDLE_TIME’,’CONNECT_TIME’);


How to configure Shared server mode

| Apr 16, 2020

Today we are going to explore the steps to use for configure Shared server mode. Let’s follow the steps. We need to set the init parameters

SQL>alter system set shared_servers=5;
SQL>alter system set max_dispatchers=10 scope=spfile;;
SQL>alter system set shared_server_sessions=5 scope=spfile;;
SQL>alter system set circuits=300 scope=spfile;;
SQL>alter system set dispatchers="(protocol=TCP)(dispatchers=3)(connections=100)" scope=spfile;

Now client tnsfile should be like


Bounce the Database To confirm shared server connection use below (more...)

RU Apr 2020 installed

| Apr 15, 2020

RU Update 19.7 done.

Environment : Exadata, X7-2, 4 node, RAC Cluster


opatch lspatches

30805684;OJVM RELEASE UPDATE: (30805684)

30869156;Database Release Update : (30869156)

29585399;OCW RELEASE UPDATE (29585399)


No error, works perfect. 👍


So we can go on to test Upgrades with „autoupgrade“ for the RAC DB’s …..


Generating Prometheus Metrics directly from your Oracle Database

| Mar 2, 2020
Prometheus ( is getting more and more common as a Monitoring Solution, many products are offering out-of-box Prometheus formatted metrics (e.g WildFly, Spring Boot and so on). Even Oracle is providing for the Oracle WebLogic Server a weblogic-monitoring-exporter, which is generating Prometheus formatted metrics (see and checkout my previous blog post "WebLogic Monitoring with Prometheus and Grafana"

Many people are using Oracle Enterprise Manager (more...)

Oracle Database client libraries for Java now on Maven Central

| Feb 27, 2020
Oracle has published its Oracle Database JDBC client libraries on Maven Central. From now on you can find Oracle Database related jar files under the group id. You will find all libraries from version (e.g. ojdbc6) to 19.3.0 (e.g. ojdbc10). Going forward, Oracle will use Maven Central as one of the primary distribution … Continue reading "Oracle Database client libraries for Java now on Maven Central"

5 ways to get an Oracle Database

| Jan 27, 2020
With great technology comes great possibility

Manual upgrade to Oracle 19c (CDB/PDB)

| Dec 20, 2019


manually to 19c …. 

Actually it is very cool to do everything with so called „auto tools“. If you prefer to do the Upgrade to 19c manually and step-by-step then you can follow my article and have fun otherwise skip this blog article

Before you start you need to read a lot Doc-ID’s from Oracle Support

My list is not complete but here are very important Doc-ID’s for the Upgrade

Pre Activities for (more...)

Machine Learning and Spatial for FREE in the Oracle Database

| Dec 6, 2019
Machine Learning and Spatial for FREE in the Oracle Database

Last week at UKOUG Techfest19 I spoke a lot about Machine Learning both with Oracle Analytics Cloud and more in depth in the Database with Oracle Machine Learning together with Charlie Berger, Oracle Senior Director of Product Management.

Machine Learning and Spatial for FREE in the Oracle Database

As mentioned several times in my previous blog posts, Oracle Analytics Cloud provides a set of tools helping Data Analysts start their path to Data Science. If, on the other hand, we're dealing with experienced Data (more...)

Oracle RU Update Assistant

| Sep 16, 2019

It is sometimes difficult to find the right Metalink note and then the correct RU update.

Now there is the interactive Oracle Assistant DOC ID 2118136.2 which is very helpful and offers quick help.

Just try it out and be amazed. It is very helpful


impdp with parameter „cluster and parallel“ for a RAC Cluster

| Jul 12, 2019

For using the „parallel“ parameter during an import (impdp) on a Oracle RAC Cluster you need to prepare your environment.

The „parallel“ parameter works correctly when you do the following:

– mount point were the export dump resides must be available on ALL cluster members

– create a Service on the database for the impdp job

srvctl add service -s impdp_service -d xdb1 -pdb xpdb1 -preferred xdb11,xdb12 -available xdb13

srvctl start (more...)

DBMS_CLOUD Package – A Reference Guide

| Jul 12, 2018

The Appendix A of the Using Oracle Autonomous Data Warehouse Cloud guide describes the DBMS_CLOUD package. Unfortunately, it documents only a subset of the subroutines. And, for some of them, the description could also be enhanced. Therefore, while I was testing all the subroutines the DBMS_CLOUD package provides, I took a number of notes. By the end of my tests, I got what I can call my personal reference guide to the package. Since it (more...)

Beware of loopback MTU size with RAC on Oracle Linux 7

| May 12, 2018

I recently had to troubleshoot a hung instance in a 2 node RAC system. 4 months earlier, the system was reinstalled in a rolling fashion due to the requirement of Linux Upgrade from Oracle Linux 5 to Oracle Linux 7. This was required because of lack of certification for a storage migration to an AllFlash Storage. The system has been stable when running with Oracle Linux 5 for several years. Around 4 months after the (more...)

Patch Recommendations for Oracle Database 12cR1 and Cloud Control 13cR2

| May 11, 2018

During my consulting engagements I see a lot of systems and many bugs. Most of the time, there is already a patch available to avoid the bug. I have collected all the recommended patches for the Oracle Database (SE2 and EE) and Oracle Enterprise Manager 13cR2. This should help to avoid most critical known issues. Versions and 18c will be added later this year.

Patch Recommendations

BAAS: Battle Against Archiver Stuck – ALTERNATE Archive Location

| Apr 27, 2018

If you are managing volatile systems, you might have encountered Archiver-Stuck in the past.

0RA-00257:archiver error, connect internal only until freed
ORA-16014:log 2 sequence# 1789 not archived, no available destinations

If this is the case, you might be interested in a rarely used feature called “ALTERNATE Archive Locations”. This is a second independent archive log destination, that is only used in case the primary destination is failing (full). In that case, the dest_2 is automatically (more...)

Modifications to hidden parameters between and

| Apr 26, 2018

I recently troubleshooted an issue with RMAN catalog database using seperate schemas for each target database and more than 300 schemas present. Catalog Database was on and suffering from severe mutex contention because of huge version counts for RMAN catalog SQL statements. I then realized that the default number of max child cursors was increased from 1024 to 8192 in This made me check what other parameters (more...)

Oracle 18c – SQLPlus cute Features

| Mar 14, 2018
As I got my fingers on the latest Oracle 18c Release, I had the chance to test some cute new features within SQLPlus, which are really helpful.


The new option WINDOW for SET LINESIZE automatically adjust your linesize to your current window size.

As you can see in the below screenshot, the first select was executed without the linesize option, so you will have the usual line break in your result set. (more...)


| Mar 9, 2018
2 years ago, I have published an article series about the undocumented feature "ALTER USER RENAME":

As I was getting access to an Oracle 18c Database, I was trying directly, if the ALTER USER RENAME still works (more...)