[askdba] Re: Materialized views problem

  • From: PV Narayanan <PNarayanan@xxxxxxxxxxxxxxxxxx>
  • To: askdba@xxxxxxxxxxxxx
  • Date: Sun, 10 Oct 2004 11:04:38 +0400

Hi vinod,
Thanks for your update. There are no entries in the dba_2pc_pending.
As I mentioned, the actual tables are located in remote databases(Multiple 
DB types) and the MVs are 
created based on synonyms which are based on views on remote DBs.
Thanks and best regards

"Vinod Gopinath          BMMI IS" <vinodg@xxxxxxxxxxx> 
Sent by: askdba-bounce@xxxxxxxxxxxxx
10/10/2004 11:00 AM
Please respond to

<askdba@xxxxxxxxxxxxx>, <PNarayanan@xxxxxxxxxxxxxxxxxx>

[askdba] Re: Materialized views problem


Suspect ORA 12560 with 02054. Login as sysdba and query DBA_2PC_PENDING.
Do u c any transaction being held there. If thats the case take a backup
of this table, truncate this table and check.
/- Vinod.

-----Original Message-----
From: askdba-bounce@xxxxxxxxxxxxx [mailto:askdba-bounce@xxxxxxxxxxxxx]
On Behalf Of PV Narayanan
Sent: Sunday, October 10, 2004 9:19 AM
To: askdba@xxxxxxxxxxxxx
Cc: askdba@xxxxxxxxxxxxx; askdba-bounce@xxxxxxxxxxxxx
Subject: [askdba] Materialized views problem

We have a scenario as below. There is a remote database from which 2
to be accessed on local database.
Both these views are on oracle database. Out of this one view
is from a table from another remote
oracle database. Another view (TRN_MAXIMO) is from a view from another=20
oracle database , which in turn is created through a query from a oracle

database and sql server.
We do not have any gateways on our local database
In order to access these remote objects in our local database ,oracle
, we have to create materialized views
as oracle apps does not fetch a LOV from forms for a view or synonym. It

has to be through a table or materialized view for remote connections.
The problem we face is, there is a lock on the TRN_MAXIMO materialized=20
view on local database.
As a result of this we are unable to refresh the materialized view nor
we able to drop and recreate the same.
The one alternative is to shutdown the instance , which is not possible=20
for our environment until the weekend backup.
In the cirsumstance , how do I drop the MV and recreate without bouncing

the DB.
I have checked the background process id , this relates to the snp=20
process. Is it ok to kill this process and will it
release the lock on the MV.
Please provide a solution ASAP to this as this a critical production=20
database. We have checked with oracle and they have no answer other than

restarting the DB.
Thanks and best regards

Other related posts: