6 Replies Latest reply on Feb 7, 2013 9:47 AM by Manjusha Muraleedas

    last month query

    user522961
      Hi,
      on 11g, I want to run a query that looks for SQL_ID of january (last month at any time I run this query, it should run 5th day of any month). And that's what happens :
      SQL> select to_char(sysdate, 'mm')-01 from dual;
      
      TO_CHAR(SYSDATE,'MM')-01
      ------------------------
                             1
      
      SQL> select sql_id from v$sqlarea where LAST_ACTIVE_TIME like '%(select to_char(sysdate, 'mm')-01 from dual)%';
      select sql_id from v$sqlarea where LAST_ACTIVE_TIME like '%(select to_char(sysdate, 'mm')-01 from dual)%'
                                                                                           *
      ERROR at line 1:
      ORA-00933: SQL command not properly ended
      How can I do that ?

      Thanks for help.
        • 1. Re: last month query
          Manjusha Muraleedas
          Try this
          select sql_id from v$sqlarea where LAST_ACTIVE_TIME like '%(select to_char(sysdate, ''mm'')-01 from dual)%';
          or

          did u mean't this?
          select sql_id,LAST_ACTIVE_TIME from v$sqlarea where to_char(LAST_ACTIVE_TIME,'mm')  like  to_char(add_months(sysdate,-1), 'mm')     
          Edited by: Manjusha Muraleedas on ७ फ़रवरी, २०१३ १२:५० अपराह्न
          1 person found this helpful
          • 2. Re: last month query
            user522961
            Thank you.

            How can I find SQL_IDs for last month ? Since "LIKE" operator may look for nn in any part of LAST_ACTIVE_TIME.
            • 3. Re: last month query
              Manjusha Muraleedas
              Previous query will return dates for last month.

              'MM' stands for month only.

              you may use = instead of LIKE. both will work alike.
              1 person found this helpful
              • 4. Re: last month query
                user522961
                realy thank you.

                How can I find all sql_id on last month (now january) ?
                If I use
                LAST_ACTIVE_TIME=to_char(add_months(sysdate,-1),

                query returns the SQL_ID for 7 of January.



                Regards.
                • 5. Re: last month query
                  jeneesh
                  where LAST_ACTIVE_TIME >=  trunc(trunc(sysdate,'mm')-1,'mm')
                  and LAST_ACTIVE_TIME < trunc(sysdate,'mm')
                  • 6. Re: last month query
                    Manjusha Muraleedas
                    create table MYDATES
                    (
                      MYDATE date
                    )
                    ;           
                    
                    INSERT INTO MYDATES SELECT SYSDATE-LEVEL FROM DUAL   CONNECT BY   LEVEL<=100
                    
                    
                    SQL>      SELECT MYDATE FROM MYDATES T WHERE       to_char(T.MYDATE,'mm')  = to_char(add_months(sysdate,-1), 'mm')      ;
                    
                    MYDATE
                    -----------
                    31/01/2013
                    30/01/2013
                    29/01/2013
                    28/01/2013
                    27/01/2013
                    26/01/2013
                    25/01/2013
                    24/01/2013
                    23/01/2013
                    22/01/2013
                    21/01/2013
                    20/01/2013
                    19/01/2013
                    18/01/2013
                    17/01/2013
                    16/01/2013
                    15/01/2013
                    14/01/2013
                    13/01/2013
                    12/01/2013
                    
                    MYDATE
                    -----------
                    11/01/2013
                    10/01/2013
                    09/01/2013
                    08/01/2013
                    07/01/2013
                    06/01/2013
                    05/01/2013
                    04/01/2013
                    03/01/2013
                    02/01/2013
                    01/01/2013
                    
                    31 rows selected
                    1 person found this helpful