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;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28539951/viewspace-2132813/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28539951/viewspace-2132813/