Oracle Database Discussions

I'm trying to get the hang of materialized views and need some help . . .

running Oracle 10gR2

I created a materialized view as follows:

CREATE MATERIALIZED VIEW "MY_SCHEMA"."USERS"
AS
SELECT * FROM "USERS"@PRODUCTION -- to copy the USERS table from my production database to this database

Worked fine. So far so good. Then, I altered it to schedule the refresh:

ALTER MATERIALIZED VIEW "MY_SCHEMA"."USERS" REFRESH FORCE START WITH SYSDATE NEXT TRUNC(SYSDATE + 1) + 4/24
-- to schedule a refresh immediately (SYSDATE) and every morning thereafter at 4:00 am (I think)

then, I wanted to enable query rewrite, so I issued the following command:
ALTER MATERIALIZED VIEW "MY_SCHEMA"."USERS" ENABLE QUERY REWRITE;

and I got the
ALTER MATERIALIZED VIEW succeeded.
message for both of those commands (I'm doing this in SQL Developer).

Then, I edit the materialized view in SQL Developer, click on the 'SQL' tab, and get the following:


CREATE MATERIALIZED VIEW "MY_SCHEMA"."USERS"
ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "TBLSPC_PHIGH"
BUILD IMMEDIATE
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "TBLSPC_PHIGH"
REFRESH FORCE ON DEMAND START WITH sysdate+0 NEXT TRUNC(SYSDATE + 1) + 4/24
WITH PRIMARY KEY USING DEFAULT LOCAL ROLLBACK SEGMENT
DISABLE QUERY REWRITE
AS SELECT "USERS"."User" "User","USERS"."Logon" "Logon","USERS"."Name" "Name","USERS"."Password"
"Password","USERS"."Level" "Level","USERS"."Producer" "Producer","USERS"."Deleted" "Deleted","USERS"."ClaimDoctor"
"ClaimDoctor","USERS"."UserType" "UserType","USERS"."Locked" "Locked","USERS"."Scheduler"
"Scheduler","USERS"."RestrictDays" "RestrictDays","USERS"."First" "First","USERS"."DisableAutoChart"
"DisableAutoChart","USERS"."MaxChartOut" "MaxChartOut","USERS"."MaxHoursOut" "MaxHoursOut","USERS"."EffDate"
"EffDate","USERS"."ExpDate" "ExpDate","USERS"."SwipeLogon" "SwipeLogon","USERS"."SwipePassword"
"SwipePassword","USERS"."PwdLastChanged" "PwdLastChanged","USERS"."Audit" "Audit","USERS"."IsInstructor"
"IsInstructor" FROM "USERS"@PRODUCTION.REGRESS.RDBMS.DEV.US.ORACLE.COM "USERS";

The problems I've encountered so far:

1) I created this two days ago and changed the value of one column in one row in the 'source' USERS table

(USERS@PRODUCTION) as a test case. So far, that change has not propagated over to my materialized view even though

it has had two chances to do so (yesterday morning at 4:00 am and this morning at 4:00 am).

2) I enabled QUERY REWRITE. Why does it still show DISABLE?

3) The primary key of the table came over (as far as I can tell) because it shows in SQL Developer. However, the

indexed columns are no longer indexed. Do I need to recreate those indexes manually and will they persist?

4) As you have guessed, I have just taken the plunge into materialized views, and have not been able to find

anything on the Web along the lines of 'Materialized Views for Dummies'. So, any and all advice/suggestions/help

will be welcome.

Thanks,

Carl