cancel
Showing results for 
Search instead for 
Did you mean: 

Table entries got deleted ,how can we find who has deleted these entries

0 Kudos

Hi Team,

We are using ECC6 /Oracle,few days back 2000 entries got deleted from one table.

How can we get the log for deleted entries with all the details like User ID ,Date and time of Deletion.

I have already checked STAD and DBACOCKPIT for SQL queries of deletion of records but could not find any log .

Kindly help me to resolve this issue.

Accepted Solutions (0)

Answers (6)

Answers (6)

0 Kudos

Thanks for your Responses.

I have table names of Deleted Table but not sure about a timestamp.

I have also checked in DBA_HIST_ACTIVE_SESS_HISTORY but could not found entry for deleted Table.

Former Member
0 Kudos

Hi

you might have to work in combination .

1. get the awr report for approximate time interval of incident.

2. then go to DB02 and run the report filtered by same time

awr report will give you SQL statement has been fired .

DB02 will give you the T-code by which the Query has been fired.

and if query has been fired from SQL level. then you can check the OS login logs for that time .

and if BASH is available even you can get the History of command fired .

Regards

Dishant.

Former Member
0 Kudos

Hello Manish,

Based on your orginal question, I assume you are asking if at all we can found any logs for deletion (or changes) to table entries in SAP.

If that is DDIC table the case and if 'change log' indicator activated in technical settings of this table, then answer is 'Yes'. You will see the change logs (incl deletions) in CDHDR/CDPOS tables. I am not infront of SAP system right now, but I guess there is one profile parameter also related to this.

Also if it is very recent, then with application logs (SLG1) , ST03 and ST04, you can figure out the details.

I am assume, you know table name and time frame to search. If that is the case oracle DBA View 'DBA_HIST_ACTIVE_SESS_HISTORY' will give you details.. here search for DELETE statement on that table.

I hope it may help you.

Regards,

Krishna

0 Kudos

if your AWR/ASH retention time is long enough you can try to find out in the ACTIVE SESSION HISTORY the delete operations agains that table.

If you have high enought kernel, the ASH will also have information about the SAP user who did the operation (if done at SAP level) on the field CLIENT_IDENTIFIER (see SAP Note 1480392 for information about that)

If it has been done at OS level, you can also find information on the ASH, but then you can see the program on the field PROGRAM and other information on other fields.

If your AWR retention is too small then you will have to use the logminer as indicated, but that will not provide information about WHO did it, only when it was done.

divyanshu_srivastava3
Active Contributor
0 Kudos

Yes, under controlled situation, you can find some information(at least date and time with what was deleted) even if deleted from SQL level.

You can use ORACLE's log miner - Using LogMiner to Analyze Redo Log Files

Or have a look at this note: 1128990 - Using Oracle Logminer with Oracle >= 10g


Regards,

Divyanshu

divyanshu_srivastava3
Active Contributor
0 Kudos

And yes, if you want to safe-guard tables from unwanted access/deletion, use authorization objects and enable table logging for critical tables.

Reagan
Advisor
Advisor
0 Kudos

If the deletion of data was done from the SQL level then there is no way you can find it.

Regards

RB