• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 808
  • Last Modified:

v$logmnr_contents with username as unknown

I applied logminer to find who deleted a lot of rows in one table. View v$logmnr_contents give me details of that operation. Unfortunately, minimum supplemental logging was not active when archived redo logs were generated, and when I query v$logmnr_contents, I found columns "username" and "session_info" as "unknown".

Would be there any chance to deduce what user did that operation? Maybe using timestamp to determine what users were logged at that time? Or using column Log_ID?
2 Solutions
slightwv (䄆 Netminder) Commented:
I assume you do not have auditing turned on.

I don't know of a way.  You can check the listener.log and get lucky if the user that did this logged in with a new connection but that really isn't a good guess since I stay logged in for days/weeks from the same sql session.
johnsoneSenior Oracle DBACommented:
It has been a very long time since I did this, so I'm going from memory.

V$LOGMNR_CONTENTS is a view.  In the underlying tables of that view, the USER# is stored and not the name.  A join is done to get the name.  If the user cannot be found in the current user table UNKOWN is listed.  You may be able to find it by user the user number in your current user table, or the user may have been dropped.

I don't remember exactly which tables that is stored in, but it is there, you just have to dig for it.  However, if the user has been dropped, you are likely out of luck.

Also, I believe that operations done on behalf of background processes are logged as UNKNOWN, but if it is a user table you are looking at, that shouldn't be the case here.

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now