When trying to add a foreign key to a table sometimes the error ORA-02298 is thrown. Let’s have two tables a MyTable and a MyTableHistory connected by a historyid column from MyTable that points to the id column from MyTableHistory Try then to add add foreign key like: If we are in the case of… Read More: #Oracle: ORA-02298: cannot validate "key" – parent keys not found »
There are instances when one needs to migrate data from a Postgres database to a different PostgreSQL database version or even worse to a restrictive PostgreSQL instance of cloud providers like #AWS-RDS. When migrating to a different version of Postgres after you used to run on a frozen production version for long time it may… Read More: Migrating a #PostgreSQL database to a diferent PostgreSQL version or… »
Sometimes you need to add by hand a primary key on a table that initially did not have any constraint. Most of the time you will run into the issue of having duplicate rows, so first we must make sure there are no duplicate rows. Duplicate rows are the rows that have duplicates in the… Read More: #SQL to remove duplicate rows from a table in #Oracle »
After upgrading my developement Oracle database to 12c I discovered that an old legacy application still under support was no longer working throwing the following Oracle error: After some digging I found the following reported bug: Bug 14575666 In 12.1, the default value for the SQLNET.ALLOWED_LOGON_VERSION parameter has been updated to 11. This means that… Read More: Migrate legacy application using Oracle 10g to Oracle 12c »
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: Create local users in #Oracle 12c »
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: #SQL to find dependency between tables in #Oracle database »
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: Recover an #Oracle installation from a full disk backup in… »
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: Learning GO: Obtaining a unique sequence from #PostgresSQL with #golang »
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: Investigate #Oracle #deadlocks »
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: Drop a not null constraint with #SQL »