Materialized creation hangs

I have several views that work just fine.. I have been changing them to materialized views.. most of them work just fine as materialized views.. but I have a few that wont compile..
Below is the code from one that works as a view, but when I try to compile it as a materialized view it just hangs.. it never finishes and never errors.. why is this?

----------------------------
create materialized view ADHOC_SOFTWARE_FAST
refresh force on demand
start with to_date('15-01-2008 12:56:46', 'dd-mm-yyyy hh24:mi:ss') next sysdate + 1
enable query rewrite
as
SELECT distinct "IS_INVENTORY"."ASSET_TAG",
 "IS_INVENTORY"."INV_DESCRIPTION" "MODEL_NO",
 "IS_INVENTORY"."MFGR_NO" "SERIAL_NO",
 "IS_INVENTORY"."ALTERNATE_TAG" "SOFTWARE_TRAILER",
 "VALUATION"."DATE_ACQUIRED",
 "VALUATION"."ACQUISITION_VALUE",
 "CONTRACT_PURCHASE"."CONTRACT_NUMBER" "PURCHASE_CONTRACT",
 "CONTRACT_MAINTENANCE"."CONTRACT_NUMBER" "MAINTENANCE_CONTRACT",
--
 "PM_ATTRIB_VALUE_BIN_NUMBER"."PHYSVALUE" "BIN_NUMBER",
--
--
 "PM_ATTRIB_VALUE_COMMENTS"."PHYSVALUE" "COMMENTS",
--
--
 "PM_ATTRIB_VALUE_CONNECTION"."PHYSVALUE" "CONNECTION",
--
--
 "PM_ATTRIB_VALUE_DESCRIPTION"."PHYSVALUE" "DESCRIPTION",
--
--
 "PM_ATTRIB_VALUE_INSTAL_DATE"."PHYSVALUE" "INSTALLATION_DATE",
--
--
 "PM_ATTRIB_VALUE_INSTALLED_BY"."PHYSVALUE" "INSTALLED_BY",
--
--
 "PM_ATTRIB_VALUE_MANUFACTURER"."PHYSVALUE" "MANUFACTURER",
--
--
 "PM_ATTRIB_VALUE_NO_OF_LICENSES"."PHYSVALUE" "NO_OF_LICENSES",
--
--
 "PM_ATTRIB_VALUE_SYSTEM"."PHYSVALUE" "SYSTEM",
--
--
 "PM_ATTRIB_VALUE_TYPE"."PHYSVALUE" "TYPE",
--
--
 "PM_ATTRIB_VALUE_VERSION"."PHYSVALUE" "VERSION",
--
--
 "PM_ATTRIB_VALUE_VENDOR"."PHYSVALUE" "VENDOR"
--

 FROM
"EXAV"."IS_INVENTORY" "IS_INVENTORY"

LEFT OUTER JOIN "EXAV"."VALUATION" "VALUATION"
ON "IS_INVENTORY"."IS_INVENTORY_ID"="VALUATION"."IS_INVENTORY_ID"

LEFT OUTER JOIN "EXAV"."INV_CONTRACT" "INV_CONTRACT_PURCHASE"
ON ("IS_INVENTORY"."IS_INVENTORY_ID"="INV_CONTRACT_PURCHASE"."IS_INVENTORY_ID")
LEFT OUTER JOIN "EXAV"."CONTRACT" "CONTRACT_PURCHASE"
ON "INV_CONTRACT_PURCHASE"."CONTRACT_ID"="CONTRACT_PURCHASE"."CONTRACT_ID"

LEFT OUTER JOIN "EXAV"."INV_CONTRACT" "INV_CONTRACT_MAINTENANCE"
ON ("IS_INVENTORY"."IS_INVENTORY_ID"="INV_CONTRACT_MAINTENANCE"."IS_INVENTORY_ID")
LEFT OUTER JOIN "EXAV"."CONTRACT" "CONTRACT_MAINTENANCE"
ON "INV_CONTRACT_MAINTENANCE"."CONTRACT_ID"="CONTRACT_MAINTENANCE"."CONTRACT_ID"

----
 LEFT OUTER JOIN "EXAV"."ATTRIBUTE_DEF" "ATTRIBUTE_DEF_BIN_NUMBER"
 ON ("IS_INVENTORY"."H_OBJECT_ID"="ATTRIBUTE_DEF_BIN_NUMBER"."H_OBJECT_ID")
 LEFT OUTER JOIN "EXAV"."PM_ATTRIB_VALUE" "PM_ATTRIB_VALUE_BIN_NUMBER"
 ON ("IS_INVENTORY"."PM_OBJECT_ID"="PM_ATTRIB_VALUE_BIN_NUMBER"."PM_OBJECT_ID")
 AND ("ATTRIBUTE_DEF_BIN_NUMBER"."ATTRIB_ID"="PM_ATTRIB_VALUE_BIN_NUMBER"."ATTRIB_ID")
