Which index can you rebuild?

I recently wrote on table reorg and rebuild index Rule number one : you cannot rebuild a partitioned index in whole. You need to rebuild each individual (sub-)partition Rule number two : to rebuild an iot, move the table instead of trying to rebuild the underlying index Rule number three : a LOB index is […]

EZCONNECT and HOSTNAME resolution methods

EZCONNECT is the easy connect protocol, available in 10g, whenever you want to connect to a database without tnsnames and without ldap. $ grep -iw directory_path $TNS_ADMIN/sqlnet.ora names.directory_path=EZCONNECT $ sqlplus scott/tiger@//srv01:1521/db01 connect to server srv01 on port 1521 for service db01 HOSTNAME was the old-fashion way to connect to a database, where hostname = sid […]

Return NULL if the column does not exist

It is a very common challenge for a dba to create scripts that work on every version. How do you return NULL if a column does not exists? Imagine I have a view that returns the table_name, column_name and retention_type of my LOBS. SQL> create table t1(c clob) lob(c) store as (retention); Table created. SQL> […]