|
|
|
|
|
|
| Re: Can we use Materialize views in Streams [message #500535 is a reply to message #500452] |
Tue, 22 March 2011 01:44   |
kesavansundaram
Messages: 183
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 #500543 is a reply to message #500540] |
Tue, 22 March 2011 02:29 |
kesavansundaram
Messages: 183
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>
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 |
kesavansundaram
Messages: 183
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
|
|
|
|
|
|
| why MVIEWs are not relfected in ALL_STREAMS_UNSUPPORTED ? [message #500709 is a reply to message #500452] |
Wed, 23 March 2011 00:51 |
kesavansundaram
Messages: 183
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
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
Thank you,
kesavan
|
|
|
|
|
|
|
|
| Re: Can we use Materialize views in Streams (merged) [message #513888 is a reply to message #500452] |
Wed, 29 June 2011 22:00 |
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
