Hi,
I have a question about the Oracle auditing functionality and how to use the created data.
For test purposes, I collect some DML data from 4 tables in our schema using Oracle Auditing.
Now, I can see in the dba_common_audit_trail view a high number of entries and inside these entries, I have SQL_TEXT and SQL_BIND column.
To reproduce the original workload, I need to merge the SQL_BIND information into the SQL_TEXT to create a bundle of INSERT, UPDATE, DELETE statements for testing.
Is there any functionality to merge this data or how can I achieve the required results?
Sample data:
SQL_TEXT =
'INSERT INTO NAINFOWLTTOSAP ( NAID, NAART, STATUS, SATZSUMME, UEBERTRAGENDURCH, WIEDERHOLUNGSZAEHLER, BENUTZERNR, KLASSE, ERSTELLDATUM, BEARBEITUNGSDATUM, ARCHIVDATUM, FEHLERNR, FEHLERTEXT, BUCHUNGNR, LASTSAPTRAN, LASTSAPTRANNR ) VALUES ( :B16 , :B15 , :B14 , :B13 , :B12 , :B11 , :B10 , :B9 , :B8 , :B7 , :B6 , :B5 , :B4 , :B3 , :B2 , :B1 )'
SQL_BIND =
' #1(7):2808660 #2(3):500 #3(4):2001 #4(1):2 #5(1):7 #6(1):0 #7(3):333 #8(1):7 #9(17):1/7/2021 14:27:52 #10(0): #11(0): #12(0): #13(0): #14(11):LVS18759643 #15(0): #16(0): `
Any help is appreciated!
I'm unsure what you mean by "merge this data". Audit is for you to have a record of what happened; it's not intended for use to reconstruct a workload. If that's your goal you may want to look into Real Application Testing.
That said you can split the values for the bind variables out using your favourite CSV-to-rows trick, for example:
with rws as (
select '#1(7):2808660 #2(3):500 #3(4):2001 #4(1):2 #5(1):7 #6(1):0 #7(3):333 #8(1):7 #9(17):1/7/2021 14:27:52 #10(0): #11(0): #12(0): #13(0): #14(11):LVS18759643 #15(0): #16(0):' vs
from dual
)
select regexp_substr ( vs, '[^#]+', 1, r )
from rws, lateral (
select level r from dual
connect by level <= regexp_count ( vs, '#[0-9]+' )
);
REGEXP_SUBSTR(VS,'[^#]+',1,R)
1(7):2808660
2(3):500
3(4):2001
4(1):2
5(1):7
6(1):0
7(3):333
8(1):7
9(17):1/7/2021 14:27:52
10(0):
11(0):
12(0):
13(0):
14(11):LVS18759643
15(0):
16(0):Then run the corresponding statement using these.
How exactly you do this depends on your application and your goal. You could use these to create a series of SQL scripts, but it's tough to make these reflect the workload on the application. There may be (non-SQL) code that processes data between statements in a transaction, concurrency effects, etc.