I am working on 11g and AIX...We got deadlock recently and we need to investigate why it happen and resolution, so it will not happen in future. When we saw deadlock trace file ,it shows 2 sqls queries on different tables..and lock is exclusive lock.
----- Information for the OTHER waiting sessions -----
SELECT 1 FROM HOS_DTL WHERE ( HOS_LCN_DTL.HOS_LCN_DTL_ID = :1 ) FOR UPDATE WAIT 180
and
----- Current SQL Statement for this session ------
DELETE FROM EI_INV WHERE ( EI_INV.EI_INV_ID = :1 )
But these two tables not related at all 'HOS_DTL' and 'EI_INV' . Can deadlock happen if sqls queries fired on those table are not related at all and deadlock trace file can show those two sqls ..?
While i am trying to execute below mentioned query i am facing "DEADLOCK FOUND WHEN TRYING TO GET LOCK" error.
UPDATE PLAN SET PLAN_PMS_BLOCK_ID ='' WHERE PLAN_PMS_BLOCK_ID<>'' AND PLAN_PMS_BLOCK_ID NOT BETWEEN '0' AND '9' AND PLAN_PMS_BLOCK_ID NOT BETWEEN 'A' AND 'Z' AND PLAN_PMS_BLOCK_ID NOT IN('-') AND LENGTH(PLAN_PMS_BLOCK_ID )=1;
I keep getting an ora-04020: deadlock detected while trying to lock object XDB. SDNRB..The statement I'm trying to issue is:
REVOKE execute on abc."descript_T" FROM PUBLIC;
I am not able to find a solution for this besides the fact of trying the operation again at a later time.I did, but get the same error every single time.
I am using oracle 10.2.0.3 and i am receiving very slow response time for the below query and sometimes resulting in a deadlock throwing ora-60 error.
DELETE FROM GBC_CORE.SPI_ELEMENT_ID TRGT WHERE (TRGT.URI) NOT IN ( SELECT DISTINCT FROMTOURI.URI FROM ( SELECT SERVICEACCESSNAME AS URI, SUBSTR( SERVICEACCESSNAME,1,INSTR( SERVICEACCESSNAME ,'_')-1) AS FROM_URI, SUBSTR( SERVICEACCESSNAME,INSTR( SERVICEACCESSNAME ,'_')+1,LENGTH(SERVICEACCESSNAME)) AS TO_URI FROM TRPT.V_TRPT_SPI_VIEW@DBLNK_FCE_TRPT ) FROMTOURI, [code]...
I am facing Deadlock issue in my transaction when record is been deleted in the same table in parallel from a PLSQL package. The package is been called from the Java code.The example and the table structure is given below.
Col1 of tab1 is foreign key to col1 of tab2. Commit will not be done until all the below dml scripts are executed in both environment.
1st session:
Delete from tab1 where col1=1001; Delete from tab2 where col1=2001;
Result: Deletion successful
2nd session:
Delete from tab1 where col1=1002; Delete from tab2 where col1=2002;
Result: Deletion successful
1st session:
Delete from tab1 where col1=1003; Delete from tab2 where col1=2003;
Result: Deletion successful
2nd session:
Delete from tab1 where col1=1004; Delete from tab2 where col1=2004;
Result: Query is executing for a longer time.
1st session:
Delete from tab1 where col1=1003; Delete from tab2 where col1=2003;
Result: Query is not executed and throws Deadlock in the back end.
i am getting a below error whenever executing the below select query. some times it will show dead lock detected while waiting for resource and terminated...some times it executes and gives result..but all the time it writes an alert to alert log
Env: Linux / Oracle 11.2.0.3.3..Error from alert log:Errors in file /u01/oracle/oracle/diag/rdbms/bdrdb/bdrdb/trace/bdrdb_p017_6076.trc:ORA-00060: deadlock detected while waiting for resourceORA-10387: parallel query server interrupt (normal) Trace file info... bdrdb_p017_6076.trc:Trace file /u01/oracle/oracle/diag/rdbms/bdrdb/bdrdb/trace/bdrdb_p017_6076.trcOracle Database 11g Enterprise Edition Release 11.2.0.3.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsORACLE_HOME = /u01/oracle/oracle/product/11.2.0/dbhome_1System. [code]....
I have a deadlock trace file to analyse and i used to be able to see the rowid as a 16 bit hex value in the trace file, which i could then query on to get the actual real world row name.I see in the 11GR2 deadlock trace the formatting is different and for the life of me i am unable to see the rowid. Has Oracle stopped reporting this now, or is their another way to get this value? The deadlock graph shows me
what could be the reason for a LMSn process not heartbeating after a global enqueue service deadlock was detected? The happened in a 3instance RAC and after the LMS1 process stop heartbeating oe of the instances crashed afterwards and another instance crashed some minutes after. reason for the process crash after resolving deadlock?
problem on oracle 11gR2 where i have to import data from a source database to an existing table without truncate or drop the target table in the target database.
we have found something called table_exist_action=append in impdp.
I have to cleanup data from our tables (Production Environment) that contain millions of rows. The question is apart from the solution of the partitioned tables what alternative recommended solution suggests Oracle?
To delete these tables by using a cursor PL/SQL block or to import all the database and in the tables that we want to remove the old rows to use the QUERY option of the data pump utility.
I have used both ways and i have to admit that datapump solution is much much faster than the deletion that suffers from I/O disk.The question again is which method from these two is more reliable and less risky for the health of the database.
I came across an implementation where data from DB2 tables are moved to Oracle tables, for BI solutioning, using some oracle procedures called from MS SQL DTS packages which are scheduled jobs.Just being curious, can this be done using OWB or ODI rather than the above detour. I suppose there are some changes being done in those procedures before the data is being loaded into Oracle tables, can't this be done using OWB/ODI? Can it be scheduled too as jobs using OWB/ODI?
In our schema we have corresponding audit tables for most of the production tables
Ex Table name Audit Table EMP EMP_AU DEPT DEPT_AU
Audit tables will have all the columns of production table along with audit columns AUDIT_DATE , AUDIT_OPERATION There are few production tables which are not having audit tables.I need to write a script to identify
1) Production tables where corresponding audit table is missing
2) Where there is column difference (In case any column missing in audit table) between Production table and Audit table
Also I've got tables tab2 and tab3 with indicatorid column in each table.
My need is to prevent inserting values into indicatorid column in tables tab2 and tab3 which are absent in id column in table tab1.
For example, at this moment id column in table tab1 has two values, 1 and 2. I can insert value 3 into indicatorid column in tables tab2 and tab3. I need to prevent from inserting value 3 into indicatorid column in tables tab2 and tab3 while it is absent in id column of tab1 table.
I have the Table name Location and synonym named Location in my DB. I am trying to create the proc where I am tryting to call the table. But its not working.
Example: CREATE PROCEDURE TESTPROC AS BEGIN DBMS_OUTPUT.PUT_LINE('testing'); select count(*) from LOCATION; END;
Compile error: Error(5,22): PL/SQL: ORA-00942: table or view does not exist
can we link a table that contains a foreign key with a sequence table? i have a table that has a sequence bookid and i want to link this table to another table that contains a foreign key of bookid.can i link this two table and how?
if the command is successful:>alter table my_table shrink;The segment will be defragmented and the High Water Mark will be moved.But what is the importance of the HWM?
Whats the difference between commands? >alter table my_table shrink; -- move HWM >alter table my_table shrink compact; -- not move HWV
I have one table in a particular database and will have to create another table in a different database. I will have to join these two tables from two different DB's.
How should I go about doing this? Is this a good practice? or is it always better to use a single DB?
What are the disadvantages of joining two tables from two different DB's?
Data migration for three tables. I have three table which are
1.npi_p_mig contain four fields (pr_id,mi_id,qty,sl_dt,fac_code) 2.np_detail(pr_id,mil_id,qty,sl_dt,facility) 3.np_ref_tab(facility,fac_code),
I need to migrated the data from based on two tables (np_detail,np_ref_tab) to new table npi_P_mig(pr_id,mi_id,qty,sl_dt,fac_code) table. i need sql script to migrate above two table to new table (npi_P_mig) .
I'm trying to do a sum over 2 different tables but can't get it to work...This is the idea:I have a table A with client ID, time-id (per day), purchase amount and segment code.
In another table (let call it B) I have a lot of client ID's and also their purchase amount, time-id and segment code. I want to sum the purchase amount for every client from table A and B for clients with certain segment code from table B.
This is what I have now:
select client_id, purchase_amountA+ purchase_amountB from tableA, tableB where A.client_id = B.client_id and time_id between 20090101 and 20091001 and B.segment_code = 'A'
This does the job, but it selects only client_id's which are in both tables. I want to select all client_id from table B with segment_code 'A' and add the purchase_amount from table A to their purchase amount from table B, at least, if they have any purchase amount in table A.
I have 2 tables SEC_MASTER_HISTA and SEC_MASTER_HISTB.
Now, I need to compare the data of the two tables column-wise.
Ideally the 2 tables should have the same security_alias values but in my case they do not as the two tables belong to 2 diff client models. There is however a main SECURITY_MASTERA and SECURITY_MASTERB tables which have the security_alias recorded and a primary_asset_id column value which can act as a link between SEC_MASTER_HISTA and SEC_MASTER_HISTB. But, I have not been able to figure out the exact query which will be ideal.
Attached are the table structures and the data it contains.
Note: I need to compare the Coupon and Freq column values of SEC_MASTER_HISTA and SEC_MASTER_HISTB.
What is the structure and the values of the default EMP and DEPT table in oracle 9i....i accidentally dropped those 2 tables .... and i don't remember the structure of it.
Each customer has a video card , When Customer rent a CD , Shopkeeper register an issue date and a Return Date . If customer return CD after Return Date Then There will be a fine of 2 Dollor .
After every 6 Months The shop Keeper review each customer Account , and Send Gifts to those customer whose Total Amount is More than 50 Dollar .and also send letters to those whose Fines Are More than 20 Dollor .
Now I am unable to understand that how many table i need to create for this .
What i have created so far is given below ,
When Customer Rent a CD then Shopkeeper will submit Following Information .
Customer_id 101 Issue DateDATE Expected_return_dateDATE Original_return_date- Fine - Total_Amount -
And at the time of return , he will Put these information .
Customer_id 101 Issue DateDATE Expected_return_dateDATE Original_return_date DATE Fine 2 Total_Amount5
But Do i need to create another table for each customer also ? That will store customer total amount , total Fines ,and shopkeeper will view it after every six months. Which type oo table i need to create ?
I have a table (promo_custom) with following colimns (id, id_promo, button_promo, button_submit, css, user, date) and other table (promo_buttons) with following columns (id, id_design, name, url, username, date) .
Columns promo_custom.button_promo and promo_custom.button_submit are foreign keys (references promo_buttons.id).
I want to create a view showing table promo_custom but instead of columns button_promo and button_submit I would like to show the column url of them.
oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production PL/SQL Release 11.1.0.6.0 - Production "CORE 11.1.0.6.0 Production"
I have a main table : CLAIMS_MAIN, in which claim_id is a primary key I have another table : CLAIMS_TRANS , in which claim_id is not a unique key, I mean, there can be more than one records with the same claim_id.. but the column transaction_id is unique, but transaction_id column doesnt exist in the main claims table.
It is required to join both these tables on claim_id...
When I join LicenseHistory,Users,UserDetails, all records are unique.
But the issues is: LicenseHistory table has duplicate records for DeployId column which is used to join table Deploybase. As a result when I join it to Deploybase, it gives me repeating entries which I don't want
I want Comments from LicenseHistory,Login from Users and User_name from UserDetails. There are multiple columns from 1st table i.e. Deploybase which I need.After reading on Exists operator, I made following query.
Select DeployId,comment from (select comments as comment,deploy_id as DeployId from LicenseHistory slh where exists (select deploy_id from symdb.symdb_deploybase sdb) and slh.comments is not null)
Above query gives me results from LicenseHistory table only. But I don't know how to add tables Users,UserDetails
1) Sal_master structure is voc_no varchar2(7),voc_date date
2) sal_detail structure is voc_no varchar2(7),item_code varchar2(10),quantity number(10,2)
3). delivery_master structure is voc_no varchar2(7),voc_date date;
4) delivery_detail structure is voc_no varchar2(7),item_code varchar2(10),quantity number(10,2)
I want to compare these four tables i have insert 10 rows in sal_master and sal_detail tables and 5 transaction in delivery tables how to compares 10 records of sal_master,detail with delivery_master and detail if not exist in delivery_master and detail tables then display only sal_master,detail records for example
Voc_no Sale Qty Deliver Qty Remaining Qty
S000075 10 5 5 if data not found from delivery master and detail then answer must be S000075 10 0 10
