Infrastructure at your Service

All posts by Oracle Team

Oracle Team



Oracle Team

COMMIT

By | Oracle | 4 Comments

By Franck Pachot . COMMIT is the SQL statement that ends a transaction, with two goals: persistence (changes are durable) and sharing (changes are visible to others). That’s a weird title and introduction for the 499th blog post I write on the dbi-services blog. 499 posts in nearly 5 years- roughly two blog posts per week. This activity was mainly motivated by the will to persist and share what I learn every day.  

 
Read More
Oracle Team

The size of Oracle Home: from 9GB to 600MB – What about SQL Server?

By | Database Administration & Monitoring | No Comments

Holidays is over and let’s start working with a funny write-up. I was reading interesting stuff done by my colleagues Franck Pachot and Daniel Westermann about reducing the size of Oracle / PostgreSQL binaries at the minimum in the context of a Docker images. I was curious what can be done on SQL Server side on Linux environment but just keep in mind that some operations performed in this post are probably not supported in…

 
Read More
Oracle Team

A tribute to Natural Join

By | Oracle | No Comments

By Franck Pachot . I know that lot of people are against the ANSI join syntax in Oracle. And this goes beyond the limits when talking about NATURAL JOIN. But I like them and use them quite often. Why is Natural Join bad? Natural join is bad because it relies on column names, and, at the time of writing the query, you don’t know which columns will be added or removed later. Here is an…

 
Read More
Oracle Team

The size of Oracle Home: from 9GB to 600MB

By | Oracle | No Comments

By Franck Pachot . This is research only and totally unsupported. When building docker images to run Oracle Database in a container, we try to get the smallest image possible. One way is to remove some subdirectories that we know will not be used. For example, the patch history is not used anymore once we have the required version. The dbca templates can be removed as soon as we have created the database… In this…

 
Read More
Oracle Team

ORACLE_HOME with symbolic link and postupgrade_fixups

By | Database Administration & Monitoring | No Comments

By Franck Pachot . Here is a quick post you may google into if you got the following error when running postupgrade_fixups.sql after an upgrade: ERROR – Cannot open the preupgrade_messages.properties file from the directory object preupgrade_dir DECLARE * ERROR at line 1: ORA-29283: invalid file operation ORA-06512: at “SYS.DBMS_PREUP”, line 3300 ORA-06512: at “SYS.UTL_FILE”, line 536 ORA-29283: invalid file operation ORA-06512: at “SYS.UTL_FILE”, line 41 ORA-06512: at “SYS.UTL_FILE”, line 478 ORA-06512: at “SYS.DBMS_PREUP”, line…

 
Read More
Oracle Team

18c runInstaller -silent

By | Oracle | No Comments

By Franck Pachot . You find two different ‘runInstaller’ under an Oracle Home. The old one, the Oracle Universal Installer, in $ORACLE_HOME/oui/bin. And the new one, in $ORACLE_HOME directly. They have the same name but are completely different. The old one was used to install an Oracle Home from the installation media. But in 18c you don’t use it. It has been used by Oracle to build the Oracle Home image. Then you download and…

 
Read More
Oracle Team

TRANSPORT_CONNECT_TIMEOUT and RETRY_COUNT

By | Database Administration & Monitoring, Oracle | One Comment

By Franck Pachot . When you have a Data Guard configuration, you want the application to connect to the right server, where the primary is, without taking too much time. The default TCP timeout is 1 minute which is too long. When you don’t want to configure a virtual IP address (VIP) you can simply list all the addresses in the client connection string. But then you need to reduce the timeout. A short duration…

 
Read More
Oracle Team

ATP vs ADW – the Autonomous Database lockdown profiles

By | Cloud, Oracle | No Comments

By Franck Pachot . The Oracle database has always distinguished two types of workloads: transactional (OLTP) and datawarehouse (VLDB, DWH, DSS, BI, analytics). There is the same idea in the managed Oracle Cloud with two autonomous database services. To show how this is old, here is how they were defined in the Oracle7 Tuning Book: The definition has not changed a lot. But the technology behind DSS/DWH has improved. Now, with In-Memory Column Store, Smart…

 
Read More
Oracle Team

MERGE JOIN CARTESIAN: a join method or a join type?

By | Oracle | No Comments

By Franck Pachot . I’ll present about join methods at POUG and DOAG. I’ll show how the different join methods work in order to better understand them. The idea is to show Nested Loops, Hash Join, Sort Merge Join, Merge Join Cartesian on the same query. I’ll run a simple join between DEPT and EMP with the USE_NL, USE_HASH, USE_MERGE and USE_MERGE_CARTESIAN hints. I’ll show the execution plan, with SQL Monitoring in text mode. And…

 
Read More