-
Creating materialized view thru db link?
Hi
Anyone knows how to create a view/materialized view of table in remote server thru database link.
I have created the following:
1. database link orcltst
SQL>create database link orcltst connect to tester identifed by tester using 'orcltst';
2. Try to create a view remotely from another server:
SQL>CREATE MATERIALIZED VIEW abcview
AS SELECT * FROM abc@orcltst;
But it failed to create the view.
Can anybody share the expertise on this
Thanks you so much for your help
Robby
-
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: ocp_9i@yahoo.com
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
|