4 Replies Latest reply: Feb 5, 2013 11:23 AM by Pavan Kumar RSS

    CTAS throws ORA-12801 , ORA-01652

    KSG
      Hello,

      10.2.0.4.0 on Solaris

      I'm running the below CTAS, it's throwing ORA error ORA-12801 , ORA-01652

      Executed: CREATE TABLE Messages TABLESPACE abc_DATA NOLOGGING
      PARALLEL 8 PCTFREE 0 PCTUSED 40 AS
      SELECT
      f.ss,
      f.date,
      f.transtype,
      f.seq
      , f.campaign
      , f.cell
      , f.d_consumer
      , f.dim_cme
      , f.actualdate
      , f.email_type
      , f.Occurred
      , f.Days

      FROM
      CME f,CMES s
      WHERE
      s.ss = f.ss

      Enough temp and default tablespace space is available.

      Thanks
      KSG

      Edited by: KSG on Feb 6, 2013 12:55 PM
        • 1. Re: CTAS throws ORA-12801 , ORA-01652
          Osama_Mustafa
          check Mos Note
          During CTAS (Create Table as Select) ORA-1652 is Reported on the Data Tablespace [ID 577643.1]
          • 2. Re: CTAS throws ORA-12801 , ORA-01652
            KSG
            Thanks Osama,

            I have 75G of free space in the tablespace
            • 3. Re: CTAS throws ORA-12801 , ORA-01652
              Osama_Mustafa
              I have 75G of free space in the tablespace
              select tablespace_name, SUM(bytes_used), sum(bytes_free)
              from v$temp_space_header
              where tablespace_name = ‘TEMP’
              group by tablespace_name;
              Run this query more than one time and see each the space of Temp.
              • 4. Re: CTAS throws ORA-12801 , ORA-01652
                Pavan Kumar
                Hi,

                After looking at the issue on first instance on error code ORA-12801 and parallel clause - then you must refer to parallel processing, at the final result it would consolidate the result (either sorting) using temp ==> so maps to ORA-06152
                So that's how link travels from ORA-12801 => ORA-01652.

                So, now we need to understand why parallel QC (query co-ordinator raised an exception). I don't want to jump for temp space, set the below event and re-run for additional information for parallel
                ALTER SYSTEM SET EVENTS '10397 trace name context forever, level 1';
                Now, this event enables me to suppress the and it would wait to get the real error underneath the QC -> Parallel slave which is hitting the problem.

                - Pavan Kumar N