Sometimes you need to inspect the content of a text blob in Oracle. Yes, if the content is not a text then this does not make sense, but often times we insert in a blob field a really long text like an XML content or JSON content of a document. What we need to do… Read More »
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 »
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 »
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 »
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 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 »
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 »
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 »
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 »
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 »