----
----
 LEFT OUTER JOIN "EXAV"."ATTRIBUTE_DEF" "ATTRIBUTE_DEF_COMMENTS"
 ON ("IS_INVENTORY"."H_OBJECT_ID"="ATTRIBUTE_DEF_COMMENTS"."H_OBJECT_ID")
 LEFT OUTER JOIN "EXAV"."PM_ATTRIB_VALUE" "PM_ATTRIB_VALUE_COMMENTS"
 ON ("IS_INVENTORY"."PM_OBJECT_ID"="PM_ATTRIB_VALUE_COMMENTS"."PM_OBJECT_ID")
 AND ("ATTRIBUTE_DEF_COMMENTS"."ATTRIB_ID"="PM_ATTRIB_VALUE_COMMENTS"."ATTRIB_ID")
----
----
 LEFT OUTER JOIN "EXAV"."ATTRIBUTE_DEF" "ATTRIBUTE_DEF_CONNECTION"
 ON ("IS_INVENTORY"."H_OBJECT_ID"="ATTRIBUTE_DEF_CONNECTION"."H_OBJECT_ID")
 LEFT OUTER JOIN "EXAV"."PM_ATTRIB_VALUE" "PM_ATTRIB_VALUE_CONNECTION"
 ON ("IS_INVENTORY"."PM_OBJECT_ID"="PM_ATTRIB_VALUE_CONNECTION"."PM_OBJECT_ID")
 AND ("ATTRIBUTE_DEF_CONNECTION"."ATTRIB_ID"="PM_ATTRIB_VALUE_CONNECTION"."ATTRIB_ID")
----
----
 LEFT OUTER JOIN "EXAV"."ATTRIBUTE_DEF" "ATTRIBUTE_DEF_DESCRIPTION"
 ON ("IS_INVENTORY"."H_OBJECT_ID"="ATTRIBUTE_DEF_DESCRIPTION"."H_OBJECT_ID")
 LEFT OUTER JOIN "EXAV"."PM_ATTRIB_VALUE" "PM_ATTRIB_VALUE_DESCRIPTION"
 ON ("IS_INVENTORY"."PM_OBJECT_ID"="PM_ATTRIB_VALUE_DESCRIPTION"."PM_OBJECT_ID")
 AND ("ATTRIBUTE_DEF_DESCRIPTION"."ATTRIB_ID"="PM_ATTRIB_VALUE_DESCRIPTION"."ATTRIB_ID")
----
----
 LEFT OUTER JOIN "EXAV"."ATTRIBUTE_DEF" "ATTRIBUTE_DEF_INSTAL_DATE"
 ON ("IS_INVENTORY"."H_OBJECT_ID"="ATTRIBUTE_DEF_INSTAL_DATE"."H_OBJECT_ID")
 LEFT OUTER JOIN "EXAV"."PM_ATTRIB_VALUE" "PM_ATTRIB_VALUE_INSTAL_DATE"
 ON ("IS_INVENTORY"."PM_OBJECT_ID"="PM_ATTRIB_VALUE_INSTAL_DATE"."PM_OBJECT_ID")
 AND ("ATTRIBUTE_DEF_INSTAL_DATE"."ATTRIB_ID"="PM_ATTRIB_VALUE_INSTAL_DATE"."ATTRIB_ID")
----
----
 LEFT OUTER JOIN "EXAV"."ATTRIBUTE_DEF" "ATTRIBUTE_DEF_INSTALLED_BY"
 ON ("IS_INVENTORY"."H_OBJECT_ID"="ATTRIBUTE_DEF_INSTALLED_BY"."H_OBJECT_ID")
 LEFT OUTER JOIN "EXAV"."PM_ATTRIB_VALUE" "PM_ATTRIB_VALUE_INSTALLED_BY"
 ON ("IS_INVENTORY"."PM_OBJECT_ID"="PM_ATTRIB_VALUE_INSTALLED_BY"."PM_OBJECT_ID")
 AND ("ATTRIBUTE_DEF_INSTALLED_BY"."ATTRIB_ID"="PM_ATTRIB_VALUE_INSTALLED_BY"."ATTRIB_ID")
