Hi Tom,
Please help me with the following.
I'm trying to create a materialized view with following syntax :
CREATE MATERIALIZED VIEW TEST_ON_LOCAL_MV
TABLESPACE "USERS"
PCTFREE 10
PCTUSED 40
INITRANS 2
STORAGE
(
INITIAL 1048576
NEXT 16777216
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
NOCACHE
NOPARALLEL
BUILD IMMEDIATE
USING INDEX
TABLESPACE "USERS"
PCTFREE 10
PCTUSED 40
INITRANS 1
STORAGE
(
INITIAL 1048576
NEXT 8388608
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
REFRESH ON DEMAND COMPLETE
WITH PRIMARY KEY
USING DEFAULT LOCAL ROLLBACK SEGMENT
DISABLE QUERY REWRITE AS
SELECT
*
FROM employees;I'm trying to create the Mview at the same schema where the employees table resides.
But I'm facing with error ORA-14071 : invalid option for an index used to enforce a constraint.
However, when I'm omitting the part after "USING INDEX" clause:
TABLESPACE "USERS"
PCTFREE 10
PCTUSED 40
INITRANS 1
STORAGE
(
INITIAL 1048576
NEXT 8388608
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
...the MatView is created successfully.
Please help me.
PCTUSED is not a setting for an index, and INITRANS needs to be 2 or higher for indexes, eg
SQL> CREATE MATERIALIZED VIEW TEST_ON_LOCAL_MV
2 TABLESPACE "USERS"
3 NOCACHE
4 NOPARALLEL
5 BUILD IMMEDIATE
6 USING INDEX
7 TABLESPACE "DEMO"
8 PCTFREE 10
9 PCTUSED 40
10 INITRANS 1
11 STORAGE
12 (
13 INITIAL 1048576
14 NEXT 8388608
15 MINEXTENTS 1
16 MAXEXTENTS 2147483645
17 PCTINCREASE 0
18 FREELISTS 1
19 FREELIST GROUPS 1
20 BUFFER_POOL DEFAULT
21 )
22 REFRESH ON DEMAND COMPLETE
23 WITH PRIMARY KEY
24 USING DEFAULT LOCAL ROLLBACK SEGMENT
25 DISABLE QUERY REWRITE AS
26 SELECT * FROM scott.emp;
SELECT * FROM scott.emp
*
ERROR at line 26:
ORA-14071: invalid option for an index used to enforce a constraint
SQL>
SQL>
SQL> drop MATERIALIZED VIEW TEST_ON_LOCAL_MV ;
drop MATERIALIZED VIEW TEST_ON_LOCAL_MV
*
ERROR at line 1:
ORA-12003: materialized view or zonemap "MCDONAC"."TEST_ON_LOCAL_MV" does not exist
SQL>
SQL> CREATE MATERIALIZED VIEW TEST_ON_LOCAL_MV
2 TABLESPACE "USERS"
3 NOCACHE
4 NOPARALLEL
5 BUILD IMMEDIATE
6 USING INDEX
7 TABLESPACE "DEMO"
8 PCTFREE 10
9 --PCTUSED 40 -- commented out
10 --INITRANS 1 -- commented out
11 STORAGE
12 (
13 INITIAL 1048576
14 NEXT 8388608
15 MINEXTENTS 1
16 MAXEXTENTS 2147483645
17 PCTINCREASE 0
18 FREELISTS 1
19 FREELIST GROUPS 1
20 BUFFER_POOL DEFAULT
21 )
22 REFRESH ON DEMAND COMPLETE
23 WITH PRIMARY KEY
24 USING DEFAULT LOCAL ROLLBACK SEGMENT
25 DISABLE QUERY REWRITE AS
26 SELECT * FROM scott.emp;
Materialized view created.
SQL>
But for me, I try keep my DDL as small and simple as possible, eg
CREATE MATERIALIZED VIEW TEST_ON_LOCAL_MV
BUILD IMMEDIATE
USING INDEX
REFRESH ON DEMAND COMPLETE
WITH PRIMARY KEY
DISABLE QUERY REWRITE AS
SELECT * FROM scott.emp;
and add other attributes (tablespace, pctfree etc) as required.