Alter view and dba_dependencies

Adding constraints to a view. Maybe better to consider recreating. Dependencies are populated more complete that way.

create table huba(a int primary key);

create table hubb(b int primary key);

create table link(a int references huba, b int references hubb);


create or replace view fact as (select a,b from link);

alter view fact add constraint f_a_fk foreign key (a) references huba disable;

alter view fact add constraint f_b_fk foreign key (b) references hubb disable;

select referenced_name from dba_dependencies where name = 'FACT';

--HUBB
--LINK

create or replace view fact (
    a
  , b
  , constraint f_a_fk foreign key (a) references huba disable
  , constraint f_b_fk foreign key (b) references hubb disable
)as (select a,b from link);

select referenced_name from dba_dependencies where name = 'FACT';

--HUBA
--HUBB
--LINK

drop table huba cascade constraints purge;

drop table hubb cascade constraints purge;

drop view fact;

Errors With Depth First Hierarchical Query and Table Of Types

Struggling with a hierarchical query and table of types. Getting ORA-00600: internal error code, arguments: [koxsi2sz1] and [rworupo.1] errors with 12.1.0.2 version.

WITH Factorial (operand,total_so_far,foo) AS (
  SELECT 5 operand, 5 total_so_far, sys.odcinumberlist(1,2) foo FROM dual    -- Using anchor member to pass in "5"
  UNION ALL
  SELECT operand-1, total_so_far * (operand-1), foo
  FROM Factorial
  WHERE operand > 1)
SEARCH breadth FIRST BY operand SET order1
SELECT * FROM Factorial
;

Breadth first and everything is fine. But changing to depth first the problems occur.

WITH Factorial (operand,total_so_far,foo) AS (
  SELECT 5 operand, 5 total_so_far, sys.odcinumberlist(1,2) foo FROM dual    -- Using anchor member to pass in "5"
  UNION ALL
  SELECT operand-1, total_so_far * (operand-1), foo
  FROM Factorial
  WHERE operand > 1)
SEARCH depth FIRST BY operand SET order1
SELECT * FROM Factorial
;

With 18.4 version the error message is more tolerable ORA-00932: inconsistent datatypes: expected UDT got SYS.ODCINUMBERLIST. But no success with the results.

Getting around the problem is to create a NumberListWrapper wrapper type for the array type. The workaround seems to work at least with 12.2 and 18.4 versions.

CREATE OR REPLACE TYPE NumberListWrapper AS OBJECT (
    numbertable sys.odciNumberlist,
    MAP MEMBER FUNCTION comparable RETURN NUMBER DETERMINISTIC
);
/

CREATE OR REPLACE TYPE BODY NumberListWrapper AS
    MAP MEMBER FUNCTION comparable RETURN NUMBER DETERMINISTIC IS
    BEGIN
        RETURN 1;
    END;
END;
/


WITH Factorial (operand,total_so_far,foo) AS (
  SELECT 5 operand, 5 total_so_far, NumberListWrapper(sys.odcinumberlist(1,2)) foo FROM dual    -- Using anchor member to pass in "5"
  UNION ALL
  SELECT operand-1, total_so_far * (operand-1), foo
  FROM Factorial
  WHERE operand > 1)
SEARCH DEPTH FIRST BY operand SET order1
SELECT * FROM Factorial
;

Select For Update Locks Joined Rows

Select for update locks the joined rows even thou columns from the table are not in the select list. The rows used in a predicate sub query are not locked. Here is an example. First preparing tables:

drop table bar;

drop table foo;

create table foo as select column_value i from table(sys.odcinumberlist(1,2,3,4));

alter table foo add constraint foo_pk primary key (i);

create table bar as select i,i j from foo;

alter table bar add constraint bar_pk primary key(j);

alter table bar add constraint bar_foo_fk foreign key(i) references foo;

Selecting for update. The foo table used in exists predicate is not locked.

select j from bar where j = 1 and exists (select 0 from foo where foo.i=bar.i) for update;

--only the row in BAR table is locked

select l.mode_held,(select object_name from dba_objects o where object_id = l.lock_id1) obj from dba_locks l where mode_held like 'Row%' and session_id = SYS_CONTEXT('USERENV', 'SID');

--Row-X (SX) BAR

rollback;

When joining is used, also the joined row in FOO table is locked

select bar.j from bar inner join foo on foo.i=bar.i where j = 1 for update;

select l.mode_held,(select object_name from dba_objects o where object_id = l.lock_id1) obj from dba_locks l where mode_held like 'Row%' and session_id = SYS_CONTEXT('USERENV', 'SID');

--Row-X (SX) FOO
--Row-X (SX) BAR

rollback;

Access Path Suggestor

Reverse engineering an existing schema or creating a new sql query. Join access paths are often following foreign keys. Here is a query that searches foreign key dependency paths between two tables.

with fks as(
select pk.owner o1, pk.table_name t1, fk.constraint_name, fk.owner o2, fk.table_name t2
  from all_constraints fk, all_constraints pk
 where fk.r_owner= pk.owner
   and fk.r_constraint_name = pk.constraint_name
   and fk.constraint_type = 'R' 
), pths as (
 select o1,t1,constraint_name,o2,t2,'-<' dir
   from fks
 union all
 select o2,t2,constraint_name,o1,t1,'>-' dir
   from fks
), rcte(o1,t1,constraint_name,o2,t2,dir,lvl,pth) as (
 select o1,t1,constraint_name,o2,t2,dir,1,o1||'.'||t1||dir||constraint_name||'-'||o2||'.'||t2
   from pths
 where o1 = :owner1
   and t1 = :table1
 union all 
 select s.o1,s.t1,s.constraint_name,s.o2,s.t2
      , s.dir,lvl+1
      , prio.pth||s.dir||case when s.constraint_name not like 'SYS\_%' escape '\' then s.constraint_name||'-' end||s.o2||'.'||s.t2
  from rcte prio inner join pths s on prio.o2=s.o1 and prio.t2=s.t1 and prio.constraint_name != s.constraint_name
 where prio.lvl < 7
) cycle o1,t1 set cycle to 1 default 0
 select lvl,cycle,pth
   from rcte
 where o2 = :owner2
   and t2 = :table2
  order by lvl,pth