----
----
 LEFT OUTER JOIN "EXAV"."ATTRIBUTE_DEF" "ATTRIBUTE_DEF_MANUFACTURER"
 ON ("IS_INVENTORY"."H_OBJECT_ID"="ATTRIBUTE_DEF_MANUFACTURER"."H_OBJECT_ID")
 LEFT OUTER JOIN "EXAV"."PM_ATTRIB_VALUE" "PM_ATTRIB_VALUE_MANUFACTURER"
 ON ("IS_INVENTORY"."PM_OBJECT_ID"="PM_ATTRIB_VALUE_MANUFACTURER"."PM_OBJECT_ID")
 AND ("ATTRIBUTE_DEF_MANUFACTURER"."ATTRIB_ID"="PM_ATTRIB_VALUE_MANUFACTURER"."ATTRIB_ID")
----
----
 LEFT OUTER JOIN "EXAV"."ATTRIBUTE_DEF" "ATTRIBUTE_DEF_NO_OF_LICENSES"
 ON ("IS_INVENTORY"."H_OBJECT_ID"="ATTRIBUTE_DEF_NO_OF_LICENSES"."H_OBJECT_ID")
 LEFT OUTER JOIN "EXAV"."PM_ATTRIB_VALUE" "PM_ATTRIB_VALUE_NO_OF_LICENSES"
 ON ("IS_INVENTORY"."PM_OBJECT_ID"="PM_ATTRIB_VALUE_NO_OF_LICENSES"."PM_OBJECT_ID")
 AND ("ATTRIBUTE_DEF_NO_OF_LICENSES"."ATTRIB_ID"="PM_ATTRIB_VALUE_NO_OF_LICENSES"."ATTRIB_ID")
----
----
 LEFT OUTER JOIN "EXAV"."ATTRIBUTE_DEF" "ATTRIBUTE_DEF_SYSTEM"
 ON ("IS_INVENTORY"."H_OBJECT_ID"="ATTRIBUTE_DEF_SYSTEM"."H_OBJECT_ID")
 LEFT OUTER JOIN "EXAV"."PM_ATTRIB_VALUE" "PM_ATTRIB_VALUE_SYSTEM"
 ON ("IS_INVENTORY"."PM_OBJECT_ID"="PM_ATTRIB_VALUE_SYSTEM"."PM_OBJECT_ID")
 AND ("ATTRIBUTE_DEF_SYSTEM"."ATTRIB_ID"="PM_ATTRIB_VALUE_SYSTEM"."ATTRIB_ID")
----
----
 LEFT OUTER JOIN "EXAV"."ATTRIBUTE_DEF" "ATTRIBUTE_DEF_TYPE"
 ON ("IS_INVENTORY"."H_OBJECT_ID"="ATTRIBUTE_DEF_TYPE"."H_OBJECT_ID")
 LEFT OUTER JOIN "EXAV"."PM_ATTRIB_VALUE" "PM_ATTRIB_VALUE_TYPE"
 ON ("IS_INVENTORY"."PM_OBJECT_ID"="PM_ATTRIB_VALUE_TYPE"."PM_OBJECT_ID")
 AND ("ATTRIBUTE_DEF_TYPE"."ATTRIB_ID"="PM_ATTRIB_VALUE_TYPE"."ATTRIB_ID")
----
----
 LEFT OUTER JOIN "EXAV"."ATTRIBUTE_DEF" "ATTRIBUTE_DEF_VERSION"
 ON ("IS_INVENTORY"."H_OBJECT_ID"="ATTRIBUTE_DEF_VERSION"."H_OBJECT_ID")
 LEFT OUTER JOIN "EXAV"."PM_ATTRIB_VALUE" "PM_ATTRIB_VALUE_VERSION"
 ON ("IS_INVENTORY"."PM_OBJECT_ID"="PM_ATTRIB_VALUE_VERSION"."PM_OBJECT_ID")
 AND ("ATTRIBUTE_DEF_VERSION"."ATTRIB_ID"="PM_ATTRIB_VALUE_VERSION"."ATTRIB_ID")
----
----
 LEFT OUTER JOIN "EXAV"."ATTRIBUTE_DEF" "ATTRIBUTE_DEF_VENDOR"
 ON ("IS_INVENTORY"."H_OBJECT_ID"="ATTRIBUTE_DEF_VENDOR"."H_OBJECT_ID")
 LEFT OUTER JOIN "EXAV"."PM_ATTRIB_VALUE" "PM_ATTRIB_VALUE_VENDOR"
 ON ("IS_INVENTORY"."PM_OBJECT_ID"="PM_ATTRIB_VALUE_VENDOR"."PM_OBJECT_ID")
 AND ("ATTRIBUTE_DEF_VENDOR"."ATTRIB_ID"="PM_ATTRIB_VALUE_VENDOR"."ATTRIB_ID")
