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 am using the Oracle 10g and I have question related to "for Update" clause.We have the data warehouse db, so no foreign key constraint between parent and child.We process the data files every hour, the condition is If we find the row in parent table then we go and look into child tables and perform insertion (if no corresponding record is present) or updation (if one corresponding record is present) in the child table.
The problem is If I run the two process simultaneously for the same kind of data, and if no record is present in the child table then it create the duplicate in child table.My question is if I use FOR Update clause while selecting the data in parent table will it lock the child table for any insertion or updation?
Ex- We have employee table for employee 1
In my data files I have the row for employee 1, so when I run the select query on employee table I found 1 row.The I look the child table "Salary" as there is no record for emp_id =1 in this table I insert the record for this
Emp_id Salary 1 500
The problem is if both the process run at same time then I get duplicate rows in child table
Emp_id Salary 1 500 1 500
we do not want the duplicate row insertion. Can I lock the child table during first process run
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 have a parent table EMPLOYEE which includes columns (sysid, serviceno,employeename...) sysid is Primary key, serviceno is Unique key and I have child table DEPENDENT includes columns (sysid,employee_sysid,name,dob...) here again SYSID is primary key for DEPENDENTS table, employee_sysid is Foreign key of EMPLOYEE table.
Now I want to change SYSID (using sequence) in EMPLOYEE table which need to be update in DEPENDENTS table as well
Note: I have 10000 records in EMPLOYEE table as well as I have 5 more child tables which need to update new SYSID.
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:
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.
We have a table in the client database that has two columns - column parent and column child. The whole hierarchy of DB table dependencies is held in this table.If Report 1 is dependent on Table A and Table A in turn is dependent on two tables Table M and Table N. Table N is dependent on table Z it will appear in the db table as,
Hierarchy Table Parent Child Report1Table A Table ATable M Table ATable N Table NTable Z
Requirement :
From the above structure, we need to build a table which will hold the complete hierarchy by breaking it into multiple columns.The o/p should look like this
-ParentChild 1Child 2 Child 3 -Report1Table ATable M -Report1Table ATable N Table Z
Child 1, Child 2, Child 3 ....and so on are columns.The number of tables and the no of hierarchical relationships are dynamic.
SQL Statements to create hierarchy table:
create table hierarchy (parent varchar2(20), child varchar2(20)); insert into hierarchy values ('Report1','Table A'); insert into hierarchy values ('Report1','Table B'); insert into hierarchy values ('Table A','Table M'); insert into hierarchy values ('Table B','Table N'); insert into hierarchy values ('Report2','Table P'); insert into hierarchy values ('Table M','Table X'); insert into hierarchy values ('Table N','Table Y'); insert into hierarchy values ('Report X','Table Z');
Approached already tried :
1) Using indentation : select lpad(' ',20*(level-1)) || to_char(child) P from hierarchy connect_by start with parent='Report1' connect by prior child=parent;
2)Using connect by path function : select * from (select parent,child,level,connect_by_isleaf as leaf, sys_connect_by_path(child,'/') as path from hierarchy start with parent='Report1' connect by prior child =parent) a where Leaf not in (0);
Both the approaches give the information but the hierarchy data appears in a single column.Ideally we would like data at each level to appear in a different column.
I have got two tables emp_dtl and iou_tab. i have already made entries i.e booking no, emp_cd, emp_name etc in emp_dtl snc its my master table. I want to retrieve the booking nos through lov in iou_tab which are generated in emp_dtl and corresponding info of emp_cd and emp_name should come in the respected fields in iou_tab.
i need to find the parent and child from the table in this case the parent is Classics & Poetry and child is Literature & Anthologies..the way of getting only the parent and child record from this table.
I have a parent table and child table. I want a row to be deleted from the parent table which is referenced by a child row. Is there a way to achieve this. I dont have permission to re create the table or alter the table using delete cascade option. Is there a way to do it in sql.
SQL> create table t1(a number primary key, b number); SQL> create table t2(c number, d number references t1(a)); SQL> insert into t1 values(1,2); SQL> insert into t1 values(2,3); SQL> insert into t1 values(3,4); SQL> insert into t2 values(10,3); SQL> insert into t2 values(20,2); SQL> delete from t1 where a=2; delete from t1 where a=2 *
ERROR at line 1: ORA-02292: integrity constraint (CISBATCH.SYS_C00763501) violated - child record found
The manual work around on populating child tables for testing purpose are taking long time and its very painful work. So I am trying for a tool that takes parent table name and child table name as input and produce insert statements for child table with foreign keys as output.
I want to delete records from parent table which are less than 2 years. Before deleting records from parent table we have to delete records from child table. How can we delete those records. I don't want to use ON DELETE CASCADE.
I am facing below error while doing Schema refresh from production to development. I am facing this error in development database.
IMP-00003: ORACLE error 2264 encountered ORA-02264: name already used by an existing constraint IMP-00017: following statement failed with ORACLE error 2264: "ALTER TABLE "TASKS" ADD CONSTRAINT "VALUE_CK" CHECK (wrkt_k" "ow <> 'S') ENABLE NOVALIDATE"
i guess "need to Remove the orphaned child records from the child table (foreign key relationship), and then re-execute the ALTER TABLE ENABLE CONSTRAINT command."
how to find orphaned child records from child table ?
I have table emp that contains empno, empname, mgr .what i want is a general procedure that will take empno as input and will give all the child rows and parent for entered empno.
for ex
E A-->B-->C-->D F-->G H
When i will pass d as node it will return c,b,a,e,f,g,h
that I need to insert into Child using seq_parent but I want to insert the same sequence for each group of rRef. I dont know how to do that using SQL not PL/SQL.
BANNER Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production PL/SQL Release 11.2.0.2.0 - Production CORE 11.2.0.2.0 Production TNS for Solaris: Version 11.2.0.2.0 - Production NLSRTL Version 11.2.0.2.0 - Production
I have loaded into an Oracle table defined as XMLTYPE. I'm okay with PL-SQL and stored procedures, where I will ultimately do this processing once I get a handle on XML DB querying from an XMLTYPE table.
Based on reading the oracle documentation about XML DB, and more importantly, reading dozens of posts to this forum,I have been successful in loading the XML files into a SQL XMLTYPE table and doing simple queries against that table to retrieve some of its data so that I can then insert that data into other target oracle relational tables.
how to select parent/child data from XMLTYPE tables. I am able to follow the forum examples and can replicate the methods shown on the many example XML contents shown on this forum, but not against the XML that I have to process. I am wondering if my struggle is caused by my lack of knowledge, or by ill-formed XML content supplied to me by the educational vendor.The XML content has structured the XML content nodes in such a way that I do not seem to be able to apply the parent/child sql methods.I have been able to use for other XML examples I have tested against.
My XML file shown below represents High School Transcript data, for which I need to be able to parse out into my own oracle relational tables for that student, his personal info, and his course info, etc. i.e., for our example, which courses he has taken for which High School grade levels. The vendor-supplied XML seems to put the Courses and the High School grade level in "parallel nodes," instead of parent/child nodes, so I am struggling to be able to use SQL to differentiate which course the student took in NinthGrade versus TenthGrade.
-- WHat I would like to determine from a select statement:
LASTNAME GradeLevel COURSETITLE ============================= Smith NinthGrade PHYS ED 101 Smith TenthGrade CALCULUS 201 Smith TenthGrade ZOOLOGY 202
(The data has been simpliied and masked, but is true to the content and is queryable).
select * from V$VERSION
BANNER Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production PL/SQL Release 11.2.0.2.0 - Production CORE 11.2.0.2.0 Production TNS for Solaris: Version 11.2.0.2.0 - Production NLSRTL Version 11.2.0.2.0 - Production [code]....
I have a base table (Table A) block with multiple records displayed. I need to track audits to this underlying table in the following way:
If user updates a field in the block I want the pre-changed record's audit fields to be set and I need to create a copy of the record with the changed values. Basically any changes will result in the record being logically deleted, and a copy record created with the newly changed values.
Tried to implement in the block's pre-update trigger which will call a package to directly update Table A then Insert into Table A, then requery the block. Is there a clean and efficient way to do this?
i want to create a trigger that will update a table when there is an insert or update.i can't across this error that i don't even know what it means "table %s.%s is mutating, trigger/function may not see it".
*Cause: A trigger (or a user defined plsql function that is referenced in this statement) attempted to look at (or modify) a table that was in the middle of being modified by the statement which fired it.
*Action: Rewrite the trigger (or function) so it does not read that table.
CREATE OR REPLACE TRIGGER set_date_end BEFORE INSERT OR UPDATE OF issued ON shares_amount FOR EACH ROW DECLARE BEGIN INSERT INTO shares_amount(date_end) VALUES(SYSDATE); END set_date_end; /
I have a column "empno" in EMP table and "deptno" in DEPT table . I want to update both the columns with single UPDATE statement. With out a creation of stored procedure or view(updating it through view).
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
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?
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:
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.
