[askdba] Re: Materialized views problem
- From: "Vinod Gopinath BMMI IS" <vinodg@xxxxxxxxxxx>
- To: <askdba@xxxxxxxxxxxxx>, <PNarayanan@xxxxxxxxxxxxxxxxxx>
- Date: Sun, 10 Oct 2004 10:00:36 +0300
PVN,
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
Hi,
We have a scenario as below. There is a remote database from which 2
views=20
to be accessed on local database.
Both these views are on oracle database. Out of this one view
(STP_MAXIMO)=20
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
apps=20
, 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
are=20
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.
=20
Thanks and best regards
P.V.Narayanan
Other related posts:
