This discussion is archived
6 Replies Latest reply: Feb 5, 2013 12:18 AM by Shaan_dmp RSS

help on query

Shaan_dmp Explorer
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    Sorry, I didn't get much help from the url you recommended
  • 3. Re: help on query
    Purvesh K Guru
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    Many thanks, it worked

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points