Top 60 Oracle Blogs

Recent comments

foreign key constraints

Refreshing Materialized Views with Referential Integrity Constraints

I have a number of tables on a reporting system which have referential integrity constraints, and whose contents are replicated from a primary system. I am going to create materialized views on these prebuilt tables to manage incremental refresh. However, the referential integrity means that some materialized view will have to be refreshed before others which refer to them.

TM locking: Checking for Missing Indexes on Foreign Key Constraints

Recently, I was working on a packaged application purchased from a third-party vendor. It is one of those platform agnostic systems that started life on Microsoft SQL Server, and has been ported to Oracle. I spend a lot of my time working with PeopleSoft, so I had a certain sense of déjà vu. However, this application uses referential integrity.

The application was upgraded, and simultaneously Oracle was upgraded to 10g and then exhibited TM contention. It had probably been suffering from TM contention while running on Oracle 9i, but we hadn't realised because Oracle9i only reports 'enqueue'.

From 10g, there are no less that 208 different enqueue wait events, that show the type of lock that the process is waiting for, and sometimes additional information. In my case it was event 175. Events can be listed from v$event_name.