Category Archives: Databases

Create local users in #Oracle 12c

By | March 16, 2019

One of the most annoying things is when some vendor decides that it is a good idea to change some very basic and long time existing basic behaviour. In the new Oracle 12c you are no longer able to create local users the old way as sys: If you try to execute the above the… Read More »

#SQL to find dependency between tables in #Oracle database

By | August 26, 2018

There are times when we need to find which are the tables that depend on a given table. This is useful when cleanup or archiving jobs have to pe performed and the interconnection of constraints is becoming a nuisance. Lucky for us in the case of Oracle database is quite simple to search through all… Read More »

Recover an #Oracle installation from a full disk backup in #Linux

By | August 26, 2018

Reinstalling things like Oracle is always a hassle and takes about one day to make it work. Lucky for me I made a full back-up of my old disk when I upgraded to a new laptop. On the new laptop I did a fresh install of Fedora 28. The idea is to migrate my old… Read More »

Learning GO: Obtaining a unique sequence from #PostgresSQL with #golang

By | May 21, 2018

At some point I discovered GO trying to do very simple and fast code for specific tasks without having to load lots of libraries, deploy application servers or set up complicated frameworks. If you want to build fast very small native micro-services that can be deployed in a docker instance < 10MB Go is the… Read More »

Investigate #Oracle #deadlocks

By | May 10, 2018

Sometimes we may end up having some application resources competing for the same resource on our Oracle database. This is usually the case when some exclusive resource must be allocated or updated as in the case of database based locking mechanisms. If there are code issues we may end up in a deadlock situation when… Read More »

Drop a not null constraint with #SQL

By | February 15, 2018

Sometimes very annoying things can be removed with a simple SQL commands which nobody remembers because is used so rarely. For example when we have to update a database schema and remove a not null constraint on a column. There is a short one liner for it: ALTER TABLE my_table ALTER COLUMN name DROP not… Read More »

#Microsoft SQL Server: Database #Replication (Mirroring) deprecated and replaced by #SQL Availability Groups

By | February 5, 2018

It looks like the Microsoft SQL Database Mirroring a feature I used a lot and for which posted a step by step tutorial that proved to be very popular was deprecated by Microsoft. In a news article on Microsoft site the alternative is presented “Basic Availability Groups (Always On Availability Groups)”. I will summarize in… Read More »

Using temporary indices to optimize DELETE operations on big tables in #DB2

By | December 18, 2019

DELETE operations are much faster in case we disable constraints. This is because not all foreign keys affected by the DELETE are indexed. We have to detect all the foreign keys that point to the table from where you delete data. The we can create temporary indices to help speed up the delete. STEP 1.… Read More »

Optimizing DELETE operations on big tables in #DB2

By | November 21, 2017

Deletes go very slow if the log buffer size is too small and we delete lots of data from big tables. It is very important to have the right settings for log buffer size to avoid having to frequent writes to the disk, writes that cause a high IO traffic that will slow down the… Read More »

Fix #DB2 SQL1639N ( SQLCODE=-1639, SQLSTATE=08001, SQLERRMC=null) authentication issue

By | November 21, 2017

After migrating my full DB2 backup (binaries and data) to a new server I got into several issue. One was already described in the previous post here. ISSUE: Even after fixing that start issue I was still not OK. My WebSphere data source that linked to the DB2 instance was not responding. Trying to ping… Read More »