How to manually corrupt the Oracle CLOB data

Question

I'm wondering if there's any way to manually corrupt the CLOB data for testing purpose.

I can find the steps for intensional block corruption, but can't find anything for the individual data in a table. Can anyone help me with this?

Below is what I'm trying to do and I need help for step 1:

1. Prepare the corrupted CLOB data
2. Run expdb and get ORA-01555 error
3. Test if my troubleshooting procedure works ok

Some background:

• DB: Oracle 12.2.0.1 SE2

• OS: Windows Server 2016

• The app we're using (from the 3rd party) seems to occasionally corrupt the CLOB data when a certain type of data gets inserted in a table. We don't know what triggers it. The corruption doesn't affect the app's function, but leaving it unfixed gives the following error when running expdb for daily backup:

  ORA-01555: snapshot too old: rollback segment number

• CLOB consists of a mix of alphanumeric characters and line breaks. It gets inserted by the app, no manual insert takes place

• Fixing/replacing the app isn't an option, so we've got a fixing procedure with us.

• I took over this from another engineer (who's left already), but since then the app is happily working and no problem has occurred so far. I want to test run the fixing procedure in DEV environment, but the app doesn't reproduce the problem for me.

• So I thought if I can manually prepare the "broken" CLOB for testing purpose

Answer 1

So this looks like it is caused by a known bug:

• https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=364607910994084&parent=DOCUMENT&sourceId=833635.1&id=787004.1&_afrWindowMode=0&_adf.ctrl-state=3xr6s00uo_200
• https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=364470181721910&id=846079.1&_afrWindowMode=0&_adf.ctrl-state=3xr6s00uo_53
• https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=364481844925661&id=833635.1&_afrWindowMode=0&_adf.ctrl-state=3xr6s00uo_102

The main point here is that the corruption isn't caused by anything inherant in the data, but is more likely caused by something like concurrent access to the LOB by multiple updates (application or end-user behavior), or just by apparently random chance. As such, I doubt that there's any way for you to easily force this condition in order to validate your test for it.