Concurrent Updates On A Value
Apr 18, 2013
You have a stock_amt value in one table and there is a procedure that updates and substracts from this stock_amt.
lets say in a store a have a stock amount of 50 items and this procedure, for each sale is subtracting from this value and it is not allowed to go below zero. The process, beside the update, on this column (set stock_amt = stock_amt - x) is doing a lot of other updates on other tables and it total it takes like 0.5 seconds. Everything is fine till I want to execute this procedure by 50 users in parallel.
The initial implementation, to avoid some dead locks and we put a lock on that column (stock_amt) but there where to much waits. we cannot hold that lock for 0.5 seconds.
What will be the best approach for this? For this stock amt problem, maybe the solution can be a trade like: do not update that column every time but once in a while, by another process or by a materialize view logic.
but what if my column is a critical value like a Prepay balance or bank balance and it needs to be updated in near real time. What will you do?
View 0 Replies
ADVERTISEMENT
May 22, 2011
I have to create a audit/history table on a master table so that I can store the old/current state of data in my audit table. I am planning to write following program.
1. Created the audit table with similar number of records.
2. Everyday at a particular time I will compare the audit/main table and push the records in audit table which are either updated or not present in the audit table so that the audit table = main table + old state of data.
I am unable to figure out the proper way to implement the point 2 above in oracle database.
View 3 Replies
View Related
Feb 19, 2013
I have a table of records with a date_modified column. I would like to simply report the count of updates made each week. For example, given:
Record Date_Modified
155291141/23/2013
157277201/24/2013
152619321/25/2013
142263211/28/2013
140043421/28/2013
150050431/28/2013
148315761/29/2013
154364281/31/2013
148066382/1/2013
[Code]...
I would like to return:
Week Updates
43
57
69
77
84
Although in place of the week number, providing either the value for the last day of the week or the first day of the week would be just as good.
how to go about this task.
View 7 Replies
View Related
Oct 24, 2013
I have a context package that uses a session_id.
I want to use multiple sessions as I will have multiple users creating contexts at the same time.
Once the context is created with a session_id I want to reference that session_id in a view.
Can this be done? Using Oracle 10g R2 Context:
-- create ctxCREATE OR REPLACE CONTEXT MY_CTX USING CTX_PKG ACCESSED GLOBALLY; -- ctx package CREATE OR REPLACE PACKAGE CTX_PKG IS PROCEDURE SET_SESSION_ID (IN_SESSION_ID VARCHAR2); PROCEDURE CLEAR_SESSION (IN_SESSION_ID VARCHAR2); PROCEDURE SET_CONTEXT(IN_NAME VARCHAR2, IN_VALUE VARCHAR2); END CTX_PKG; -- context packatge CREATE OR REPLACE PACKAGE BODY CTX_PKG IS GC$SESSION_ID VARCHAR2 (100); PROCEDURE SET_SESSION_ID (IN_SESSION_ID VARCHAR2) IS BEGIN GC$SESSION_ID := IN_SESSION_ID;
[Code]...
The first query returns valoc but the second query doesn't return the session_id.
View 5 Replies
View Related
Apr 6, 2012
SESSIONID , returns the auditing session identifier am i right ?
my question is when oracle updating session id ?
SQL> show user;
USER is "SYS"
SQL> select sid,serial# from v$session where audsid=sys_context
2 ('userenv','sessionid');
[code]....
View 3 Replies
View Related
Oct 8, 2010
having a concurrent request that is always up?
As part of an interface I've implemented, I have a 'listener' concurrent request that registers for a dbms_alert and uses wait_one to detect appropriate alerts. When an alert has been detected, a loader concurrent request is kicked-off, before the listener loops back to the wait_one call. I've set a nominal wait_one timeout period of ten minutes. It is not envisaged that many alerts will be raised - maybe several a day at the moment, but I am always reluctant to use polling unless I can avoid it.
Our EBS system has gradually ground to a halt after the release of this program, and the DBAs suspect this as a possible cause. CPU usage was very high overall, with various java-related usage figure also being high.
The overall idea is to have a 'poor-mans' equivalent of OAI, so I'd hope to add more listeners, with each being the equivalent of an OAI adapter.
Surely a big powerful thing like Oracle EBS is not going to keel over just because of a few processes doing little more than waiting for alerts?
View 7 Replies
View Related
Feb 24, 2011
I am trying to execute two scripts at the same time (concurrent) in Oracle SQL Developer. I know we can schedule a job using DBMS_job package and define the job. But is there any other way of doing it using Threads ?
View 3 Replies
View Related
Nov 7, 2012
The updates occur always that a significantly change was done? (Like create, rename or drop datafiles and tablespaces)
Or the RMAN catalog owner must issue "resync catalog" frequently?
View 2 Replies
View Related
May 17, 2011
Check the below simple sql stmts:
create table tab_tab(col1 number, col2 number);
insert into tab_tab values(1, 1);
insert into tab_tab values(2, 2);
insert into tab_tab values(3, null);
[code]......
My question is why the table content didn't look like:
col1 col2
----------
1 2
2 2
3 2
4 3
5 4
I thought that when oracle will update 1st, 2nd & 3rd rows then the sub query will get a count equal to 2, but the time when it goes to update the 4th & 5th row the sub query should get back a count equal to 3 & 4 resp. BUT that is not what exactly is happening!!
View 12 Replies
View Related
Apr 8, 2011
Consider the statement below:
Update employee e
set e.dept_id = (select d.dept_id
from dept d
[Code].....
The above is not the exact code which I am executing but an exact replica of the logic implied in my code.
Now, when i display the value of 'rows_updated' it returns a value greater than 0,i.e 3 but it should ideally return 0
since there are no records matching for the condition:
(select d.dept_id
from dept d
where e.dept_name = d.dept_name)
So, I executed the statement:
select count(*) from employee e
where emp_id = 1234
and exists
(select 1
from emp_his ee
where e.emp_id = ee.emp_id)
and the result was 3 which is the same value returned by %rowcount.
why this is happening as I am getting incorrect values in %rowcount for the number of rows updated.
View 7 Replies
View Related
Feb 17, 2012
I have a table of 3 columns:
SQL> show user
USER is "ANDREY"
SQL>
SQL>
SQL>
SQL> --create the table:
[code]...
I insert rows into it:
SQL> --fill it with data:
SQL>
SQL> insert into a(key1 , key2) values (1 , 1);
1 row created.
SQL> insert into a(key1 , key2) values (1 , 5);
[code]...
i want to perform a logic by which:for every distinct value of key1 - values of key2 will be checked in all records holding that particular key1 value, and update the key3 field to 'inactive' where the key2 value for that particular key1 is the highest in number.
i've found out that i could do it by an SQL statement:
update a
set key3 = 'inactive'
where key2 = (
select max(key2)
from a a2 where a2.key1=a.key1
);
however I wanted to use the cursor to "load" the max key2 values FOR EACH distinct key1 value exists in the table,and do the same thing as the update statement above WITH A CURSOR,So tried and wrote the following:
SQL> create or replace procedure proc1
2 IS
3
4
5 var1 a.key1%type;
[code]...
unfortunately, it works only for one row, and i don't understand what's wrong, I executed, and checked what has changed:
SQL> exec proc1;
PL/SQL procedure successfully completed.
SQL> select * from a;
KEY1 KEY2 KEY3
---------- ---------- ----------
1 1 active
1 5 incative
2 24 active
2 21 active
ORA-01034: ORACLE not available
View 10 Replies
View Related
Oct 9, 2012
I have a stored procedure that is run from a command within our Clarity application.
The procedure involves some SQL Reads and SQL Inserts.
We have experienced users running the SP at the same time (slim chance to do this) and it creating duplicate entries.
if there is a clever way of preventing the same SP to be run concurrently?
Initially I was thinking of having the first step of the SP to interrogate a flag into a custom table - which the SP then sets to 1 if it is running, and 0 at the end.
Are there better more efficient/effective ways of doing this?
View 7 Replies
View Related
Oct 26, 2010
My requirement is:
I want to run multiple Reports Concurrently, being called from my Form.
Suppose I have 10 Reports Say Report1, Report2, ....Report10.
I am using "RUN_PRODUCT" to call these Reports from my Form. But it's taking too long time to run all the Reports one after another. Can I run all these Reports Concurrently at the Same time.
View 4 Replies
View Related
Jun 7, 2013
Is this the correct forum to ask questions about Oracle CPU's?
I ran the CPU-JUL-2012 on a workstation for version 11.1.0.7, got Return Code 0 and "OPatch Succeeded".
Yet a Retina scan ran after shows no change....?
I've tried reading the availability document, but I have no experience at Oracle patching.
View 4 Replies
View Related
Dec 5, 2012
This problem went away for me in 4.2, but I am limited to 4.1 in production. The problem is this: I have three editable columns in my Tabular Form
1) I make a change to column 3 and press submit. The values in columns 1 & 2 (that were not changed) are overwritten with null during the update (submit). Column 3 is saved correctly.
I deleted my tabular form and re-created it thinking that I may have trashed something. But it appears that is the way it works. How do I prevent the unchanged editable columns from being overwritten with nulls?
View 2 Replies
View Related
Sep 26, 2012
I am attempting to perform regular updates on several Oracle tables. The scripts performing the updates are scheduled to run every two minutes, get a value and update the table with that value.
The value doesn't always change but the scripts will still attempt to perform an update.
The same script is part of 7 objects, all of them are scheduled to run at the same time. They update the same table but never the same row.Even though the script is mostly the same on the 7 objects, they run completely independently of each other. The first object will usually perform the update without any problems but when it comes to the second object the script will time out.
View 9 Replies
View Related
Nov 5, 2012
We have an application with database Oracle 10g.
I want to add a new validation to restrict concurrent user and/or session from a client. (we have almost 60 client firms using the software to enter daily trasnactions). All users from all clients are connecting to the database using a common functional ID.
What I did was:
1) Add a column 'user_logged_in' in the master table for client and update it as Y when user from that client logged on to the system,
2) Insert the application logon details (we can figure out the client details from this) into a global temp table,
3) Create a logoff trigger to update the 'user_logged_in'flag in client master table by using values from global temp table when session logged off and
4) Restrict the users from same client if the flag is 'Y'
But the problem in this case is logoff trigger will not be executed in case if the session got killed or terminated abnormally.
View 3 Replies
View Related
Feb 4, 2013
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.
View 3 Replies
View Related
Nov 15, 2011
We are designing a three tiered system (client, application/web server, database server) that will allow clients through a web interface to select a text file from the operating system and load that file into a intermediate table (import database table). Many users will do this concurrently and data will load into a single table. The text files come in monthly for about 100 firms. No user is able to insert or update the data of another users data (there is a check out system). Their are about 30 to 40 users that will be using the system doing various functions but it is possible for 10 to 20 users to import data at one time. The files can have anywhere from 2000 to 25000 records at a record length of 398.I am concerned about having a good design strategy as well as decent performance.
Problems with each of the Oracle loaders.
1) External tables - Can not read data text files on the application server(which is where they want the text files to go) secondly you cannot create a instance of a external table. Multiple users will be using the external table to point to different text files and loading at the same time.
2) Sqlloader - is mainly a OS level tool and I am not sure how I could programatically point it to a different text file each time a user wants to load. The client will have to have the ability through code to point sqlloader to the correct file name.
I had a creative approach and was wondering if this would work. I would like to use external tables just like a connection pool. I would propose first a scheduled OS job to move files to the database server. I would create about 20 external tables with 20 different directory objects. Using a stored procedure for the user to call and pass in file name and audit info as needed. I would use a Load lock pool table (my invention) to load the name or a code for the external table in use. The procedure loads this code into my load lock pool table when a external table is in use and deletes the name when the load is completed. The procedure would check through a series of if statements whether a particular external table was in use. If in use (exist in load lock pool table) I would check the next available external table until a external table not in use is encountered. Now potentially 20 users at one time but not likely would be laoding into the same table at one time.My questions
1) Could Oracle handle this strategy? What do I need to consider performance wise with the possibility of so many users loading into a single table at one time?
2) Do any of you maybe have another strategy to do this?
View 8 Replies
View Related
Feb 5, 2011
I have a table with counter value which will be incremented or decremented by several application servers.
SQL> select * from test;
COUNTER
----------
10
Application servers(multiple servers) will be running update against this row for increasing the counter value or decreasing the counter value.
update test set counter=counter+1;
update test set counter=counter-1;
update test set counter=counter+1;
update test set counter=counter+1;
So when update happens concurrently to this table will the counter value gets messed up?
I did a small test by opening multiple sessions for running update and the result I got for above update statement was 11,10,11,12.
But our developer is bit skeptical about this approach and he is using select for update and then updating the row.
Which approach will be better?
View 9 Replies
View Related
Apr 27, 2010
I have a requirement to calculate the maximum number of concurrent calls from the following data:
Create_date connect_date_time disconnect_date_time duration ...
12/01/10 13:20:26 1263253551 1263254153 602
...
I have attempted to use the analytic function to keep a running total of the count of active calls based on the connect and disconnect times given for each record row.
e.g.
SELECT
count(*) calls,
avg(duration)/60 average_duration_mins,
max(duration)/60 max_duration_mins,
sum(duration)/60 total_mins,
(SUM(DURATION)/60)*0.04 total_cost_4c_per_min
[code]....
View 7 Replies
View Related
Jun 16, 2011
How can we run concurrent program on SAVE (push button) in oracle forms?
View 3 Replies
View Related
Nov 16, 2012
We are experiencing a problem with SSO causing 2nd or 3rd concurrent Oracle sessions to hang. The Oracle application hangs during loading and the task manager has to be used to close the application.
I have tested logging onto our application servers using SSO and I cannot load more than 3 concurrent Oracle sessions. When I bypass the SSO and logon to the same server I can load more than 20.
View 1 Replies
View Related
Jan 20, 2011
I need some detail information regarding the CPM Patches released by oracle quarterly.
Is it compulsory to set the CPM patch when it is being realized. How to set or apply this patch. (either by runInstaller or OPatch utility i am not sure about this)
What is the difference between the interim & CPM patches.
View 3 Replies
View Related
Aug 30, 2012
How to update a form fields which taking a searched value from another table .
I have a form that records student data and which has a drop down to select the 'student registration No' that comes from different table.
When insert or update a student record, first we select the student registration No from drop down to see if he is an existing student.
If he is already registered student then relevant student name and other details should be shown in the form and should be able to enter few other details as well.
View 3 Replies
View Related
Feb 5, 2013
This is my stored procedure
I have below store procedure:
create or replace
PROCEDURE TESTPERFORMANCE (
o_statuscode OUT NUMBER,
o_statusdescription OUT VARCHAR2,
starttime out timestamp,
time_after_query_TESTJOB out timestamp,
[Code]...
This procedure is taking around 35 minutes when there are 35000 records to loop over (i.e cursor has 35000 records) and TESTJOBTRANSACTIONS table has 90000 records. How to reduce execution time.
View 12 Replies
View Related
Aug 7, 2012
There is a detail table
There is a summary table
During batch process record is entered in detail table as well as summary table.
The process first checks if record exists in summary table for same group_no and if 'yes' then "updates" the record with the newly added amount (sums it) else inserts a new record
Whereas in the detail table it inserts the record directly
detail table :
group_no doc_no amount
101 doc1 100
101 doc2 200
102 doc3 300
102 doc4 400
summary table :
group_no amount
101 300
102 700
Now if the batch process runs in parallel, (out of many) two different sessions insert same group_no; This is because while sesond session inserts a record, first session inserting the same record (group_no) has not yet committed ; So second session Not knowing that already there is same Group_no (101) inserted, again inserts another record with same group_no rather than summing it.
Can it be solved without using temp table, select for update?
View 4 Replies
View Related
Jan 23, 2013
I have a problem. I have created a report that will list out details of checks using Report Builder. I use XML Publisher to register a template for the report. When running the program through concurrent request, the output will come in pdf format but are not in order.I have add 'Order By' statement in my sql scripts in report builder. Its working fine if test in report builder but not in concurrent program.
View 4 Replies
View Related
Mar 12, 2013
I was given a task by manager to keep track of changes on a given table including os_user who made it.Should I create a trigger on it (on any update, insert, delete etc.) or there is a better way of doing it ?I think there could be some info already in some data dictionary views or something like it.
If I CREATE MATERIALIZED VIEW LOG on that table.
View 4 Replies
View Related
Nov 18, 2011
Need to find out what the maximum number of concurrent logins are over a period of 1 month. We need to do this as we may need to buy more licences.
View 4 Replies
View Related
