Infrastructure at your Service

All Posts By

Franck Pachot

Franck Pachot

Running SQL Server on the Oracle Free tier

By | Cloud, Oracle, SQL Server | 2 Comments

By Franck Pachot The Oracle Cloud is not only for Oracle Database. You can create a VM running Oracle Linux with full root access to it, even in the free tier: a free VM that will be always up, never expires, with full ssh connectivity to a sudoer user, where you are able to tunnel any port. Of course, there are some limits that I’ve detailed in a previous post. But that is sufficient to…

Read More
Franck Pachot

ROLLBACK TO SAVEPOINT;

By | Oracle | 2 Comments

By Franck Pachot . I love databases and, rather than trying to compare and rank them, I like to understand their difference. Sometimes, you make a mistake and encounter an error. Let’s take the following example: create table DEMO (n int); begin transaction; insert into DEMO values (0); select n “after insert” from DEMO; update DEMO set n=1/n; select n “after error” from DEMO; commit; select n “after commit” from DEMO; The “begin transaction” is…

Read More
Franck Pachot

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
Franck Pachot

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
Franck Pachot

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

By | Oracle | 2 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
Franck Pachot

ORACLE_HOME with symbolic link and postupgrade_fixups

By | Database Administration & Monitoring | One Comment

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
Franck Pachot

18c runInstaller -silent

By | Oracle | 2 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
Franck Pachot

TRANSPORT_CONNECT_TIMEOUT and RETRY_COUNT

By | Database Administration & Monitoring, Oracle | 4 Comments

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
Franck Pachot

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