In this article I show simple trick how to add extra filter to already opened cursor in PL/SQL. Continue reading →

General rule for creating indexes in Oracle is following:

• Bitmap indexes – always index nulls
• B*Tree cluster indexes – always indexes nulls
• B*Tree indexes – can’t index data if all indexed columns are NULL

The last one can cause some performance issues but there is trick to index all NULL columns also for B*Tree indexes.

Continue reading →

This article describes how AUTOTRACE works in sql*plus.

Continue reading →

Unfortunately in Oracle you can’t create or drop database link in another schema. It’s very frustrating. Simple solution is to create anonymous block which will first create dummy function in another schema and then reuse it to create or drop an object(in our case database link).

Of course it can be reused to do other types of object as well.

Continue reading →

If you want try to select CLOB column via database link then you get error ORA-22992

select * from <TABLE_NAME>@<DATABASE LINK>;

ORA-22992: cannot use LOB locators selected from remote tables
22992. 00000 -  "cannot use LOB locators selected from remote tables"
*Cause:    A remote LOB column cannot be referenced.
*Action:   Remove references to LOBs in remote tables.

This article shows how to avoid this error

Continue reading →

This article presents how-to dump SQL as CSV files using PL/SQL

Continue reading →

The most popular method to clone a schema in Oracle is impdp tool. It allows to clone one or many schema between databases or inside the same database.

Impdp tool is based on PL/SQL package DBMS_DATAPUMP. This article shows how to use the package and clone a schema using pure PL/SQL.

Continue reading →

Oracle internal table SYS.USER$ has got many interesting columns for DBAs. This article describes some of them.

Continue reading →