Oracle

Autonomous Database

By Franck Pachot . Larry Ellison has announced Oracle 18c to be the Autonomous Database, the no-human labor, and self-driven, database. Here is a quick recap of what it is behind the marketing words. My opinion only.

“_suppress_identifiers_on_dupkey” – the SAP workaround for bad design

By Franck Pachot . In SQL, 'upsert' is a conditional insert or update: if the row is there, you update it, but if it is not there, you insert it. In Oracle, you should use a MERGE statement for that.

When deterministic function is not

By Franck Pachot . When you declare a function-based index, the function is deterministic, which means that calling it with same arguments will always return the same result. This is required because the indexed values, which are the result of(…)

Am I a DBA 3.0 or just an SQL*DBA?

By Franck Pachot . There are currently a lot of new buzz words and re-namings which suggest that our DBA role is changing, most of them escorted with a #cloud hashtag. Oracle Technology Network is now called Oracle Developer Community.

Be careful when putting the Oracle ADR on xfs, or better 4K sector format drives

Today, after we did a fresh setup of a Grid Infrastructure cluster (12.1.0.2.170814) we faced two issues reported in the alert.log of the ASM instances (in fact you would see the same for the alert logs of any instance in(…)

Active Data Guard services in Multitenant

By Franck Pachot . A database (or the CDB in multitenant) registers its name as the default service. When a standby database is on the same server, or same cluster, you have no problem because this database name is the(…)

Create constraints in your datawarehouse – why and how

By Franck Pachot . We still see some developers not declaring referential integrity constraints in datawarehouse databases because they think they don't need it (integrity of data has been validated by the ETL). Here is a small demo I did(…)

impdp content=metadata_only locks the stats

By Franck Pachot . With Oracle you can learn something every day. Today, preparing a migration to 12.2, I found all tables had locked statistics. I learned that it is the expected behavior since 10.2 when importing metadata_only including statistics,(…)

When PDB name conflicts with CDB name

By Franck Pachot . Going to multitenant architecture is not a big change. The administration things (DBA, monitoring, backups) connect to the CDB and the application things connect to the PDB. Without the multitenant option, it is still recommended to(…)

Postgres vs. Oracle access paths XI – Sample Scan

By Franck Pachot . I was going to end this series with the previous post because the last access path available in Postgres is a bit special: a Seq Scan that returns only a sample of(…)