The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments. Please have a happy and safe festive season and as always, thanks for being a member of the AskTOM community.
Thanks for the question, Debasish.
Asked: June 28, 2017 - 2:18 am UTC
Last updated: June 29, 2017 - 3:01 am UTC
Version: 11g
Viewed 1000+ times
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;
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 )
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>
Share and learn SQL and PL/SQL; free access to the latest version of Oracle Database!
Classes, workouts and quizzes on Oracle Database technologies. Expertise through exercise!