v$logmnr_contents with username as unknown

Posted on 2012-09-13
Last Modified: 2012-09-18
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?
Question by:miyahira
    LVL 76

    Assisted Solution

    by:slightwv (䄆 Netminder)
    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.
    LVL 34

    Accepted Solution

    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

    Courses: Start Training Online With Pros, Today

    Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

    Join & Write a Comment

    How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
    From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
    Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
    Via a live example, show how to take different types of Oracle backups using RMAN.

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now