Category: Oracle

Corrupt index not detected by analyze table validate structure cascade

Pretty wild stuff. This was in 11.2.0.3.9, EE… SQL> select * from dcspp_order_rel where order_id = 'EXP1439962334' and sequence_num = 5; ORDER_ID RELATIONSHIPS SEQUENCE_NUM —————————————- —————————————- ———— EXP1439962334 r406622317 5 SQL> select * from dcspp_order_rel where order_id = 'EXP1439962334' and…

Drop all tables in a schema

This assumes the tables have no integrity constraints related to a table in another schema. This just loops until all tables are dropped. If you are the nervous type, add an exception handler for the integrity constraint exception, and exit…

PURGE DBA_RECYCLEBIN taking a long time

Just check DBA_SEGMENTS for SEGMENT_NAME like ‘BIN%’, and it should be dropping in number… SQL> select count(*) from dba_segments where segment_name like 'BIN%'; COUNT(*) ———- 2307 SQL> select count(*) from dba_segments where segment_name like 'BIN%'; COUNT(*) ———- 2297 SQL> select…