alon (Prolink) asked a question.
Sometimes collections are aborted because of "Circuit Breaker".
When the "Circuit Breaker" is because of many new or missing records it is very understandable how we should research (We will check if the change of data is correct or made by some kind of an error).
But when the Circuit Breaker occurs because of many changed records we don't know how to research as there is no indication which attributes were changed for the changed records.
We would like to ask for a DB table where we can find this information.
For example, we already know about T_AV_USER_ATTR_CHANGES that holds changes where made in the user object.
We are looking for a table and a query to check what changes were made for a specific account.
I'm attaching a screenshot from my development machine with an example of collection with 20 records which were changed.
In this example that I created, the change was made in the Last Login Date attribute.
I know this because I created the changes myself. I would like to find a SQL query that will show the attributes which were changed for a specific account.
The information might be available in T_DC_SOURCEDATA_* tables.
@Boris.Lekumovich (RSA)
Thanks !
Do you know what is the difference between for example
T_DC_SOURCEDATA_ACCOUNT and T_DC_SOURCEDATA_ACCOUNT_MAP or between
T_DC_SOURCEDATA_GROUP and T_DC_SOURCEDATA_GROUP_MEM ?
I tried to write a query to fetch the changed records in accounts of a certain application.
I wrote something like this (in my example):
SELECT name, last_login_date, CAI1, CAS3, CAS4, is_disabled, object_sid
FROM T_DC_SOURCEDATA_ACCOUNT
WHERE run_id = 5003
MINUS
SELECT name, last_login_date, CAI1, CAS3, CAS4, is_disabled, object_sid
FROM T_DC_SOURCEDATA_ACCOUNT
WHERE run_id = 4998
AND name IN (SELECT name
FROM T_DC_SOURCEDATA_ACCOUNT
WHERE run_id = 5003)
I can find the changed records with this query, but still not which attribute was changed.
In my development case there are not many attributes so I can perform the query each time with 1 attribute until I will find the attribute that was changed.
For example, in my case the attribute that was changed was last_login_date:
SELECT name, last_login_date
FROM T_DC_SOURCEDATA_ACCOUNT
WHERE run_id = 5003
MINUS
SELECT name, last_login_date
FROM T_DC_SOURCEDATA_ACCOUNT
WHERE run_id = 4998
AND name IN (SELECT name
FROM T_DC_SOURCEDATA_ACCOUNT
WHERE run_id = 5003)
Is there a better (and a faster) approach to find which attribute was changed ?
T_DC_SOURCEDATA_ACCOUNT is the collected data of accounts (with all the relevant attributes)
T_DC_SOURCEDATA_ACCOUNT_MAP is the collected data of account mappings (you should see significantly less number of columns populated with information)
T_DC_SOURCEDATA_GROUP is the collected data of groups (with all the relevant attributes)
T_DC_SOURCEDATA_GROUP_MEM is the collected data of group members
with regards to the SQL query to identify the change attribute, not sure.
I would start with your preferred AI engine to suggest a working query for this use case :)
If you are able to craft such a query, please share it
Thanks !
Here is an example for a procedure that inserts into a table a comparison between 2 runs of account collections (We can create similar procedures that check other tables of T_DC_SOURCEDATA_*)
create or replace PROCEDURE p_calculate_account_changes (prev_run_id NUMBER, cur_run_id NUMBER) AS
CURSOR c_get_columns IS
SELECT column_name
FROM all_tab_columns
WHERE table_name = 'T_DC_SOURCEDATA_ACCOUNT'
AND column_name NOT IN ('NAME', 'RUN_ID', 'SRCID');
v_sql VARCHAR2(4000);
BEGIN
-- truncate table (delete the data of previous run)
EXECUTE IMMEDIATE 'TRUNCATE TABLE t_account_changes';
FOR C IN c_get_columns LOOP
v_sql := 'INSERT INTO t_account_changes ' ||
' SELECT old.name, ''' || C.column_name || ''' AS column_name, old.' || C.column_name ||
' AS old_value, new.' || C.column_name || ' AS new_value ' ||
' FROM T_DC_SOURCEDATA_ACCOUNT old, T_DC_SOURCEDATA_ACCOUNT new ' ||
' WHERE old.run_id = ' || prev_run_id || ' AND new.run_id = ' || cur_run_id ||
' AND old.name = new.name ' ||
' AND old.' || C.column_name || ' <> new.' || C.column_name;
--dbms_output.put_line(v_sql);
EXECUTE IMMEDIATE v_sql;
COMMIT;
END LOOP;
COMMIT;
END;