Re: Completely insane execution plan with but not with

  • From: Niall Litchfield <niall.litchfield@xxxxxxxxx>
  • To: greg@xxxxxxxxxxxxxxxxxx
  • Date: Tue, 24 Jul 2012 21:32:43 +0100

Test case builder does indeed not require a separate license (us outside
the Oracle firewall tend to be conscious of these things for some reason).


One thing that caught me out when investigating test case builder is that
it recreates the objects and dependencies involved in a new schema. This is
of course by design. Now consider a schema as follows pseudo code

User A
create table A.t1 (id number not null
, c1 varchar2(50)
, c2 date
, constraint pk_t1 primary key (id) );

create table A.t1_aud(
   id number
,  c2 date
,  last_update);

create or replace trigger A.trg_t1
before insert on A.t1
  insert into A.t2 (id,c2,last_update) values (,new.c2, sysdate);

then the code with the fully qualified object references will be created
with the same fully qualified object references in the test case schema.
Subsequent testing will try and insert into the wrong table which either
invalidates the test case, or messes up your original data. Fortunately
this failed for me because the trigger in test failed to compile because
the test case user didn't have privs on user A's objects.

On Tue, Jul 24, 2012 at 4:58 PM, Greg Rahn <greg@xxxxxxxxxxxxxxxxxx> wrote:

> I'm nearly 100% sure that dbms_sqldiag is not part of any management pack
> license (do note you mentioned dbms_sqltune which is not required for TCB
> export).
> See there is no mention of it here:
> I think your best bet is to be able to package the issue so Oracle Support
> can reproduce it.
> On Tue, Jul 24, 2012 at 4:26 AM, Thomas Kellerer <
> thomas.kellerer@xxxxxxxxxx
> > wrote:
> > Hi Greg,
> >
> > thanks. Will do that but it will take a while because we don't have
> direct
> > access to production.
> > (And the tuning/diagnostic packs are not licensed on either machine, so I
> > cannot use dbms_sqltune)
> >
> >
> --
> Regards,
> Greg Rahn  |  blog <>  |  twitter <
>>  |
>  linkedin <>
> --

Niall Litchfield
Oracle DBA


Other related posts: