Category Archives: Databases

#Oracle # SQL Covert view to table issue: ORA-01723: zero-length columns are not allowed

By | July 31, 2023

In an Oracle database I was trying to convert an existing view to a table and preserve the data in it. For most of my views the following SQL worked perfectly: Then in one on the instances the following error occurred: While inspecting the SQL code of the views I noticed that the view with… Read More »

Activate #infinispan second level cache with #jboss and #hibernate

By | November 23, 2022

Second level cache is an extremely powerful way to improve performance of an enterprise application if done right. In the following I will explain how to activate and configure second level cache when using Hibernate and Jboss 7.4+ application server. Jboss 7.4 comes with Infinispan as the build in second level cache solution. This makes… Read More »

#Oracle quick way to investigate slow or time consuming application queries

By | November 21, 2022

In the following post I will describe my quick procedure I always use to debug the database queries generated by an application that hit an Oracle database. In a lot of cases your queries that hit the database are not written explicitly by a developer. That may be the case when intermediate layers as Hibernate… Read More »

#SQL #Oracle : Move all indexes to a different tablespace

By | October 18, 2022

In general is good to have a separate tablespace for your index tables. Sometimes the default tablespace, in our case DATA, is used by an application using hibernate layer to automatically create all the tables and indexes. Then the issue is how to move the automatically created indexes to the special created tablespace for indexes,… Read More »

#Oracle ORA-00600: internal error code, arguments: [ktbdchk1: bad dscn]

By | November 2, 2021

Oracle 11g database with Data Guard active. A switch of the sites (from production to disaster recovery node) is done and all seems good. Suddenly one day after the switch (more than one month) the enterprise application using it cannot connect any-more (connection pool getting filled up). The underlying issue that causes this is reported… Read More »

#Oracle: #SQL to get the latest executed queries in a schema

By | February 20, 2021

I was trying to obtain the last executed query on an Oracle instance. Seems easy but I wanted also: to exclude the queries that are done by sys user as maintenance to focus on queries executed by a specific client type. The best way to do it is to use the V$SQLAREA system table. V$SQLAREA… Read More »

#Oracle: ORA-02298: cannot validate "key" – parent keys not found

By | December 17, 2019

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 »

Migrating a #PostgreSQL database to a diferent PostgreSQL version or to a restrictive Postgres instance of cloud providers like #AWS-RDS

By | December 11, 2019

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 »

#SQL to remove duplicate rows from a table in #Oracle

By | December 11, 2019

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 »

Migrate legacy application using Oracle 10g to Oracle 12c

By | March 16, 2019

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 »