In this article I show simple trick how to add extra filter to already opened cursor in PL/SQL. Continue reading
Category Archives: Important learnings
Indexing NULL for Oracle
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.
Set autotrace in sqlplus
This article describes how AUTOTRACE works in sql*plus.
Create drop database link in another schema Oracle
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.
ORA-22992, ORA-64202 cannot use LOB locators selected from remote tables
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
Oracle estimate index size for create rebuild operation
This article presents simple method how you can estimate index size for CREATE REBUILD operation
Oracle dump SQL as CSV files
This article presents how-to dump SQL as CSV files using PL/SQL
Oracle clone schema using PL/SQL
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.
Measure storage performance in Oracle
This article presents methods to check storage performance IOPS and MBPS in Oracle:
- DBMS_RESOURCE_MANAGER.CALIBRATE_IO
- ORION tool
SYS.USER$ table in Oracle – last password change time, last locked, last expired, creation time, failed logon
Oracle internal table SYS.USER$ has got many interesting columns for DBAs. This article describes some of them.