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.
This article describes how AUTOTRACE works in sql*plus.
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.
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
This article presents simple method how you can estimate index size for CREATE REBUILD operation
This article presents how-to dump SQL as CSV files 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.
This article presents methods to check storage performance IOPS and MBPS in Oracle:
- ORION tool
Oracle internal table SYS.USER$ has got many interesting columns for DBAs. This article describes some of them.
This article presents simple methods to write or read CLOB to/from file using following packages: