Exporting and Importing an Materialized view.. Help please fast!!

How can I export and import a materialized view? I know it can be done. I need to do it right away.
I can't recreate the materialized view it takes too much temp_ts so I have to run it in a matter that I don't like so it takes longer.

What all do I need to do?  Any parameters... etc..

Thanks, so much
Gates2005Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sujit_kumarCommented:
you cannot export a materialized view independantly (as far as i know). You can export/import a User and Materialized view will come in it. I know it's not the most efficient solution, but the only possible one as per my knowledge.
anand_2000vCommented:
Why do you want to recreate it. Make sure that its refresh is FAST.
DrSQLCommented:
Gates2005,
   Materialized views have to be done with the USER or FULL options.  Importing them does not recreate them.  But, there are cautions if snapshot logs are involved.  On import, you cannot use the "table" option, although you can change schemas (fromuser/touser).  The work-around is to create (in the target db) an object (small table) for everything that might be in the export file - except the materialized view - and use ignore=n.

Here's a link:

http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10825/exp_imp.htm#sthref2734

Good luck!
DrSQL
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Gates2005Author Commented:
Oh, See I have a materialized view that was using a whole lot of temp space.   Well our database is set up in a matter not to over write extents in temp_ts which is a huge pain in the butt.  Well that temp_ts is now at 52GB and 99.98% full.  I can't create this materialized view without this NUMBER
alter session set "_optimizer_sortmerge_join_enabled"=FALSE;
therefore the creation takes FOREVER!! So what I wanted to do is just export the materialized view and import it into the other database / server.  Yes, it is a fast fresh mview I have a log created.

What the heck should I do.  I sure can't let this run 19 hours again.

Gates2005Author Commented:
The user I used to create the materialized view pretty much owns everything so that wouldn't work for me.
I only want the materialized views or  m view and that that's it.
DrSQLCommented:
Gates2005,
    I hope you're going to post a question about your temp space issue.  However, the work-around for your mv is going to take some experimentation.  This is what I expect you can do:

1) Create a table based on the materialized view

create table new_<mv name> as select * from <mv name>;

2) Export the table.
3) Import the table into the other db
4) Rename the table to the old mv name
5) create a materialized view (using the old definition), but tell it to use the table you just brought over. Use the "ON PREBUILT TABLE" clause.
6) Check the status, it may need a consider fresh or compile.

Here's a link to using a table to generate a materialized view:
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96520/mv.htm#574693

Good luck!
DrSQL
Gates2005Author Commented:
Hey I put that question out there.  I want to see if you are thinking in the same way I am.  
Gates2005Author Commented:
This is what I get when I try this.. I can export as a table and import then I try to make it a materialized view with fresh..
ERROR at line 4:
ORA-12014: table 'IM5_GAL_VIEW' does not contain a primary key constraint

Why??
anand_2000vCommented:
just create a primary key for that table 'IM5_gal_view'
Gates2005Author Commented:
Create the field and don't put anything in it.  
DrSQLCommented:
Gates2005,
   Either make the MV "WITH ROWID" (not recommended in most cases), or create a primary key CONSTRAINT.

alter table <table> primary key (<cols>);

But seeing the name of the object leads me to believe we're dealing with a view.  In that case, you'll need to select the rowid of the table being viewed and you WILL need to make the MV rowid based.  Can you post the definition of the MV?

Good luck!
DrSQL
Gates2005Author Commented:
MATERIALIZED VIEW QUERY - YES THESE WERE VIEWS THAT WE WANTED TO INCREASE PERFORMANCE.  PLUS THE FACT OF THE MIGRATING TO 9I AND COST FROM RULE.
SELECT
        PID.PID_NBR                      AS PARENT_ID,
        PID.PID_NAME                     AS PID_NAME,
        PID.CONV_FLAG                    AS CONV_FLAG,
        ACCT.ACCT_NBR                    AS ACCOUNT_NBR,
        ACCT.ACCT_NAME                   AS ACCOUNT_NAME,
        ACCT_LOI.RISK_NBR                AS RISK_NBR,
        CLAIM_1.CLM_NBR                  AS CLAIM_NBR,
        CLAIM_1.CLM_SUFFIX               AS CLAIM_SUFFIX,
        ACCT_LOI.LOI                     AS LOI,
        ACCT_LOI.EMPLR_SIC_CD            AS EMPLOYER_SIC_CODE,
        ACCT_LOI.CARR_INIT               AS CARRIER_INIT,
        INCIDENT.ST_CLM_NBR              AS INC_ST_CLM_NBR,
        INCIDENT.OPN_CLSD                AS INC_OPN_CLSD,
        INCIDENT.DT_CLSD                 AS INC_DT_CLSD,
        INCIDENT.DT_INCIDENT             AS DATE_OF_INCIDENT,
        INCIDENT.TIME_INCIDENT           AS TIME_OF_INCIDENT,
        INCIDENT.DT_RPT_EMPLR            AS INC_DT_RPT_EMPLR,
        INCIDENT.INCIDENT_LOCATION       AS INCIDENT_LOCATION,
        INCIDENT.POLICE_NOTIFY_INDICATOR AS POLICE_NOTIFY_INDICATOR,
        INCIDENT.POLICE_DEPARTMENT       AS POLICE_DEPARTMENT,
        INCIDENT.POLICE_REPORT_NUMBER    AS POLICE_REPORT_NUMBER,
        INCIDENT.INCIDENT_DESCRIPTION    AS INCIDENT_DESCRIPTION,
        INCIDENT.CONTACT_LAST_NAME       AS CONTACT_LAST_NAME,
        INCIDENT.CONTACT_FIRST_NAME      AS CONTACT_FIRST_NAME,
        INCIDENT.CONTACT_PHONE           AS CONTACT_PHONE,
        INCIDENT.INCIDENT_STATUS         AS INCIDENT_STATUS,
        INCIDENT.LAST_CLAIM_SUFFIX       AS LAST_CLAIM_SUFFIX,
        INCIDENT.LAST_KIND_SUFFIX        AS LAST_KIND_SUFFIX,
        INCIDENT.LAST_PARTY_SUFFIX       AS LAST_PARTY_SUFFIX,
        CLAIM_1.ST_CLM_NBR               AS STATE_CLAIM_NBR,
        ACCT_LEVEL1.LEVEL_1              AS LOCATION_1,
        ACCT_LEVEL1.LEVEL_NAME           AS LOCATION_1_NAME,
        ACCT_LEVEL1.DT_FROM_LOI          AS LOCATION_1_FROM_DT,
        ACCT_LEVEL1.DT_THRU_LOI          AS LOCATION_1_THRU_DT,
        ACCT_LEVEL2.LEVEL_2              AS LOCATION_2,
        ACCT_LEVEL2.LEVEL_NAME           AS LOCATION_2_NAME,
        ACCT_LEVEL2.DT_FROM_LOI          AS LOCATION_2_FROM_DT,
        ACCT_LEVEL2.DT_THRU_LOI          AS LOCATION_2_THRU_DT,
        ACCT_LEVEL3.LEVEL_3              AS LOCATION_3,
        ACCT_LEVEL3.LEVEL_NAME           AS LOCATION_3_NAME,
        ACCT_LEVEL3.DT_FROM_LOI          AS LOCATION_3_FROM_DT,
        ACCT_LEVEL3.DT_THRU_LOI          AS LOCATION_3_THRU_DT,
        ACCT_LEVEL4.LEVEL_4              AS LOCATION_4,
        ACCT_LEVEL4.LEVEL_NAME           AS LOCATION_4_NAME,
        ACCT_LEVEL4.DT_FROM_LOI          AS LOCATION_4_FROM_DT,
        ACCT_LEVEL4.DT_THRU_LOI          AS LOCATION_4_THRU_DT,
        ACCT_LEVEL5.LEVEL_5              AS LOCATION_5,
        ACCT_LEVEL5.LEVEL_NAME           AS LOCATION_5_NAME,
        ACCT_LEVEL5.DT_FROM_LOI          AS LOCATION_5_FROM_DT,
        ACCT_LEVEL5.DT_THRU_LOI          AS LOCATION_5_THRU_DT,
        SERV_OFC.SERV_OFC                AS SERV_OFC,
        SERV_OFC.SERV_OFC_NAME           AS SERV_OFC_NAME,
        CLAIM_1.EXAMR_RESP               AS EXAMINER_RESPONSIBLE,
        CLAIM_1.BEN_ST                   AS BENEFIT_ST,
        CLAIM_1.OPN_CLSD                 AS OPEN_CLOSED,
        CLAIM_1.CLM_TYPE                 AS CLAIM_TYPE,
        SUBSTR(CLAIM_1.CLM_STAT,1,5)     AS CLAIM_STATUS,
        CLAIM_1.CLMT_LST_NAME            AS CLMNT_LAST_NAME,
        CLAIM_1.CLMT_FIRST_NAME          AS CLMNT_FIRST_NAME,
        CLAIM_1.CLMT_MID_INIT            AS CLMNT_MIDDLE_INITIAL,
        CLAIM_2.CLMT_ADDR1               AS CLMNT_ADDR1,
        CLAIM_2.CLMT_ADDR2               AS CLMNT_ADDR2,
        CLAIM_2.CLMT_CITY                AS CLMNT_CITY,
        CLAIM_2.CLMT_ST                  AS CLMNT_ST,
        CLAIM_2.CLMT_ZIP_CD              AS CLMNT_ZIP_CD,
        CLAIM_1.CLMT_SSN                 AS CLMNT_SSN,
        CLAIM_2.CLMT_CNTRY               AS CLMNT_CNTRY,
        CLAIM_2.CLMT_HOME_PHN            AS CLMNT_HOME_PHN,
        CLAIM_2.CLMT_EXT                 AS CLMNT_EXT,
        CLAIM_1.DT_INJ                   AS DATE_OF_INJURY,
        CLAIM_1.TIME_INJ                 AS TIME_OF_INJURY,
        CLAIM_1.TIME_INJ_CD              AS TIME_OF_INJ_CD,
        CLAIM_1.DT_RPT_EMPLR             AS DATE_RPT_TO_EMPLOYER,
        CLAIM_1.DT_RECV                  AS DATE_RCVD_BY_GMCD,
        CLAIM_1.DT_INPT                  AS DATE_CLAIM_ENTERED,
        CLAIM_1.DT_CLSD                  AS DATE_CLAIM_CLOSED,
        CLAIM_1.DT_LST_ACTVY             AS DATE_OF_LAST_ACTIVITY,
        CLAIM_1.DT_REOPN                 AS DATE_REOPENED,
        CLAIM_1.INJ_CAUSE                AS INJ_CAUSE_CD,
        IJCA_DESC.TBL_DESC               AS INJ_CAUSE_DESC,
        CLAIM_1.PART_OF_BODY             AS POB_CD,
        IJPB_DESC.TBL_DESC               AS POB_DESC,
        CLAIM_1.NATR                     AS INJ_NATURE_CD,
        IJNT_DESC.TBL_DESC               AS INJ_NATURE_DESC,
        CLAIM_1.AGCY                     AS AGENCY_CD,
        AGAL_DESC.TBL_DESC               AS AGENCY_DESC,
        CLAIM_1.MANL_CLSR_REQR           AS MANUAL_CLASS_NBR,
        CLAIM_1.REC_ONLY                 AS RECORD_ONLY_FLAG,
        CLAIM_1.SEX                      AS SEX,
        NVL(CLAIM_1.AGE,TRUNC((CLAIM_1.DT_INJ - CLAIM_1.DT_BIRTH) / 365.25))
                                         AS AGE,
        CLAIM_1.TIME_EMPLD               AS TIME_EMPLOYED,
        CLAIM_1.DT_MED_TO_INDM           AS DATE_MED_CHG_TO_IND,
        CLAIM_1.DT_INDM_TO_MED           AS DATE_IND_CHG_TO_MED,
        TARGET_BUCKET('G','P',
         DECODE(PID.CONV_FLAG,'Y',CLAIM_1.CLM_KIND,'WC00'),
         '200',
         CLAIM_1.PTD_1,
         CLAIM_1.PTD_2,
         CLAIM_1.PTD_3,
         CLAIM_1.PTD_4,
         CLAIM_1.PTD_5,
         CLAIM_1.PTD_6,
         CLAIM_1.PTD_7,
         CLAIM_1.PTD_8,
         CLAIM_1.PTD_9)                   AS PTD_MEDICAL,
        TARGET_BUCKET('G','P',
         DECODE(PID.CONV_FLAG,'Y',CLAIM_1.CLM_KIND,'WC00'),
         '201',
         CLAIM_1.PTD_1,
         CLAIM_1.PTD_2,
         CLAIM_1.PTD_3,
         CLAIM_1.PTD_4,
         CLAIM_1.PTD_5,
         CLAIM_1.PTD_6,
         CLAIM_1.PTD_7,
         CLAIM_1.PTD_8,
         CLAIM_1.PTD_9)                   AS PTD_INDEMNITY,
        TARGET_BUCKET('G','P',
         DECODE(PID.CONV_FLAG,'Y',CLAIM_1.CLM_KIND,'WC00'),
         '202',
         CLAIM_1.PTD_1,
         CLAIM_1.PTD_2,
         CLAIM_1.PTD_3,
         CLAIM_1.PTD_4,
         CLAIM_1.PTD_5,
         CLAIM_1.PTD_6,
         CLAIM_1.PTD_7,
         CLAIM_1.PTD_8,
         CLAIM_1.PTD_9)                   AS PTD_EXPENSES,
        TARGET_BUCKET('G','P',
         DECODE(PID.CONV_FLAG,'Y',CLAIM_1.CLM_KIND,'WC00'),
         '203',
         CLAIM_1.PTD_1,
         CLAIM_1.PTD_2,
         CLAIM_1.PTD_3,
         CLAIM_1.PTD_4,
         CLAIM_1.PTD_5,
         CLAIM_1.PTD_6,
         CLAIM_1.PTD_7,
         CLAIM_1.PTD_8,
         CLAIM_1.PTD_9)                   AS PTD_RECOVERABLE,
        TARGET_BUCKET('G','P',
         DECODE(PID.CONV_FLAG,'Y',CLAIM_1.CLM_KIND,'WC00'),
         '204',
         CLAIM_1.PTD_1,
         CLAIM_1.PTD_2,
         CLAIM_1.PTD_3,
         CLAIM_1.PTD_4,
         CLAIM_1.PTD_5,
         CLAIM_1.PTD_6,
         CLAIM_1.PTD_7,
         CLAIM_1.PTD_8,
         CLAIM_1.PTD_9)                   AS PTD_PROPERTY_DAMAGE,
        TARGET_BUCKET('G','P',
         DECODE(PID.CONV_FLAG,'Y',CLAIM_1.CLM_KIND,'WC00'),
         '205',
         CLAIM_1.PTD_1,
         CLAIM_1.PTD_2,
         CLAIM_1.PTD_3,
         CLAIM_1.PTD_4,
         CLAIM_1.PTD_5,
         CLAIM_1.PTD_6,
         CLAIM_1.PTD_7,
         CLAIM_1.PTD_8,
         CLAIM_1.PTD_9)                   AS PTD_BODILY_INJURY,
        TARGET_BUCKET('G','P',
         DECODE(PID.CONV_FLAG,'Y',CLAIM_1.CLM_KIND,'WC00'),
         '206',
         CLAIM_1.PTD_1,
         CLAIM_1.PTD_2,
         CLAIM_1.PTD_3,
         CLAIM_1.PTD_4,
         CLAIM_1.PTD_5,
         CLAIM_1.PTD_6,
         CLAIM_1.PTD_7,
         CLAIM_1.PTD_8,
         CLAIM_1.PTD_9)                   AS PTD_OTHER,
        TARGET_BUCKET('G','P',
         DECODE(PID.CONV_FLAG,'Y',CLAIM_1.CLM_KIND,'WC00'),
         '207',
         CLAIM_1.PTD_1,
         CLAIM_1.PTD_2,
         CLAIM_1.PTD_3,
         CLAIM_1.PTD_4,
         CLAIM_1.PTD_5,
         CLAIM_1.PTD_6,
         CLAIM_1.PTD_7,
         CLAIM_1.PTD_8,
         CLAIM_1.PTD_9)                   AS PTD_CONTENTS,
        TARGET_BUCKET('G','P',
         DECODE(PID.CONV_FLAG,'Y',CLAIM_1.CLM_KIND,'WC00'),
         '208',
         CLAIM_1.PTD_1,
         CLAIM_1.PTD_2,
         CLAIM_1.PTD_3,
         CLAIM_1.PTD_4,
         CLAIM_1.PTD_5,
         CLAIM_1.PTD_6,
         CLAIM_1.PTD_7,
         CLAIM_1.PTD_8,
         CLAIM_1.PTD_9)                   AS PTD_STRUCTURES,
        TARGET_BUCKET('G','P',
         DECODE(PID.CONV_FLAG,'Y',CLAIM_1.CLM_KIND,'WC00'),
         '209',
         CLAIM_1.PTD_1,
         CLAIM_1.PTD_2,
         CLAIM_1.PTD_3,
         CLAIM_1.PTD_4,
         CLAIM_1.PTD_5,
         CLAIM_1.PTD_6,
         CLAIM_1.PTD_7,
         CLAIM_1.PTD_8,
         CLAIM_1.PTD_9)                   AS PTD_RECOVERY,
        TARGET_BUCKET('G','R',
         DECODE(PID.CONV_FLAG,'Y',CLAIM_1.CLM_KIND,'WC00'),
         '200',
         RESERVE.RESV_1,
         RESERVE.RESV_2,
         RESERVE.RESV_3,
         RESERVE.RESV_4,
         RESERVE.RESV_5,
         RESERVE.RESV_6,
         RESERVE.RESV_7,
         RESERVE.RESV_8,
         RESERVE.RESV_9)                  AS RESV_MEDICAL,
        TARGET_BUCKET('G','R',
         DECODE(PID.CONV_FLAG,'Y',CLAIM_1.CLM_KIND,'WC00'),
         '201',
         RESERVE.RESV_1,
         RESERVE.RESV_2,
         RESERVE.RESV_3,
         RESERVE.RESV_4,
         RESERVE.RESV_5,
         RESERVE.RESV_6,
         RESERVE.RESV_7,
         RESERVE.RESV_8,
         RESERVE.RESV_9)                  AS RESV_INDEMNITY,
        TARGET_BUCKET('G','R',
         DECODE(PID.CONV_FLAG,'Y',CLAIM_1.CLM_KIND,'WC00'),
         '202',
         RESERVE.RESV_1,
         RESERVE.RESV_2,
         RESERVE.RESV_3,
         RESERVE.RESV_4,
         RESERVE.RESV_5,
         RESERVE.RESV_6,
         RESERVE.RESV_7,
         RESERVE.RESV_8,
         RESERVE.RESV_9)                  AS RESV_EXPENSES,
        TARGET_BUCKET('G','R',
         DECODE(PID.CONV_FLAG,'Y',CLAIM_1.CLM_KIND,'WC00'),
         '203',
         RESERVE.RESV_1,
         RESERVE.RESV_2,
         RESERVE.RESV_3,
         RESERVE.RESV_4,
         RESERVE.RESV_5,
         RESERVE.RESV_6,
         RESERVE.RESV_7,
         RESERVE.RESV_8,
         RESERVE.RESV_9)                  AS RESV_RECOVERABLE,
        TARGET_BUCKET('G','R',
         DECODE(PID.CONV_FLAG,'Y',CLAIM_1.CLM_KIND,'WC00'),
         '204',
         RESERVE.RESV_1,
         RESERVE.RESV_2,
         RESERVE.RESV_3,
         RESERVE.RESV_4,
         RESERVE.RESV_5,
         RESERVE.RESV_6,
         RESERVE.RESV_7,
         RESERVE.RESV_8,
         RESERVE.RESV_9)                  AS RESV_PROPERTY_DAMAGE,
        TARGET_BUCKET('G','R',
         DECODE(PID.CONV_FLAG,'Y',CLAIM_1.CLM_KIND,'WC00'),
         '205',
         RESERVE.RESV_1,
         RESERVE.RESV_2,
         RESERVE.RESV_3,
         RESERVE.RESV_4,
         RESERVE.RESV_5,
         RESERVE.RESV_6,
         RESERVE.RESV_7,
         RESERVE.RESV_8,
         RESERVE.RESV_9)                  AS RESV_BODILY_INJURY,
        TARGET_BUCKET('G','R',
         DECODE(PID.CONV_FLAG,'Y',CLAIM_1.CLM_KIND,'WC00'),
         '206',
         RESERVE.RESV_1,
         RESERVE.RESV_2,
         RESERVE.RESV_3,
         RESERVE.RESV_4,
         RESERVE.RESV_5,
         RESERVE.RESV_6,
         RESERVE.RESV_7,
         RESERVE.RESV_8,
         RESERVE.RESV_9)                  AS RESV_OTHER,
        TARGET_BUCKET('G','R',
         DECODE(PID.CONV_FLAG,'Y',CLAIM_1.CLM_KIND,'WC00'),
         '207',
         RESERVE.RESV_1,
         RESERVE.RESV_2,
         RESERVE.RESV_3,
         RESERVE.RESV_4,
         RESERVE.RESV_5,
         RESERVE.RESV_6,
         RESERVE.RESV_7,
         RESERVE.RESV_8,
         RESERVE.RESV_9)                  AS RESV_CONTENTS,
        TARGET_BUCKET('G','R',
         DECODE(PID.CONV_FLAG,'Y',CLAIM_1.CLM_KIND,'WC00'),
         '208',
         RESERVE.RESV_1,
         RESERVE.RESV_2,
         RESERVE.RESV_3,
         RESERVE.RESV_4,
         RESERVE.RESV_5,
         RESERVE.RESV_6,
         RESERVE.RESV_7,
         RESERVE.RESV_8,
         RESERVE.RESV_9)                  AS RESV_STRUCTURES,
        TARGET_BUCKET('G','R',
         DECODE(PID.CONV_FLAG,'Y',CLAIM_1.CLM_KIND,'WC00'),
         '209',
         RESERVE.RESV_1,
         RESERVE.RESV_2,
         RESERVE.RESV_3,
         RESERVE.RESV_4,
         RESERVE.RESV_5,
         RESERVE.RESV_6,
         RESERVE.RESV_7,
         RESERVE.RESV_8,
         RESERVE.RESV_9)                   AS RESV_RECOVERY,
        CLAIM_1.DT_DEATH                   AS DATE_OF_DEATH,
        CLAIM_1.SHIFT                      AS SHIFT,
        CLAIM_1.INJ_CLASS_CD               AS INJ_CLASS_CD,
        ICXX_DESC.TBL_DESC                 AS INJ_CLASS_DESC,
        CLAIM_1.OCCUP_CD                   AS OCCUPATION_CD,
        CLAIM_1.OCCUP                      AS OCCUPATION_DESC,
        CLAIM_1.NATR_OF_INJ                AS INJ_NATURE,
        CLAIM_1.ACCD_DESC                  AS ACCIDENT_DESC,
        CLAIM_2.EMPL_NBR                   AS EMPL_NBR,
        PAYMENT.DT_PYMT                    AS DATE_OF_PAYMENT,
        PAYMENT.CPO_CHK_NBR                AS CPO_CHECK_NBR,
        PAYMENT.PYMT_AMT                   AS PAYMENT_AMT,
        PAYMENT.PYMT_STAT                  AS PAYMENT_STATUS,
        DECODE(PAYMENT.TAXPYR_PAYEE_1,NULL,
           DECODE(PAYMENT.PAY_CLMT,'Y',
          (RTRIM(CLAIM_1.CLMT_LST_NAME) || ',' ||
           RTRIM(CLAIM_1.CLMT_FIRST_NAME) || ' ' ||
           RTRIM(CLAIM_1.CLMT_MID_INIT)),
           RTRIM(TIN.TAXPYR_PAYEE_1)),RTRIM(PAYMENT.TAXPYR_PAYEE_1))
                                           AS PAYEE_NAME_1,
        DECODE(PAYMENT.TAXPYR_PAYEE_2,NULL,
                DECODE(PAYMENT.PAY_CLMT,'Y',' ',
        RTRIM(TIN.TAXPYR_PAYEE_2)),RTRIM(PAYMENT.TAXPYR_PAYEE_2))
                                           AS PAYEE_NAME_2,
        DECODE(PAYMENT.TAXPYR_ADDR1,NULL,TIN.TAXPYR_ADDR1,
               PAYMENT.TAXPYR_ADDR1)       AS PAYEE_ADDRESS_1,
        DECODE(PAYMENT.TAXPYR_ADDR2,NULL,TIN.TAXPYR_ADDR2,
               PAYMENT.TAXPYR_ADDR2)       AS PAYEE_ADDRESS_2,
        DECODE(PAYMENT.TAXPYR_CITY,NULL,TIN.TAXPYR_CITY,
               PAYMENT.TAXPYR_CITY)        AS PAYEE_CITY,
        DECODE(PAYMENT.TAXPYR_ST,NULL,TIN.TAXPYR_ST,
               PAYMENT.TAXPYR_ST)          AS PAYEE_ST,
        DECODE(PAYMENT.TAXPYR_ZIP_CD,NULL,TIN.TAXPYR_ZIP_CD,
               PAYMENT.TAXPYR_ZIP_CD)      AS PAYEE_ZIP_CD,
        PAYMENT.TIN                        AS PAYMENT_TIN,
        TIN.FED_SSN_FLAG                   AS TIN_FED_SSN_FLAG,
        TIN.TAXPYR_ADDR_SEQ                AS TIN_ADDR_SEQ,
        PAYMENT.PYMT_CD                    AS PAYMENT_CD,
        PYCD_DESC.TBL_DESC                 AS PAYMENT_DESC,
        PAYMENT.CYCL_IND                   AS PAYMENT_CYCLE_IND,
        PAYMENT.DT_CHK_REGTR               AS DATE_CHECK_REGISTER,
        PAYMENT.DT_RECV                    AS DATE_BILL_RECEIVED,
        PAYMENT.DT_FROM_SERV               AS DATE_FROM_SERVICE,
        PAYMENT.DT_THRU_SERV               AS DATE_THRU_SERVICE,
        PAYMENT.DT_INPT                    AS DATE_PAYMENT_INPUT,
        PAYMENT.INPT_PROCS_UN              AS PAYMENT_INPUT_PROCESSOR,
        PAYMENT.TIME_INPT                  AS TIME_PAYMENT_INPUT,
        PAYMENT.WKLY_COMP_RT               AS PAYMENT_WKLY_COMP_RT,
        PAYMENT.PARTL_FINL_IND             AS PAYMENT_PARTL_FINL_IND,
        PAYMENT.PYMT_ACTN                  AS PAYMENT_ACTION_CD,
        PAYMENT.FORM                       AS PAYMENT_FORM,
        PAYMENT.MULTI_CHK_IND              AS MULTI_CHECK_IND,
        PAYMENT.MANL_PYMT_IND              AS MANUAL_PYMT_IND,
        PAYMENT.TAIL_CLM_PYMT              AS TAIL_CLAIM_PYMT_IND,
        PAYMENT.POLCY_NBR                  AS POLICY_NBR,
        PAYMENT.LST_ACTVY_UN               AS PAY_LAST_ACTIVITY_UNIT,
        PAYMENT.DT_LST_ACTVY               AS DATE_OF_PAY_LAST_ACTIVITY,
        PAYMENT.PAY_OPTL                   AS PAY_TO_OPTIONAL,
        PAYMENT.PAY_CLMT                   AS PAY_TO_CLAIMANT,
        PAYMENT.CKACCT_NBR_KEY             AS CHECK_ACCT_NBR,
        PAYMENT.PYMT_GRP_ID                AS PYMT_GRP_ID,
        PAYMENT.TOT_GRP_REC_CNT            AS TOT_GRP_REC_CNT,
        PAYMENT.TOT_GRP_PYMT_AMT           AS TOT_GRP_PYMT_AMT,
        ASSOCIATE.HP_LOGON                 AS FIELD_OFFICE,
        ASSOCIATE.USER_LST_NAME            AS EXAMINER_LAST_NAME,
        ASSOCIATE.USER_FIRST_NAME          AS EXAMINER_FIRST_NAME,
        ASSOCIATE.DT_HIRE                  AS DATE_EXAMINER_HIRED,
        ASSOCIATE.DT_TERMN                 AS DATE_EXAMINER_TERMINATED,
        ASSOCIATE.SUPV                     AS EXAMINERS_SUPERVISER,
        ASSOCIATE.MGR                      AS EXAMINERS_MANAGER,
        CLAIM_1.CLM_KIND                   AS CLM_KIND,
        CLAIM_1.BUS_UNIT                   AS BUSINESS_UNIT,
        CLAIM_1.NURSE_RESP                 AS NURSE_RESP,
        CLAIM_2.NURSE_PROCS_UN             AS NURSE_PROCESSING_UN,
        CLAIM_2.MAJ_CLASS_CD               AS MAJ_CLASS_CD,
        CLAIM_1.DT_BIRTH                   AS DATE_OF_BIRTH,
        CLAIM_2.AVG_WK_COMP_RT             AS AVG_WEEKLY_COMP_RATE,
        CLAIM_2.TAIL_CLM_IND               AS TAIL_CLAIM_INDICATOR,
        CLAIM_2.CLM_REF_1                  AS CLAIM_REF_1
FROM
        SERV_OFC,
        ASSOCIATE,
        AGAL_DESC,
        ICXX_DESC,
        IJCA_DESC,
        IJNT_DESC,
        IJPB_DESC,
        PYCD_DESC,
        ACCT_LEVEL5,
        ACCT_LEVEL4,
        ACCT_LEVEL3,
        ACCT_LEVEL2,
        ACCT_LEVEL1,
        PAYMENT,
        RESERVE,
        CLAIM_2,
        CLAIM_1,
        INCIDENT,
        TIN,
        ACCT_LOI,
        ACCT,
        PID
WHERE
        PID.PID_SEQ_ID             = ACCT.PID_SEQ_ID_FK            AND
        ACCT.ACCT_SEQ_ID           = ACCT_LOI.ACCT_SEQ_ID_FK       AND
        INCIDENT.ACCT_LOI_SEQ_ID_FK(+) = ACCT_LOI.ACCT_LOI_SEQ_ID  AND
        CLAIM_1.INC_SEQ_ID_FK(+)       = INCIDENT.INC_SEQ_ID       AND
        CLAIM_1.CLM_SEQ_ID         = PAYMENT.CLM_SEQ_ID_FK         AND
        CLAIM_1.OPN_CLSD           <> 'Z'                          AND
        RESERVE.CLM_SEQ_ID_FK(+)   = CLAIM_1.CLM_SEQ_ID            AND
        CLAIM_2.CLM_SEQ_ID_FK      = CLAIM_1.CLM_SEQ_ID            AND
        (
         (EXISTS (SELECT * FROM RESERVE R
            WHERE R.CLM_SEQ_ID_FK = CLAIM_1.CLM_SEQ_ID))           AND
          (RESERVE.RESV_SEQ_ID =
            (SELECT MAX(R.RESV_SEQ_ID) FROM RESERVE R
             WHERE R.CLM_SEQ_ID_FK = CLAIM_1.CLM_SEQ_ID))          OR
         (NOT EXISTS (SELECT * FROM RESERVE R
            WHERE R.CLM_SEQ_ID_FK = CLAIM_1.CLM_SEQ_ID)))          AND
        ACCT_LEVEL1.LEVEL1_SEQ_ID(+) = CLAIM_1.LEVEL1_SEQ_ID_FK    AND
        ACCT_LEVEL2.LEVEL2_SEQ_ID(+) = CLAIM_1.LEVEL2_SEQ_ID_FK    AND
        ACCT_LEVEL3.LEVEL3_SEQ_ID(+) = CLAIM_1.LEVEL3_SEQ_ID_FK    AND
        ACCT_LEVEL4.LEVEL4_SEQ_ID(+) = CLAIM_1.LEVEL4_SEQ_ID_FK    AND
        ACCT_LEVEL5.LEVEL5_SEQ_ID(+) = CLAIM_1.LEVEL5_SEQ_ID_FK    AND
        ASSOCIATE.PROCS_UN(+) = CLAIM_1.EXAMR_RESP                   AND
        ASSOCIATE.SERV_OFC_SEQ_ID_FK(+) = CLAIM_1.SERV_OFC_SEQ_ID_FK AND
        TIN.TIN_SEQ_ID (+)       = PAYMENT.TIN_SEQ_ID_FK         AND
        SERV_OFC.SERV_OFC_SEQ_ID = CLAIM_1.SERV_OFC_SEQ_ID_FK    AND
        (AGAL_DESC.TBL_TYPE      = ACCT_LOI.AGCY_CD_TBL          OR
         AGAL_DESC.TBL_TYPE IS NULL)                             AND
         AGAL_DESC.TBL_CD(+)     = CLAIM_1.AGCY                  AND
        ICXX_DESC.TBL_CD    (+)  = CLAIM_1.INJ_CLASS_CD          AND
        IJCA_DESC.TBL_CD    (+)  = CLAIM_1.INJ_CAUSE             AND
        IJPB_DESC.TBL_CD    (+)  = CLAIM_1.PART_OF_BODY          AND
        IJNT_DESC.TBL_CD    (+)  = CLAIM_1.NATR                  AND
        PYCD_DESC.TBL_CD    (+)  = PAYMENT.PYMT_CD
DrSQLCommented:
Gates2005,
    You would need a materialized view log on every table or on every table that comprises:

        SERV_OFC,
        ASSOCIATE,
        AGAL_DESC,
        ICXX_DESC,
        IJCA_DESC,
        IJNT_DESC,
        IJPB_DESC,
        PYCD_DESC,
        ACCT_LEVEL5,
        ACCT_LEVEL4,
        ACCT_LEVEL3,
        ACCT_LEVEL2,
        ACCT_LEVEL1,
        PAYMENT,
        RESERVE,
        CLAIM_2,
        CLAIM_1,
        INCIDENT,
        TIN,
        ACCT_LOI,
        ACCT,
        PID

Every table should have a primary key constraint. But, I don't see the IM5_gal_view?  Can you show us the FULL "create materialized view" command (without reposting the query portion)?

Good luck!
DrSQL
Gates2005Author Commented:
OH, Sorry they all do have materialized view logs.
Gates2005Author Commented:
With me creating all the logs and just stating to use the primary key and not telling Oracle which is the primary key it won't work?
DrSQLCommented:
Gates2005,
   Primary keys need to be declared, or the MV has to be rowid based.  I still don't see the IM5_gal_view from your error message?  And, can you show us the FULL "create materialized view" command (without reposting the query portion)?

Good luck!
DrSQL
Gates2005Author Commented:
/****************************************************************************/
/* V5GMCLGL.SCRIPT */
/* Revised: 05/16/2000 16:22 **/
/*  06/15/00 J. Fisher Added link to agal_desc table.                       */
/****************************************************************************/

create materialized view cas2_claim5_gl_view
tablespace mviews_ts
refresh complete
start with sysdate next sysdate + 1
with primary key
as
     SELECT
        claim_1.clm_seq_id                As clm_seq_id,
        PID.PID_NBR                       AS PID_NBR,
        PID.PID_NAME                      AS PID_NAME,
        PID.CONV_FLAG                     AS CONV_FLAG,
        ACCT.ACCT_NBR                     AS ACCOUNT_NBR,
        ACCT_LOI.ACCT_NAME                AS ACCOUNT_NAME,
        ACCT_LEVEL1.LEVEL_1               AS LOCATION_1,
        ACCT_LEVEL1.LEVEL_NAME            AS LOCATION_1_NAME,
        ACCT_LEVEL2.LEVEL_2               AS LOCATION_2,
        ACCT_LEVEL2.LEVEL_NAME            AS LOCATION_2_NAME,
        ACCT_LEVEL3.LEVEL_3               AS LOCATION_3,
        ACCT_LEVEL3.LEVEL_NAME            AS LOCATION_3_NAME,
        ACCT_LEVEL4.LEVEL_4               AS LOCATION_4,
        ACCT_LEVEL4.LEVEL_NAME            AS LOCATION_4_NAME,
        ACCT_LEVEL5.LEVEL_5               AS LOCATION_5,
        ACCT_LEVEL5.LEVEL_NAME            AS LOCATION_5_NAME,
        CLAIM_GL.KIND_SUFFIX              AS CLAIM_SUFFIX,
        ACCT_LOI.LOI                      AS LOI,
        INCIDENT.CLM_NBR                  AS CLAIM_NBR,
        INCIDENT.CLM_KIND                 AS CLAIM_KIND,
        CLAIM_GL.ISO_CAT_NBR              AS ISO_CAT_NBR,
        INCIDENT.ST_CLM_NBR               AS INC_ST_CLM_NBR,
        INCIDENT.OPN_CLSD                 AS INC_OPN_CLSD,
        INCIDENT.DT_CLSD                  AS INC_DT_CLSD,
        INCIDENT.DT_INCIDENT              AS DATE_OF_INCIDENT,
        INCIDENT.TIME_INCIDENT            AS TIME_OF_INCIDENT,
        INCIDENT.DT_RPT_EMPLR             AS INC_DT_RPT_EMPLR,
        INCIDENT.INCIDENT_LOCATION        AS INCDNT_LOC,
        INCIDENT.POLICE_NOTIFY_INDICATOR  AS POLICE_NOTIFY_INDICTR,
        INCIDENT.POLICE_DEPARTMENT        AS POLICE_DEPT,
        INCIDENT.POLICE_REPORT_NUMBER     AS POLICE_REPORT_NUM,
        INCIDENT.INCIDENT_DESCRIPTION     AS INCIDNT_DESC,
        INCIDENT.INCIDENT_STATUS          AS INCIDNT_STAT,
        INCIDENT.LAST_CLAIM_SUFFIX        AS LST_CLM_SUFFIX,
        INCIDENT.LAST_KIND_SUFFIX         AS LST_KIND_SUFFIX,
        INCIDENT.LAST_PARTY_SUFFIX        AS LAST_PARTY_SUFFIX,
        INCIDENT.EXAMR_RESP               AS EXAMR_RESP,
        INCIDENT.CONTACT_LAST_NAME        AS CONTACT_LAST_NAME,
        INCIDENT.CONTACT_FIRST_NAME       AS CONTACT_FIRST_NAME,
        SERV_OFC.SERV_OFC                 AS SERV_OFC,
        SERV_OFC.SERV_OFC_NAME            AS SERV_OFC_NAME,
        CLAIM_1.INPT_PROCS_UN             AS PROCESS_UNIT,
        CLAIM_1.CLM_TYPE                  AS CLAIM_TYPE,
        SUBSTR(CLAIM_1.CLM_STAT,1,5)      AS CLAIM_STATUS,
        CLAIM_1.OPN_CLSD                  AS CLAIM_OPEN_CLOSED,
        CLAIM_1.CLMT_LST_NAME             AS CLMNT_LAST_NAME,
        CLAIM_1.CLMT_FIRST_NAME           AS CLMNT_FIRST_NAME,
        CLAIM_1.CLMT_MID_INIT             AS CLMNT_MIDDLE_INITIAL,
        CLAIM_1.CLMT_SSN                  AS CLMNT_SSN,
        CLAIM_1.DT_BIRTH                  AS DATE_OF_BIRTH,
        CLAIM_1.DT_INJ                    AS DATE_OF_INJURY,
        CLAIM_1.DT_RPT_EMPLR              AS DATE_RPT_EMPLOYER,
        CLAIM_1.DT_RECV                   AS DATE_RCVD_BY_GMCD,
        CLAIM_1.DT_INPT                   AS DATE_CLAIM_ENTERED,
        CLAIM_1.TIME_INPT                 AS TIME_CLAIM_ENTERED,
        CLAIM_1.DT_CLSD                   AS DATE_CLAIM_CLOSED,
        CLAIM_1.DT_REOPN                  AS DATE_REOPENED,
        CLAIM_1.INJ_CAUSE                 AS INJ_CAUSE_CD,
        CLAIM_2.TAIL_CLM_IND              AS TAIL_CLAIM_INDICATOR,
        IJCA_DESC.TBL_DESC                AS INJ_CAUSE_DESC,
        CLAIM_1.PART_OF_BODY              AS POB_CD,
        IJPB_DESC.TBL_DESC                AS POB_DESC,
        CLAIM_1.NATR                      AS INJ_NATURE_CD,
        IJNT_DESC.TBL_DESC                AS INJ_NATURE_DESC,
        CLAIM_1.AGCY                      AS AGENCY_CD,
        AGAL_DESC.TBL_DESC                AS AGENCY_CD_DESC,
        CLAIM_1.REC_ONLY                  AS RECORD_ONLY_FLAG,
        CLAIM_1.SEX                       AS CLMNT_SEX,
        NVL(CLAIM_1.AGE,TRUNC((CLAIM_1.DT_INJ - CLAIM_1.DT_BIRTH) / 365.25))
                                          AS AGE,
        CLAIM_1.TIME_EMPLD                AS CLMNT_TIME_EMPLOYED,
        TARGET_BUCKET('G','P',
        DECODE(PID.CONV_FLAG,'Y',CLAIM_1.CLM_KIND,'WC00'),
        '200',
        CLAIM_1.PTD_1,
        CLAIM_1.PTD_2,
        CLAIM_1.PTD_3,
        CLAIM_1.PTD_4,
        CLAIM_1.PTD_5,
        CLAIM_1.PTD_6,
        CLAIM_1.PTD_7,
        CLAIM_1.PTD_8,
        CLAIM_1.PTD_9)                    AS PTD_MEDICAL,
        TARGET_BUCKET('G','P',
        DECODE(PID.CONV_FLAG,'Y',CLAIM_1.CLM_KIND,'WC00'),
        '201',
        CLAIM_1.PTD_1,
        CLAIM_1.PTD_2,
        CLAIM_1.PTD_3,
        CLAIM_1.PTD_4,
        CLAIM_1.PTD_5,
        CLAIM_1.PTD_6,
        CLAIM_1.PTD_7,
        CLAIM_1.PTD_8,
        CLAIM_1.PTD_9)                 AS PTD_INDEMNITY,
        TARGET_BUCKET('G','P',
        DECODE(PID.CONV_FLAG,'Y',CLAIM_1.CLM_KIND,'WC00'),
        '202',
        CLAIM_1.PTD_1,
        CLAIM_1.PTD_2,
        CLAIM_1.PTD_3,
        CLAIM_1.PTD_4,
        CLAIM_1.PTD_5,
        CLAIM_1.PTD_6,
        CLAIM_1.PTD_7,
        CLAIM_1.PTD_8,
        CLAIM_1.PTD_9)                 AS PTD_EXPENSES,
        TARGET_BUCKET('G','P',
        DECODE(PID.CONV_FLAG,'Y',CLAIM_1.CLM_KIND,'WC00'),
        '203',
        CLAIM_1.PTD_1,
        CLAIM_1.PTD_2,
        CLAIM_1.PTD_3,
        CLAIM_1.PTD_4,
        CLAIM_1.PTD_5,
        CLAIM_1.PTD_6,
        CLAIM_1.PTD_7,
        CLAIM_1.PTD_8,
        CLAIM_1.PTD_9)                 AS PTD_RECOVERABLE,
        TARGET_BUCKET('G','P',
        DECODE(PID.CONV_FLAG,'Y',CLAIM_1.CLM_KIND,'WC00'),
        '204',
        CLAIM_1.PTD_1,
        CLAIM_1.PTD_2,
        CLAIM_1.PTD_3,
        CLAIM_1.PTD_4,
        CLAIM_1.PTD_5,
        CLAIM_1.PTD_6,
        CLAIM_1.PTD_7,
        CLAIM_1.PTD_8,
        CLAIM_1.PTD_9)                 AS PTD_PROPERTY_DAMAGE,
        TARGET_BUCKET('G','P',
        DECODE(PID.CONV_FLAG,'Y',CLAIM_1.CLM_KIND,'WC00'),
        '205',
        CLAIM_1.PTD_1,
        CLAIM_1.PTD_2,
        CLAIM_1.PTD_3,
        CLAIM_1.PTD_4,
        CLAIM_1.PTD_5,
        CLAIM_1.PTD_6,
        CLAIM_1.PTD_7,
        CLAIM_1.PTD_8,
        CLAIM_1.PTD_9)                 AS PTD_BODILY_INJURY,
        TARGET_BUCKET('G','P',
        DECODE(PID.CONV_FLAG,'Y',CLAIM_1.CLM_KIND,'WC00'),
        '206',
        CLAIM_1.PTD_1,
        CLAIM_1.PTD_2,
        CLAIM_1.PTD_3,
        CLAIM_1.PTD_4,
        CLAIM_1.PTD_5,
        CLAIM_1.PTD_6,
        CLAIM_1.PTD_7,
        CLAIM_1.PTD_8,
        CLAIM_1.PTD_9)                 AS PTD_OTHER,
        TARGET_BUCKET('G','P',
        DECODE(PID.CONV_FLAG,'Y',CLAIM_1.CLM_KIND,'WC00'),
        '207',
        CLAIM_1.PTD_1,
        CLAIM_1.PTD_2,
        CLAIM_1.PTD_3,
        CLAIM_1.PTD_4,
        CLAIM_1.PTD_5,
        CLAIM_1.PTD_6,
        CLAIM_1.PTD_7,
        CLAIM_1.PTD_8,
        CLAIM_1.PTD_9)                 AS PTD_CONTENTS,
        TARGET_BUCKET('G','P',
        DECODE(PID.CONV_FLAG,'Y',CLAIM_1.CLM_KIND,'WC00'),
        '208',
        CLAIM_1.PTD_1,
        CLAIM_1.PTD_2,
        CLAIM_1.PTD_3,
        CLAIM_1.PTD_4,
        CLAIM_1.PTD_5,
        CLAIM_1.PTD_6,
        CLAIM_1.PTD_7,
        CLAIM_1.PTD_8,
        CLAIM_1.PTD_9)                 AS PTD_STRUCTURES,
        TARGET_BUCKET('G','P',
        DECODE(PID.CONV_FLAG,'Y',CLAIM_1.CLM_KIND,'WC00'),
        '209',
        CLAIM_1.PTD_1,
        CLAIM_1.PTD_2,
        CLAIM_1.PTD_3,
        CLAIM_1.PTD_4,
        CLAIM_1.PTD_5,
        CLAIM_1.PTD_6,
        CLAIM_1.PTD_7,
        CLAIM_1.PTD_8,
        CLAIM_1.PTD_9)                 AS PTD_RECOVERY,
        TARGET_BUCKET('G','R',
        DECODE(PID.CONV_FLAG,'Y',CLAIM_1.CLM_KIND,'WC00'),
        '200',
        RESERVE.RESV_1,
        RESERVE.RESV_2,
        RESERVE.RESV_3,
        RESERVE.RESV_4,
        RESERVE.RESV_5,
        RESERVE.RESV_6,
        RESERVE.RESV_7,
        RESERVE.RESV_8,
        RESERVE.RESV_9)                AS RESV_MEDICAL,
        TARGET_BUCKET('G','R',
        DECODE(PID.CONV_FLAG,'Y',CLAIM_1.CLM_KIND,'WC00'),
        '201',
        RESERVE.RESV_1,
        RESERVE.RESV_2,
        RESERVE.RESV_3,
        RESERVE.RESV_4,
        RESERVE.RESV_5,
        RESERVE.RESV_6,
        RESERVE.RESV_7,
        RESERVE.RESV_8,
        RESERVE.RESV_9)                AS RESV_INDEMNITY,
        TARGET_BUCKET('G','R',
        DECODE(PID.CONV_FLAG,'Y',CLAIM_1.CLM_KIND,'WC00'),
        '202',
        RESERVE.RESV_1,
        RESERVE.RESV_2,
        RESERVE.RESV_3,
        RESERVE.RESV_4,
        RESERVE.RESV_5,
        RESERVE.RESV_6,
        RESERVE.RESV_7,
        RESERVE.RESV_8,
        RESERVE.RESV_9)                AS RESV_EXPENSES,
        TARGET_BUCKET('G','R',
        DECODE(PID.CONV_FLAG,'Y',CLAIM_1.CLM_KIND,'WC00'),
        '203',
        RESERVE.RESV_1,
        RESERVE.RESV_2,
        RESERVE.RESV_3,
        RESERVE.RESV_4,
        RESERVE.RESV_5,
        RESERVE.RESV_6,
        RESERVE.RESV_7,
        RESERVE.RESV_8,
        RESERVE.RESV_9)                AS RESV_RECOVERABLE,
        TARGET_BUCKET('G','R',
        DECODE(PID.CONV_FLAG,'Y',CLAIM_1.CLM_KIND,'WC00'),
        '204',
        RESERVE.RESV_1,
        RESERVE.RESV_2,
        RESERVE.RESV_3,
        RESERVE.RESV_4,
        RESERVE.RESV_5,
        RESERVE.RESV_6,
        RESERVE.RESV_7,
        RESERVE.RESV_8,
        RESERVE.RESV_9)                AS RESV_PROPERTY_DAMAGE,
        TARGET_BUCKET('G','R',
        DECODE(PID.CONV_FLAG,'Y',CLAIM_1.CLM_KIND,'WC00'),
        '205',
        RESERVE.RESV_1,
        RESERVE.RESV_2,
        RESERVE.RESV_3,
        RESERVE.RESV_4,
        RESERVE.RESV_5,
        RESERVE.RESV_6,
        RESERVE.RESV_7,
        RESERVE.RESV_8,
        RESERVE.RESV_9)                AS RESV_BODILY_INJURY,
        TARGET_BUCKET('G','R',
        DECODE(PID.CONV_FLAG,'Y',CLAIM_1.CLM_KIND,'WC00'),
        '206',
        RESERVE.RESV_1,
        RESERVE.RESV_2,
        RESERVE.RESV_3,
        RESERVE.RESV_4,
        RESERVE.RESV_5,
        RESERVE.RESV_6,
        RESERVE.RESV_7,
        RESERVE.RESV_8,
        RESERVE.RESV_9)                AS RESV_OTHER,
        TARGET_BUCKET('G','R',
        DECODE(PID.CONV_FLAG,'Y',CLAIM_1.CLM_KIND,'WC00'),
        '207',
        RESERVE.RESV_1,
        RESERVE.RESV_2,
        RESERVE.RESV_3,
        RESERVE.RESV_4,
        RESERVE.RESV_5,
        RESERVE.RESV_6,
        RESERVE.RESV_7,
        RESERVE.RESV_8,
        RESERVE.RESV_9)                AS RESV_CONTENTS,
        TARGET_BUCKET('G','R',
        DECODE(PID.CONV_FLAG,'Y',CLAIM_1.CLM_KIND,'WC00'),
        '208',
        RESERVE.RESV_1,
        RESERVE.RESV_2,
        RESERVE.RESV_3,
        RESERVE.RESV_4,
        RESERVE.RESV_5,
        RESERVE.RESV_6,
        RESERVE.RESV_7,
        RESERVE.RESV_8,
        RESERVE.RESV_9)                AS RESV_STRUCTURES,
        TARGET_BUCKET('G','R',
        DECODE(PID.CONV_FLAG,'Y',CLAIM_1.CLM_KIND,'WC00'),
        '209',
        RESERVE.RESV_1,
        RESERVE.RESV_2,
        RESERVE.RESV_3,
        RESERVE.RESV_4,
        RESERVE.RESV_5,
        RESERVE.RESV_6,
        RESERVE.RESV_7,
        RESERVE.RESV_8,
        RESERVE.RESV_9)                    AS RESV_RECOVERY,
        CLAIM_1.DT_DEATH                   AS DATE_OF_DEATH,
        CLAIM_1.INJ_CLASS_CD               AS INJ_CLASS_CD,
        ICXX_DESC.TBL_DESC                 AS INJ_CLASS_DESC,
        CLAIM_1.SIDE_OF_BODY               AS SIDE_BODY,
        ASSOCIATE.HP_LOGON                 AS FIELD_OFFICE,
        ASSOCIATE.USER_LST_NAME            AS EXAMINER_LAST_NAME,
        ASSOCIATE.USER_FIRST_NAME          AS EXAMINER_FIRST_NAME,
        ASSOCIATE.DT_HIRE                  AS DATE_EXAMINER_HIRED,
        ASSOCIATE.DT_TERMN                 AS DATE_EXAMINER_TERMINATED,
        ASSOCIATE.SUPV                     AS EXAMINERS_SUPERVISER,
        ASSOCIATE.MGR                      AS EXAMINERS_MANAGER,
        CLAIM_GL.PROD_MFR                  AS PROD_MFR,
        CLAIM_GL.PROD_ADDR1                AS PROD_ADDR1,
        CLAIM_GL.PROD_ADDR2                AS PROD_ADDR2,
        CLAIM_GL.PROD_CITY                 AS PROD_CITY,
        CLAIM_GL.PROD_ST                   AS PROD_STATE,
        CLAIM_GL.PROD_ZIP_CD               AS PROD_ZIP_CODE,
        CLAIM_GL.PROD_PHN                  AS PROD_PHONE,
        CLAIM_GL.PROD_TYPE                 AS PROD_TYPE,
        CLAIM_GL.PROD_LOC                  AS PROD_LOC,
        CLAIM_GL.OWNR_LST_NAME             AS OWNER_LAST_NAME,
        CLAIM_GL.OWNR_FIRST_NAME           AS OWNER_FIRST_NAME,
        CLAIM_GL.OWNR_MID_INIT             AS OWNER_MID_INIT,
        CLAIM_GL.OWNR_ADDR1                AS OWNER_ADDR1,
        CLAIM_GL.OWNR_ADDR2                AS OWNER_ADDR2,
        CLAIM_GL.OWNR_CITY                 AS OWNER_CITY,
        CLAIM_GL.OWNR_ST                   AS OWNER_STATE,
        CLAIM_GL.OWNR_ZIP                  AS OWNER_ZIP
FROM
         AGAL_DESC
        ,EMST_DESC
        ,ICXX_DESC
        ,IJCA_DESC
        ,IJNT_DESC
        ,IJPB_DESC
        ,ASSOCIATE
        ,SERV_OFC
        ,ACCT_LEVEL5
        ,ACCT_LEVEL4
        ,ACCT_LEVEL3
        ,ACCT_LEVEL2
        ,ACCT_LEVEL1
        ,RESERVE
        ,CLAIM_2
        ,CLAIM_1
        ,CLAIM_GL
        ,INCIDENT
        ,ACCT_LOI
        ,ACCT
        ,PID
WHERE
        CLAIM_GL.KIND_SUFFIX           = DECODE(CLAIM_1.KIND_SUFFIX,
                                          NULL,'01',
                                          ' ' ,'01',
                                          'XX','01',
                                          CLAIM_1.KIND_SUFFIX)        AND
        CLAIM_1.OPN_CLSD               <> 'Z'                         AND
        ACCT.PID_SEQ_ID_FK               = PID.PID_SEQ_ID             AND
        ACCT_LOI.ACCT_SEQ_ID_FK          = ACCT.ACCT_SEQ_ID           AND
        INCIDENT.ACCT_LOI_SEQ_ID_FK(+)   = ACCT_LOI.ACCT_LOI_SEQ_ID   AND
        CLAIM_1.INC_SEQ_ID_FK          = INCIDENT.INC_SEQ_ID          AND
        CLAIM_GL.INC_SEQ_ID_FK           = INCIDENT.INC_SEQ_ID        AND
        CLAIM_2.CLM_SEQ_ID_FK          = CLAIM_1.CLM_SEQ_ID           AND
        RESERVE.CLM_SEQ_ID_FK(+)       = CLAIM_1.CLM_SEQ_ID           AND
        (to_char(reserve.dt_inpt,'YYYYMMDD')
        || reserve.time_inpt ||
        ltrim(to_char(reserve.resv_seq_id,'000000000000')) =
        (select(max(to_char(r.dt_inpt,'YYYYMMDD') || r.time_inpt ||
        ltrim(to_char(r.resv_seq_id,'000000000000'))))
        from reserve r where R.CLM_SEQ_ID_FK = CLAIM_1.CLM_SEQ_ID
        ) OR
        (
        NOT EXISTS
        (SELECT * FROM RESERVE R
        WHERE R.CLM_SEQ_ID_FK     = CLAIM_1.CLM_SEQ_ID)))             AND
        SERV_OFC.SERV_OFC_SEQ_ID(+)     = CLAIM_1.SERV_OFC_SEQ_ID_FK  AND
        ACCT_LEVEL1.LEVEL1_SEQ_ID(+)   = INCIDENT.LEVEL1_SEQ_ID_FK    AND
        ACCT_LEVEL2.LEVEL2_SEQ_ID(+)   = INCIDENT.LEVEL2_SEQ_ID_FK    AND
        ACCT_LEVEL3.LEVEL3_SEQ_ID(+)   = INCIDENT.LEVEL3_SEQ_ID_FK    AND
        ACCT_LEVEL4.LEVEL4_SEQ_ID(+)   = INCIDENT.LEVEL4_SEQ_ID_FK    AND
        ACCT_LEVEL5.LEVEL5_SEQ_ID(+)   = INCIDENT.LEVEL5_SEQ_ID_FK    AND
        ASSOCIATE.PROCS_UN(+)           = CLAIM_1.EXAMR_RESP          AND
        ASSOCIATE.SERV_OFC_SEQ_ID_FK(+) = CLAIM_1.SERV_OFC_SEQ_ID_FK  AND
       (AGAL_DESC.TBL_TYPE              = ACCT_LOI.AGCY_CD_TBL        OR
        AGAL_DESC.TBL_TYPE IS NULL)                                   AND
        AGAL_DESC.TBL_CD(+)             = CLAIM_1.AGCY                AND
        EMST_DESC.TBL_CD(+)             = CLAIM_2.EMPLMT_STAT         AND
        ICXX_DESC.TBL_CD(+)             = CLAIM_1.INJ_CLASS_CD        AND
        IJCA_DESC.TBL_CD(+)             = CLAIM_1.INJ_CAUSE           AND
        IJNT_DESC.TBL_CD(+)             = CLAIM_1.NATR                AND
        IJPB_DESC.TBL_CD(+)             = CLAIM_1.PART_OF_BODY
       

DrSQLCommented:
Gates2005,

   I thought you said this was a fast refresh MV?  However, to switch to rowid basd:

create materialized view cas2_claim5_gl_view
tablespace mviews_ts
refresh complete
start with sysdate next sysdate + 1
with rowid
as

   That should get rid of the error message.

Good luck!
DrSQL
DrSQLCommented:
Gates2005,

   I thought you said this was a fast refresh MV?  However, to switch to rowid basd:

create materialized view cas2_claim5_gl_view
tablespace mviews_ts
refresh complete
start with sysdate next sysdate + 1
with rowid
as

   That should get rid of the error message.

Good luck!
DrSQL
Gates2005Author Commented:
I have a few of these views.
We are trying it with rowid and we will see what happens.
I'll let you know.

Thanks so much.
Gates2005Author Commented:
Yes, we want to do a fast refresh with the payments since it took 19 hours to create because I couldn't use any temp_ts.
Gates2005Author Commented:
Complete refresh isn't going to work of us no way.  Too long...
I have primary keys on all these tables then the below ones I don't.   I want to try to get this to work on a fast refresh.
CLAIM_GL.KIND_SUFFIX           = DECODE(CLAIM_1.KIND_SUFFIX,
                                          NULL,'01',
                                          ' ' ,'01',
                                          'XX','01',
                                          CLAIM_1.KIND_SUFFIX)        AND
        CLAIM_1.OPN_CLSD               <> 'Z'                         AND
        ACCT.PID_SEQ_ID_FK               = PID.PID_SEQ_ID             AND
        ACCT_LOI.ACCT_SEQ_ID_FK          = ACCT.ACCT_SEQ_ID           AND
        INCIDENT.ACCT_LOI_SEQ_ID_FK(+)   = ACCT_LOI.ACCT_LOI_SEQ_ID   AND
        CLAIM_1.INC_SEQ_ID_FK          = INCIDENT.INC_SEQ_ID          AND
        CLAIM_GL.INC_SEQ_ID_FK           = INCIDENT.INC_SEQ_ID        AND
        CLAIM_2.CLM_SEQ_ID_FK          = CLAIM_1.CLM_SEQ_ID           AND
        RESERVE.CLM_SEQ_ID_FK(+)       = CLAIM_1.CLM_SEQ_ID           AND
        (to_char(reserve.dt_inpt,'YYYYMMDD')
        || reserve.time_inpt ||
        ltrim(to_char(reserve.resv_seq_id,'000000000000')) =
        (select(max(to_char(r.dt_inpt,'YYYYMMDD') || r.time_inpt ||
        ltrim(to_char(r.resv_seq_id,'000000000000'))))
        from reserve r where R.CLM_SEQ_ID_FK = CLAIM_1.CLM_SEQ_ID
ONES That don't have primary key or rowid
 ACCT_LEVEL1.LEVEL1_SEQ_ID(+)   = INCIDENT.LEVEL1_SEQ_ID_FK    AND
        ACCT_LEVEL2.LEVEL2_SEQ_ID(+)   = INCIDENT.LEVEL2_SEQ_ID_FK    AND
        ACCT_LEVEL3.LEVEL3_SEQ_ID(+)   = INCIDENT.LEVEL3_SEQ_ID_FK    AND
        ACCT_LEVEL4.LEVEL4_SEQ_ID(+)   = INCIDENT.LEVEL4_SEQ_ID_FK    AND
        ACCT_LEVEL5.LEVEL5_SEQ_ID(+)   = INCIDENT.LEVEL5_SEQ_ID_FK    AND
        ASSOCIATE.PROCS_UN(+)           = CLAIM_1.EXAMR_RESP          AND
        ASSOCIATE.SERV_OFC_SEQ_ID_FK(+) = CLAIM_1.SERV_OFC_SEQ_ID_FK  AND
       (AGAL_DESC.TBL_TYPE              = ACCT_LOI.AGCY_CD_TBL        OR
        AGAL_DESC.TBL_TYPE IS NULL)                                   AND
        AGAL_DESC.TBL_CD(+)             = CLAIM_1.AGCY                AND
        EMST_DESC.TBL_CD(+)             = CLAIM_2.EMPLMT_STAT         AND
        ICXX_DESC.TBL_CD(+)             = CLAIM_1.INJ_CLASS_CD        AND
        IJCA_DESC.TBL_CD(+)             = CLAIM_1.INJ_CAUSE           AND
        IJNT_DESC.TBL_CD(+)             = CLAIM_1.NATR                AND
        IJPB_DESC.TBL_CD(+)             = CLAIM_1.PART_OF_BODY

I have to have a primary key on all tables for a fast refresh right???
DrSQLCommented:
Gates2005,
   Since you are doing joins, you are actually going to have to go with rowid, the primary keys will only help in the outer-join situations.  It's pretty involved, so I suggest you look at this document (I can't answer the questions and it's pretty much a check-list):

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96520/mv.htm#40960

I think the multiple references to "reserve" will be problematic fo fast refresh.

Keep in mind that there is always the possibility of creating cascading materialized views.  In that scenario, you create multiple materialized views to get the components and then create one or more materialized views based on THOSE materialized views, and so on, until you have your final materialized view or standard view.  This can often get around the restrictions for fast refresh of join materialized views.

Good luck!
DrSQL

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.