|
-
Re: Creating materialized view thru db link?
First as tester in orcltst run (change the names of the tablespaces):
CREATE MATERIALIZED VIEW LOG ON abcview TABLESPACE MV_LOG_TS
PCTFREE 60 PCTUSED 30 MAXTRANS 255
STORAGE (INITIAL 1M NEXT 1M MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0)
WITH PRIMARY KEY INCLUDING NEW VALUES;
Then on the snapshot DB:
CREATE MATERIALIZED VIEW abcview
PCTFREE 10 PCTUSED 40 MAXTRANS 255
STORAGE (INITIAL 1024K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1)
TABLESPACE TS_DATA_NAME
BUILD IMMEDIATE
USING INDEX TABLESPACE TS_INDEX_NAME
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 256K NEXT 256K PCTINCREASE 0 MINEXTENTS 1 MAXEXTENTS 2147483645
FREELISTS 1 FREELIST GROUPS 1)
REFRESH force WITH PRIMARY KEY
START WITH trunc(sysdate) + 4/24
NEXT trunc(sysdate+1) + 4/24
AS
SELECT * FROM abc@orcltst;
This MV will be fast refreshesd every morning at 4AM.
Oracle Certified Master
Oracle Certified Professional 6i,8i,9i,10g,11g,12c
email: [email protected]
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|