Home » RDBMS Server » Server Administration » ORA-01652: unable to extend temp segment by 16 in tablespace DATA03 (Oracle 10.2.0 , Windows )
|
|
|
|
|
|
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/7587f1e1dc7836c6b81ad21d234a2aad?s=64&d=mm&r=g) |
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
|
|
|
|
|
|
|
Goto Forum:
Current Time: Sun May 19 19:13:58 CDT 2024
|