4 Replies Latest reply on Aug 7, 2019 12:52 PM by Wernfried

    Getting ORA-01792: maximum number of columns in a table or view is 1000 on remote DB

    Wernfried

      I have a query as this but when I run it I get an error:

       

      SELECT 
          ... about 500 columns
      FROM PRSDBNBI.F_F1526726705_LTECELL_DH@prs t1
          JOIN PRSDBNBI.F_LTECELLPI0_LTECELL_DH@prs t2 USING (LTECELLID,STARTTIME,HOUR,DSTOFFSET,RULEID)
          JOIN PRSDBNBI.F_LTECELLPI1_LTECELL_DH@prs t3 USING (LTECELLID,STARTTIME,HOUR,DSTOFFSET,RULEID)
          JOIN PRSDBNBI.F_TNN000010_LTECELL_DH@prs t4 USING (LTECELLID,STARTTIME,HOUR,DSTOFFSET,RULEID)
          JOIN PRSDBNBI.F_TNN000019_LTECELL_DH@prs t5 USING (LTECELLID,STARTTIME,HOUR,DSTOFFSET,RULEID)
          JOIN PRSDBNBI.F_TNN000081_LTECELL_DH@prs t6 USING (LTECELLID,STARTTIME,HOUR,DSTOFFSET,RULEID)
          JOIN PRSDBNBI.F_TNN000125_LTECELL_DH@prs t7 USING (LTECELLID,STARTTIME,HOUR,DSTOFFSET,RULEID)
          JOIN PRSDBNBI.F_TNN000220_LTECELL_DH@prs t8 USING (LTECELLID,STARTTIME,HOUR,DSTOFFSET,RULEID)
          JOIN PRSDBNBI.F_TNN000250_LTECELL_DH@prs t9 USING (LTECELLID,STARTTIME,HOUR,DSTOFFSET,RULEID)
          JOIN PRSDBNBI.F_TNN000252_LTECELL_DH@prs t10 USING (LTECELLID,STARTTIME,HOUR,DSTOFFSET,RULEID)
          JOIN PRSDBNBI.F_TNN000269_LTECELL_DH@prs t11 USING (LTECELLID,STARTTIME,HOUR,DSTOFFSET,RULEID)
          LEFT OUTER JOIN PRSDBNBI.F_TNN000279_LTECELL_DH@prs t12 USING (LTECELLID,STARTTIME,HOUR,DSTOFFSET,RULEID)
          LEFT OUTER JOIN PRSDBNBI.F_TNN000320_LTECELL_DH@prs t13 USING (LTECELLID,STARTTIME,HOUR,DSTOFFSET,RULEID)
      WHERE STARTTIME = DATE '2019-07-29'
          AND RULEID = 10000002;
      
      ORA-01792: maximum number of columns in a table or view is 1000
      ORA-02063: preceding line from PRS
      
      
      
      

          

       

      The total number of columns in all selected tables exceeds 1000 (total columns), I would expect such error only if I run "SELECT * ..."

      Obviously I select less than 1000 columns.

       

      Yesterday I had to add the 13th table to the query. With 12 tables it was running fine, although the total number of columns in all selected table was already exceeding 1000.

      I don't get any error if I run the same query directly on the remote database.

       

       

       

      Any idea how to get rid of this error? Maybe a (undocumented) hint?

       

      I run the query in different flavors. In principle I would expect the same behavior on this query, but it runs without any problem:

       

      SELECT 
          ... about 500 columns
      FROM PRSDBNBI.F_F1526726705_LTECELL_D@prs t1
          JOIN PRSDBNBI.F_LTECELLPI0_LTECELL_D@prs t2 USING (LTECELLID,STARTTIME)
          JOIN PRSDBNBI.F_LTECELLPI1_LTECELL_D@prs t3 USING (LTECELLID,STARTTIME)
          JOIN PRSDBNBI.F_TNN000010_LTECELL_D@prs t4 USING (LTECELLID,STARTTIME)
          JOIN PRSDBNBI.F_TNN000019_LTECELL_D@prs t5 USING (LTECELLID,STARTTIME)
          JOIN PRSDBNBI.F_TNN000081_LTECELL_D@prs t6 USING (LTECELLID,STARTTIME)
          JOIN PRSDBNBI.F_TNN000125_LTECELL_D@prs t7 USING (LTECELLID,STARTTIME)
          JOIN PRSDBNBI.F_TNN000220_LTECELL_D@prs t8 USING (LTECELLID,STARTTIME)
          JOIN PRSDBNBI.F_TNN000250_LTECELL_D@prs t9 USING (LTECELLID,STARTTIME)
          JOIN PRSDBNBI.F_TNN000252_LTECELL_D@prs t10 USING (LTECELLID,STARTTIME)
          JOIN PRSDBNBI.F_TNN000269_LTECELL_D@prs t11 USING (LTECELLID,STARTTIME)
          LEFT OUTER JOIN PRSDBNBI.F_TNN000279_LTECELL_D@prs t12 USING (LTECELLID,STARTTIME)
          LEFT OUTER JOIN PRSDBNBI.F_TNN000320_LTECELL_D@prs t13 USING (LTECELLID,STARTTIME)
      WHERE STARTTIME = DATE '2019-07-29';
      
      

       

      The remote database is version 11.2.0.4.0,

      The local database is version 11.2.0.3.0 (will be upgraded soon)

       

      Kind Regards

      Wernfried

        • 1. Re: Getting ORA-01792: maximum number of columns in a table or view is 1000 on remote DB
          MihaiF

          Hi Wernfried

           

          According to the My Oracle Support, ORA-01792 is caused due to an unpublished bug.

          ORA-01792: maximum number of columns in a table or view is 1000 on remote DB

           

          Oracle suggests to alter the query and/or view definitions to avoid the error. However in cases where the SQL cannot be adjusted then the checking can be disabled by:

          SQL> alter system set "_fix_control"='17376322:OFF';

           

          Workaround

          The preference is to alter the query and/or view definitions to avoid  the error. However in cases where the SQL cannot be adjusted then the checking can be disabled by setting "_fix_control" to "17376322:OFF".

           

          Alternatively an interim patch for this fix (patch 19509982) can be applied to disable the error by default.

           

          Thanks,

          MihaiF

          • 2. Re: Getting ORA-01792: maximum number of columns in a table or view is 1000 on remote DB
            Jonathan Lewis

            @MihaiF,

             

            That may be the relevant bug, but the patch says that a patch for 12.1.0.2 is a prerequisite for the fix, and the OP is on 11.2.0.3/4

             

            @Wernfried

            This problem has shown up before now on the forum (Oracle 12c: ORA-01792 but not in Oracle 11g ) and then it was a  case of using "select *" in the query, or (in a model I made: https://jonathanlewis.wordpress.com/2015/03/27/ansi-expansion/  ) select 'literal' ...

             

            Yout "select 500 columns" suggests you haven't used "select *", but maybe one of your columns is a literal and the hack of changing it to an inline (select 'literal' from dual) might have some effect.  Otherwise you could use the dbms_sql2 package I mention in the link to see what Oracle is generating from the 12 table and 13 table joins to see where it's getting the >1,000 columns from.

             

            Regards

            Jonathan Lewis

            • 3. Re: Getting ORA-01792: maximum number of columns in a table or view is 1000 on remote DB
              Wernfried

              In deed, my query is like this

               

              SELECT /*+ 
              CARDINALITY(t1 20510) 
              CARDINALITY(t2 20510) 
              CARDINALITY(t3 20510) 
              CARDINALITY(t4 20510) 
              CARDINALITY(t5 20510) 
              CARDINALITY(t6 20510) 
              CARDINALITY(t7 20510) 
              CARDINALITY(t8 20510) 
              CARDINALITY(t9 20510) 
              CARDINALITY(t10 20510) 
              CARDINALITY(t11 20510) 
              CARDINALITY(t12 20510) 
              CARDINALITY(t13 20510) 
               */ 
              LTECELLID AS MO_ID
              ,TO_TIMESTAMP_TZ(TO_CHAR(STARTTIME, 'YYYYMMDD')||HOUR||'+'||LPAD(TO_CHAR(1+DSTOFFSET/60), 2, '0'), 'YYYYMMDDHH24TZH') AT TIME ZONE 'Europe/Zurich' AS START_TIME
              ,DSTOFFSET, HOUR
              ... about 500 plain column names
              
              

               

               

              If I remove `TO_TIMESTAMP_TZ(...` then it is working fine!

              This line is used to transform STARTTIME+HOUR+DSTOFFSET into proper TIMESTAMP WITH TIME ZONE value.

               

              I will try different styles to achieve the transformation.

               

              Thank you for the hint

              Wernfried

              • 4. Re: Getting ORA-01792: maximum number of columns in a table or view is 1000 on remote DB
                Wernfried

                I found a solution, it works with this one:

                 

                FROM_TZ(CAST(STARTTIME AS TIMESTAMP) + NUMTODSINTERVAL(HOUR, 'HOUR'), '+'||LPAD(TO_CHAR(1+DSTOFFSET/60), 2, '0')||':00') AT LOCAL AS START_TIME
                

                 

                Of course, I have to ensure that my SESSIONTIMEZONE is set properly to "Europe/Zurich" but this is no issue.

                 

                Thanks for your help

                Wernfried