;

Finding shortest paths in lenght from a hierarchy

A slight modification to my previous post. This way it is possible to find shortest paths from all start nodes in a graph to a specified end point.

with tre as (
select 0 parent, 1 child, 1 len from dual union all
select 1 parent, 2 child, 2 len from dual union all
select 2 parent, 3 child, 3 len from dual union all
select 3 parent, 1 child, 4 len from dual union all
select 3 parent, 4 child, 5 len from dual union all
select 4 parent, 5 child, 6 len from dual union all
select 5 parent, 6 child, 7 len from dual union all
select 2 parent, 5 child, 20 len from dual
), rcte( root, parent, child, len, lvl, foundpth, pthlen, minpthlen, pth) as (
select t.parent
     , t.parent
     , t.child
     , t.len
     , 1 lvl
     , case when t.child = :endnode then 1 else 0 end foundpth
     , t.len pthlen
     , case when t.child = :endnode then t.len else 9999999 end minpthlen
     , t.parent||'-'||t.child pth
  from tre t
 where t.parent != :endnode
union all
select r.root
     , t.parent
     , t.child
     , t.len
     , r.lvl+1 lvl
     , case when t.child = :endnode then 1 else 0 end
     , r.pthlen+t.len pthlen
     , min(case when t.child = :endnode then r.pthlen+t.len else 9999999 end)over(partition by r.root) minpthlen
     , r.pth||'-'||t.child pth
  from tre t, rcte r
 where t.parent = r.child 
   and r.pthlen + t.len < r.minpthlen 
  ) search breadth first by parent,child set ordr
    cycle parent,child set cycle to 1 default 0
, pths as (
select root,child,lvl,pthlen,pth,cycle,foundpth,row_number()over(partition by root order by pthlen, lvl, pth) rn
  from rcte
 where foundpth = 1
)
select * 
  from pths 
 where rn=1
 order by root,pthlen, lvl, pth 
;

Finding shortest path in lenght from a hierarchy

Using an analytical function to find out when to stop browsing a hierarchy. Here is an example without further explanation what is happening.

with tre as (
select 0 parent, 1 child, 1 len from dual union all
select 1 parent, 2 child, 2 len from dual union all
select 2 parent, 3 child, 3 len from dual union all
select 3 parent, 1 child, 4 len from dual union all
select 3 parent, 4 child, 5 len from dual union all
select 4 parent, 5 child, 6 len from dual union all
select 5 parent, 6 child, 7 len from dual union all
select 2 parent, 5 child, 20 len from dual
), rcte( root, parent, child, len, lvl, foundpth, pthlen, minpthlen, pth) as (
select t.parent
     , t.parent
     , t.child
     , t.len
     , 1 lvl
     , case when t.child = :endnode then 1 else 0 end foundpth
     , t.len pthlen
     , case when t.child = :endnode then t.len else 9999999 end minpthlen
     , t.parent||'-'||t.child pth
  from tre t
 where t.parent = :startnode
   and t.parent != :endnode
union all
select r.root
     , t.parent
     , t.child
     , t.len
     , r.lvl+1 lvl
     , case when t.child = :endnode then 1 else 0 end
     , r.pthlen+t.len pthlen
     , min(case when t.child = :endnode then r.pthlen+t.len else 9999999 end)over() minpthlen
     , r.pth||'-'||t.child pth
  from tre t, rcte r
 where t.parent = r.child 
   and r.pthlen + t.len < r.minpthlen 
  ) search breadth first by parent,child set ordr
    cycle parent,child set cycle to 1 default 0
select *
  from rcte
 where foundpth = 1
 order by minpthlen, lvl, pth 
 fetch first row only
;

A slow SQL using TEMP, when and how much.

A really slow SQL clause using a lot of temp space. How much and when? Here is a query that reports hourly maximum usage of temp from ASH.

select dy
     , hr
     , coalesce(tmp,0) tmp
     , coalesce(tm,' ') tm
  from (
  select trunc(sample_time,'hh24') samplehr
        , max(tempgb) tmp
        , rpad(' ',max(tempgb)/mx*80,'*') tm
   from ( 
   select sample_time
        , tempgb
        , max(tempgb)over() mx
     from (
    select sample_time
         , sample_id
         , trunc(sum(temp_space_allocated)/1024/1024/104) tempgb
      from dba_hist_active_sess_history 
     where sql_id like coalesce(:sql_id,'%')
     group by sample_time,sample_id
         )
        )
   group by trunc(sample_time,'hh24') 
          , mx
      ) tmps 
 right outer join (
 select hr - level/24 hr 
      , to_char(hr - level/24,'dy','NLS_DATE_LANGUAGE = AMERICAN') dy
   from (
  select trunc(min(sample_time),'hh24') mi
       , trunc(sysdate,'hh24') hr
    from dba_hist_active_sess_history
       ) connect by level < (hr - mi)*24
      ) hours 
    on hours.hr=tmps.samplehr
order by hr desc
;

If you need more detailed information just browse the views as I have done earlier.