SQLCL Monitoring itself with Longops

Longops is a great way to monitor things that take some time to do work.  There's an easy example of using longops on oracle-base here.  I borrowed the script from there and put it into a file named my_slow_thing.sql.  Now here's a nice example of what's possible with sqlcl. The easy way to using this is add it to your login.sql which is what I did. Of course the script could run at (more...)

Client support for WITH using PL/SQL

My employer has been using 12c for about a year now, migrating away from 11gR2. It's fun working out the new functionality, including wider use of PL/SQL.

In the 'old' world, you had SQL statements that had to include PL/SQL, such as CREATE TRIGGER, PROCEDURE etc). And you had statements that could never include PL/SQL, such as CREATE SYNONYM, CREATE SEQUENCE. DML (SELECT, INSERT, UPDATE, DELETE and MERGE) were in the latter category.

One of (more...)

Change Item Position using jQuery

See this example on how to change the item position. In APEX you can position the buttons after the action bar in an interactive report. However, you can't put the items there. Using jQuery this is easy to achieve.

React on Tab Change

See this example on how to react on tab change in APEX 5. The problem is to determine the right selector to trigger the corresponding dynamic action. Thanks Christian Rokitta for your help.

Oracle Composite – leftover oramds imports

On designtime we use the oramds to develop our processflows in our bpel processes.

Basically the composite.xml part for a bpel process would look like this :

<component name="saveEmployee" version="1.0">
    <implementation.bpel src="BPEL/saveEmployee.bpel"/>
      <service name="client_ep" ui:wsdlLocation="oramds:/apps/xsd/myapp/1.0/saveEmployee.wsdl">
        <interface.wsdl interface="http://mynamespace.nl/saveEmployee#wsdl.interface(bewaarB2bEmployeeServicePort)"/>

When you open the bpel in jdeveloper, jdeveloper will automatically add the next import to your .bpel file :

  <import namespace="http://mynamespace.nl/saveEmployee"
          importType="http://schemas.xmlsoap. (more...)

PerfSheet.js: Oracle AWR Data Visualization in the Browser with JavaScript Pivot Charts

PerfSheet.js is a tool aimed at DBAs and Oracle performance analysts. It provides a simplified interface to extract and visualize AWR time series data in the browser using javascript.

The reason for a tool like PerfSheet.js is to make the analysis of AWR data easier by providing a graphical interactive interface and by automating several repetitive steps of data extraction and chart preparation. Pivot charts provide a flexible and easy to use way to navigate (more...)

Big Data – Tez, MR, Spark Execution Engine : Performance Comparison

| Feb 25, 2016

There is no question that massive data is being generated in greater volumes than ever before. Along with the traditional data set, new data sources as sensors, application logs, IOT devices, and social networks are adding to data growth. Unlike traditional ETL platforms like Informatica, ODI, DataStage that are largely proprietary commercial products, the majority of Big ETL platforms are powered by open source.

With many execution engines, customers are always curious about their usage (more...)

Slow full table scan due to row chaining

Few days ago I’ve received complaint that simple count on 2 million rows table is running forever.

This was the statement:

select count(1)
from CLIENT k
where k.expires is null;

I've used fake names for table name and columns.
Database version:

Indeed, query was running longer than I would expect. Oracle was using FULL SCAN of the table with "db file sequential read" wait events. This was little odd (more...)

OCI Client-Side Deployment in 12C

The 12c version has introduced a new features called “OCI Client-Side Deployment Parameters” this feature gives you the possibility to override some OCI parameters without even changing your application code.

Let’s make a simple test case using Python and the cx_oracle module (which use the OCI interface) for connecting to the oracle database.

You can find this module for your python version and the database 12c version here.

This is the code of our test case:

#! (more...)

Taking off the red fedora. Hello Lightbend!

