title: Creating a materialized view filling up the complete temp space
body: <p>I want to create a materialized view(mv) please see below SQL query. When I try to create the materialized view my temp tablespace completely being (~128g) used and given below error
  SQL Error: ORA-12801: error signaled in parallel query server P007
  ORA-01652: unable to extend temp segment by 64 in tablespace TEMP1
  12801. 00000 -  "error signaled in parallel query server %s"</p>
  
  <p>then I checked in the OEM it used parallelism 8 degree. So I disabled the parallelism using alter statement (ALTER SESSION DISABLE PARALLEL QUERY). Then the mv ran long and took several hours and got created. Please suggest is there any approaches to create it with out using much space temp space. The count for the select query for this MV is around 55 million rows. Any suggestions really appreciated.</p>
  
  <p>DB: Oracle 11gR2</p>
  
  <p>CREATE MATERIALIZED VIEW TEST NOLOGGING
    REFRESH FORCE ON DEMAND 
    ENABLE QUERY REWRITE 
    AS 
   select 
      table4.num as "Number",table4.num as "SNum", 
      table4.status as "S_status", 
      'Open' as "NLP", 
      create_table2.fmonth as "SMN",
      table6.wgrp as "SOW", 
      (table2.end_dt - create_table2.dt) as "elp",
      table6.d_c as "SDC",
      create_table2.fiscal_quarter_name as "SQN",
      'TS' as "SSL", 
      table3.table3_id as "SR Owner CEC ID", 
      table4.sev as "ssev",
      SUBSTR(table8.stech,1,INSTR(table8.stech,'=>')-1) as "srtech", 
      SUBSTR(table8.stech,INSTR(table8.stech,'=>')+2)  as "srstech",
      table5.sr_type as "SR Type", 
      table5.problem_code as "SR Problem Code", 
      --null as "SR Entry Channel",
      --null as "SR Time in Status (Days)",
      table6.center,
      table6.th1col, 
      table6.master_theater,
      table6.rol_3,
      table7.center hier_17_center,
      table7.rol_1 
      table7.rol_2,
      table7.rol_3 wg,
      table2.dt as "SBD",
      table2.wk_n as "SBFW",
      table2.fmonth as "SBFM",
      table3.defect_indicator as "Has Defect",
      table2.sofw,
      table2.sofm
  from 
      A table1
      join B table2 on (table1.date_id = table2.dw_date_key)
      join  C table3 on (table1.date_id = table3.date_id and table1.incident_id = table3.incident_id) 
      join  D table4 on (table3.incident_id = table4.incident_id and table4.key_d &lt;= table3.date_id  and table3.table3_id = table4.current_owner_table3_id)
      join E table5 on table4.incident_id = table5.incident_id
      join B create_table2 on (table5.creation_dw_date_key = create_table2.dw_date_key)
      join F table6 on (table1.objectnumber=table6.DW_WORKGROUP_KEY)
      join G table7 on (table1.objectnumber=table7.DW_WORKGROUP_KEY)
      left outer JOIN H table8 ON  (table8.natural_key= table5.UPDATED_COT_TECH_KEY)
  where
  table4.bl_incident_key in (select max(bl_incident_key) from D b 
  where b.incident_id=table3.incident_id and b.key_d &lt;= table3.date_id and b.current_owner_table3_id = table3.table3_id)
  and table2.fiscal_year_name in ('FY2013','FY2014')</p>