Improvement on JDE environment audit script

I’ve put a number of iterations of this on my blog, but hopefully they keep getting better.  I’m using the current one to devise a script that keeps a couple of “Systems” in sync.  On a nightly basis I want to replicate a number of data changes from one system to the other.

This uses a left outer join, so that you get all of the JDE tables that are in OL910, but also all of the copies and full package table names – which is really handy.

I use this to identify the tables that have data and then use this for my synchronisation logic.

DROP TABLE SY910.DATACOUNTS ;

cREATE TABLE "SY910"."DATACOUNTS"
   (    "TABLE_NAME" NVARCHAR2(20),
        "TABLE_OWNER" NVARCHAR2(20),
        "ORACLE_COUNT_E1SYS" NUMBER,
        "ORACLE_COUNT_E1SYS2" NUMBER,
        "TABLE_DESCRIPTION" NVARCHAR2(256)
   )
      TABLESPACE "SY910T" ;

--I use the below statement to get me a likely list of JDE owners that have JDE tables.  This is using the F% criteria, note that it essentially builds the “IN” clause for you.

SELECT '''' || OWNER || ''',', count(1) FROM ALL_TABLES WHERE TABLE_NAME LIKE 'F%' group by owner;
   
grant all on SY910.DATACOUNTS to public ;

delete from sy910.datacounts ;

insert into SY910.datacounts select table_name,owner,0,0,simd 
from all_tables left outer join ol910.f9860  on ltrim(rtrim(siobnm)) = ltrim(rtrim(table_name))
where owner in ('SVMDV01',
'JDE',
'SVMAP01',
'PD910',
'DD910',
'OL910',
'SVMAP02',
'SY910',
'DVDD910');

commit;

select 'update SY910.dataCounts set ORACLE_COUNT_E1SYS2 = (select count(1) from ' || table_owner || '.' || table_name || ' ) WHERE table_name = ' || '''' || table_name || '''' || ' and table_owner = ''' || table_owner || ''';' from sy910.datacounts 
 where table_owner in ('SVMDV01',
'JDE',
'SVMAP01',
'PD910',
'DD910',
'OL910',
'SVMAP02',
'SY910',
'DVDD910')
 order by table_name desc ;
 

--update SY910.dataCounts set ORACLE_COUNT_E1SYS2 = (select count(1) from SY910.SHAE ) WHERE table_name = 'SHAE' and table_owner = 'SY910';
--update SY910.dataCounts set ORACLE_COUNT_E1SYS2 = (select count(1) from SY910.F99410 ) WHERE table_name = 'F99410' and table_owner = 'SY910';
--update SY910.dataCounts set ORACLE_COUNT_E1SYS2 = (select count(1) from SY910.F98TMPL ) WHERE table_name = 'F98TMPL' and table_owner = 'SY910';