Oracle logmining to check the transactions on particular table

1.select supplemental_log_data_min  from v$database;

If supplemental log data is not enabled then

2.ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;


3.  To check the max of logfile.

SELECT NAME FROM V$ARCHIVED_LOG
       WHERE FIRST_TIME = (SELECT MAX(FIRST_TIME) FROM V$ARCHIVED_LOG);

4.For checking log files in specific time and date:
SELECT *
  FROM (SELECT MIN (
                     'EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '''
                  || val.name
                  || ''', OPTIONS => DBMS_LOGMNR.NEW);')
          FROM V$ARCHIVED_LOG val
        WHERE     val.first_time BETWEEN TO_TIMESTAMP (
                                             '&&START_DDMONYYYYHH24MISS.',
                                             'DDMONYYYYHH24MISS')
                                      AND TO_TIMESTAMP (
                                             '&&END_DDMONYYYYHH24MISS.',
                                             'DDMONYYYYHH24MISS')
               AND val.dest_id = 1
               )
UNION ALL
(SELECT    'EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '''
        || val.name
        || ''', OPTIONS => DBMS_LOGMNR.ADDFILE);'
   FROM V$ARCHIVED_LOG val
  WHERE     val.first_time BETWEEN TO_TIMESTAMP (
                                             '&&START_DDMONYYYYHH24MISS.',
                                             'DDMONYYYYHH24MISS')
                               AND TO_TIMESTAMP (
                                             '&&END_DDMONYYYYHH24MISS.',
                                             'DDMONYYYYHH24MISS')
       AND val.dest_id = 1
        );

It will ask Start Date & time and End date and time. (Ex : 28APR2013070000)

Take the output....and execute plsql procedures completely.

EXECUTE DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME => '/disk05/oradata/DEVT/archive/1_7315_613831530.arc',OPTIONS => DBMS_LOGMNR.NEW);
EXECUTE DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME => '/disk05/oradata/DEVT/archive/1_7316_613831530.arc', OPTIONS => DBMS_LOGMNR.ADDFILE);
.
.
.
.
EXECUTE DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME => '/disk05/oradata/DEVT/archive/1_7330_613831530.arc', OPTIONS => DBMS_LOGMNR.ADDFILE);

       
5.Start log miner:
To collect also the commit timestamp -> EXECUTE DBMS_LOGMNR.START_LOGMNR( OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.COMMITTED_DATA_ONLY);

6.  Collect the row IDs for particular seg_name and seg_owner

select distinct row_id from v$logmnr_contents where seg_name='CMS_INVENTORY' and seg_owner='LILYPLU';


7.create table  as
  select timestamp,commit_timestamp, audit_sessionid,seg_owner,seg_name,username,session#,serial#,operation_code,sql_redo,sql_undo,row_id  FROM V$LOGMNR_CONTENTS where seg_owner = 'LILYPLU' and seg_name = 'COATING_ORDER' and
  row_id in ('AAAeHJAAQAAJ0f5AAU',
'AAAeHJAAQAAJ0eLAAg',
'AAAeHJAAQAAJ0f5AAO',
'AAAeHJAAQAAJ0f2AAU',
'AAAeHJAAQAAJ0ftAAi',
'AAAeHJAAQAAJ0fZAAe',
'AAAeHJAAQAAJ0f5AAR',
'AAAeHJAAQAAJ0f2AAV',
'AAAeHJAAQAAJ0fpAAk',
'AAAeHJAAQAAJ0f5AAk',
'AAAeHJAAQAAJ0fTAAL',
'AAAeHJAAQAAJ0fpAAg',
'AAAeHJAAQAAJ0ftAAl',
'AAAeHJAAQAAJ0f5AAS'
)

8.Below query gives number of Update,insert & delete for particular owner for particular table.

select seg_owner, seg_name, username, operation, count(*) from V$LOGMNR_CONTENTS where seg_name='RUN' and seg_owner='LILYPGO'group by seg_owner, seg_name, username, operation

9. To find the user session on the audit log when the information at the logminer is not complete
==============================================================================================-=

    select * from dba_audit_session where sessionid = '123'

    :Where 123 is the value of column AUDIT_SESSIONID you took from the V$LOGMNR_CONTENTS table


select distinct audit_sessionid from v$logmnr_contents where seg_name='RUN' and seg_owner='LILYPGO';

10.Turn off the minering

    EXECUTE DBMS_LOGMNR.END_LOGMNR();


NOTES:  When we are using sql developer, it is better to divide the time period, create table, and  turn off log miner. Then start for second set of log mining session. (Example : If there is more than 1000 rows for row IDs we can't execute the script on sqldeveloper)


To get the visible timestamps:
select TO_CHAR (timestamp ,'YYYY-MON-DD HH24:MI:SS') as time,TO_CHAR (COMMIT_TIMESTAMP ,'YYYY-MON-DD HH24:MI:SS') as commit_time, AUDIT_SESSIONID,SEG_OWNER,SEG_NAME,USERNAME,session#,SERIAL#,OPERATION_CODE,SQL_REDO,SQL_UNDO,ROW_ID  from babus.miner01 where SEG_OWNER = 'LILYPLU' and SEG_NAME = 'CMS_INVENTORY' and
  row_id in ('AAAeGXAAQAAAv2GAAA');

how to connect sql server database using visual studio with asp.net and C#.

Asp.Net code

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Registration.aspx.cs" Inherits="dbconnect_web.dbconnect" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
        <div>
            <h4>Register page</h4>
            <table>
                <tr>
                    <td><asp:Label ID="username" runat="server" Text="User Name :"></asp:Label></td>
                    <td><asp:TextBox ID="input1" runat="server"></asp:TextBox></td>
                    <asp:RequiredFieldValidator ID="validate" runat="server" ControlToValidate="input1" ErrorMessage="Enter your user name" EnableClientScript="false"></asp:RequiredFieldValidator>
                    </tr>
                <tr>
                    <td><asp:Label ID="password" runat="server" Text="Password :"></asp:Label></td>
                    <td><asp:TextBox ID="input2" runat="server" TextMode="Password"></asp:TextBox></td>                 
                </tr>
                <tr>
                <td><asp:Label ID="mobile" runat="server" Text="mobile :"></asp:Label></td>
                    <td><asp:TextBox ID="input3" runat="server"></asp:TextBox></td>
                </tr>
                <tr>
                <td><asp:Label ID="email" runat="server" Text="email :"></asp:Label></td>
                    <td><asp:TextBox ID="input4" runat="server"></asp:TextBox></td>
                    </tr>
                <tr>
                    <td> <asp:Button ID="Button1" runat="server" text="Register" OnClick="button1_click" ForeColor="#66FF66" /> </td>
                </tr>
                <tr>
                    <td><asp:Label ID="label1" runat="server"></asp:Label></td>
                </tr>
                <tr>
                    <td><asp:Label ID="label2" runat="server"></asp:Label></td>
                </tr>
                <tr>
                    <td>&nbsp;</td>
                </tr>
            </table>
        </div>
    </form>
</body>
</html>

C# Code

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Text;

using System.Data.SqlClient;

namespace dbconnect_web

{

    public partial class dbconnect : System.Web.UI.Page

    {

        public static string Encryptdata(string password)

        {

            byte[] b = System.Text.ASCIIEncoding.ASCII.GetBytes(password);

            string encrypted = Convert.ToBase64String(b);

            return encrypted;

        }

        public string DecryptString(string password)

        {

            byte[] b;

            string decrypted;

            try

            {

                b = Convert.FromBase64String(password);

                decrypted = System.Text.ASCIIEncoding.ASCII.GetString(b);

            }

            catch (FormatException fe)

            {

                decrypted = "";

            }

            return decrypted;

        }

        protected void button1_click(object sender, EventArgs e)

        {

            try

            {

                // Build connection string

                SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();

                builder.DataSource = "localhost\\SQL";

                builder.UserID = "sa";

                builder.Password = "admin";

                builder.InitialCatalog = "AdventureWorks2012";

                // Connect to SQL

                Console.Write("Connecting to SQL Server ... ");

                using (SqlConnection connection = new SqlConnection(builder.ConnectionString))

                {

                    connection.Open();

                    string sql = "insert into Users values (@username,@password,@mobile,@email);";

                    SqlCommand cmd = new SqlCommand(sql, connection);

                    string user = input1.Text;

                    string password = input2.Text;                

                    string mobile= input3.Text;

                    string email = input4.Text;

                    cmd.Parameters.AddWithValue("@username", user);

                    cmd.Parameters.AddWithValue("@password", Encryptdata(password));

                    cmd.Parameters.AddWithValue("@mobile", mobile);

                    cmd.Parameters.AddWithValue("@email", email);

                    int rowsAffected = cmd.ExecuteNonQuery();

                    label1.Text = "Registration complted";

                }

            }

            catch (SqlException error)

            {

                label2.Text = error.Message;

            }

        }

    }

    

}