January 2009

January 07: We can use a single character wild card search In LIKE. For an example - where a.text LIKE '%_L_NK%'. '_' is replace by any character.

January 15: In parent child situation , you can delete records from parent if you delete child records first. In this situation you can truncate child table but not parent table. To truncate parent table you must disable all foreign key constraint of child tables. Interesting :)

ORA-14086

ORA-14086: a partitioned index may not be rebuilt as a whole

Cause: User attempted to rebuild a partitioned index using ALTER INDEX REBUILD statement, which is illegal.

Action: Rebuild the index, a partition at a time or drop and recreate the entire index.


SQL> ALTER INDEX TEST_ID_IDX REBUILD ONLINE COMPUTE STATISTICS;
ORA-14086: a partitioned index may not be rebuilt as a whole

Rebuilding a partitioned index is slightly different then rebuilding a normal (non-partitioned) index. So you should do this in the following way

SQL> ALTER INDEX TEST_ID_IDX REBUILD PARTITION TEST_2005 ONLINE COMPUTE STATISTICS;

Related Topics:

1. Rebuilding a partitioned index.
   

Rebuilding a Partitioned Index

Rebuilding a partitioned index is slightly different then rebuilding a normal (non-partitioned) index.

Let see what happen when we try to rebuild a partitioned index in usual way

SQL> ALTER INDEX TEST_ID_IDX REBUILD ONLINE COMPUTE STATISTICS;
ORA-14086: a partitioned index may not be rebuilt as a whole

Cause: User attempted to rebuild a partitioned index using ALTER INDEX REBUILD statement, which is illegal.

Action: Rebuild the index, a partition at a time or drop and recreate the entire index


If you choose to rebuild that partition then run the following SQL

SQL> ALTER INDEX TEST_ID_IDX REBUILD PARTITION TEST_2005 ONLINE COMPUTE STATISTICS;

Here is a SQL for finding/ rechecking partitioned index

SELECT uip.index_name
, utp.table_name
, uip.partition_name
, ui.index_type
, ui.global_stats
, uip.last_analyzed
FROM USER_IND_PARTITIONS uip
, USER_TAB_PARTITIONS utp
, USER_INDEXES ui
WHERE uip.partition_name= utp.partition_name
AND ui.index_name=uip.index_name
AND ui.table_name = utp.table_name
AND uip.index_name= 'TEST_ID_IDX'
AND utp.table_name= 'TEST';

SET DEFINE

'SET DEFINE' is useful when you are working with sql*plus. There are three combinations of 'SET DEFINE' and those are:

1. set define on.
2. set define off.
3. set define x.
   

1. set define x: set define x specifies the prefix-character for substitution variables. The default is the ampersand (&).

set define &;

select * from dba_objects where object_name like '%&object_name%';


2. set define on: Turns on substitution variables.

set define on;

select '&hello' from dual;

If define is set to on and SQL*Plus finds the current substitution prefix, it asks for a string to be entered. In the following example, I entered: hasan

Enter value for hello: this string was entered

old 1: select '&hello' from dual
new 1: select 'this string was entered' from dual

'HASAN'
---------------
hasan

It might be annoying to see the following lines printed by SQL*Plus:

old 1: select '&hello' from dual
new 1: select 'this string was entered' from dual

This behavior can be turned off by setting verify off.


3. set define off


Turns off substitution variables.

set define off;
select '&hello' from dual;

'&HELL
----------------
&hello