SQL & PL/SQL :: How To Know Who Is Locking Table
Apr 12, 2010I want to Who is locking the table by an SQL statement
View 32 Replies
ADVERTISEMENT
Locking Users By A Table Field Value
Dec 27, 2006We have a table with several columns (id, title, description, area).The data in the table looks like this
1 sometitle1 description1 USA
2 sometitle2 description2 Germany
3 sometitle3 description3 Japan
4 sometitle4 description4 Honduras
We have Oracle users with their usernames and password. We would like to lock every single user to a certain area.Example:
user 1 may see only records, where AREA=USA
user 2 may see only records, where AREA=Honduras
How can I do this in Oracle. I am using Oracle Enterprise 9.2
11g Child Table Update Locking?
May 26, 2013I'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?
Get Table RowID Of Session That Is Locking In RAC Database
Jun 11, 2012I 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:
select vs.inst_id,
vs.audsid audsid,
locks.sid sid,
[Code]....
Table Locking When Many Users Retrieve Data
Sep 2, 2009I 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.
View 4 Replies View RelatedRow Locking In Production
Jan 10, 2013I 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
SQL & PL/SQL :: Locking And Foreign Key Indexes
Sep 1, 2013Say 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?
Forms :: Record Locking?
Oct 21, 2010I 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.
Way To Resolve Database Locking
Aug 27, 201311.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:
SQL> exec dbms_workload_repository.add_colored_sql(sql_id => 'fn3qv2dhsu3nb');
PL/SQL procedure successfully completed.Can I now run AWR,ASH,ADDM, to identify/capture the sql being run?
PL/SQL :: Row Locking During Update Statement?
Aug 9, 2013Using 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.
Archive Processes Locking In Oracle
Nov 9, 2010I 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?
Performance Tuning :: Locking Of Two Sessions?
Apr 27, 2012how 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.
SQL & PL/SQL :: Locking Views (rows Of Data While Updating)
Nov 10, 2011I 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;
Server Administration :: Sessions And LGWR Locking?
Jul 13, 2010We 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.
View 4 Replies View RelatedPerformance Tuning :: One Procedure Is Blocking And Another Is Locking It
Jan 25, 2011I am new to performance tuning. One procedure is blocking and another is locking it.
SQL> @glockers
Oracle OS
INSTANCE SID TY Usernam SERIAL# SPID Process LMODE REQUEST
---------- ------ -- ------- ---------- ------------ ------- ------------------------------------ ------------------------------------
[code]...
SQL & PL/SQL :: Automate Locking / Unlocking Of User Accounts
Nov 1, 2011I 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?
PL/SQL :: How Database Handles Record Locking When Aggregate Function Called
Jul 17, 2012how the DB handles record locking when an aggregate function is called? For instance:
...
select count(*)
into v_count
from x;
...Is there a lock maintained on table x for the duration of the transaction so no rows can be inserted or deleted?
Hibernate Optimistic Locking Versus Database Isolation Levels
Jan 26, 2013Among 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?
View 2 Replies View RelatedSQL & PL/SQL :: Incorporate Parallel Loading To Load Data Into Database - Oracle Locking Not Working?
Aug 3, 2011At 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.
Application Express :: Application Locking - Force Logout All Current Users And Prevent Login
Jun 12, 2012I have an application that I'd like to prevent activity in after a certain time of day, say 3pm. Is there any way I can force logout of all current users and then prevent re-login until 9am the next day (short of deleting all of their login credentials and then recreating them the next morning)?
View 7 Replies View RelatedServer Administration :: Move Partitioned Table Between Table Spaces Of Different Block Size?
Apr 4, 2011I was about to move some tables from one table space to another but it seems it is not possible to move partitioned tables between table spaces of different block sizes.
So far the only option I have is to export and then import back the data.
know if there is any way to move a partitioned table between table spaces of different block size?
Server Utilities :: Geometric Data From Text To Table And Wrong CTL Upload Into Table
Jul 11, 2013I have a requirement to import text files which are generated from 3d modelling software xsteel where it records all geometric information and i want to import this information into oracle table.
CREATE TABLE dstv_head ( wo_no VARCHAR2(12),struct VARCHAR2(12),rev_no NUMBER,
mark VARCHAR2(12),pos VARCHAR2(12),grade VARCHAR2(12),qty NUMBER,PROFILE VARCHAR2(24),TYPE VARCHAR2(12),
len NUMBER,width_web NUMBER,width_bottom NUMBER,flange_thk NUMBER,web_thk NUMBER,radius NUMBER,kgm NUMBER,
kgm1 NUMBER,kgm2 NUMBER,bevel_plus NUMBER,bevel_minus NUMBER,holes_yn VARCHAR2(1),holes_v_yn VARCHAR2(1),
hole_x_dim NUMBER,hole_y_dim NUMBER,hole_dia NUMBER,no_of_holes NUMBER)
-- All the data which has to go under specific field for example **9005.nc1 will go into wo_no field, 1239401A will go under struct.
ST
** 9005.nc1 --WO_NO
1239401A - STRUCT
1 -REV_NO
9005 -MARK
9005 --POS
S275JR --GRADE
2 --QTY
[code]....
SQL & PL/SQL :: Primary Constraint On Table Affecting Procedure To Insert Rest Of Rows In Table?
Jun 12, 2012primary key constraint on transaction_dtl_bk is affecting the insertion of next correct rows.
CREATE OR REPLACE PROCEDURE NP_DB.san_po_nt_wnpg_1 (
dt DATE
)
IS
v_sql_error VARCHAR2 (100); -- added by sanjiv
v_sqlcode VARCHAR2 (100); ---- added by sanjiv added by sanjiv
[code]...
SQL & PL/SQL :: Interface Table Compared With Normal Table - Result Dumped If Match Found
Aug 17, 2012Oracle 10g, Windows XP
There is an interface table and there is an normal transcational table..interface table is being compared with normal table and if match found the result is dumped into another normal table.
I am using two cursors one is to query the interface table and in a for loop pass the results to the second cursor..The interface table is having 5000 + rows and the transcation table is having more than 3.7 millions ..and the program is taking lots of time to execute..took almost 35-45 minutes..
create table x_interface /* INterface table */ -- 5000 + rows
( name varchar2(80), addr_line1 varchar2(35), addr_line2 varchar2(35), addr_line3 varchar2(35),
addr_line4 varchar2(35), addr_line5 varchar2(35), addr_line6 varchar2(35), suffix varchar2(35),
city varchar2(15), state varchar2(10), zcode varchar2(10))
[code]....
Creating SQL Script That Can Update Info From One Table In Dbase1 To Another Table In Dbase2?
May 16, 2013creating an sql script that can update info from one table in dbase1 to another table in dbase2 that has the same columns and if possible insert date and time in one column when the synchronized is done?
View 3 Replies View RelatedSQL & PL/SQL :: Dynamic Function - Create Physical Table And Return Table Name In Out Variable
Aug 30, 2011I am trying to execute dynamic SQL in Stored Function and I don't know how to do this.
Explanation:
In the function I am calling pr_createtab is procedure which will create a physical table and return the table name in the out variable v_tbl_nm.
I need to query on this dynamic table and return the result as return result. But i am not able to do it.
Here T_web_loylty_report_table is a type.
CREATE OR REPLACE function CDW_DSS.f_ReturnTable(i_mrkt_id in number, i_cmpgn_year in number)
return T_web_loylty_report_table is
v_tbl_nm varchar2(50);
i_cntry_cd varchar2(20);
v_sql_str varchar2(32567);
[code]......
SQL & PL/SQL :: Load 10 Million Rows In Table From Another Table Based On Multiple Joins
Sep 24, 2010We have to load 10 million rows in a table from another table based on the multiple joins. How much tablespace size we allocate to the table and for performance point of view how much should be the SGA size.
View 11 Replies View RelatedSQL & PL/SQL :: Create Complete Hierarchical Table From Table With Only Two Columns - Parent And Child
Aug 13, 2012We 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.
SQL & PL/SQL :: Command Is Used To Create Table By Copying Structure Of Another Table Including Constraints?
Jul 14, 2012what command is used to create a table by copying the structure of another table including constraints ?
View 2 Replies View RelatedSQL & PL/SQL :: Assign Doc_num From Doc_id Table To 4 Customers In Rent Table Randomly
Jan 6, 2012I've 2 table with below colums
Create table rent (customer_id number(10),
doc_num varchar2(20)
);
Create table doc_id (doc_num varchar2(20));
Insert into rent(customer_id) values (1);
Insert into rent(customer_id) values (2);
Insert into rent(customer_id) values (3);
Insert into rent(customer_id) values (4);
[code]...
Now my requirement is i need to assign doc_num from doc_id table to 4 customers in rent table randomly. I mean update doc_num in rent table from doc_id table randomly. how to write update statement.