Infrastructure at your Service

All Posts By

Lazhar Felahi

Lazhar Felahi

Oracle REST Data Services – Create Web Services

By | Database Administration & Monitoring | No Comments

In my previous blog Oracle REST Data Services – Installation and Configuration : We have installed ORDS in standalone mode. Configured ORDS to be administer by SQL Developer In this blog, we will explain how to create simple RESTful Web Services using PL/SQL and a browser Rest Client.. My sources are : Oracle REST Data Services documentation.  Thatjeffsmith. oracle-base. First, let’s try to REST the schema/tables “HR/EMPLOYEES” using the Auto REST feature. Auto REST feature…

Read More
Lazhar Felahi

Oracle REST Data Services – Installation and Configuration

By | Database Administration & Monitoring | No Comments

The goal of this blog is to install and configure Oracle REST Data Services 19.2 in Standalone mode into an oracle database 12.2. The official documentation is stored here and you will find a lot of resources and examples from the Thatjeffsmith website. The first step is to download the last version of ORDS from the Oracle REST Data Services download page. Unzip the zip file into a directory (or folder) of your choice :…

Read More
Lazhar Felahi

Oracle Data Pump Integration for Table instantiation with Oracle Golden Gate

By | Database Administration & Monitoring, Oracle | No Comments

From Oracle GoldenGate (OGG) version 12.2 and above, there is a transparent integration of OGG with Oracle Data Pump as explained in the Document ID 1276058.1. The CSN for each table is captured on an Oracle Data Pump export. The CSN is then applied to system tables and views on the target database on the import. These views and system tables are referenced by Replicat when applying data to target database. This 12.2 feature, no…

Read More
Lazhar Felahi

Oracle GoldenGate 19c: Cannot register Integrated EXTRACT due to ORA-44004

By | Application integration & Middleware, Database Administration & Monitoring, Oracle | No Comments

The global_name in an oracle database has a direct impact on the Golden Gate Extract process registration we need to do when we create an Integrated Extract. In my example below, I use Oracle GoldenGate MicroServices architecture but the same behaviour occurs with Oracle GoldenGate Classic architecture.   Let’s start with the creation of the Extract process by clicking on the plus button :   Choose Integrated Extract and click on Next button: Fulfill all…

Read More
Lazhar Felahi

How to use DBMS_SCHEDULER to improve performance ?

By | Database Administration & Monitoring, Development & Performance, Oracle | 3 Comments

From an application point of view, the oracle scheduler DBMS_SCHEDULER allows to reach best performance by parallelizing your process. Let’s start with the following PL/SQL code inserting in serial several rows from a metadata table to a target table. In my example, the metadata table does not contain “directly” the data but a set a of sql statement to be executed and for which the rows returned must be inserted into the target table My_Target_Table_Serial…

Read More
Lazhar Felahi

Oracle Text : Using and Indexing – the CONTEXT Index

By | Database Administration & Monitoring, Development & Performance, Oracle | 2 Comments

Everybody has already faced performance problem with oracle CLOB columns. The aim of this blog is to show you (always from a real user case) how to use one of Oracle Text Indexes (CONTEXT index) to solve performance problem with CLOB column. The oracle text complete documentation is here : Text Application Developer’s Guide Let’s start with the following SQL query which take more than 6.18 minutes to execute : SQL> set timing on SQL>…

Read More
Lazhar Felahi

Oracle Materialized View Refresh : Fast or Complete ?

By | Database Administration & Monitoring, Oracle | 2 Comments

In contrary of views, materialized views avoid executing the SQL query for every access by storing the result set of the query. When a master table is modified, the related materialized view becomes stale and a refresh is necessary to have the materialized view up to date. I will not show you the materialized view concepts, the Oracle Datawarehouse Guide is perfect for that. I will show you, from a user real case,  all steps…

Read More
Lazhar Felahi

SQL Tuning – Mix NULL / NOT NULL Values

By | Development & Performance, Oracle | No Comments

One of the difficulty when writing a SQL query (static SQL) is to have in the same Where Clause different conditions handling Null Values and Not Null Values for a predica. Let’s me explain you by an example : Users can entered different values for a user field from an OBI report: – If no value entered then all rows must be returned. – If 1 value entered then only row(s) related to the filter…

Read More
Lazhar Felahi

Where come from Oracle CMP$ tables and how to delete them ?

By | Database Administration & Monitoring | No Comments

Regarding the following “MOS Note Is Table SCHEMA.CMP4$222224 Or Similar Related To Compression Advisor? (Doc ID 1606356.1)”, we know that since Oracle 11.2.0.4 BP1 or Higher, due to the failure of Compression Advisor some tables with names that include “CMP”, created “temporary – the time the process is running” by Compression Advisor process (ie CMP4$23590) are not removed from the database as that should be the case. How theses tables are created ? How to…

Read More