oracle實用sql(12)--查詢資料是否在某個使用者下各個表的各個列

--存放使用者的使用者名稱，表名，列名，列型別,批次號(表中50個列分一個批次)。owner根據自己的需求改
--drop table tb_all_column
create table tb_all_column  as
select owner, table_name, column_name, data_type,trunc(row_number() over (partition by owner,table_name order by 1)/50) lot
  from dba_tab_columns a
 where table_name not like 'BIN$%'
   and data_type in('VARCHAR2','CHAR','NVARCHAR2','NCHAR','CLOB')
   and owner='SCOTT'
   
--存放使用者的使用者名稱，表名，批次號，is_search是否檢查過，is_exists表中是否存在滿足條件的表。owner根據自己的需求改
--後面可以透過查詢表tb_search，來確認有沒有檢查完，有沒有滿足條件的表   
--drop table tb_search
create table tb_search as
select distinct owner, table_name,lot, 'N' is_search, 'N' is_exists
  from tb_all_column
 order by owner, table_name,lot;

--處理
declare
  v_search varchar2(50) := 'xxx';
  i_count  integer;
begin
  --對於沒有檢查過的表進行檢查
  for i in (select owner,
                   table_name,
                   lot,
                    'select /*+parallel(t 8)*/ count(1) from ' || owner || '.' || table_name ||
                   ' t where 1=2 ' || col_where i_sql
              from (select a.owner,
                           a.table_name,
                           a.lot,
                           to_char(replace(wmsys.wm_concat('or ' ||
                                                           a.column_name ||
                                                           ' like ' || '''%' ||
                                                           v_search || '%'' '),
                                           ',',
                                           '')) col_where
                      from tb_all_column a, tb_search b
                     where a.owner = b.owner
                       and a.table_name = b.table_name
                       and a.lot = b.lot
                       and b.is_search = 'N'
                     group by a.owner, a.table_name, a.lot)) loop
    --這裡我只是把滿足條件的表找出來，也可以透過rowid的方式把表中指定的行記錄下來。                 
    execute immediate i.i_sql
      into i_count;
    --如果沒有滿足條件的記錄，只標記 is_search = 'Y';如果有滿足條件的記錄，標記 is_search = 'Y', is_exists = 'Y'
    if i_count = 0 then
      begin
        update tb_search
           set is_search = 'Y'
         where owner = i.owner
           and table_name = i.table_name
           and lot = i.lot;
        commit;
      end;
    else
      begin
        update tb_search
           set is_search = 'Y', is_exists = 'Y'
         where owner = i.owner
           and table_name = i.table_name
           and lot = i.lot;
        commit;
      end;
    end if;
  end loop;
end;

-------------listagg版

--存放使用者的使用者名稱，表名，列名，列型別,批次號。owner根據自己的需求改

--drop table tb_all_column

create table tb_all_column  as

select owner, table_name, column_name, data_type,trunc(row_number() over (partition by owner,table_name order by 1)/50) lot

  from dba_tab_columns a

 where table_name not like 'BIN$%'

   and data_type in('VARCHAR2','CHAR','NVARCHAR2','NCHAR','CLOB')

   and owner='SYSMAN'

 

--存放使用者的使用者名稱，表名，批次號，is_search是否檢查過，is_exists表中是否存在滿足條件的表。owner根據自己的需求改

--後面可以透過查詢表tb_search，來確認有沒有檢查完，有沒有滿足條件的表

--drop table tb_search

create table tb_search as

select distinct owner, table_name,lot, 'N' is_search, 'N' is_exists

  from tb_all_column

 order by owner, table_name,lot;

 

declare

  v_search varchar2(50) := 'from mgmt$metric_current';

  i_count  integer;

begin

  --對於沒有檢查過的表進行檢查

  for i in (select owner,

                   table_name,

                   lot,

                    'select /*+parallel(t 4)*/ count(1) from ' || owner || '.' || table_name ||

                   ' t where 1=2 ' || col_where i_sql

              from (select a.owner,

                           a.table_name,

                           a.lot,

                            listagg('or "' ||a.column_name || '" like ' || '''%' ||v_search || '%'' ')

                           within group (order by a.column_name) col_where

                      from tb_all_column a, tb_search b

                     where a.owner = b.owner

                       and a.table_name = b.table_name

                       and a.lot = b.lot

                       and b.is_search = 'N'

                     group by a.owner, a.table_name, a.lot)) loop

    --這裡我只是把滿足條件的表找出來，也可以透過rowid的方式把表中指定的行記錄下來。              

    execute immediate i.i_sql

      into i_count;

    --如果沒有滿足條件的記錄，只標記 is_search = 'Y';如果有滿足條件的記錄，標記 is_search = 'Y', is_exists = 'Y'

    if i_count = 0 then

      begin

        update tb_search

           set is_search = 'Y'

         where owner = i.owner

           and table_name = i.table_name

           and lot = i.lot;

        commit;

      end;

    else

      begin

        update tb_search

           set is_search = 'Y', is_exists = 'Y'

         where owner = i.owner

           and table_name = i.table_name

           and lot = i.lot;

        commit;

      end;

    end if;

  end loop;

end;

-------------x$表

--drop table  tb_all_colum

create table tb_all_column  as

select kqfcotob table_id,kqfconam column_name

from x$kqfco a

where kqfcodty=1

--drop table tb_search

create table tb_search as

select kqftaobj table_id,kqftanam table_name, 'N' is_search, 'N' is_exist from x$kqfta;

declare

v_search varchar2(50) := '1438';

i_count  integer;

begin

--對於沒有檢查過的表進行檢查

for i in (select table_name,

'select /*+parallel(t 2)*/ count(1) from ' ||  table_name ||

' t where 1=2 ' || col_where i_sql

from (select b.table_name,

to_char(replace(wmsys.wm_concat('or "' ||

a.column_name ||

'" like ' || '''%' ||

v_search || '%'' '),

',',

'')) col_where

from tb_all_column a, tb_search b

where a.table_id = b.table_id

and b.is_search = 'N'

group by b.table_name)) loop

--這裡我只是把滿足條件的表找出來，也可以透過rowid的方式把表中指定的行記錄下來。

execute immediate i.i_sql

into i_count;

--如果沒有滿足條件的記錄，只標記 is_search = 'Y';如果有滿足條件的記錄，標記 is_search = 'Y', is_exists = 'Y'

if i_count = 0 then

begin

update tb_search

set is_search = 'Y'

where table_name = i.table_name;

commit;

end;

else

begin

update tb_search

set is_search = 'Y', is_exist = 'Y'

where  table_name = i.table_name ;

commit;

end;

end if;

end loop;

end;