获取Oracle对象的安装顺序

Question

好的，我有一个复杂的递归问题。我想要获取 Oracle 11g 数据库中所有对象（all_objects 表）的依赖安装顺序。

首先，我创建了一个包含所有依赖项的视图

create or replace 
view REALLY_ALL_DEPENDENCIES as
select * 
  from ALL_DEPENDENCIES
union
select owner, index_name, 'INDEX', table_owner, table_name, table_type, null, null
  from all_indexes
union
select p.owner, p.table_name, 'TABLE', f.owner, f.table_name, 'TABLE', null, null
  from all_constraints p
  join all_constraints f 
    on  F.R_CONSTRAINT_NAME = P.CONSTRAINT_NAME 
    and F.CONSTRAINT_TYPE = 'R'
    and p.constraint_type='P'
;
/

编辑

我尝试使用此函数连接所有依赖项：

create 
 or replace
function dependency(
   i_name varchar2
  ,i_type varchar2
  ,i_owner varchar2
  ,i_level number := 0
  ,i_token clob := ' ') return clob
is
  l_token clob := i_token;
  l_exist number := 0;
begin
  select count(*) into l_exist 
    from all_objects
    where   object_name  = i_name
      and   object_type  = i_type
      and   owner = i_owner;  

  if l_exist > 0 then
    l_token := l_token || ';' || i_level || ';' || 
      i_name  || ':' || i_type || ':' || i_owner;
  else
    -- if not exist function recursion is finished
    return l_token;
  end if;

  for tupl in (
    select distinct
       referenced_name
      ,referenced_type
      ,referenced_owner
      from REALLY_ALL_DEPENDENCIES 
      where name  = i_name
      and   type  = i_type
      and   owner = i_owner
    )
  loop
   -- if cyclic dependency stop and shout!
    if i_token like '%' || tupl.referenced_name || ':' || tupl.referenced_type || ':' || tupl.referenced_owner || '%' then
      select count(*) into l_exist 
        from REALLY_ALL_DEPENDENCIES
        where   name  = tupl.referenced_name 
          and   type  = tupl.referenced_type 
          and   owner =  tupl.referenced_owner;  
      if  l_exist > 0 then
        return '!!!CYCLIC!!! (' || i_level || ';' || tupl.referenced_name || ':' || tupl.referenced_type || ':' || tupl.referenced_owner || '):' || l_token;
      end if;
    end if;

    -- go into recursion
    l_token := dependency(
       tupl.referenced_name
      ,tupl.referenced_type
      ,i_owner /* I just want my own sources */
      ,i_level +1
      ,l_token);
  end loop;

  -- no cyclic condition and loop is finished
  return l_token;
end;
/

我可以通过查询

select
   object_name
  ,object_type
  ,owner
  ,to_char(dependency(object_name, object_type, owner)) as dependecy
  from all_objects 
  where owner = 'SYSTEM'
;

好吧，也许这类似于“作弊”，但你不能做循环创建时的依赖关系。因此，至少作为人类，我只能创建一个又一个的对象:-)并且这个序列应该是“能够进行逆向工程的”。

现在我对解决方案比以前更感兴趣;-)它仍然是棘手的部分......“如何从按其安装顺序排序的模式中选择所有源（依赖对象列表在使用对象之前）”？ 这只是某种排序问题，不是吗？

Answer 1

通常，您通过按特定顺序创建对象来“作弊”。例如，您可以首先创建序列（它们具有零依赖性）。然后你可以做表格。之后是封装规格，然后是封装主体，等等。

请记住，包之间可能存在循环依赖关系，因此在某些情况下无论如何都不可能在创建时满足所有依赖关系。

这里的商业案例是什么？是否存在真正的“问题”或只是一个练习？

编辑

我们使用的导出工具按以下顺序导出对象：

• 数据库链接
• 序列
• 类型
• 表
• 视图
• 主键
• 索引
• 外键
• 触发器
• 约束
• 物化视图
• 物化视图日志
• 包规格
• 包主体
• 最后
• 过程函数

，我们运行dbms_utility.compile_schema 过程，以确保一切都有效并且不会遗漏任何依赖项。如果您使用除这些之外的其他对象类型，我不确定它们会按照这个顺序去哪里。

Answer 2

好的，我有时间再次查看该工作，我想分享结果。也许另一个人遇到这个线程来寻找解决方案。首先，我将 SQL 作为 SYS 执行，但我认为您可以使用公共同义词在每个模式中执行此操作。

过程“exec obj_install_seq.make_install('SCOTT');”制作一个包含 sql+ 兼容 sql 文件的 clob，假设您的源名为“object_name.object_type.sql”。只要把它卷出来就可以了。

干杯
克里斯

create global temporary table DEPENDENCIES on commit delete rows as 
select * from ALL_DEPENDENCIES where 1=2 ;
/

create global temporary table install_seq(
     idx   number
    ,seq   number
    ,iter  number
    ,owner varchar2(30)
    ,name  varchar2(30)
    ,type  varchar2(30)
) on commit delete rows;
/

create global temporary table loop_chk(
     iter  number
    ,lvl   number
    ,owner varchar2(30)
    ,name  varchar2(30)
    ,type  varchar2(30)
) on commit delete rows;
/

create or replace package obj_install_seq is
  procedure make_install(i_schema varchar2 := 'SYSTEM');
