Home » Server Options » Replication » Problem with advance replication from Master to Mv and Mv to Master
Problem with advance replication from Master to Mv and Mv to Master [message #422732] Fri, 18 September 2009 10:00 Go to next message
lesanch
Messages: 1
Registered: September 2009
Junior Member
Hi all friends,

I need help to make an advance replication between two tables in Oracle 8i actually i can do it only in one way, from master to materialized but i can't do it from materialized to master.

Those are the scripts:

1- CREATE MASTER TABLE IN DATABASE A:

CREATE TABLE SAM.TABLE
(
NRO_PEDIDO_WEB NUMBER(10) NOT NULL,
NRO_POSICION_WEB NUMBER(10) NOT NULL,
ID_SESSION VARCHAR2(100),
D_TIPO_DIMENSION VARCHAR2(255),
)
TABLESPACE SAM_DATA
PCTUSED 40
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
LOGGING
NOCACHE
NOPARALLEL;

CREATE UNIQUE INDEX SAM.PK_TABLE ON SAM.TABLE
(NRO_PEDIDO_WEB, NRO_POSICION_WEB)
LOGGING
TABLESPACE SAM_DATA
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
NOPARALLEL;

CREATE PUBLIC SYNONYM TABLE FOR SAM.TABLE;

ALTER TABLE SAM.TABLE ADD (
CONSTRAINT PK_TABLE
PRIMARY KEY
(NRO_PEDIDO_WEB, NRO_POSICION_WEB)
USING INDEX
TABLESPACE SAM_INDEX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
));

GRANT DELETE, INSERT, SELECT, UPDATE ON SAM.TABLE TO USUARIO1;

GRANT DELETE, INSERT, SELECT, UPDATE ON SAM.TABLE TO USUARIO2;

------------------------------------------------------------------------
2- CREATE Materialized View Log ON DATABASE A

CREATE MATERIALIZED VIEW LOG ON SAM.TABLE
TABLESPACE SAM_DATA
PCTUSED 30
PCTFREE 60
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOCACHE
LOGGING
NOPARALLEL
WITH PRIMARY KEY;

-------------------------------------------------------------------------

3- CREATE Materialized View ON DATABASE B

CREATE MATERIALIZED VIEW SAM.TABLE
TABLESPACE SAM_DATA
PCTUSED 40
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
NOCACHE
LOGGING
NOPARALLEL
BUILD IMMEDIATE
USING INDEX
TABLESPACE SAM_INDEX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
REFRESH FAST
WITH PRIMARY KEY
FOR UPDATE
AS
SELECT
"TABLE"."NRO_PEDIDO_WEB" "NRO_PEDIDO_WEB",
"TABLE"."NRO_POSICION_WEB" "NRO_POSICION_WEB",
"TABLE"."ID_SESSION" "ID_SESSION",
"TABLE"."D_TIPO_DIMENSION" "D_TIPO_DIMENSION"
FROM "TABLE"@DBLINK.WORLD "TABLE";

COMMENT ON TABLE SAM.TABLE IS 'snapshot table for snapshot SAM.TABLE';

CREATE PUBLIC SYNONYM TABLE FOR SAM.TABLE;

GRANT DELETE, INSERT, SELECT, UPDATE ON SAM.TABLE TO USUARIO1;

GRANT DELETE, INSERT, SELECT, UPDATE ON SAM.TABLE TO USUARIO2;

---------------------------------------------------------------------------

4- CREATE THE Refresh Group ON DATABASE B

DECLARE
SnapArray SYS.DBMS_UTILITY.UNCL_ARRAY;
BEGIN
SnapArray(1) := 'SAM.TABLE';
SnapArray(2) := NULL;
SYS.DBMS_REFRESH.MAKE (
name => 'SAM.REFRESHTABLE'
,tab => SnapArray
,next_date => TO_DATE('09/16/2009 12:22:33', 'MM/DD/YYYY HH24:MI:SS')
,interval => '/*10:Mins*/ sysdate + 10/(60*24)'
,implicit_destroy => FALSE
,lax => TRUE
,job => 0
,rollback_seg => NULL
,push_deferred_rpc => TRUE
,refresh_after_errors => TRUE
,purge_option => 0
,parallelism => 0
,heap_size => 0
);
Commit;
END;
/

THANKS IN ADVANCE.
REGARDS,
lesanch.
Re: Problem with advance replication from Master to Mv and Mv to Master [message #422777 is a reply to message #422732] Sat, 19 September 2009 01:01 Go to previous message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member

What's your problem?

Why your not using code tag for posting sql code??

Thanks
Previous Topic: How to create physical standby database without data guard
Next Topic: materialised views not getting refreshed
Goto Forum:
  


Current Time: Thu Mar 28 11:00:38 CDT 2024