----


 WHERE (("CONTRACT_PURCHASE"."CONTRACT_TYPE" = 'PURCHASE'
         OR "CONTRACT_PURCHASE"."CONTRACT_TYPE" is null)
 OR ("CONTRACT_MAINTENANCE"."CONTRACT_TYPE" = 'MAINTENANCE'
 OR "CONTRACT_MAINTENANCE"."CONTRACT_TYPE" is null))

------
 AND "ATTRIBUTE_DEF_BIN_NUMBER"."ATTRIB_NAME" = 'BIN NUMBER'
------
------
 AND  "ATTRIBUTE_DEF_COMMENTS"."ATTRIB_NAME" = 'COMMENTS'
------
------
 AND  "ATTRIBUTE_DEF_CONNECTION"."ATTRIB_NAME" = 'CONNECTION'
------
------
 AND  "ATTRIBUTE_DEF_DESCRIPTION"."ATTRIB_NAME" = 'DESCRIPTION'
------
------
 AND  "ATTRIBUTE_DEF_INSTAL_DATE"."ATTRIB_NAME" = 'INSTALLATION DATE'
------
------
 AND  "ATTRIBUTE_DEF_INSTALLED_BY"."ATTRIB_NAME" = 'INSTALLED BY'
------
------
 AND  "ATTRIBUTE_DEF_MANUFACTURER"."ATTRIB_NAME" = 'MANUFACTURER'
------
------
 AND  "ATTRIBUTE_DEF_NO_OF_LICENSES"."ATTRIB_NAME" = 'NUMBER OF LICENSES'
------
------
 AND  "ATTRIBUTE_DEF_SYSTEM"."ATTRIB_NAME" = 'SYSTEM'
------
------
 AND  "ATTRIBUTE_DEF_TYPE"."ATTRIB_NAME" = 'TYPE'
------
------
 AND  "ATTRIBUTE_DEF_VERSION"."ATTRIB_NAME" = 'VERSION'
------
------
 AND  "ATTRIBUTE_DEF_VENDOR"."ATTRIB_NAME" = 'VENDOR'
------
 AND "IS_INVENTORY"."ASSET_TAG" LIKE '%SW%'
 AND "IS_INVENTORY"."INV_DELETED" <> '1';
ODOTServerAsked:
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.

MilleniumaireCommented:
Hi, this is a long shot but try reformatting the view to cut down on the number of characters.  You could:

remove all double quotes - as far as I can see these aren't necessary
remove the blank lines between conditions
remove the comment lines

I realise these things are included for readability, but try taking a copy of your original and running it with the changes mentioned.
0
MilleniumaireCommented:
Is it possible that the objects reference by the materialised view are being used (locked)?
To confirmthe sql for your creation statement isn't causing the problem, try creating your own copies of the tables used by the view and create the view on these copy tables.  You don't really need to put any data in the tables if they are large.

If this works it suggests the issue is due to accessing the tables used by the view.
0
ODOTServerAuthor Commented:
here is a much shorter example.. I get same results with this one.. it just hangs creating it..
These tables are isolated (not being used by anyone) the database shows no locks.
and it is a smaller character count..
create materialized view VENDOR_CLAIM_DUPLICATES
refresh force on demand
start with to_date('30-01-2008 20:01:00', 'dd-mm-yyyy hh24:mi:ss') next sysdate + 1 
as
select distinct
     vendor.c_vendor_no,
     vendor_claim_detail.c_enc_no,
     vendor_claim.a_claim_amount
from
     vendor,
     vendor_claim_detail,
     vendor_claim
where
     vendor.c_vendor_no = vendor_claim_detail.c_vendor_no (+) and
     vendor.c_vendor_no = vendor_claim.c_vendor_no (+) and
     vendor.rowid = (select max(v.rowid) 
                     from 
                            vendor v, 
                            vendor_claim_detail c, 
                            vendor_claim a
                      where
                        v.c_vendor_no = vendor.c_vendor_no and
                        c.c_enc_no = vendor_claim_detail.c_enc_no and
                        a.a_claim_amount = vendor_claim.a_claim_amount
                        group by v.c_vendor_no having count(*)>1)

Open in new window

0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

jwittenmCommented:
Do you have an MLOG on the base tables (I see you are doing a force refresh)?
Do you have an index on the snaptime$$ columns of the MLOGs?
0
ODOTServerAuthor Commented:
no i dont
0
MilleniumaireCommented:
Can you provide me with the sql to create the tables and I'll try to create the view myself.  Either an export (preferably without data) or a script would be okay.

What version of Oracle are you using?

How big are the tables (number of rows, number of columns)?
0
ODOTServerAuthor Commented:
Found a newer database version to try it in, using a database link to the tables in the old database and it worked there... must be a bug that was fixed..
0

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.