This discussion is archived
4 Replies Latest reply: Feb 5, 2013 9:23 AM by PavanKumar RSS

CTAS throws ORA-12801 , ORA-01652

KSG Explorer
Currently Being Moderated
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 Oracle ACE
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    Thanks Osama,

    I have 75G of free space in the tablespace
  • 3. Re: CTAS throws ORA-12801 , ORA-01652
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    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
    PavanKumar Guru
    Currently Being Moderated
    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

Legend

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