DBUnit oracle possible to determine tables written to after a given test

I have an application with ca. 200 tables and approx. 20 java server / batch programs. I have a pretty good canned suite of System tests. These JUnit tests are Black box tests in that they send in transactions via system interfaces and then check the expected behaviour via the interfaces.

I also have a pretty good CI setup wherein check-ins trigger compile type checks but I want to extend these checks to be proper Unit test cases. By Unit Test cases I really mean a single java server or Batch program plus the database (mock objects would be of dubious value开发者_高级运维). Half of the work is already done in that I can use the checks from the system test in the Unit Tests however the main issue now is of course how to get the database to a known state during the Unit tests.

I've been looking at DBUnit to help here and the dataset / clean insert functionality is pretty cool. My main issue now is is there any way of getting a list of what tables have changed between 2 points ? For example I run a System test suite which focuses on component X. What is the easiest way of determining which tables have been inserted / deleted / updated during the test suite ? Any feedback appreciated Peter

You can use AUDIT or table monitoring and ALL_TAB_MODIFICATIONS. The latter is more there to keep database stats up to date so isn't intended to be 100% reliable for this purpose. It may not cope correctly with rollbacks, for example.

I don't think this information is readily available in the Oracle system catalogs (or through a different way).

The only solution I can think of is to create a (statement) trigger on each table that records the time of the DML statement into some audit table. Your tests can truncate the audit table before running and check it afterwards.

As the trigger is pretty easy it should be possible to automatically create them through e.g. a PL/SQL block.