6 Replies Latest reply on Jan 1, 2013 6:09 PM by rp0428

    How to display last 10 minutes data  only using sql query

    982278
      Hi,

      Presently, I'm using version is,

      Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
      PL/SQL Release 11.1.0.6.0 - Production
      CORE 11.1.0.6.0 Production
      TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
      NLSRTL Version 11.1.0.6.0 - Production


      So, please consider SCOTT Schema to resolve my issue,

      I want to display only last 10 minutes what records are inserted or updated or deleted.

      Please provide in many ways!!

      ThankQ!!
        • 1. Re: How to display last 10 minutes data  only using sql query
          sb92075
          979275 wrote:
          Hi,

          Presently, I'm using version is,

          Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
          PL/SQL Release 11.1.0.6.0 - Production
          CORE 11.1.0.6.0 Production
          TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
          NLSRTL Version 11.1.0.6.0 - Production


          So, please consider SCOTT Schema to resolve my issue,

          I want to display only last 10 minutes what records are inserted or updated or deleted.

          Please provide in many ways!!

          ThankQ!!
          This could be done via DBMS_LOGMNR; but I doubt it is worthwhile to do so.
          • 2. Re: How to display last 10 minutes data  only using sql query
            Peter vd Zwan
            Hi,

            See below:
            select
              *
            
            from
              emp
            ;
            /*
            EMPNO ENAME      JOB        MGR HIREDATE      SAL    COMM DEPTNO
            ----- ---------- --------- ---- --------- ------- ------- ------
             7369 SMITH      CLERK     7902 17-DEC-80     800             20 
             7499 ALLEN      SALESMAN  7698 20-FEB-81    1600     300     30 
             7521 WARD       SALESMAN  7698 22-FEB-81    1250     500     30 
             7566 JONES      MANAGER   7839 02-APR-81    2975             20 
             7654 MARTIN     SALESMAN  7698 28-SEP-81    1250    1400     30 
             7698 BLAKE      MANAGER   7839 01-MAY-81    2850             30 
             7782 CLARK      MANAGER   7839 09-JUN-81    2450             10 
             7788 SCOTT      ANALYST   7566 19-APR-87    3000             20 
             7839 KING       PRESIDENT      17-NOV-81    5000             10 
             7844 TURNER     SALESMAN  7698 08-SEP-81    1500       0     30 
             7876 ADAMS      CLERK     7788 23-MAY-87    1100             20 
             7900 JAMES      CLERK     7698 03-DEC-81     950             30 
             7902 FORD       ANALYST   7566 03-DEC-81    3000             20 
             7934 MILLER     CLERK     7782 23-JAN-82    1300             10 
            
             14 rows selected 
            */
            select
              *
            
            from
              emp
            minus
            select
              *
            
            from
              emp
               as of timestamp (systimestamp - interval '10' minute)
            ;
            /*
            EMPNO ENAME      JOB        MGR HIREDATE      SAL    COMM DEPTNO
            ----- ---------- --------- ---- --------- ------- ------- ------
            
             0 rows selected 
            */
            update
              emp
            set
              ename = ename || ' x'
            where
              empno = 7934
            ;
            --1 rows updated.
            
            select
              *
            
            from
              emp
            minus
            select
              *
            
            from
              emp
               as of timestamp (systimestamp - interval '10' minute)
            ;
            /*
            EMPNO ENAME      JOB        MGR HIREDATE      SAL    COMM DEPTNO
            ----- ---------- --------- ---- --------- ------- ------- ------
             7934 MILLER x   CLERK     7782 23-JAN-82    1300             10 
            */
            For changes, deletes and inserts compair the two result (emp and emp as of timestamp (systimestamp - interval '10' minute) ) with each other.

            Regards,

            Peter
            1 person found this helpful
            • 3. Re: How to display last 10 minutes data  only using sql query
              Stew Ashton
              DROP TABLE EMP;
              CREATE TABLE EMP AS SELECT * FROM SCOTT.EMP;
              
              -- WAIT 5 MINUTES for SCN to propagate to flashback mapping table
              
              DELETE FROM EMP WHERE ROWNUM = 1;
              commit;
              
              UPDATE EMP SET SAL = SAL*11/10 WHERE ROWNUM = 1;
              commit;
              
              INSERT INTO EMP
              SELECT EMPNO+1, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO FROM EMP
              WHERE ROWNUM = 1;
              COMMIT;
              
              select versions_startscn, versions_starttime,
              VERSIONS_ENDSCN, VERSIONS_ENDTIME,
              VERSIONS_XID, VERSIONS_OPERATION,
              EMP.* FROM EMP 
              VERSIONS BETWEEN TIMESTAMP SYSTIMESTAMP - INTERVAL '10' MINUTE AND SYSTIMESTAMP
              where versions_operation is not null;
              • 4. Re: How to display last 10 minutes data  only using sql query
                rp0428
                Welcome to the forum!
                >
                So, please consider SCOTT Schema to resolve my issue,

                I want to display only last 10 minutes what records are inserted or updated or deleted.

                Please provide in many ways!!
                >
                Sounds like an interview question.

                What are the different ways that you have come up with?

                The easiest way I know is if you can create a materialized view log on the table. Then Oracle will do all of the work for you by keeping track of all dml to the table.

                Then you can just query the MV log to find out what rows changed.
                • 5. Re: How to display last 10 minutes data  only using sql query
                  982278
                  Hi,

                  Can you please explain with an example.

                  Thanks!!
                  • 6. Re: How to display last 10 minutes data  only using sql query
                    rp0428
                    >
                    Can you please explain with an example.
                    >
                    This sample code shows one way. After creating the MV log you add a MODIFIED_DATE column with a DEFAULT of SYSDATE.
                    Then all changes to the EMP1 table get recorded in the log including the ROWID and the date the change occured.

                    Then you can join the EMP1 table to the log table to find the EMPNO for rows that were changed (UPDATED or INSERTED) during the time period you are interested in. Note - this particular query will NOT show rows that were deleted in the time period since their EMPNO may no longer be in the emp1 table
                    DROP MATERIALIZED VIEW LOG ON EMP1
                    
                    DROP TABLE EMP1 CASCADE CONSTRAINTS
                    
                    CREATE TABLE EMP1 AS SELECT * FROM EMP
                    
                    ALTER TABLE EMP1 ADD CONSTRAINT EMP1_PK PRIMARY KEY (EMPNO)
                    
                    CREATE MATERIALIZED VIEW LOG ON EMP1
                    WITH ROWID, PRIMARY KEY, SEQUENCE
                    INCLUDING NEW VALUES;
                    
                    ALTER TABLE MLOG$_EMP1 ADD (MODIFY_DATE DATE DEFAULT SYSDATE)
                    
                    INSERT INTO EMP1 (EMPNO, ENAME, JOB) VALUES (9999, 'new name', 'new job')
                    
                    
                    SELECT * FROM EMP1 WHERE EMPNO IN (
                    SELECT EMPNO FROM MLOG$_EMP1
                    WHERE MODIFY_DATE >= SYSDATE - 1/24/6)
                    
                    EMPNO     ENAME     JOB     MGR     HIREDATE     SAL     COMM     DEPTNO
                    9999     new name     new job