end;
/

create or replace package body obj_install_seq is
  subtype install_seq_t is install_seq%rowtype;
  type dependency_list_t is table of DEPENDENCIES%rowtype;

  procedure set_list_data(i_schema varchar2 := user)
  is
    l_owner varchar2(30) := i_schema;
  begin
    -- collect all dependencies
    insert into DEPENDENCIES 
      select *  
        from (select * 
              from ALL_DEPENDENCIES
             where owner = l_owner
               and referenced_owner = l_owner
             union
            select owner, index_name, 'INDEX', table_owner, table_name, table_type, null, null
              from all_indexes
             where owner = l_owner
               and table_owner = l_owner
             union
             select p.owner, p.table_name, 'TABLE', f.owner, f.table_name, 'TABLE', null, null
              from all_constraints p
              join all_constraints f 
                on  F.R_CONSTRAINT_NAME = P.CONSTRAINT_NAME 
               and F.CONSTRAINT_TYPE = 'R'
               and p.constraint_type='P'
               and p.owner = f.owner
             where p.owner = l_owner
           ) all_dep_tab;

     -- collect all objects
     insert into install_seq   
     select rownum, null,null, owner, object_name, object_type
       from (select distinct owner, object_name, object_type, created
               from all_objects
              where owner = l_owner
              order by created) objs;
  end;

  function is_referencing(
      i_owner varchar2
     ,i_name varchar2
     ,i_type varchar2
     ,i_iter number
     ,i_level number := 0
  ) return boolean
  is
    l_cnt number;
  begin
    select count(*) into l_cnt 
      from loop_chk 
     where name  = i_name
       and owner = i_owner
       and type  = i_type
       and iter  = i_iter
       and lvl   < i_level;

    insert into loop_chk values(i_iter,i_level,i_owner,i_name,i_type);

    if l_cnt > 0 then
      return true;
    else 
      return false;
    end if;
  end;

  procedure set_seq(
    i_owner varchar2
   ,i_name varchar2
   ,i_type varchar2
   ,i_iter number
   ,i_level number := 0)
  is
    -- l_dep all_dependencies%rowtype;
    l_idx number;
    l_level number := i_level +1;
    l_dep_list dependency_list_t;
    l_cnt number;
  begin
    -- check for dependend source
    begin
      select * bulk collect into l_dep_list
        from dependencies 
       where name  = i_name
         and owner = i_owner
         and type  = i_type;

      if l_dep_list.count <= 0 then
        -- recursion finished
        return;
      end if;
    end;

    for i in 1..l_dep_list.count loop
      if is_referencing(   
         l_dep_list(i).referenced_owner
        ,l_dep_list(i).referenced_name
        ,l_dep_list(i).referenced_type
        ,i_iter
        ,i_level
      ) then
        -- cyclic dependecy
        update install_seq
           set seq = 999
              ,iter = i_iter
         where name  = l_dep_list(i).referenced_name
           and owner = l_dep_list(i).referenced_owner
           and type  = l_dep_list(i).referenced_type; 
      else
        --chek if sequence is earlier
        select count(*) into l_cnt 
          from install_seq 
         where name  = l_dep_list(i).referenced_name
           and owner = l_dep_list(i).referenced_owner
           and type  = l_dep_list(i).referenced_type
           and seq   > l_level *-1;

        -- set sequence      
        if l_cnt > 0 then
          update install_seq
             set seq = l_level *-1
                ,iter = i_iter
           where name  = l_dep_list(i).referenced_name
             and owner = l_dep_list(i).referenced_owner
             and type  = l_dep_list(i).referenced_type; 
        end if;

        -- go recusrion  
        set_seq(
           l_dep_list(i).referenced_owner
          ,l_dep_list(i).referenced_name
          ,l_dep_list(i).referenced_type
          ,i_iter + (i-1)
          ,l_level
      );

      end if;          
    end loop;
  end;


  function get_next_idx return number
  is
    l_idx number;
  begin
    select min(idx) into l_idx
      from install_seq
     where seq is null;

   return l_idx;
  end;

  procedure make_install(i_schema varchar2 := 'SYSTEM') 
  is
    l_obj install_seq_t;
    l_idx number;
    l_iter number := 0;
    l_install_clob clob := chr(10);
  begin
    set_list_data(i_schema);
    l_idx := get_next_idx;

    while l_idx is not null loop
      l_iter := l_iter +1;

      select * into l_obj from install_seq where idx = l_idx;
      update install_seq set iter = l_iter where idx = l_idx;
      update install_seq set seq = 0 where idx = l_idx;
      set_seq(l_obj.owner,l_obj.name,l_obj.type,l_iter);

      l_idx := get_next_idx;
    end loop;

    for tupl in ( select * from install_seq order by seq, iter, idx ) loop
      l_install_clob := l_install_clob || '@' || 
        replace(tupl.name,' ' ,'') || '.' || 
        replace(tupl.type,' ' ,'') || '.sql' || 
        chr(10);
    end loop;

    l_install_clob := l_install_clob || 
      'exec dbms_utility.compile_schema(''' || upper(i_schema) || ''');';

    -- do with the install file what you want
    DBMS_OUTPUT.PUT_LINE(dbms_lob.substr(l_install_clob,4000));
  end;
end;
/