Oracle Tips and Tricks — David Fitzjarrell

September 14, 2019

Unexpected Behavior

Filed under: General — dfitzjarrell @ 10:06

"Expect everything, I always say, and the unexpected never happens."
― Norton Juster, The Phantom Tollbooth

DBMS_LOGMNR is a great tool for finding information about what’s going on in an Oracle database. If it’s written to the redo logs DBMS_LOGMNR can find it. But sometimes it can find too much information, especially when assumptions are made regarding table ownership. Application code is often tested with ‘pristine’ database configurations, where ‘one table, one owner’ is the rule. Unfortunately that isn’t always the case. Let’s look at an example of how this can produce unwanted and unexpected results.

A database is configured where eight different users all have the same table, albeit with different data. Let’s use a new application that uses DBMS_LOGMNR to retrieve data changes for a given table. If the application was written to expect the ‘one owner, one table’ philosophy it could return a larger volume of data than the developers expected. In our database the APPLE table occurs eight times, once for each different owner. Let’s ‘fire up’ LogMiner and run an application query that could result from the single-owner theory and examine the results:


SQL> 
SQL> --
SQL> -- Let's start LogMiner
SQL> --
SQL> exec dbms_logmnr.start_logmnr(options => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.COMMITTED_DATA_ONLY)

PL/SQL procedure successfully completed.

SQL> 
SQL> --
SQL> -- We'll look for inserts and updates
SQL> -- to the apple table
SQL> --
SQL> -- We'll assume there's only one apple
SQL> -- table in the database owned by
SQL> -- BRAEBURN
SQL> --
SQL> -- The first query is, well, wrong
SQL> --
SQL> SELECT SEG_OWNER, OPERATION, SQL_REDO, SCN, CSCN
  2  from v$logmnr_contents
  3  where table_name = 'APPLE'
  4  AND operation in ('INSERT', 'UPDATE', 'DELETE')
  5  /
RED_DELICIOUS        INSERT         insert into "RED_DELICIOUS"."APPLE"("ID","DESCR") values ('1','Apple 1');             4590598788237 4590598788240
...
RED_DELICIOUS        INSERT         insert into "RED_DELICIOUS"."APPLE"("ID","DESCR") values ('1000','Apple 1000');       4590598788239 4590598788240
GRANNY_SMITH         INSERT         insert into "GRANNY_SMITH"."APPLE"("ID","DESCR") values ('1','Apple 1');              4590598788257 4590598788260
...
GRANNY_SMITH         INSERT         insert into "GRANNY_SMITH"."APPLE"("ID","DESCR") values ('1000','Apple 1000');        4590598788259 4590598788260
PINK_LADY            INSERT         insert into "PINK_LADY"."APPLE"("ID","DESCR") values ('1','Apple 1');                 4590598788278 4590598788281
...
PINK_LADY            INSERT         insert into "PINK_LADY"."APPLE"("ID","DESCR") values ('1000','Apple 1000');           4590598788280 4590598788281
BRAEBURN             INSERT         insert into "BRAEBURN"."APPLE"("ID","DESCR") values ('1','Apple 1');                  4590598788300 4590598788303
...
BRAEBURN             INSERT         insert into "BRAEBURN"."APPLE"("ID","DESCR") values ('1000','Apple 1000');            4590598788302 4590598788303
GOLDEN_DELICIOUS     INSERT         insert into "GOLDEN_DELICIOUS"."APPLE"("ID","DESCR") values ('1','Apple 1');          4590598788320 4590598788323
...
GOLDEN_DELICIOUS     INSERT         insert into "GOLDEN_DELICIOUS"."APPLE"("ID","DESCR") values ('1000','Apple 1000');    4590598788322 4590598788323
GALA                 INSERT         insert into "GALA"."APPLE"("ID","DESCR") values ('1','Apple 1');                      4590598788343 4590598788347
...
GALA                 INSERT         insert into "GALA"."APPLE"("ID","DESCR") values ('1000','Apple 1000');                4590598788346 4590598788347
ROME                 INSERT         insert into "ROME"."APPLE"("ID","DESCR") values ('1','Apple 1');                      4590598788365 4590598788368
...
ROME                 INSERT         insert into "ROME"."APPLE"("ID","DESCR") values ('1000','Apple 1000');                4590598788367 4590598788368
HONEYCRISP           INSERT         insert into "HONEYCRISP"."APPLE"("ID","DESCR") values ('1','Apple 1');                4590598788385 4590598788388
...
HONEYCRISP           INSERT         insert into "HONEYCRISP"."APPLE"("ID","DESCR") values ('1000','Apple 1000');          4590598788387 4590598788388

8000 rows selected.

SQL> 

The application wasn’t expecting 8000 rows of data since only 1000 were inserted into the APPLE table owned by BRAEBURN. The issue with the query is that the segment owner was not included in the WHERE clause; had there only been one APPLE table then the results would have been correct, that is until another user creates an APPLE table. When that happens LogMiner won’t know to search only for the BRAEBURN copy and, as can be seen by the results above, will return all inserts, updates and deletes for every table named APPLE in the database. If the application is using those results to process data for the BRAEBURN schema there will be a very large number of errors for ‘bad data’.

Let’s try this again, this time restricting the query to only the BRAEBURN schema:


SQL> 
SQL> --
SQL> -- There's MUCH more data here than
SQL> -- we expected
SQL> --
SQL> -- Let's try again and tell LogMiner
SQL> -- whose table to look for
SQL> --
SQL> 
SQL> SELECT SEG_OWNER, OPERATION, SQL_REDO, SCN, CSCN
  2  from v$logmnr_contents
  3  where table_name = 'APPLE'
  4  and seg_owner = 'BRAEBURN'
  5  AND operation in ('INSERT', 'UPDATE', 'DELETE')
  6  /
BRAEBURN             INSERT         insert into "BRAEBURN"."APPLE"("ID","DESCR") values ('1','Apple 1');                  4590598788300 4590598788303
...
BRAEBURN             INSERT         insert into "BRAEBURN"."APPLE"("ID","DESCR") values ('1000','Apple 1000');            4590598788302 4590598788303

1000 rows selected.

SQL> 

Now the query returns the expected results, and any processing done with that data will (hopefully) not encounter errors.

Assumptions can create unforseen problems in applications, especially when those applications touch a database. As mentioned previously not every database is built on the ‘one owner, one table’ mantra and it’s a very good idea to test code where that condition does not exist. Doing so can prevent any number of confusing mishaps.

If you expect something, make sure you get what you expected.

Create a free website or blog at WordPress.com.