Say we have an employee(id_emp) table with a primary key on id_emp. We have also some history tables emp_stuff with columns say (id_emp, dat_event, some_stuff) with primary key id_emp, dat_event.
This means that we have a unique index on (id_emp,dat_event). We also have a foreign key id_emp that references employee(id_emp). When we update id_emp on employee, we still have a lock on emp_stuff. According to this (end of the page) :
Quote:So, in short, with releases prior to Oracle Database 11g Release 1, you will want an index on the foreign key of the child table if you do any of the following:
Update the parent table primary key
Delete from the parent table
Merge into the parent table
So is id_emp in emp_stuff considered as indexed (through the unique index of the primary key) or do we have to add an explicit index
like this CREATE INDEX emp_stuff ON emp_stuff(id_emp) to avoide child table locks?
Create table Car (Car_cd VARCHAR2(5), Car_Desc VARCHAR2(50) ); alter table Car add constraint Car_PK primary key (Car_CD); INSERT INTO Car (Car_Cd, Car_Desc) VALUES ('CORLA','COROLLA'); Commit; [code]....
The requirement necessitates a new table to map car to manufacturer. This mapping table may later be expanded to contain other attributes Engine, MPG, etc to hold details specific to a car.But this is in future.
--Not able to create this as Car_cd is already a PK in this table and therefore has Unique Index ALTER TABLE Car_Mapping_Details ADD CONSTRAINT Car_Mapping_Details_fk1 FOREIGN KEY (Car_Cd)REFERENCES Car (Car_Cd); [code]....
But in this case the Car_Mapping_Details.Car_cd is itself is a primary key and therefore has Unique index.Although I was able to create foreign key constraint on Car_mapping_details.car_cd column (which is also Primary Key), I was not able to create Foreign Key Index on this column. It gives me Quote:ORA-01408: such column list already indexed.In other words, not creating foreign index for foreign key column lead to table-level lock? Or will the Unique Index in that primary key column be sufficient to avoid table-level lock?
I am facing the row lock issue in production. I have been trying to resolve the issue but i coud'nt. I traced out by using different queries which sql query is locking which but everything looks good.
And i also checked for connections open and close everything is in good place but unable to resolve the issue. we are running a batch file which runs in every night some of the records are processing and if any one record is failed it is blocking another records.My oracle version is oracle 10.2.0
I have developed a form, containing 5 tab pages and also referencing to 5 tables.tab page a of table a some fields are required and some or not.when i open form and start entering record, and then i change mine and dont enter and want to navigate to some other page, it simply allows me to leave it blank and move to some other tab page.
tab page b of table bsome fields are required and some or not.when i open this tab page and one i click in the first field and then do not enter anything in it and want to leave it , it does not allow me to leave blank.what could be the reason. why one tab page allow me to leave blank and navigate and why the other does not?first field of both tab pages are required.
11.2.0.1 I am still resolving the locking issues in our database Often the delete is blocked by some transaction. The operations said that it is intermittent. Last night they were able to delete the 1M transactions without the locking.But the past days lock often occurred, and they have to bounced the database to release the locks.
I there a way I can compare what happened last night why the batch was able to delete smoothly , and compare it against the other night where the delete was blocked?By using ASH, AWR, ADDM? I also run now this procedure, to identify the blocking sql statement:
Using Oracle 11gr2. If I call the following update statement from session A, session A will lock the row until a commit/rollback statement is issued from session A. If session B calls the same update statement and same row, session B will have to wait until the lock is released in session A. However, the application developers are speaking in terms of threads. Could it be possible that the update statement is called within the same session by multiple requests? If yes, could the case statement be evaluated without the row being locked which could lead to false results? Trying to keep this post brief. tableA has columnA (primary key, number) and columnB (number)
{update tableAset columnB = case when columnB = 3 then 4 when columnB = 4 then 5 else columnB endwhere columnA = 6;}
Could 2 requests (almost at the exact same time) in the same session evaluate columnB as 3. The desired result would be the first request sets the column to 4 and the second request sets the column to 5.
I am having a data guard setup done in oracle 10g(10.2.0.4.0) WINDOWS Platform. some of the archive process are locked in primary db side.
PROCESS STATUS LOG_SEQUENCE STAT -------- ---------- ------------ ---- 0 ACTIVE 0 IDLE 1 ACTIVE 804462 IDLE 2 ACTIVE 0 IDLE 3 ACTIVE 0 IDLE 4 ACTIVE 0 IDLE 5 ACTIVE 800011 BUSY 6 ACTIVE 0 IDLE 7 ACTIVE 800009 BUSY 8 ACTIVE 0 IDLE 9 ACTIVE 802335 BUSY 10 ACTIVE 0 IDLE
Shall we kill the locking archive processes? killing archive processes will cause any problem?
how to avoid oracle deadlock with two sessions. Here is my example, We use Select query with For Update in order to get the unique number from a table. When one user has accessed this query, while the other user tries, System gives a Performace slow and when checked it is due to locking of this select query with For update.
We don't want to use For update WAIT or FOR UPDATE NOWAIT since these will result in missing of number.
I've a table TXN1 transaction and has FKs to 3 different tables Account, customer, country and currency. ALL FKs are indexed (bitmap). I am updating TXN1 of amount column about 10,000 rows. (SID 1) As expected, it has taken lock type 3 SX on TXN1. But it has taken on lock type 4 (share) on Account, customer and country. Committing every 10k rows.
At the same time sid 2 is inserting into another TXN2 table which has FK to the same dimensions account, customer and currency. Only FK on ac_id is bitmap indexed. The inserts have taken SX lock(type 3) on tXN2 table (expected). But it is trying to take SX type 3 lock on account, customer, currency tables. typ3 lock taken on CCY but waiting on CST. But It is blocked by sid 1. It has resulted into Enque-TM contention and resulted into anywhere 60-300 secs wait time..
I understand update/delete in parent table results into locking of SX of child tables and need the FKs to be indexed to avoid etc.
1. Why is SID1 taking shared lock on the parent tables Account,customer,country and currency tables? The update statement is not updating any of those FK columns nor referring them in where clause(if it matters!). Is it to ensure that the parent rows are not deleted?
2. Why is SID2 taking SX lock on the dimension tables? Why is it not taking RS lock type 2 on parent rows? Why is SID1 taking shared lock type 4, but not 2?
I have a set of rows based on a complex view from multiple table.
I will be updating some of its columns from front-end . Is there any possible ways to lock those rows of data while updating and no other users can update it;
I am a developer and not a DBA and I need to find th correct query to find the exact rowid of the record locked on a table. This is for a RAC database and locked record can be from the web form in oracle application server. When I try to get the correct row id, I get the following error:
ORA-01410 - Invalid row id For the criteria, the output is Dbms_Rowid.rowid_create(1, -1, 36, 7845, 0), why I get a -1 for the ROW_WAIT_OBJ#?
Additional Information: The lock type is DML and the lock mode is: Row Exclusive, the table is locked and the program is web oracle forms executing.
I am executing the query in Oracle Database 11g Enterprise Edition Release 11.1.0.7.0
How to accomplish getting the correct rowid? Below is the selection criteria I have:
We had an issue last week were we had a session with a very basic SQL query lock up the database, spiking the CPU at 100%. When you would kill the session, the lock would just jump to another session and so on. We finally had to restart the database since our clients were being kicked out. After the restart of the database, the LGWR ended up locking and held the CPU between 85-95%. The archive logs were switching every 5 minutes, when normally it would be every 45min. We spoke with Oracle Support, but they just ended up brushing the issue off and saying it was a hardware issue and were not able to provide any kind of backing to that.
I have been plagued by people logging into my database and making changes when a clone is in process.. Having said that ,I am looking to lock accounts and unlock them when I am done.
I envision my code looking something like this:
sqlplus -s / <<END SET PAGESIZE 0 SET FEEDBACK OFF SET VERIFY OFF; set heading off; spool /tmp/lockusers.sql select 'alter user ' || username || ' account lock;' from dba_users where username not in (....) and not locked?; spool off; END;
sqlplus -s / <<END @/tmp/lockusers.sql END;
When it comes time to unlock the accounts I want to be able to unlock those accounts I previously locked and not all of them. Is there a query, I can use that can tell me when the accounts were locked or some other way about going about this so I dont unlock accounts that were locked prior to my lock script running?
I have a "select ... into" in my PL/SQL and it doesn't retreive any data(I suppose just certain columns because if it doesn't retreive a row then the PL/SQL should throw an error) when there are many users accessing this table. It shouldn't be some table locking right? Because if it is, it should wait until the table is available then it will retreive data. Currently it just proceeds with the PL/SQL and selecting nothing in that query. what details should I look at to trace the cause of this problem.
Among Hibernate optimistic locking & Database Isolation levels, which one to use? Which gives the better consistency, concurrency and scalability. I read in couple of links that Isolation level will suffer if there is a huge load on the application with multiple users access the appliation at the same time, moreover in islation levels normally we need to look for READ_COMITTED and NON_REPEATABLE_READ to get a better performance? Whether these are true? Whether we can use both Hibernate optimistic locking(version & timestamp) & Database Isolation levels in the same application? What are the implications using these? Which one will be preferred over the other and when?
At the moment, we were loading the file in our system serially. This is a very old and established system.We would like to incorporate parallel loading for our loaders to load data into the database.
Most of the issues would be due to multiple inserts happening due to the files being loaded in parallel. For some reasons, we cannot give regular commits untill the entire batch of items is processed in case the process needs to rollback. A file can contain different set of batch of items clubbed together for loading.
The issue here is untill the first file finishes loading and commits, the second file would just hang. In fact, mulitiple files might hang for the first file to finish. what can I do to overcome this?I tried to used "lock table t1 in SHARE ROW EXCLUSIVE mode nowait". When the leading process is doing inserts, the failing process will fail with a resource busy and acquire with NOWAIT specified. We would catch this exception and redirect that batch to an error file to be reloaded at a later date.
I have an already populated table that refers to a wrong foreign key in another table. I have table abc that has the column fk_id. this column currently refers to column x_id1 in the table abc while it should refer to column x_id2 in xyz. I am trying to replace the data but I guess I miss something in the logic or the right way of doing it:
commit; insert all into abc values (10,'ab1',1) into abc values (11,'ab2',2) into abc values (12,'ab3',5) into abc values (13,'ab4',7) into abc values (14,'ab5',9) into xyz values (1,1,'d1') into xyz values (2,2,'d2') into xyz values (5,3,'d3') into xyz values (7,4,'d4') into xyz values (9,5,'d5') select * from dual; commit;
--this following select returns 5 rows
select * from abc, xyz where abc.fk_id = xyz.x_id1;
-- the following update only updates 3 rows and sets the other 2 -- to null!
update abc set fk_id = (select x_id2 from xyz where xyz.x_id1 = abc.fk_id); commit;
I'm not really sure why oracle is not finding my Foreing Key, I'm creating an easy set of table for a company and I'm declaring all Primary keys and foreing keys as necessary and this is my
I have a parent table called dept and one child table emp which is referenced on deptno and now both have data . Now i want to truncate the dept table and i have truncated emp table after it when i am trying to truncate dept table then oracle throws a error that is-
"ORA-02266: unique/primary keys in table referenced by enabled foreign keys" ,
but when i tried to delete some records 1 by 1 then these have delete but truncate is not still working. why? if i have 10 millon records.
You should almost always index foreign keys because they are frequently used in joins. In addition, if you intend to delete or update unique or primary keys on the parent table, you should index the foreign keys to improve the locking of child records.what I don't understand are, I should create index on foreign key column in parent table or child table or both ?
Is there any view in oracle which gives the foreign key mapping.
E.g.
Table DEPT has DEPTNO as PK. Table Emp has DEPTNO as FK to DEPT.DEPTNO.
I can get the information for DEPT table from R_CONSTRAINT_NAME column of all_constraints table.My requirement is to get the the which column of Emp table refers to DEPT.DEPTNO column .
I am trying to establish PK-FK relationship between 2 tables. Table 1 has VACC_ID as a primary key. Table 2 has VACC_ID and SCH_ID as the primary keys. I am trying to add VACC_ID from Table 2 as a FK to Table 1. This is the code I used
I also tried to add it without writing the query and editing the Table 1. By default, I am getting 2 columns(both VACC_ID) in the local column being referenced to the 2 primary keys(VACC_ID and SCH_ID) of table 2. I also uploaded the screeshot of the error, when I run the query.
