Find A Search String in All Tables

pr1v4t3
exp system/manager full=Y file=apu.dmp

ACS_TRANSACTION_DETAILS
select owner from dba_tables where table_name = 'ACS_TRANSACTION_DETAILS';
export ORACLE_HOME=/u01/oracle/product/9.2
set serveroutput on;
declare
cursor c1 is select owner, table_name, column_name from dba_tab_cols where owner
not in ('SYSTEM',
'XDB',
'SYS',
'TSMSYS',
'MDSYS',
'EXFSYS',
'WMSYS',
'ORDSYS',
'OUTLN',
'DBSNMP') and data_type like '%CHAR%';
v_schema varchar2(100);
v_table varchar2(100);
v_column varchar2(100);
v_count number;
begin
execute immediate 'truncate table search_string';
open c1;
loop
<<fetch_cursor>> fetch c1 into v_schema, v_table, v_column;
exit when c1%notfound;
begin
execute immediate 'insert into search_string select ''' ||v_schema||''',
'''||v_table||''','''||v_column||''', count(*) from '||v_schema||'.'||v_table||'
where '||v_column ||' like ''CSBNE%''';
commit;
exception when others then dbms_output.put_line ('insert into search_str
ing select ''' ||v_schema||''','''||v_table||''','''||v_column||''', count(*) fr
om '||v_schema||'.'||v_table||' where '||v_column ||' like ''CSBNE%''');
GOTO fetch_cursor;
end;
end loop;
close c1;
end;
--DECLARE
match_count integer;
v_search_string varchar2(4000) := 'CSBNE';
BEGIN
FOR t IN (SELECT owner, table_name, column_name FROM dba_tab_cols where data_t
ype like '%CHAR%') LOOP
EXECUTE IMMEDIATE
'SELECT COUNT(*) FROM '||t.owner || '.' || t.table_name||
' WHERE '||t.column_name||' = :1'
INTO match_count
USING v_search_string;
IF match_count > 0 THEN
--dbms_output.put_line( t.owner || '.' || t.table_name ||' '||t.column_nam
e||' '||match_count );
insert into search_string values (t.owner,t.table_name,t.column_name,mat
ch_count);
END IF;
END LOOP;
END;
/
select utility_text from UCS.UCS_TO_MANHATTAN_FILE_EXTRACTS where utility_text l

ike 'CSBNE%' and rownum=1;
select count(*) from UCS.UCS_TO_MANHATTAN_FILE_EXTRACTS where utility_text like
'CSBNE%';
desc search_string;
select schema_name||'.'||table_name||'.'||column_name from search_string where n
o_of_records > 0;
select * from UCS.UCS_TO_MANHATTAN_FILE_EXTRACTS where utility_text like 'CSBNE%
';
UCS.UCS_TO_MANHATTAN_FILE_EXTRACTS.UTILITY_TEXT
UCS.UCS_COMMS.SERVICE_NUMBER
UCS.UCS_COMMS_TRANSACTIONS.SERVICE_NUMBER
UCS.FELIC_NEW_COMMS.SERVICE_NUMBER
set
set
set
set
set
set
colsep ,
pagesize 0
trimspool on
headsep off
linesize 1000
numw 36
spool UCS_COMMS.csv
select * from UCS.UCS_COMMS where SERVICE_NUMBER like 'CSBNE%';
select count(*) from search_String where no_of_records > 0;
insert into search_string select
'UCS','UCS_SITE_AGREEMENTS_V','PMS_CURRENT_TENANT', count(*) from
UCS.UCS_SITE_AGREEMENTS_V where PMS_CURRENT_TENANT like 'CSBNE%'

'UCS','BAC_ELECTRICAL_METERS_V','TENANT_DESCRIPTION', count(*) from
UCS.BAC_ELECTRICAL_METERS_V where TENANT_DESCRIPTION like 'CSBNE%'
insert into search_string select 'UCS','UCS_SITE_AGREEMENTS_V','PMS_SITE_ID',
count(*) from UCS.UCS_SITE_AGREEMENTS_V where PMS_SITE_ID like 'CSBNE%'
'UCS','UCS_SITE_AGREEMENTS_V','PMS_AGREEMENT_ID', count(*) from
UCS.UCS_SITE_AGREEMENTS_V where PMS_AGREEMENT_ID like 'CSBNE%'
insert into search_string select 'ACS','TEST_TRANS','accounting_period',
count(*) from ACS.TEST_TRANS where accounting_period like 'CSBNE%'
insert into search_string select 'UCS','UCS_SITE_AGREEMENTS_V','PMS_SITE_NAME',
count(*) from UCS.UCS_SITE_AGREEMENTS_V where PMS_SITE_NAME like 'CSBNE%'
'UCS','UCS_ELEC_USER_SUMMERY_V','TENANT_DESCRIPTION', count(*) from
UCS.UCS_ELEC_USER_SUMMERY_V where TENANT_DESCRIPTION like 'CSBNE%'
'UCS','BAC_ELECTRICAL_METER_PERIODS_V','TENANT_DESCRIPTION', count(*) from
UCS.BAC_ELECTRICAL_METER_PERIODS_V where TENANT_DESCRIPTION like 'CSBNE%'
'UCS','UCS_SITE_AGREEMENTS_V','PMS_DEBTOR_NAME', count(*) from
UCS.UCS_SITE_AGREEMENTS_V where PMS_DEBTOR_NAME like 'CSBNE%'
mkfs -olog=INLINE,ea=v2 -s4G -Vjfs2 /dev/usbms0
P@ssw0rd1
data3online.local\hm-admin
0282165807 simone
scl.signet.com.au\adhmansoor
okay, so the SA account
Cluster 01= xu*U4huT@SQLS
Cluster 02 = pRU2R+Qu@SA

Find A Search String in All Tables

Uploaded by

Copyright:

Available Formats

Find A Search String in All Tables

Uploaded by

Document Information

Original Description:

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Find A Search String in All Tables

Uploaded by

Copyright:

Available Formats

pr1v4t3

exp system/manager full=Y file=apu.dmp

select utility_text from UCS.UCS_TO_MANHATTAN_FILE_EXTRACTS where utility_text l

UCS.UCS_SITE_AGREEMENTS_V where PMS_CURRENT_TENANT like 'CSBNE%'

You might also like