If you have come trying to find out why you can’t connect when you know you haven’t fat fingered your password, take heart, you aren’t going crazy. You likely used instructions in the following post to connect SQL Developer to…
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…
Oracle database on NFS – Failure scenario testing
This post will provide a simple test case for the efficacy of creating and using an Oracle database cluster using NFS storage. The basic configuration will be shown, as well as the test case scenarios and results. A complete installation…
Does dropping a foreign key constraint drop the corresponding index?
No, you can safely drop a foreign key referential integrity constraint without affecting any underlying indexes. SQL> create table p(t number primary key); Table created. SQL> create table c(f number primary key, t number references p(t)); Table created. SQL> create…
The PL/SQL commit “optimization”
For those of you that may not know, PL/SQL has what is normally an enhancement that results in a full commit being performed at the end of a job, rather than when the programmer instructs it do so. The reason…
Gotcha on automatic PL/SQL recompilation
What is below is taken from an actual analysis of heavy latching and invalid objects after a change was made. This was done in early 2011. For a long period of time, we have avoided making changes that would result…
Programatically creating GoldenGate conflict detection/resolution rules
We initially started with checking all columns in every table for any differences in an active-active environment, and aborting the replicat if it failed. That seems a bit over the top in hindsight, so we changed it to simply discard…
Oracle analytic functions continue to exhibit the coolness factor
Sorry, but I never grow tired of these. We wanted to know the average duration between an online shopper creating their cart and when they actually checked out. Analytic functions make this a snap. select min(gap), bucket from (select submitted_date…