I improved the query I posted the 22th of November 2016. It provides better debugging and better performance. But it comes at a cost. For large tables, ORA-01489 easily occurs. The output has changed as a table gets checked in its entirety and not any longer column by column. 👽In the face of the first problem, you might want to have a dry run beforehand to get the problematic tables you should handle separately - look for the pattern …\(\d+\), and for the latter - sorry, get used to it 😉.
declare
-- constants
C_DO_DEBUG constant boolean := false;
C_DO_DRY_RUN constant boolean := false;
C_SEARCH_PATTERN constant varchar2(32767 char) := '(begin|select|insert|update|delete|merge)'; -- case insensitive
C_MINIMUM_DATAL_LENGTH constant pls_integer := 1000;
C_SCHEMA_INCLUSION_PATTERN constant varchar2(32767 char)
:= '^(OWN|SST)_.*$';
C_INCLUDE_VIEW_FLAG constant char(1 byte) := null; -- null und '' bedeutet Nö, alles andere Jau
C_DO_LIST_NO_HITS constant boolean := false;
C_TAB_NAME_EXCLUSION_PATTERN constant varchar2(32767 char)
:= '(^(BCKP|BACKUP|BAK|BCK|BKP|TE?MP)[_#$]|[_#$](ERROR|ARCHIVE?)[_#$]|LOG|.*\d{8}$|.*[_$#](PLAN_SQL|AUDIT_TRAIL)|SQLTRACE)' ;
C_TAB_NAME_INCLUSION_PATTERN constant varchar2(32767 char)
:= '.*';
C_COL_NAME_EXCLUSION_PATTERN constant varchar2(32767 char)
:= '(^(BCKP|BACKUP|BAK|BCK|BKP)[_#$]|[_#$]ERROR[_#$]|LOG)';
C_COL_NAME_INCLUSION_PATTERN constant varchar2(32767 char)
:= '.*';
C_DATA_TYPE_EXCLUSION_PATTERN constant varchar2(32767 char)
:= '^(LONG)$'; -- LONG is a nuisance
C_DATA_TYPE_INCLUSION_PATTERN constant varchar2(32767 char)
:= '^(CHAR|CLOB|NCHAR|NCLOB|NVARCHAR2|VARCHAR2)$' ;
C_PARALLEL_DEGREE constant varchar2(1000 char) := 'auto';
C_PLACEHOLDER_SCHEMA_NAME constant char(6 char) := ' PHSN ';
C_PLACEHOLDER_TABLE_NAME constant char(6 char) := ' PHTN ';
C_PLACEHOLDER_WHERE_CLAUSE constant char(6 char) := ' PHWC ';
C_PLACEHOLDER_COLUMN_LIST constant char(6 char) := ' PHCL ';
C_STATEMENT_STUB constant clob
:= 'select /*+ parallel(' ||
C_PARALLEL_DEGREE ||
') */' ||
CHR(10) ||
' COUNT(*) as RESULT_COUNT,' ||
CHR(10) ||
C_PLACEHOLDER_COLUMN_LIST || ' as RESULT' ||
CHR(10) ||
' from ' ||
C_PLACEHOLDER_SCHEMA_NAME ||
'.' ||
C_PLACEHOLDER_TABLE_NAME ||
CHR(10) ||
' where 1 = 0' ||
CHR(10) ||
C_PLACEHOLDER_WHERE_CLAUSE ||
CHR(10) ||
' or 1 = 0';
-- variables
V_STATEMENT clob := null;
V_RESULT_COUNT integer := 0;
V_RESULT varchar2(32767 byte) := null;
-- subroutines
procedure DEBUG (I_MSG in varchar2) is
begin
if C_DO_DEBUG then
DBMS_OUTPUT.PUT_LINE(q' DEBUG - ' || I_MSG);
end if;
end;
begin
dbms_output.put_line('"search_4_string.sql" started' || chr(10));
if C_DO_DRY_RUN then
DEBUG(q' C_DO_DRY_RUN: TRUE ');
else
DEBUG(q' C_DO_DRY_RUN: FALSE ');
end if;
DEBUG(q' C_SEARCH_PATTERN: ' || C_SEARCH_PATTERN || q' ');
DEBUG(q' C_SCHEMA_INCLUSION_PATTERN: ' || C_SCHEMA_INCLUSION_PATTERN || q' ');
DEBUG(q' C_INCLUDE_VIEW_FLAG: ' || C_INCLUDE_VIEW_FLAG || q' ');
if C_DO_LIST_NO_HITS then
DEBUG(q' C_DO_LIST_NO_HITS: TRUE ');
else
DEBUG(q' C_DO_LIST_NO_HITS: FALSE ');
end if;
DEBUG(q' C_TAB_NAME_EXCLUSION_PATTERN: ' || C_TAB_NAME_EXCLUSION_PATTERN || q' ');
DEBUG(q' C_TAB_NAME_INCLUSION_PATTERN: ' || C_TAB_NAME_INCLUSION_PATTERN || q' ');
DEBUG(q' C_COL_NAME_EXCLUSION_PATTERN: ' || C_COL_NAME_EXCLUSION_PATTERN || q' ');
DEBUG(q' C_COL_NAME_INCLUSION_PATTERN: ' || C_COL_NAME_INCLUSION_PATTERN || q' ');
DEBUG(q' C_DATA_TYPE_EXCLUSION_PATTERN: ' || C_DATA_TYPE_EXCLUSION_PATTERN || q' ');
DEBUG(q' C_DATA_TYPE_INCLUSION_PATTERN: ' || C_DATA_TYPE_INCLUSION_PATTERN || q' ');
DEBUG(q' C_PARALLEL_DEGREE: ' || C_PARALLEL_DEGREE || q' ');
DEBUG(q' C_PLACEHOLDER_SCHEMA_NAME: ' || C_PLACEHOLDER_SCHEMA_NAME || q' ');
DEBUG(q' C_PLACEHOLDER_TABLE_NAME : ' || C_PLACEHOLDER_TABLE_NAME || q' ');
DEBUG(q' C_PLACEHOLDER_COLUMN_LIST: ' || C_PLACEHOLDER_COLUMN_LIST || q' ');
DEBUG(q' C_PLACEHOLDER_WHERE_CLAUSE: ' || C_PLACEHOLDER_WHERE_CLAUSE || q' ');
DEBUG(q' C_STATEMENT_STUB: ' || C_STATEMENT_STUB || q' ');
for REC
in (
with TC as
(select OWNER as SCHEMA_NAME
, TABLE_NAME
, COLUMN_NAME
, DATA_TYPE
from ALL_TAB_COLS
where 1 = 1
and REGEXP_LIKE(OWNER, C_SCHEMA_INCLUSION_PATTERN)
and not REGEXP_LIKE(
TABLE_NAME
, C_TAB_NAME_EXCLUSION_PATTERN
)
and REGEXP_LIKE(
TABLE_NAME
, C_TAB_NAME_INCLUSION_PATTERN
)
and not REGEXP_LIKE(
COLUMN_NAME
, C_COL_NAME_EXCLUSION_PATTERN
)
and REGEXP_LIKE(
COLUMN_NAME
, C_COL_NAME_INCLUSION_PATTERN
)
and not REGEXP_LIKE(
DATA_TYPE
, C_DATA_TYPE_EXCLUSION_PATTERN
)
and REGEXP_LIKE(
DATA_TYPE
, C_DATA_TYPE_INCLUSION_PATTERN
)
and (coalesce(DATA_LENGTH
,0) >= C_MINIMUM_DATAL_LENGTH
or DATA_TYPE in ('CLOB'
,'NCLOB'))
and HIDDEN_COLUMN = 'NO'
and 1 = 1)
select --+ parallel(auto)
TC.SCHEMA_NAME
, TC.TABLE_NAME
, q' ' '
|| TC.SCHEMA_NAME
|| q' . '
|| TC.TABLE_NAME
|| q' ' '
|| chr(10)
|| listagg(q' || chr(10) || ' '
|| TC.COLUMN_NAME
|| q' : ' || sum(case when regexp_like( '
|| TC.COLUMN_NAME
|| q' , ' '
|| C_SEARCH_PATTERN
|| q' ', 'i') then 1 else 0 end) '
,chr(10)
on overflow truncate '…' with count) as COLUMN_LIST
, listagg(q' or '
|| q' regexp_like( '
|| TC.COLUMN_NAME
|| q' , ' '
|| C_SEARCH_PATTERN
|| q' ', 'i') '
,chr(10)
on overflow truncate '…' with count) as WHERE_CLAUSE
from TC
left outer join ALL_VIEWS V
on TC.SCHEMA_NAME = V.OWNER
and TC.TABLE_NAME = V.VIEW_NAME
where C_INCLUDE_VIEW_FLAG is not null or V.OWNER is null
group by TC.SCHEMA_NAME
, TC.TABLE_NAME
order by TC.SCHEMA_NAME asc
, TC.TABLE_NAME asc
)
loop
begin
if C_DO_LIST_NO_HITS then
DBMS_OUTPUT.PUT_LINE(q' *** ' || REC.SCHEMA_NAME || q' . ' || REC.TABLE_NAME || q' *** ');
end if;
DEBUG(q' REC.COLUMN_LIST: ' || REC.COLUMN_LIST || q' ');
DEBUG(q' REC.WHERE_CLAUSE: ' || REC.WHERE_CLAUSE || q' ');
V_STATEMENT :=
REPLACE(
REPLACE(
REPLACE(
REPLACE(
C_STATEMENT_STUB
, C_PLACEHOLDER_SCHEMA_NAME
, REC.SCHEMA_NAME
)
, C_PLACEHOLDER_TABLE_NAME
, REC.TABLE_NAME)
, C_PLACEHOLDER_COLUMN_LIST
, REC.COLUMN_LIST)
, C_PLACEHOLDER_WHERE_CLAUSE
, REC.WHERE_CLAUSE);
if C_DO_DEBUG then
DEBUG(
q' Executing following statement' ' ||
CHR(10) ||
V_STATEMENT);
elsif C_DO_DRY_RUN then
DBMS_OUTPUT.PUT_LINE(
q' Executing following statement' ' ||
CHR(10) ||
V_STATEMENT);
end if;
if not C_DO_DRY_RUN then
execute immediate V_STATEMENT
into V_RESULT_COUNT ,V_RESULT;
end if;
if C_DO_LIST_NO_HITS or V_RESULT_COUNT > 0 then
DBMS_OUTPUT.PUT_LINE(V_RESULT);
end if;
exception
when others
then
DBMS_OUTPUT.PUT_LINE('*** Error stack ***');
DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_STACK);
DBMS_OUTPUT.PUT_LINE('*** Error backtrace ***');
DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
DBMS_OUTPUT.PUT_LINE('');
end;
end loop;
dbms_output.put_line(chr(10) || '"search_4_string.sql" ended');
end;
/
