Home » RDBMS Server » Server Administration » ORA-01652: unable to extend temp segment by 16 in tablespace DATA03 (Oracle 10.2.0 , Windows )
ORA-01652: unable to extend temp segment by 16 in tablespace DATA03 [message #615645] Fri, 06 June 2014 08:22 Go to next message
Jack14
Messages: 497
Registered: December 2011
Location: INDIA
Senior Member
Hi,

When i try to create Materialize view i get following error.

ERROR at line 62:
ORA-01652: unable to extend temp segment by 16 in tablespace DATA03[/code

Based on error i increased DATA03 tablespace but still i get same error.Now the tablespace has 20 GB free space.
Help me to fix it.

Thanks,
Jack

[Updated on: Fri, 06 June 2014 08:23]

Report message to a moderator

Re: ORA-01652: unable to extend temp segment by 16 in tablespace DATA03 [message #615646 is a reply to message #615645] Fri, 06 June 2014 08:23 Go to previous messageGo to next message
BlackSwan
Messages: 25793
Registered: January 2009
Location: SoCal
Senior Member
[oracle@localhost mesg]$ oerr ora 1652
01652, 00000, "unable to extend temp segment by %s in tablespace %s"
// *Cause:  Failed to allocate an extent of the required number of blocks for
//          a temporary segment in the tablespace indicated.
// *Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more
//          files to the tablespace indicated.

Re: ORA-01652: unable to extend temp segment by 16 in tablespace DATA03 [message #615648 is a reply to message #615646] Fri, 06 June 2014 08:25 Go to previous messageGo to next message
Jack14
Messages: 497
Registered: December 2011
Location: INDIA
Senior Member
Blackswan,

i resized datafile to 10 GB and total space datafile has is 20 GB but still i get this error.
Moreover i monitored tablespace size while running Materialized view there was no change in datafile size.

[Updated on: Fri, 06 June 2014 08:26]

Report message to a moderator

Re: ORA-01652: unable to extend temp segment by 16 in tablespace DATA03 [message #615649 is a reply to message #615648] Fri, 06 June 2014 08:38 Go to previous messageGo to next message
BlackSwan
Messages: 25793
Registered: January 2009
Location: SoCal
Senior Member
Either error message is correct or it is wrongly thrown.
We can't know which is your reality.
If error is wrongly thrown, then you need to submit Bug Report with My Oracle Support.
If error is valid, then you must do specified ACTION.
Re: ORA-01652: unable to extend temp segment by 16 in tablespace DATA03 [message #615650 is a reply to message #615649] Fri, 06 June 2014 08:41 Go to previous messageGo to next message
Jack14
Messages: 497
Registered: December 2011
Location: INDIA
Senior Member
<If error is valid, then you must do specified ACTION.>
i took corrective action by increasing datafile size but still the output is same.

<If error is wrongly thrown, then you need to submit Bug Report with My Oracle Support.>
How to confirm its oracle bug and wrong error ?

Regds,
jack

[Updated on: Fri, 06 June 2014 08:42]

Report message to a moderator

Re: ORA-01652: unable to extend temp segment by 16 in tablespace DATA03 [message #615651 is a reply to message #615650] Fri, 06 June 2014 08:45 Go to previous messageGo to next message
BlackSwan
Messages: 25793
Registered: January 2009
Location: SoCal
Senior Member
post query & EXPLAIN PLAN
Re: ORA-01652: unable to extend temp segment by 16 in tablespace DATA03 [message #615669 is a reply to message #615651] Fri, 06 June 2014 11:48 Go to previous messageGo to next message
Jack14
Messages: 497
Registered: December 2011
Location: INDIA
Senior Member
Below is the statement i am using to create Materialized view.

  CREATE MATERIALIZED VIEW "DEMO"."IT_ORG_MV"
  ORGANIZATION HEAP PCTFREE 0 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "DATA04"
  BUILD IMMEDIATE
  USING INDEX
  REFRESH COMPLETE ON DEMAND START WITH sysdate+0 NEXT TRUNC(SYSDATE + 1) + 22/24
  WITH ROWID USING DEFAULT LOCAL ROLLBACK SEGMENT
  DISABLE QUERY REWRITE
  AS SELECT
init_opty.row_id as ROW_ID,
Init_opty.x_init_prod_line opportunity_type,
Init_opty.name opportunity_name,
customer.mid_name as CLIENT_NUM,
emp.login as SALES_REP,
customer.x_pb_customer as CUSTOMER,
customer_x.ATTRIB_35 as CUST_TYPE,
branch2.loc as CUSTOMER_BRANCH,
branch.loc as BRANCH_NSC,
init_opty.x_pb_sales_method sales_method,
init_opty_x.ATTRIB_40 as SALES_STAGE,
opty_src.name as OPTY_SOURCE,
init_opty_x.attrib_16 as ESTIM_VOL,
init_opty.win_confidence_cd as PROBABILITY,
init_opty.x_pb_sales_stage as OPPORTUNITY_STAGE,
init_opty.desc_text as DESCRIPTION,
init_opty.x_init_prod_line as PROD_TYPE  ,
init_opty_x.attrib_20 as term,
init_opty_x.attrib_13 as Open_date,
init_opty.actl_cls_dt as Close_date,
init_opty.stg_start_dt as Sales_Stage_date,
init_opty.Created as created,
pip.x_pip_dc as rm_code_hc,
init_opty.x_pb_rm_code AS rm_code,
init_opty.last_upd as opty_last_upd,
init_opty.last_upd_by as opty_last_upd_by,
init_opty_x.last_upd as optyx_last_upd,
init_opty_x.last_upd_by as optyx_last_upd_by,
customer.last_upd as cust_last_upd,
customer.last_upd_by as cust_last_upd_by,
customer_x.last_upd as custx_last_upd,
customer_x.last_upd_by as custx_last_upd_by,
init_opty_x.attrib_08 as deletion_flag,
init_opty_x.attrib_41 as deleted_by,
init_opty_x.attrib_42 as deletion_date,
init_opty_x.attrib_46 as deletion_reason,
init_opty_x.attrib_12 as expect_draw_down,
init_opty.invst_stg_cd as expect_close_qtr,
init_opty.sort_flg as opty_closed,
init_opty.BDGT_AMT as WRITTEN_IEP,
init_opty.CONSUMER_OPTY_AMT as ISSUED_IEP,
init_opty_x.attrib_17 as goal_obj_amt,
init_opty_x.ATTRIB_04 as opty_sub_type,
init_opty.x_pb_rm_area         AS rm_area,
init_opty.x_pb_rm_contact_type AS rm_contact_type,
init_opty.x_pb_rm_first_name   AS rm_first_name,
init_opty.x_pb_rm_last_name    AS rm_last_name,
init_opty.x_pb_rm_region       AS rm_region,
init_opty.x_pb_rm_segment      AS rm_segment
FROM
s_opty init_opty,
s_opty_x init_opty_x,
s_contact customer,
s_contact pip,
s_contact_x customer_x,
s_opty_con customer_inx,
s_user emp,                     --siebel 7 change: employee is now managed from
s_party_per,
s_org_ext branch,
s_org_ext branch2,
s_postn pos,
s_party_per emp_pos,            --seibel 7 change (see above)
s_src opty_src
WHERE
init_opty_x.par_row_id = init_opty.row_id AND
opty_src.row_id = init_opty.PR_SRC_ID and
init_opty.pr_postn_id = pos.row_id AND
pos.row_id = emp_pos.party_id AND
emp_pos.person_id = emp.row_id AND
branch.par_row_id (+)= init_opty.pr_dept_ou_id AND
customer_inx.opty_id = init_opty.row_id AND
customer.par_row_id = customer_inx.per_id AND
init_opty.pr_con_id = customer.par_row_id AND
customer_x.par_row_id = customer.par_row_id AND
branch2.par_row_id (+)= customer.pr_dept_ou_id AND
pip.par_row_id (+) = customer.x_pip_id AND
init_opty.par_opty_id is null AND 
pos.PR_EMP_ID = emp.row_id (+)


When i try to do explain plan for MV , i get error.Please find below.

SQL> explain plan for   CREATE MATERIALIZED VIEW "DEMO"."IT_ORG_MV"
  2    ORGANIZATION HEAP PCTFREE 0 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGIN
  3    STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
  4    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  5    TABLESPACE "DATA04"
  6    BUILD IMMEDIATE
  7    USING INDEX
  8    REFRESH COMPLETE ON DEMAND START WITH sysdate+0 NEXT TRUNC(SYSDATE + 1) + 22/24
  9    WITH ROWID USING DEFAULT LOCAL ROLLBACK SEGMENT
 10    DISABLE QUERY REWRITE
 11    AS SELECT
 12  init_opty.row_id as ROW_ID,
 13  Init_opty.x_init_prod_line opportunity_type,
 14  Init_opty.name opportunity_name,
 15  customer.mid_name as CLIENT_NUM,
 16  emp.login as SALES_REP,
 17  customer.x_pb_customer as CUSTOMER,
 18  customer_x.ATTRIB_35 as CUST_TYPE,
 19  branch2.loc as CUSTOMER_BRANCH,
 20  branch.loc as BRANCH_NSC,
 21  init_opty.x_pb_sales_method sales_method,
 22  init_opty_x.ATTRIB_40 as SALES_STAGE,
 23  opty_src.name as OPTY_SOURCE,
 24  init_opty_x.attrib_16 as ESTIM_VOL,
 25  init_opty.win_confidence_cd as PROBABILITY,
 26  init_opty.x_pb_sales_stage as OPPORTUNITY_STAGE,
 27  init_opty.desc_text as DESCRIPTION,
 28  init_opty.x_init_prod_line as PROD_TYPE  ,
 29  init_opty_x.attrib_20 as term,
 30  init_opty_x.attrib_13 as Open_date,
 31  init_opty.actl_cls_dt as Close_date,
 32  init_opty.stg_start_dt as Sales_Stage_date,
 33  init_opty.Created as created,
 34  pip.x_pip_dc as rm_code_hc,
 35  init_opty.x_pb_rm_code AS rm_code,
 36  init_opty.last_upd as opty_last_upd,
 37  init_opty.last_upd_by as opty_last_upd_by,
 38  init_opty_x.last_upd as optyx_last_upd,
 39  init_opty_x.last_upd_by as optyx_last_upd_by,
 40  customer.last_upd as cust_last_upd,
 41  customer.last_upd_by as cust_last_upd_by,
 42  customer_x.last_upd as custx_last_upd,
 43  customer_x.last_upd_by as custx_last_upd_by,
 44  init_opty_x.attrib_08 as deletion_flag,
 45  init_opty_x.attrib_41 as deleted_by,
 46  init_opty_x.attrib_42 as deletion_date,
 47  init_opty_x.attrib_46 as deletion_reason,
 48  init_opty_x.attrib_12 as expect_draw_down,
 49  init_opty.invst_stg_cd as expect_close_qtr,
 50  init_opty.sort_flg as opty_closed,
 51  init_opty.BDGT_AMT as WRITTEN_IEP,
 52  init_opty.CONSUMER_OPTY_AMT as ISSUED_IEP,
 53  init_opty_x.attrib_17 as goal_obj_amt,
 54  init_opty_x.ATTRIB_04 as opty_sub_type,
 55  init_opty.x_pb_rm_area         AS rm_area,
 56  init_opty.x_pb_rm_contact_type AS rm_contact_type,
 57  init_opty.x_pb_rm_first_name   AS rm_first_name,
 58  init_opty.x_pb_rm_last_name    AS rm_last_name,
 59  init_opty.x_pb_rm_region       AS rm_region,
 60  init_opty.x_pb_rm_segment      AS rm_segment
 61  FROM
 62  s_opty init_opty,
 63  s_opty_x init_opty_x,
 64  s_contact customer,
 65  s_contact pip,
 66  s_contact_x customer_x,
 67  s_opty_con customer_inx,
 68  s_user emp,                     --siebel 7 change: employee is now managed from
 69  s_party_per,
 70  s_org_ext branch,
 71  s_org_ext branch2,
 72  s_postn pos,
 73  s_party_per emp_pos,            --seibel 7 change (see above)
 74  s_src opty_src
 75  WHERE
 76  init_opty_x.par_row_id = init_opty.row_id AND
 77  opty_src.row_id = init_opty.PR_SRC_ID and
 78  init_opty.pr_postn_id = pos.row_id AND
 79  pos.row_id = emp_pos.party_id AND
 80  emp_pos.person_id = emp.row_id AND
 81  branch.par_row_id (+)= init_opty.pr_dept_ou_id AND
 82  customer_inx.opty_id = init_opty.row_id AND
 83  customer.par_row_id = customer_inx.per_id AND
 84  init_opty.pr_con_id = customer.par_row_id AND
 85  customer_x.par_row_id = customer.par_row_id AND
 86  branch2.par_row_id (+)= customer.pr_dept_ou_id AND
 87  pip.par_row_id (+) = customer.x_pip_id AND
 88  init_opty.par_opty_id is null AND
 89  pos.PR_EMP_ID = emp.row_id (+)
 90  /
pos.PR_EMP_ID = emp.row_id (+)
                             *
ERROR at line 89:
ORA-00900: invalid SQL statement


Thanks,
Jack
Re: ORA-01652: unable to extend temp segment by 16 in tablespace DATA03 [message #615670 is a reply to message #615669] Fri, 06 June 2014 11:51 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

How can you have an error "RA-01652: unable to extend temp segment by 16 in tablespace DATA03" with asyntaxically invalid SQL statement?
Once again you tell us lies.

Re: ORA-01652: unable to extend temp segment by 16 in tablespace DATA03 [message #615671 is a reply to message #615670] Fri, 06 June 2014 12:08 Go to previous messageGo to next message
BlackSwan
Messages: 25793
Registered: January 2009
Location: SoCal
Senior Member
>TABLESPACE "DATA04"
posted code contains above yet you claim it throws error below
>ORA-01652: unable to extend temp segment by 16 in tablespace DATA03

you have magical fingers, vivid imagination, & little credibility.

You're On Your Own (YOYO)!

[Updated on: Fri, 06 June 2014 18:24]

Report message to a moderator

Re: ORA-01652: unable to extend temp segment by 16 in tablespace DATA03 [message #615685 is a reply to message #615671] Sat, 07 June 2014 00:11 Go to previous messageGo to next message
Jack14
Messages: 497
Registered: December 2011
Location: INDIA
Senior Member
There is no use in posting my queries hereafter.Absolutely useless forum.
Re: ORA-01652: unable to extend temp segment by 16 in tablespace DATA03 [message #615687 is a reply to message #615685] Sat, 07 June 2014 00:22 Go to previous message
BlackSwan
Messages: 25793
Registered: January 2009
Location: SoCal
Senior Member
Jack14 wrote on Fri, 06 June 2014 22:11
There is no use in posting my queries hereafter.Absolutely useless forum.


So do NOT post here again.
Previous Topic: Suggestion to add ASM disk to ASM group
Next Topic: Checking swap space 0Mb available ,150 required
Goto Forum:
  


Current Time: Wed Jan 17 00:18:28 CST 2018

Total time taken to generate the page: 0.01482 seconds