Almost two years in Red Hat JBoss Middleware have been a tremendous journey for me. Getting to know so many amazing and gifted people to work with in all kinds of Java EE and integration products and projects really made me realize there is far more talent in open source communities than anywhere else.
You’ve known and seen me at different conferences and Java User Groups meetups or read my blogs or are following me (more...)

Configuring VNCR for Oracle RAC

By default on an Oracle RAC installation, the listeners are configured to allow any database to register with them.  There is no security out of the box to determine which databases may register.  While this makes it easy to create new databases without worrying about listener registration, this can cause potential problems in a real environment.

This can be dangerous working with RAC environments where the database registers with both a local and remote listener.  The (more...)

Unexpected behaviour using SSO Authentication for APEX

A customer of mine switched to a Singe Sign On Authentication Scheme for their APEX application - using Oracle Access Manager, but that's irrelevant to this case.
In the application there's a function to create an - temporarily - "real" APEX user. That APEX user is used in another JavaScript application that uses ORDS and the built-in APEX authentication - thus requiring the existence of APEX users. That function ran flawlessly when the application was (more...)

First meeting of AZEROUG in 2016 year!

I'm speaking at AZEROUG (Azerbaijan Oracle Users Group) Meeting February 27th, 2016 at Qafqaz University/ Baku, Azerbaijan. I'll speak about amazing Oracle Database option In-Memory.

See you !

Best Regards

C# Web-Based Forms with Oracle Database Access

February 20, 2016 I have been experimenting a bit with ASP.Net, having used classic ASP (with VBScript) for generating various web-based reports somewhat extensively more than a decade ago.  While before I used little more than Microsoft’s Notepad application and occasionally Microsoft FrontPage for HTML syntax verification, the development environment that I have been using […]

Detecting Soft Corruption on 12c – V$NONLOGGED_BLOCK, ORA-01578/ORA-26040

Last week we have created standby database in our dev environment and performed some ETL actions on primary side. Loading data or rebuilding indexes was performed with NOLOGGING option. After few days we noticed lots ORA-01578/ORA-26040 errors.
Corruption happened because we forgot to enable force logging.

As this was new dev database there wasn’t backup, but maybe not everything was lost. If only corrupted segments are indexes we could easily rebuild them.

Then I’ve learnt (more...)

Creating Tables in the UNDO Tablespace??

I was reading an article written by Martin Widlake in Oracle Scene Issue 58 (Autumn/Winter 2015). It said:

The second new item is the UNDO tablespace. This is a special tablespace that is only used for internal purposes and one that users cannot put any tables or indexes into.

This seemed perfectly reasonable so I wondered what might happen if I tried to do it. In an Oracle database Oracle returned (more...)

Navigating through your APEX Calendar – the easy way

The renewed Calendar Region in APEX is awesome. With just one SQL statement you can provide your users with a calendar view of their data that looks very familiar to what they already know, use and love. Apart from one thing: Navigating to a specific date is cumbersome.
The shortest route is:

  • Navigate to the right month (can be any number of clicks) 
  • Switch to "Week view" 
  • Navigate to the right week (between 0 and (more...)


Oracle PARTIAL INDEXES – a new feature in Oracle 12c, initial impression was really good, but the implementation is quite poor. With this feature, you can define INDEXING OFF for a partition so that any index with PARTIAL clause will skip this partition while creating the index. The advantage is you can selectively create indexes on partitions. And for a global index or a global partitioned index, the rows from the INDEXING OFF partitions will (more...)

Maintenance free security implementation in OBIEE 11g

This blog talks about a hassle free mechanism of OBIEE security implementation with almost zero overhead.
We will first look at the data level security implementation and then extend it to object level security.

Data Level Security:
Data level security is usually implemented using database tables. These tables store the users and the filter (more...)

Oracle DBA, IT Manager, or Something Else

February 14, 2016 In September 2014 I posted an article on this blog where I mentioned that I am the IT Manager and an Oracle Database administrator at a company that specializes in welding and machining very large metal parts – in the article I attempted a bit of humor while trying to explain a few items.  […]