5 Replies Latest reply: Oct 28, 2008 1:08 PM by 594273 RSS

    ora-01830

    575295
      I'm trying to run sql to grab just the weekend partitions

      We have table partitions named as such

      PAR20080302
      PAR20080303
      ...

      When I run this

      select * from (
      select to_char(to_date(substr(partition_name,4),'YYYYMMDD'),'DY') pardate
      from dba_tab_partitions
      where table_name like 'PAR')

      I get output like this

      Partition_name pardate
      --------------- -------
      PAR20080325 TUE
      PAR20080326 WED

      Now when I try to grab just the partitions from Saturday and Sunday by running this

      select * from (
      select to_char(to_date(substr(partition_name,4),'YYYYMMDD'),'DY') pardate
      from dba_tab_partitions
      where table_name like 'PAR')
      where pardate in ('SAT','SUN')

      it bombs with ORA-01830

      How can I limit the results to just the weekends?

      Thanks
        • 1. Re: ora-01830
          Peter Gjelstrup
          Hi,

          Could it be that have table partions which name is longer than 11 characters?

          This part of your query works fine:
          select to_char(to_date(substr('PAR20080302',4),'YYYYMMDD'),'DY') pardate from dual;
          Unless you encounter a partion with a longer name:
          select to_char(to_date(substr('PAR20080302_more',4),'YYYYMMDD'),'DY') pardate from dual;
          ORA-01830: date format picture ends before converting entire input string
          Depending of the nature of the other partion names you could change your arguments to substr:
          select to_char(to_date(substr('PAR20080302_more',4,8),'YYYYMMDD'),'DY') pardate from dual;
          Regards
          Peter
          • 2. Re: ora-01830
            Anurag Tibrewal
            Hi,

            The behaviour you have mentioned in something strange. So I was just interested in it. Do let me know if you have solved the issue. If yes then how and if no then could you please post the exact output and query.

            I have also noticed that in where clause you have used table_name like 'PAR', but it should be something like table_name like 'PAR%'. You might have mistyped this while posting the query and would have used it correctly in your implementation. Correct?

            Regards
            • 3. Re: ora-01830
              Laurent Schneider
              this is a very common mistake
              select to_char(to_date(substr(partition_name,4),'YYYYMMDD'),'DY') pardate
              from dba_tab_partitions
              where table_name like 'PAR%'
              the function to_date is possibly executed for every partition, the where condition is evaluated after the to_date.

              you probably want
              select to_char(to_date(substr(partition_name,4),'YYYYMMDD'),'DY') pardate
              from (
                select rownum, partition_name
                from dba_tab_partitions
                where table_name like 'PAR%'
              )
              rownum should prevent view merging.

              HTH
              • 4. Re: ora-01830
                575295
                Thanks for the replies. When I get a chance I'll test them out and update
                • 5. Re: ora-01830
                  594273
                  I'm trying to run sql to grab just the weekend partitions on table AR_ATB_DATA

                  We have table partitions named as such

                  AR_ATB_DATA_2006
                  AR_ATB_DATA_2007
                  AR_ATB_DATA_2008
                  AR_ATB_DATA_2009
                  ...

                  When I run this below query, I am getting the following error:


                  select to_char(to_Date(substr(partition_name,6),'YYYY-MM-DD'),'DD-MON-YYYY') from (select partition_name from dba_tab_partitions where table_name='AR_ATB_DATA' and table_owner='ADBA' order by 1 desc) where rownum < 2
                  *
                  ERROR at line 1:
                  ORA-01841: (full) year must be between -4713 and +9999, and not be 0

                  Can you please help me how to avoid this error