如何识别和获取Oracle数据库中固定列的固定值

Question

嗨，我需要在DWH模式中从固定列中查找和识别特定值，理想情况下是为了识别与每个数字相关的系统名称，以便跟踪与该系统相关的表的总数

假设我有100个从1到100个数字开始的系统，每个系统由一个数字

定义

例子

SYSTEM NO
LOGISTIC 1
FINANCE 2
SALES 3
CREDIT 4
SECUTIRY 5

以此类推，直到100

在DB中，我有数千个表，每个表都有一个固定的列，名为ETL_SOURCE_SYSTEM，其中定义了这个数字，这样就会有一个元数据静态表/查找表，其中包含系统名称及其相关的系统编号，所以系统和系统编号不是唯一的

所以现在我需要一个动态sql来扫描DWH中的所有模式以确定哪个系统名称和编号具有所有表中所有模式的总数

查询

select * from dba_tab_cols
where column_name like '%ETL_SOURCE_SYSTEM%'

查询-新思路

select 'with tmp(schema_name,table_name, system_nbr) as (' from dual 
union all 
select 'select '''||owner||'''''as SCHEMA_NAME'','''||object_name||'''''as TABLE_NAME'',ETL_SOURCE_SYSTEM from '||object_name||' union all ' from dba_objects 
where owner not in ('EXCLUDE the some of the nor relevant schemas like SYS,PUBLIC..')
--and owner=''
and object_type ='TABLE'
union all
select 'select '''','''',0 from dual) select owner,table_name,system_nbr from tmp order by owner,system_nbr desc ;' from dual; 

如果这真的是可能的，在动态sql中，为了从所有表中获取值system no 1到100，以获得以下期望的结果

预期结果

SCHEMA_NAME TOTAL_COUNTS_OF_TABLES SYSTEM_NAME SYSTEM_NUMBER 
STAGE       150                    LOGISTIC    1
EDW         300                    LOGISTIC    1
MART        600                    LOGISTIC    1
STAGE       150                    FINANCE     2
EDW         300                    FINANCE     2
MART        600                    FINANCE     2
STAGE       150                    SALES       3
EDW         300                    SALES       3
MART        600                    SALES       3
STAGE       150                    CREDIT      4
EDW         300                    CREDIT      4
MART        600                    CREDIT      4

注意:DB是Oracle 11g，我只涵盖了几个模式，例如模式列表是大的50+或更多

有什么建议吗?

Answer 1

如您所料，它是动态SQL。这里有一个例子来说明如何做到这一点。我想你可能需要稍微修改一下(或者更多一点)，但是原则应该是可以的。

为简单起见，我只是将输出显示到屏幕上。如果你想用不同的方法，例如使用返回集合或refcursor的函数，可以这样做。

读取代码中的注释

SQL> declare
2    l_cnt number;         -- number of tables in each schema
3    l_tab varchar2(30);   -- one (any; I chose MAX) table in each schema, as all of them contain ETL_SOURCE_SYSTEM column
4    l_ess number;         -- value of ETL_SOURCE_SYSTEM in L_TAB table
5    l_sys varchar2(20);   -- system name
6    l_out varchar2(200);  -- output string
7  begin
8    -- display header
9    dbms_output.put_line('Schema      Tabs   System     ESS' ||chr(10) ||
10                            '----------- ----   ---------- ---');
11    -- loop through all users; I'm using only two users in my database
12    for cur_r in (select username from dba_users
13                  where username in ('MICHAEL', 'SCOTT')
14                 )
15    loop
16      -- pick any (I chose MAX) table name and count of tables for each schema
17      execute immediate 'select max(table_name), count(*) from dba_tables where owner = ' ||
18        chr(39) || cur_r.username || chr(39)
19        into l_tab, l_cnt;
20
21      -- fetch ETL_SOURCE_SYSTEM value in that schema
22      execute immediate 'select etl_source_system from ' || cur_r.username ||'.'|| l_tab into l_ess;
23
24      -- find system name in the lookup table (located in schema I use to run this code; should
25      -- be a privileged user which has access to DBA_ views
26      select system
27        into l_sys
28        from t_lookup
29        where etl_source_system = l_ess;
30
31      -- compose the output string
32      l_out := rpad(cur_r.username, 12, ' ') || to_char(l_cnt, '990') || '   ' ||
33               rpad(l_sys         , 10, ' ') || to_char(l_ess, '990');
34
35      -- display the result
36      dbms_output.put_line(l_out);
37
38    end loop;
39  end;
40  /
Schema      Tabs   System     ESS
----------- ----   ---------- ---
SCOTT         30   LOGISTIC     1
MICHAEL       27   FINANCE      2
PL/SQL procedure successfully completed.
SQL>