Unexplainable Exclusive Transaction Locks?
Aug 23, 2010
I have a job which runs the procedure below concurrently in 4 different sessions; It updates two tables.
PROCEDURE UPDATE_TAB_1_AND_TAB_2( traceIdTab IN pl_sql table,
individualIdTab IN pl_sql table,
fileSeqIdTab IN pl_sql table,
[Code].....
Each session is passed a sequence generated file ID; so the records processed in each session are completely different.
However, everytime this job runs, without failure, ONLY TWO sessions process concurrently, while the other two sessions are blocked. Once the first two have finished, then the remaining two sessions start work.
I have being monitoring the sessions on each run daily, and realized that the first two sessions processing are each holding a transaction exclusive lock on objects; hence blocking the other two sessions.
When I tried to find the objects that the two running sessions have a lock on, I cant seem to find anything by searching DBA_OBJECTS.object_id. However, when I look for the objects that are being waited on, its either a partitioned table or a partitioned index on either table_1 or table_2.
At first, I thought it was an ITL lock and after much analysis, I did not find anything which definitely pointed to an ITL lock. However, just to make sure, I increased the INITTRANS value from 2 to 5, but the problem did not go away.
At this point, I have applied all I know to understand the origin of the TX lock and how I can work around this situation, and decided to ask higher powers in Oracle for advise.
View 4 Replies
ADVERTISEMENT
Jan 6, 2011
Under what scenarios do we lock a table in the exclusive mode.
View 1 Replies
View Related
Aug 16, 2013
I read that the value EXCLUSIVE (REMOTE_LOGIN_PASSWORDFILE parameter) is supported for backward compatibility. It now has the same behavior as the value SHARED.So is the EXCLUSIVE value deprecated? Oracle Database Reference doesn't mention that.
View 6 Replies
View Related
Sep 16, 2010
there's a table with 3 columns oid, value, status in a scheme. Now i want this table to be used in an other scheme. So i used grant select to the other scheme so it could read the data. But now i want the column status for independent use of updates in the 2nd scheme. So i want the columns oid & value in one place (table in 1st scheme) and 2 places for status (in original table and in the 2nd scheme. I could create a status table in scheme 2 with oid and status and create an updatable view over table from scheme 1 and this status table - joined over oid. Sounds not too tough but if data is inserted in scheme 1 (all 3 colums) the status table in scheme 2 has no idea of this new data.I want to prevent using triggers or notifications.
View 23 Replies
View Related
Sep 27, 2011
@ the end of the day means After date changes i need to populate the new row in Some of my tables with the previous date values with the new date.. In that time some other Transaction will Occur on those tables so we need to lock all the tables in that time..Even i tried by doing the Exclusive lock on the tables...
execute immediate 'LOCK TABLE CTR_SALES IN EXCLUSIVE MODE NOWAIT';
execute immediate 'LOCK TABLE CTR_STK_ASSIGN IN EXCLUSIVE MODE NOWAIT';
And how to run this function @ 12:00 in the midnight by scheduling.... And 1 more thing how to pick the recent (Transaction) row on that table. I tried from the rowid it's not working
View 1 Replies
View Related
Nov 28, 2011
i create password file in oracle 10g now i want to Set the EXCLUSIVE to REMOTE_LOGIN_PASSWORD initialization parameter. so what should i do.
View 5 Replies
View Related
Nov 6, 2012
i backup my database using rman into my external disk using this command
RMAN> run
2> {
3> backup database format 'H:ackupkp_%u' tag='sh';
4> backup archivelog ALL format 'H:ackuparc_%u' Delete input;
5> }
Starting backup at 06-NOV-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=F:ORACLEPRODUCT10.2.0ORADATAORCLSYSTEM01.DBF
[code]...
View 5 Replies
View Related
Apr 28, 2003
How i create Standby Database in Same HOST, we using SUN OS 5.8.
I read form Oracle DOC that i can create Standby Database in Same HOST and follow the tutorial , but when i try to mount standby database using ALTER DATABASE MOUNT STANDBY DATABASE EXCLUSIVE; i got a error message ORA-01102: cannot mount database in EXCLUSIVE mode
View 6 Replies
View Related
May 10, 2002
I performed Shutdown Immediate in Oracle 8I and now I am unable to mount the database.I'm getting the following message: ORA-01102 Cannot mount database in Exclusive mode/I only have one database running on this machine. It was automatically created by Oracle installer upon installation of Oracle software. I can no longer connect via SQL * Plus,Only through Server Manager (connect internal)
View 8 Replies
View Related
Jan 28, 2013
I have a question. If we have two scott sessions. I am updating a table EMP in session
1. It means it is exclusively locked.It cannot be used by session 2. Then can we use select command on table EMP in session
2.? This command should not work according to me. But it is working.
View 14 Replies
View Related
Jun 30, 2008
I'm trying to create a physical standby database on my Oracle9i DB runing on WinXP.
Note: I have both Primary and Standby on the same system.
Actually everything went well .... I did created the standby DB but the problem is I can not start my primary DB if my standby DB is mounted. I get this error:
ORA-01102: cannot mount database in EXCLUSIVE mode
And when I read about the error message I learnt that I should start my DB in compatible mode ...
View 5 Replies
View Related
May 13, 2011
I have the below questions on the locking mechanism in a partition table. Example I execute the below query,
delete from table_name subpartition (subpartition_name);
In that case,
1. If we perform above query, then which level of lock is happened to the table/partition & sub-partitions?
2. If we perform above query on same sub-partition of the same table with different where clause, what will happen?
3. If we perform above query for same table but different sub partition at same time, what will happen?
View 2 Replies
View Related
Apr 13, 2013
oracleDatabase version:11gR2
Os:solaris
how to check locks on database objects .it might be any lock? any select sql query to check database object levlel locks?what basis locks will occur?
View 3 Replies
View Related
Feb 25, 2012
I have a sql script where triggers, procedures and functions are written. The triggers are causing db outages and causing problems in the application as well.
I am trying my best but with my low experience and expertise, am not able to make good progress.
Scenario: Course enrollments are inserted, deleted and updated in course_main and course_users table. This is done in gui as well as in background snapshot scheduler in a cron process. Course_main table contains all course enrollments and course_users table has crsmain_pk1 as foreign key.
Its quite a big file and am not sure what should i paste here so am uploading the file in txt.
View -1 Replies
View Related
Oct 8, 2012
We have encountered dead locks in EBS R12 Database 10.2.0.3. Even after bouncing the appstier and dbtier completely does not release the locks. How to clear the locks?
View 4 Replies
View Related
Sep 6, 2010
I used v$locked_object and v$lock query to get the output.. But still I'm an one year exp in ORACLE. How to analyze the output of lock queries. what are the parameters to be analyzed on AWR report.
How to do proper performance checkup in ORACLE database as well analyze it.
View 4 Replies
View Related
Jun 30, 2011
At a customers site I see lot of long lasting library cache locks during a complex ETL run. Several Sessions run in parallel and create Database tables with dynamic sql ( CREATE TABLE AS SELECT ....) .
Sometimes these procedures wait for each other with wait event 'Library Cache' . I presume that this is a side effect of the dynamic DDL in the stored procedures. Is that possible even when the Procs create different Tables but reference the same tables in their SELECT clauses ?
I presume that this is plausible but I need some arguments to convince my colleagues.
View 1 Replies
View Related
Jun 8, 2011
On DB123, I have 2 KILLED sessions which are not disappearing and are holding locks on some tables in ONE schema.
The sessions belonged to ETL process and I have killed them last Friday after they have been hanging for 3 days. Unfortunately, I've also killed the corresponding Unix session - and now I've read on the Internet that in those cases the KILLED sessions might be hanging forever (=until the DB is shutdown).
how to remove these locks.
View 5 Replies
View Related
May 24, 2012
After ran db health check, my database report gives the following details
dml_locks OK. dml_locks = 3396, transactions = 849.
View 6 Replies
View Related
Nov 8, 2011
how to avoid blocking locks.
View 27 Replies
View Related
Jun 29, 2012
writing a script where there is a blocking lock for more than 30 minutes ?
View 2 Replies
View Related
Aug 21, 2012
How to view the number of locks defined for a database? I am using Oracle 10g.
How to calculate the number of locks for a given sql select statement?
say for example,
select a.eno,b.ename,c.salary,d.dob,e.address
from a,b,c,d,e
where...
...
..
Assume all of them are row level locking.
I want to calculate the number of locks . How many locks will be held while running the above query?
View 16 Replies
View Related
Aug 3, 2011
My code executes a Select For Update before updating a table. In some cases the network is disconnected and it causes the lock to hang. Then, I must kill the session in order to realese this lock.
I want to do it automatically. I would like to create a job that kills session that has a lock due to Select For Update that is not alive.
View 18 Replies
View Related
May 6, 2011
I have a forms 11g application that of course has several message and alert windows that pop up when various errors or messages need to be displayed. This all works great on the developer machine, but when I try and run the form on another machine using any browser the message and alert windows appear to be hidden and the application locks up with the rolling bar across the bottom. I would guess it is waiting for a response, but I cannot get the window to appear.
Following is an example of an alert to be displayed:
declare
vAlertButton number;
begin
vAlertButton := show_alert('NO_EMAIL');
end;
View 1 Replies
View Related
Oct 18, 2010
Scenario:
Our application is using a two instance, one for the live active data and the other for the reports data. We have a process which moves the data from the live instance to reports instance every night. In a single db environment the process is working without any issues. However when we move to the RAC environment the reports db's (insert) in large table get locked and we are unable to insert data to the reports db.
What we are performing is:
Insert into my_table_rpt select * from may_table_live@db_link_to_livedb;
Issues:
my_table_rpt get locked
We have found the workaround by disable locking in destination and subsequent to the insert enable locking
ALTER TABLE my_table_rpt DISABLE TABLE LOCK;
Insert the data to the reports database table
Then
ALTER TABLE my_table_rpt ENABLE TABLE LOCK
Question:
Why does the large destination table (my_table_rpt) get locked in the RAC environment?
View 2 Replies
View Related
Aug 31, 2013
Having PL/SQL procedure and calling sys dba procedure to alter rollback segment to online and after that to set the transaction to this rollback. The rollback altered to online but transaction still running on available rollback and not the one I was meant to. The user have executing grant on the sys dba procedure.
View 21 Replies
View Related
Jul 28, 2010
what is autonomous transaction
View 8 Replies
View Related
Mar 11, 2013
Is it possible for me as a DBA to find IP address of the client who ran a specific transaction or query in past?
Oracle server version I'm using is 11g.
View 4 Replies
View Related
Jun 16, 2011
application was not starting due to some pending transaction in database.Pending transactions were rolled back by DBA team.To avoid such situations what I thought was, having a job that will call a procedure that will monitor table status everyday and will send the mail. Now that job is working fine for no pending transactions in DBA_PENDING_TRANSACTIONS.
But now I am in doubt if someday there are PENDING TRANSACTIONs in the table DBA_PENDING_TRANSACTIONS, will SELECT * FROM DBA_ PENDING_ TRANSACTIONs query will work as normal or this whole process of monitoring table and sending mail will work fine?
View 3 Replies
View Related
Jun 6, 2011
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE10.2.0.4.0Production
TNS for Solaris: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
We are running a code through informatica and are frequently getting the ORA-08007. The error and the code causing it are mentioned below. (The '?' implies a variable which is supplied by informatica). The source is a flat file.
ORA-08007: Further changes to this block by this transaction not allowed
UPDATE table_20
SET capxuser = ?,
capxtimestamp = ?,
capxaction = ?,
[code]...
1. Tried initially by reducing the commit interval to commit at 100 rows. But still getting the same error.
2. DBA has installed the patch # 6790768. But it still fails.
View 10 Replies
View Related