6 Replies Latest reply: Feb 5, 2013 2:18 AM by Shaan_dmp RSS

    help on query

    Shaan_dmp
      Hi,

      Could you please help me on a query, I would like to pull report for two month, last month and current

      TABLE_OWNER,     TABLE_NAME, LAST_MONTH, CURRENT_MONTH, TABLESPACE_NAME




      SELECT TABLE_OWNER,
                TABLE_NAME,
      count(cm.PARTITION_NAME) AS "LAST_MONTH",
      TABLESPACE_NAME
      FROM DBA_TAB_PARTITIONS cm,
      WHERE TABLE_OWNER like '<Schema_name>' and Partition_name like '%201302%'
      group by table_owner,table_name, tablespace_name
      ORDER BY table_name,table_owner;


      Thanks in advance

      regards,
      Shaan
        • 1. Re: help on query
          asahide
          Hi,

          HTH
          <<http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:592469000346200132>>

          Regards,
          • 2. Re: help on query
            Shaan_dmp
            Sorry, I didn't get much help from the url you recommended
            • 3. Re: help on query
              Purvesh K
              Shaan_dmp wrote:
              Hi,

              Could you please help me on a query, I would like to pull report for two month, last month and current
              Perhaps something below way:
              select table_owner,
                table_name,
                count (cm.partition_name) as "LAST_MONTH",
                tablespace_name
              from dba_tab_partitions cm
              where table_owner like '<Schema_name>'
              and (instr(partition_name, to_char(sysdate, 'YYYYMM')) > 0 or instr(partition_name, to_char(sysdate, 'YYYYMM') - 1) > 0)
              group by table_owner,
                table_name,
                tablespace_name
              order by table_name,
                table_owner;
              • 4. Re: help on query
                Shaan_dmp
                Appreciate if you can share any other ideas


                Report need to show below informations..., we would like to compare between last and current month if same number of partition are created for same schema tables

                TABLE_OWNER, TABLE_NAME, LAST_MONTH, CURRENT_MONTH, TABLESPACE_NAME

                Edited by: Shaan_dmp on Feb 5, 2013 11:15 AM
                • 5. Re: help on query
                  Purvesh K
                  Shaan_dmp wrote:
                  Appreciate if you can share any other ideas


                  Report need to show below informations..., we would like to compare between last and current month if same number of partition are created for same schema tables

                  TABLE_OWNER, TABLE_NAME, LAST_MONTH, CURRENT_MONTH, TABLESPACE_NAME

                  Edited by: Shaan_dmp on Feb 5, 2013 11:15 AM
                  This might do the job for you
                  select table_owner,
                    table_name,
                    count ( case when instr(partition_name, to_char(sysdate, 'YYYYMM')) > 0 then cm.partition_name else null end) "CURR_MONTH",
                    count ( case when instr(partition_name, to_char(sysdate, 'YYYYMM') - 1) > 0 then cm.partition_name else null end) "LAST_MONTH",
                    tablespace_name
                  from dba_tab_partitions cm
                  where table_owner like '<Schema_name>'
                  and (instr(partition_name, to_char(sysdate, 'YYYYMM')) > 0 or instr(partition_name, to_char(sysdate, 'YYYYMM') - 1) > 0)
                  group by table_owner,
                    table_name,
                    tablespace_name
                  order by table_name,
                    table_owner;
                  • 6. Re: help on query
                    Shaan_dmp
                    Many thanks, it worked