Hi Guys
I have a problem that's got me stumped.
We have many materialized views but I have chosen the top 7 in size and changed the way we perform a full refresh on them. As part of those changes I converted 5 of them to use pre-built tables (the other two were already on pre-built tables). Everything is working correctly, except for one materialised view in each of our 6 schemas which is telling me that the materialized view log is not there, even though it is, and refusing to do a fast refresh. The others are fine.
I converted the materialized view to be on a prebuilt table by using DROP MATERIALISED VIEW PRESERVE TABLE and then recreated it with the ON PREBUILT TABLE clause along with the log file and adding it to the refresh group.
In my testing I am logged in as the owner of the materialized views and log. I have dropped and recreated everything (table, indexes and constraints included) multiple times. I have even copied the unchanged materialised view back from live and it's telling me the log for that doesn't exist too. I have checked that there are no synonyms for the table and there are none. I have grabbed the DDL out of the database and the log is there.
Googling has not told me much, other than permissions, which I wouldn't have thought are an issue given that I am logged in as the owner and other materialized views are working correctly.
Database - 11.2.0.3
Platform - Solaris
The following code is DDL extracted from the database:
MV:
CREATE MATERIALIZED VIEW "LTS_DATA"."ODS_TRANSACTION_LOG" ("ZZ_PTRAN_KEY", "PTR", "DATABASE_NAME", "CLNO", "ACT", "CSEQ", "ASSNO", "BALNO", "TRCDE", "TYP", "AMNT", "TYPTR", "PDATE", "UUDATE", "UUTIME", "ORA_YR", "ORA_CPER", "SUPPRESS", "NARR1", "IS_PENDING", "LAST_CHANGED_DATE")
ON PREBUILT TABLE WITHOUT REDUCED PRECISION
USING INDEX
REFRESH FORCE ON DEMAND
WITH PRIMARY KEY USING DEFAULT LOCAL ROLLBACK SEGMENT
USING ENFORCED CONSTRAINTS ENABLE QUERY REWRITE
AS ( SELECT zz_ptran_key,
ptr,
'LTS' database_name,
DECODE( clno, ' ', NULL, clno ) clno,
DECODE( act, ' ', NULL, act ) act,
DECODE( cseq, ' ', NULL, cseq ) cseq,
DECODE( assno, ' ', NULL, assno ) assno,
balno,
DECODE( trcde, ' ', NULL, trcde ) trcde,
DECODE( typ, ' ', NULL, typ ) typ,
amnt,
DECODE( typtr, ' ', NULL, typtr ) typtr,
pdate,
uudate,
DECODE( uutime, ' ', NULL, uutime ) uutime,
DECODE( ora_yr, ' ', NULL, ora_yr ) ora_yr,
DECODE( ora_cper, ' ', NULL, ora_cper ) ora_cper,
DECODE( suppress, ' ', NULL, suppress ) suppress,
DECODE( narr1, ' ', NULL, narr1 ) narr1,
DECODE( is_pending, ' ', NULL, is_pending ) is_pending,
last_changed_date last_changed_date
FROM ods_transaction_log@lts_link )
MV Log:
CREATE MATERIALIZED VIEW LOG ON "LTS_DATA"."ODS_TRANSACTION_LOG"
PCTFREE 0 PCTUSED 0 INITRANS 2 MAXTRANS 255 NOLOGGING
STORAGE(INITIAL 1048576 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 MAXSIZE UNLIMITED
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "LTS_DATA_MVIEW_LOG_TS"
WITH PRIMARY KEY EXCLUDING NEW VALUES
Pre-built Table:
CREATE TABLE lts_data.ods_transaction_log
(
zz_ptran_key VARCHAR2( 10 BYTE ) NOT NULL,
ptr VARCHAR2( 6 BYTE ) NOT NULL,
database_name CHAR( 3 BYTE ),
clno VARCHAR2( 7 BYTE ),
act VARCHAR2( 2 BYTE ),
cseq VARCHAR2( 2 BYTE ),
assno VARCHAR2( 6 BYTE ),
balno NUMBER,
trcde VARCHAR2( 3 BYTE ),
typ VARCHAR2( 2 BYTE ),
amnt NUMBER,
typtr VARCHAR2( 1 BYTE ),
pdate DATE,
uudate DATE,
uutime VARCHAR2( 6 BYTE ),
ora_yr VARCHAR2( 4 BYTE ),
ora_cper VARCHAR2( 3 BYTE ),
suppress VARCHAR2( 1 BYTE ),
narr1 VARCHAR2( 80 BYTE ),
is_pending VARCHAR2( 1 BYTE ),
last_changed_date DATE
)
TABLESPACE lts_transactions_data_ts
RESULT_CACHE (MODE DEFAULT)
PCTUSED 0
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE( INITIAL 16 M
NEXT 128 K
MAXSIZE UNLIMITED
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT )
LOGGING
COMPRESS BASIC
NOCACHE
NOPARALLEL
MONITORING;
Indexes and Constraint:
CREATE UNIQUE INDEX lts_data.temp_ods_transaction_pk
ON lts_data.ods_transaction_log( zz_ptran_key, ptr, database_name )
LOGGING
TABLESPACE lts_transactions_index_ts
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE( INITIAL 1 M
NEXT 128 K
MAXSIZE UNLIMITED
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT )
NOPARALLEL;
ALTER TABLE lts_data.ods_transaction_log ADD (
CONSTRAINT temp_ods_transaction_pk
PRIMARY KEY
(zz_ptran_key, ptr, database_name)
USING INDEX lts_data.temp_ods_transaction_pk
ENABLE VALIDATE);
Any help would be greatly appreciated.
Thanks
Simon