Blog
The Café Database Blog
UN World Innovation Day Hackaton – Projects and teams
On May 12, a five-day Hackathon was held on the occasion of the World Innovation day of the United Nations. I had the honor of mentoring the participating teams. During this time they had to come up with an app that would provide an improvement for the world. Various...
UN World Innovation Day Hackaton – Mentor
On May 9 I received an email from the organization of the #WorldInnovationDay Hack 2023 confirming that they had appointed me as a mentor for the Hackathon to be held from May 12 to 15. I mean, that same week.
Indexing columns with non pure functions
When you filter a column using a function, the optimizer does not use the indexes on that particular column because the function "transforms" the values and makes the index non useful to get the proper ROWIDs. To get around that hurdle, Oracle has function-based...
Unordered sorts
For the result of an SQL query to be ordered, the only valid clause is ORDER BY. Oracle only guarantees the ordered return of rows when ORDER BY is set as the sort criteria. I have repeated these two phrases like a mantra thousands of times. Any other way to get the...
The 7 most common errors in Oracle19c GoldenGate
When you first start a replication deployment with Oracle GoldenGate, it's easy that you miss one or several steps that lead you so some errors. Sometimes, the error message in GoldenGate is not so descriptive, or the implications within are not so clear, so here I...
SQL command generator for cleaning up old partitions.
As every year, it's time to clean up the databases Marie Kondo style. Database administrators have to historicize, compress or delete old partitions that are no longer needed. Obviously, partitioned tables (and their local indexes) are the best place to delete...
Get a daily backup report.
Let's talk about physical backups and how to get a report based on your own "safety criteria" about the successfull backups needed to ensure a safe recovery scenario. In many places, they get reports when backups end successfully, or sometimes only when backup fails,...
How to calculate the growth estimation of all schema objects.
What’s the growth trend of an entire tablespace? or an entire schema? Well, it depens on the segments behaviour upon time. Tables, indexes,… yes, they behave different, but Oracle provides some tools you may use to launch a “growth estimation” and surprise your boss or managers with your predictions.
How to defrag the entire contents in a tablespace
It happens. Sometimes segments grow up and fill the entire tablespace. Maybe you want to shrink the tablespace and there is something stored at the end of the file.
Yes, this is what you may do: move all segments to a new empty tablespace and “defrag it all”