Home » Server Options » Replication » Can we use Materialize views in Streams (merged) (oracle 11.2.0.1.0)
Can we use Materialize views in Streams (merged) [message #500452] Mon, 21 March 2011 07:32 Go to next message
kesavansundaram
Messages: 181
Registered: October 2007
Location: MUMBAI
Senior Member

Dear Sir/Madam, 
Can we use Materialized views in Streams like how we use tables... ? 
Thank you
kesavan 
Re: Can we use Materialize views in Streams [message #500457 is a reply to message #500452] Mon, 21 March 2011 08:04 Go to previous messageGo to next message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member

http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10755/statviews_1168.htm#I1020391

Chere here
Re: Can we use Materialize views in Streams [message #500463 is a reply to message #500457] Mon, 21 March 2011 08:30 Go to previous messageGo to next message
kesavansundaram
Messages: 181
Registered: October 2007
Location: MUMBAI
Senior Member

HI GentleBabu,

Yes, I referred your below link.. M.V.tables are not supported by Streams...Thank you for your quick turn around.
-Kesavan

[Updated on: Mon, 21 March 2011 08:59] by Moderator

Report message to a moderator

Re: Can we use Materialize views in Streams [message #500535 is a reply to message #500452] Tue, 22 March 2011 01:44 Go to previous messageGo to next message
kesavansundaram
Messages: 181
Registered: October 2007
Location: MUMBAI
Senior Member

Hi Babu,
I have 2 questions:

question-1
------------
i cheked all_streams_unsupported - view.
it says, below types of tables are not supported by streams...

IOT
column with user-defined type
unsupported column exists
object table
AQ queue table
temporary table
sub object
external table
materialized view
FILE column exists
materialized view log
materialized view container table
streams unsupported object
domain index
IOT with overflow
IOT with LOB
IOT with physical Rowid mapping
mapping table for physical rowid of IOT
IOT with LOB
IOT with row movement
summary container table

- in this materialized view also listed..
But, when my manager connect one schema and checked the query
SELECT * FROM DBA_STREAMS_UNSUPPORTED ORDER BY REASON;
in the output, mv.logs are listed, but M.VIEW are not listed.. what is the reason for this... ? but we have MVIEWS presented in a particular schema...

question:2
-------------
My manager raising below question:

If all the underlying base tables of a MV is part of ODS and if they are streamed into ODS from source systems then we don't need to stream MV separately, since the MV is refreshed automatically after source tables refreshed.

But if the MV is part of source system and if all underlying base tables of that MV is not part of the tables that are streamed, then we have the issue unable to stream MV separately.

Am I correct to say this?


COULD YOU PLEASE CLARIFY ABOVE BOTH QUESTIONS ?

THANK YOU
KESAVAN

[Updated on: Tue, 22 March 2011 02:14] by Moderator

Report message to a moderator

Re: Can we use Materialize views in Streams [message #500540 is a reply to message #500535] Tue, 22 March 2011 02:16 Go to previous messageGo to next message
Michel Cadot
Messages: 67488
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Code tags are ONLY for code or (pre)formatted output not for the whole post.

Regards
Michel


Re: Can we use Materialize views in Streams [message #500543 is a reply to message #500540] Tue, 22 March 2011 02:29 Go to previous messageGo to next message
kesavansundaram
Messages: 181
Registered: October 2007
Location: MUMBAI
Senior Member

Hi Michel,
I am sorry for the incon(.)

Babu,
further MVIEWS are table it seems. Pl refer below example:

SQL> create materialized view mv_test
  2  as
  3  select * from scott.emp;

Materialized view created.

SQL> select table_name
  2  from user_tables
  3  where table_name = 'MV_TEST';

Object Name
--------------------------------------------------------------------------------
MV_TEST

SQL> select owner, table_name, reason
  2  from DBA_STREAMS_UNSUPPORTED
  3  where table_name='MV_TEST';

no rows selected

SQL>
so, please confirm my previous 2 queries( in my earlier mail ).

Thank you,
kesavan
Re: Can we use Materialize views in Streams [message #500555 is a reply to message #500463] Tue, 22 March 2011 04:13 Go to previous messageGo to next message
kesavansundaram
Messages: 181
Registered: October 2007
Location: MUMBAI
Senior Member

Hi Babu,
Could you/anyone guide me on those 2 questions posted earlier ?

ie.
1.
why m.view object is not listed in dba_streams_unsupported view eventhough it is not supported streams..?

and

2.
If all the underlying base tables of a MV is part of ODS and if they are streamed into ODS from source systems then we don't need to stream MV separately, since the MV is refreshed automatically after source tables refreshed.

But if the MV is part of source system and if all underlying base tables of that MV is not part of the tables that are streamed, then we have the issue unable to stream MV separately.

Am I correct to say this?

Thank you very much,
kesavan

Re: Can we use Materialize views in Streams [message #500566 is a reply to message #500555] Tue, 22 March 2011 04:28 Go to previous messageGo to next message
Michel Cadot
Messages: 67488
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You ask Babu to answer you, why other one would interfere in your discussion?
Wait for Babu to answer you.

Regards
Michel
why MVIEWs are not relfected in ALL_STREAMS_UNSUPPORTED ? [message #500709 is a reply to message #500452] Wed, 23 March 2011 00:51 Go to previous messageGo to next message
kesavansundaram
Messages: 181
Registered: October 2007
Location: MUMBAI
Senior Member

Dear Sir/Madam,

We have some materialized views presented in our test schema ( IDENTITY )..
I just want to check whey this materialized view is not reflected in ALL_STREAMS_UNSUPPORTED...



select owner, mview_name from user_mviews

OWNER                          MVIEW_NAME                     
------------------------------ ------------------------------ 
IDENTITY                       TECH_EFF_HR_LVL_HRCHY_DIM_MV   
IDENTITY                       HR_HIERARCHY_EMP_SUP_INFN_MV   
IDENTITY                       COX_IDENTITY_HR_DIM            

3 rows selected
Further, I am able to see one row for this MVIEW from user_tables.

when i query all_streams_unsupported, It is not appearing..

SELECT * FROM ALL_STREAMS_UNSUPPORTED WHERE OWNER = 'IDENTITY' and table_name = 'COX_IDENTITY_HR_DIM'

OWNER                          TABLE_NAME                     REASON                                  AUTO_FILTERED 
------------------------------ ------------------------------ --------------------------------------- ------------- 

0 rows selected


when i check distinct REASON column in all_streams_unsupported view, it shows only MV.LOG and not 'MATERIALZED VIEW'

SELECT distinct reason from all_streams_unsupported

REASON                                  
--------------------------------------- 
materialized view log                   

1 rows selected


note: in general, we have below distinct values for REASON column from all_streams_unsupported:

IOT
column with user-defined type
unsupported column exists
object table
AQ queue table
temporary table
sub object
external table
materialized view
FILE column exists
materialized view log
materialized view container table
streams unsupported object
domain index
IOT with overflow
IOT with LOB
IOT with physical Rowid mapping
mapping table for physical rowid of IOT
IOT with LOB
IOT with row movement
summary container table
Please advise on the same.

Thank you,
kesavan
Re: Can we use Materialize views in Streams [message #502008 is a reply to message #500566] Sun, 03 April 2011 00:43 Go to previous messageGo to next message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member

SQL> create materialized view mv_test
  2  as
  3  select * from scott.emp;

Materialized view created.

SQL> select table_name
  2  from user_tables
  3  where table_name = 'MV_TEST';

Object Name
--------------------------------------------------------------------------------
MV_TEST

SQL> select owner, table_name, reason
  2  from DBA_STREAMS_UNSUPPORTED
  3  where table_name='MV_TEST';

no rows selected

SQL>


1. So, The object "MV_TEST" it's table or materialized view? Why are you looking into USER_TABLES? Why not USER_MVIEWS / DBA_MVIEWS?

>>why m.view object is not listed in dba_streams_unsupported view eventhough it is not supported streams..?

Already answered.

2. Streams only support if the objects as per requirement

- Babu
Re: why MVIEWs are not relfected in ALL_STREAMS_UNSUPPORTED ? [message #502009 is a reply to message #500709] Sun, 03 April 2011 00:53 Go to previous messageGo to next message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member

Have you installed streams in your database?



Re: Can we use Materialize views in Streams (merged) [message #513888 is a reply to message #500452] Wed, 29 June 2011 22:00 Go to previous message
Oyunbold
Messages: 21
Registered: September 2007
Location: MGL
Junior Member
we use GOLDENGATE
Example
Source
CREATE TABLE TESTA
(
ID NUMBER,
NAME VARCHAR2(16 CHAR)
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;

CREATE TABLE TESTB
(
ID NUMBER,
NAME VARCHAR2(16 CHAR)
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;

create MATERIALIZED VIEW LOG ON TESTA WITH ROWID;
create MATERIALIZED VIEW LOG ON TESTB WITH ROWID;

CREATE MATERIALIZED VIEW TESTAB
STORAGE ( INITIAL 1M NEXT 1M PCTINCREASE 0 )
REFRESH FAST WITH ROWID ON COMMIT
AS
SELECT A.ID,
A.NAME AS NAMEA,
B.NAME AS NAMEB,
A.ROWID ARID,
B.ROWID BRID
FROM TESTA A, TESTB B
WHERE B.ID=A.ID;
--------------
GOLDENGATE ggsci>
edit params ext1
EXTRACT ext1
USERID ggs_owner, PASSWORD ggs_owner
RMTHOST repserver, MGRPORT 7809
EXTTRAIL d:\oracle\ggs\dirdat\rt
TABLE GGS_OWNER.TESTAB;



TARGET

CREATE TABLE TESTAB
(
ID NUMBER,
NAMEA VARCHAR2(16 CHAR),
NAMEB VARCHAR2(16 CHAR),
ARID ROWID,
BRID ROWID
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;

GOLDENGATE ggsci>
EDIT PARAMS rep1
REPLICAT rep1
ASSUMETARGETDEFS
USERID ggs_owner, PASSWORD ggs_owner
MAP GGS_OWNER.TESTAB, TARGET OTHERUSER.TESTAB;

After you insert delete update ..

Well Done.

Previous Topic: Problem using goldengate to extract data from ASM - 10G
Next Topic: GoldenGate 11.1.1.1 Encrypted TableSpace
Goto Forum:
  


Current Time: Sat Oct 31 09:17:44 CDT 2020