Server Administration :: Updating Table Through Concurrent Sessions

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


ADVERTISEMENT

SSO Preventing Multiple Concurrent Oracle Sessions From Loading

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

Performance Tuning :: How To Control Concurrent Inserts Of Record By Parallel Sessions

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

Server Administration :: Find Maximum Number Of Concurrent Logins Over A Period Of 1 Month

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

Server Administration :: Sessions Having Status Sniped?

Mar 9, 2012

My application is opening a lot of sessions in my DB server. I applied resource_limit=true and idle_time=15 min. ans assign this profile to all application user.

Now I am seeing a lot of sessions having status sniped in v$session.

I want to clean up these sniped sessions and what they mean.

View 19 Replies View Related

Server Administration :: Oracle 10g Blocking Sessions

Aug 19, 2010

I am using oracle 10g as server in my lab. I faced some problems initially, but later after increasing the USERS tablespace it is working fine.

But there is still one problem. During the query execution some queries will be blocked and it doesn't leave any consequent queries to execute from the same user.

The blocked sessions will be displayed in the admin page under blocking sessions link. There is a option to kill the session. But when i do that, it affects all the users and the connection will be lost to all the users. again I have startup the database from beginning.

View 1 Replies View Related

Server Administration :: Sessions And LGWR Locking?

Jul 13, 2010

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.

View 4 Replies View Related

Server Administration :: Database Sessions - Setting Value Of Parameter

Mar 26, 2013

I have a simple question about database sessions. The value of parameter "sessions" is set to 500 and the users connect to database through an application server(Jboss). There are more than 500 users connect to the database through application.

My question is, how more than 500 users can connect to the database without any issue, if we set the value of "sessions" parameter to 500?

View 2 Replies View Related

Server Administration :: Identifying Proxied User Sessions

Mar 30, 2011

I'm sure you are all familiar with proxy users, they've been around since 9i: orcl> create user low identified by low;

User created.
orcl> create user high identified by high;
User created.
orcl> grant dba to high;
Grant succeeded.

orcl> alter user high grant connect through low;

User altered.

orcl> connect low[high]/low
Connected.
orcl> sho user
USER is "HIGH"
orcl>

Is there any way that I can find out which of the current sessions was proxied, and through what user? I know that from within the session I can query my userenv context and find out, but I can't see how to do it otherwise. It must be possible: the audit trail records both the real user and the proxy user.

View 3 Replies View Related

Server Administration :: Killed Sessions Exists Even After 2 Days

Oct 27, 2010

I killed some 70 sessions which where inactive couple of days ago using alter system kill session(sid,serial#) immediate;

But even after killing them it still exists in the v$session view when I query select sid,serial# from v$session where username is not null; It has the same sid and serial#....I tried killing them every now and then but it still shows those exists

View 3 Replies View Related

Server Administration :: Number Of Sessions In Database MES (production) Coming From Another Machine

May 25, 2010

We have quite a number of sessions in database MES (production) coming from another machine.

From v$session, the program is oracle@WID27 (TNS V1-V3). This WID27 (hostname) consists of quite a number of development databases inside. We have to trace which jobs are actually triggering this, as WID27 are not suppose to connect to production databases.

How can we tell whether the sessions came in is from dblink or from the machine itself?

View 3 Replies View Related

Server Administration :: Updating Jdk 6 On Oracle Application Server 10.1.3 On EBS 12.0.4?

Mar 2, 2011

In Process of upgrading Oracle EBS 12.0.4 to 12.1.1 I updated the $IAS_ORACLE_HOME/appsutil/jdk to jdk 6u24. After replacing the original jdk with jdk 6u24 when I attempted to start the apps adoacorectl.sh failed with below error.

Quote:ld.so.1: /d4/app/oracle/apps/12.0.0/vdd700/web/10.1.3/appsutil/jdk/bin/java: fatal: libjli.so: open failed: No such file or d
irectory

I followed below steps to update the jdk
1. extracted the jdk-6u24-solaris-sparcv9.tar.Z to $IAS_ORACLE_HOME/appsutil/jdk
2. Copied lib from old jdk to $IAS_ORACLE_HOME/appsutil/jdk
3. Copied lib from oldjdk/jre to $IAS_ORACLE_HOME/appsutil/jdk/jre
4. The java was not infact in jdk/bin rather it was /bin/sparcv9 so I copied them to /jdk/bin. The same way copied the executables from /jdk/jre/bin/sparcv9 to /jdk/jre/bin .

View 1 Replies View Related

Server Administration :: Updating Statistics - Not Working?

Jul 4, 2010

if there are any problems with updating statistics? My manager told me that they had big performance issue when they updated the statistics of whole database. Is this a rare case?

I know the advantages of updating statistics,but are there any dis-advantages?

View 2 Replies View Related

SQL & PL/SQL :: Difference In Table Data Between Sessions

May 27, 2011

I create table in session1:

create table marco_tmp_270511
as
select 5 as t from dual

Then I log in session2 and perform query to

select * from marco_tmp_270511 and receive 5 in column t.

Then I return to session1 and perform:

delete marco_tmp_270511;
insert into marco_tmp_270511
select 99 as t from dual ;
select * from marco_tmp_270511;

Then I go again to session2 and perform query to

select * from marco_tmp_270511

and still receive 5 in column t. How do I synchronize data between sessions?

View 3 Replies View Related

Server Utilities :: Slow Exports Using Exp Command Due To Open Sessions

Nov 11, 2011

I have had the following problem open with Oracle support since March 2011 (8 months), and still no resolution.

When I export all our schema's on Sunday night it takes about 1 hour 50 minutes. When I export the same schema's on any other night it takes 7 hours. The only difference is that on Sunday at 4:00am we drop all connections in the connection pools and reestablish new connections. Then 19hours later on Sunday at 23:00 we perform the exports which only take 2 hours to complete.

I have also tried recreating the connections in the connection pools during the week, and the exports have then only taken 2 hours to complete. But the following night after the connections have been used during the day, the exports again take 7 hours. So it appears the export speed gets significantly slower when there are many open connections that have been used and not closed.

From the stats pack report I found 2 SQL statements internal to the export command, that had an order of magnitude in difference when looking at the elapsed execution time between the fast export, and the slow export (see below).

How to speed up the exports without having to drop and recreate the database connections in the connection pools each night.

FAST:
elapsed_time: 430.90
executions: 161,388
Module: exp@Oracle1 (TNS V1-V3)
SELECT COLNAME, COLNO, PROPERTY, NOLOG FROM SYS.EXU10CCL
WHERE CNO = :1 ORDER BY COLNO

elapsed_time: 264.29
executions: 50,349
Module: exp@Oracle1 (TNS V1-V3)
SELECT TOWNER, TNAME, NAME, LENGTH, PRECISION, SCALE, TYPE, ISNULL, CONNAME, COLID, INTCOLID, SEGCOLID, COMMENT$, DEFAULT$, DFLTLEN, ENABLED, DEFER, FLAGS, COLPROP, ADTNAME, ADTOWNER, CHARSETID, CHARSETFORM, FSPRECISION, LFPRECISION, CHARLEN, TFLAGS, 100 FROM SYS.EXU8COL
WHERE TOBJID = :1 ORDER BY INTCOLID

SLOW:
elapse_time: 8264.16
executions: 124,662
Module: exp@Oracle1 (TNS V1-V3)
SELECT COLNAME, COLNO, PROPERTY, NOLOG FROM SYS.EXU10CCL
WHERE CNO = :1 ORDER BY COLNO

elapsed_time: 3877.78
executions: 38,813
Module: exp@Oracle1 (TNS V1-V3)
SELECT TOWNER, TNAME, NAME, LENGTH, PRECISION, SCALE, TYPE, ISNULL, CONNAME, COLID, INTCOLID, SEGCOLID, COMMENT$, DEFAULT$, DFLTLEN, ENABLED, DEFER, FLAGS, COLPROP, ADTNAME, ADTOWNER, CHARSETID, CHARSETFORM, FSPRECISION, LFPRECISION, CHARLEN, TFLAGS, 100 FROM SYS.EXU8COL
WHERE TOBJID = :1 ORDER BY INTCOLID

I use the following export command for each schema:
$ORACLE_HOME/bin/exp user/pass file=somefile.dmp owner=$SCHEMA log=somelog.log buffer=9000000

I have an Oracle Standard edition 11.1.0.7 database on 64bit Linux with a 7GB SGA. I currently export (I use exp not datapump because datapump is a lot slower and we can't use parallel processing features of datapump on a standard edition database) approx 200 schema's each night. The export normally takes 1 hour 50 minutes which is approximately 2 schema's exported every minute. When the exports run slowly each export takes almost 2 minutes to complete.

The database has about 20 GB data and 50 GB indexes. The database has also approx 500 connections via toplink connection pools from 8 application servers.

View 2 Replies View Related

Server Administration :: Move Partitioned Table Between Table Spaces Of Different Block Size?

Apr 4, 2011

I 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?

View 14 Replies View Related

Server Administration :: Reorganize A Table And Index After The Deletion Of Records From Table?

Feb 7, 2012

We deleted millions of records from a table.

1.Is it necessary to reorganize a table and index after the deletion of records from table ? Because i see some change in table size after table and index reorganization.

2.Will re org table and index improve the database performance ?

View 7 Replies View Related

Updating Table With Same Key?

Nov 19, 2008

I am using oracle 9i, and having trouble with updating a table.

I get ORA-00001(unique constraint) Error on executing the sql below; I know sql below is little strange( which use unique key in 'SET' statement) . but It worked on My Oracle Server. but it didn't on Client's.

why this error occurs or why this error did not occur on my PC,

[Update sql](key is CD and SDATE)
Update TBL1
set CD = 'A',

[Code]....

View 4 Replies View Related

Updating Two Table Via SQL Loader

Jun 27, 2011

I am trying to write a control file that will read information from two INFILES and update two tables with the different information via SQL Loader. I am using Oracle 11g on Linux. i am not sure how to take the result from the first insert query and use it as input to the second insert query. Currently I have the following control file:

LOAD DATA
INFILE 'table1.dat'
INFILE 'table2.dat'
APPEND
INTO TABLE table1
WHEN tid='1'
FIELDS TERMINATED BY ","
OPTIONALLY ENCLOSED BY '"'
(pid "pid.nextval", p_fname, p_lname, tid)
[code]....

The pid in the second insert query should be taken from the pid generated in the first insert query. However, I do not know how to do this. Does SQL Loader run the first insert query for all entries in the table1.dat file and then the second insert query or will it do one record at a time? Here are my INFILES:

table1.dat
,John,Doe,1
,Joe,Smith,1

table2.dat
10,,abc,1,
11,,xyz,1,

The second field in table2.dat should be taken from the result of creating a record in the table1.dat. Is this something that can be achieved using SQL Loader? The first part of the control file is successful, I can see the table being updated with the contents on table1.dat, but the second query fails.

View 1 Replies View Related

SQL & PL/SQL :: Trigger Updating Second Table With PK Value

Oct 20, 2011

Oracle 11.2 - The goal is to create a trigger on table and anytime an update, delete or insert is done on the table, write values to a second table. I have the trigger and it works except it is not loading my col1/PK values. I understand I need to do a new/old value. Col1 is my PK on Table that I want to load anytime there is an update/delete/insert on the table. How do I code the old/new variable?

My
CREATE OR REPLACE TRIGGER TRIGGER_NAME
AFTER INSERT OR UPDATE OR DELETE
ON TABLE_NAME
FOR EACH ROW
DECLARE
v_col1 TABLE_NAME.COLUMN%TYPE;
BEGIN
[code]...

View 5 Replies View Related

Updating A Table Column Which Is XML Datatype

Nov 1, 2011

I am updating a table column which is xml datatype and am getting above error.Below is the process what i did. since the xml is too large i split them into small chunks.

DECLARE
conditionXML CLOB;
ls_xml_2 Clob;
ls_xml_3 clob;
ls_xml_4 Clob;
ls_xml_5 Clob;
ls_xml_6 clob;
ls_xml_7 Clob;
[code]...

View 1 Replies View Related

SQL & PL/SQL :: Updating Fields Of History Table

Aug 25, 2011

pgit_policy is transaction table having producer code field.

pgith_policy is history table, on that table if any endorsement passed new records created with same polh_sys_id and increment on POLH_END_NO_IDX.

I am trying to update all records of the history table but its updating only higest POLH_END_NO_IDX only. i need to update all producer code.

update pgith_policy a
set a.polh_producer_code= (select b.pol_producer_code
from pgit_policy b
where b.pol_no=a.polh_no
--and b.POL_END_NO_IDX= a.POLH_END_NO_IDX and b.POL_END_SR_NO = a.POLH_END_SR_NO
and b.pol_producer_code is NOT NULL
and b.pol_class_code='10')
where a.polh_class_code='10'
and a.polh_producer_code is null
and a.polh_appr_dt between to_date('01-06-2011', 'dd-mm-yyyy') and to_date('30-06-2011', 'dd-mm-yyyy')

View 4 Replies View Related

SQL & PL/SQL :: Updating Table Statistics - View Not Appropriate

Oct 22, 2010

how many rows certain tables have.

updating the statistics for a table (with GATER_TABLE_STATS) and using NUM_ROWS then. This works fine for me as long as I am the owner of the table, but when someone else is, I always get this error: ORA-20000: Table does not exist or insufficient privileges.what privileges do I need to use GATHER_ TABLE_ STATS on all Tables, which were created by Users?

when I tried to use ANALYZE TABLE TEST_TABLE COMPUTE STATISTICS on a certain table I got the following error: a view is not appropriate here. The strange thing is, TEST_TABLE is not a view (at least it is not listed in ALL_VIEWS and is listed in ALL_TABLES, so it cant be a view right?).

Besides, is there another way to gather Table Statistics (not using Analyze Table or Gather_Table_Stats)?

View 3 Replies View Related

SQL & PL/SQL :: Trigger For Updating All Columns Of A Table

Sep 12, 2013

I have created the following trigger whcich will track all the column changes and insert the row in log table. here i have some doubt while substituting the cursor value.

create or replace trigger historylog_trigger
before update on log_dev_test
for each row
declare
PRAGMA AUTONOMOUS_TRANSACTION;
in_loamid number(10);
in_col_name varchar2(10);
in_old_val varchar2(100);

[Code]..

are the below assignement of values will work ?

in_old_val:= ':old.'||r.column_name;
in_new_val:= ':new.'||r.column_name;

i want to take the coulmname from the cursor and assign tat to psuedo columns like :new.r.column_name .

View 8 Replies View Related

SQL & PL/SQL :: Updating Table With Latest Record

Mar 17, 2011

DROP TABLE TESTING CASCADE CONSTRAINTS;
CREATE TABLE TESTING
(
DATAPERIOD DATE,
EMPLID VARCHAR2(20 BYTE),
B_OS_REASON VARCHAR2(9 BYTE)
)
TABLESPACE USERS;

[Code] ........

SQL> select * from testing order by 1;

DATAPERIO EMPLID B_OS_REAS
--------- -------------------- ---------
01-OCT-10 2387972
01-NOV-10 2387972
01-DEC-10 2387972 XXXXXX
01-JAN-11 2387972
01-FEB-11 2387972

In the above result, We need to go from bottom up and when we hit some value we need to update with the lastest record as below.("Blank" space are considered as null.)

DATAPERIO EMPLID B_OS_REAS
--------- -------------------- ---------
01-OCT-10 2387972
01-NOV-10 2387972
01-DEC-10 2387972 XXXXXX
01-JAN-11 2387972
01-FEB-11 2387972 XXXXXX

View 5 Replies View Related

SQL & PL/SQL :: Updating Another Table For Equal Strings

Jun 8, 2010

There are two tables:

create table songs(song_name text, song_artist text,song_url text, song_cat text, last_edit text);
create table categories(cat_name text, cat_total int);

im trying to create a trigger that, when i insert a new song in the songs table, it will check the category of the song (song_cat) and increase the respective cat_total (from table categories) by 1.

here is what i've done so far:

drop trigger countcat;
CREATE TRIGGER countcat AFTER INSERT ON songs FOR EACH ROW
update categories SET cat_total= cat_total +1
WHERE cat_name = (select song_cat FROM inserted);

What to write in the cat_name = (select ...). I have tried lots of stuff but still nothing. when i use this, i get the error that mydatabase.inserted doesnt exist

View 4 Replies View Related

SQL & PL/SQL :: Updating A Table With A Select Statement?

Nov 8, 2011

this is the correct syntax for updating a table with a select statement included. Table created easily and the alter table ran fine, but the update is running quite a log time.

My ultimate goal is to populate the "children" field with a count of children for each household id.

create table NON_GBC_Members nologging as
select distinct hcp.household_master_ID
from mrtcustomer.household_child_profile hcp
where hcp.child_birth_dt between '31-OCT-2000' and '30-OCT-2011'
group by hcp.household_master_id
minus

[code]....

View 5 Replies View Related

Updating A Column Of A Table With 70,000,000 Records

Aug 1, 2012

I need to update column of a table with +,- 70,000,000 records.

If I perform an update it lasts......too much and does not finish!

View 6 Replies View Related

Connecting To Oracle With VB7 And Updating Table?

Jul 7, 2013

I am trying to learn Oracle and trying to use Visual Basic 7 to connect to an Oracle database, upload a text file to an empty table and then update a second Oracle table based on the info in the first table. I know you can do this with SQL*Loader, but how about VB? I did some research and found this posting: Re: Using Bulk insert or SQL Loader in VB6 I borrowed the code from there and altered it for my application (see below). 

Dim con As New ADODB.ConnectionDim rst As New ADODB.RecordsetDim rst2 As New ADODB.RecordsetDim rst3 As New ADODB.Recordset con.ConnectionString = "Provider=OracleDB;User ID=john;Password=doe;Data Source=TestDB;"con.Open 'open the text filerst.Open "C:OracleTestCombinedCombined.txt" 'rst2 is an empty recordset because TempTable is empty at this time.rst2.Open "select * from TempTable", con, adOpenDynamic, adLockOptimistic 'adding rows into TempTable.Do Until rst.EOFrst2.AddNew Array("PACKET", "DATERET"), Array(rst.Fields("PACKET"), rst.Fields("DATERET"))rst.MoveNextLoop rst.Closerst2.Closecon.Close  

My second question is this -  say I had a second Oracle table in the same DB called "MasterTable", with the same two fields as "TempTable" (PACKET and DATERET). I want to update the DATERET field in "MasterTable" with the DATERET field in "TempTable" ONLY when the "PACKET" field matches in both tables.

View 5 Replies View Related

Updating Multiple Records In A Table

Oct 29, 2012

OS:Solaris
DB:10G

I have a situation where there are multiple records for a join criteria. I am trying to find a way to update a particular column for all the records returned by the join criteria. Example :

Table A

id number

1 1000
2 2000
3 3000
4 4000

Table B

id number
1 9999
1 9999
1 9999
1 9999
1 9999
1 9999
2 8888
2 8888
3 6666
3 6666

Result after update:

Table B

id number
1 1000
1 1000
1 1000
1 1000
1 1000
1 1000
2 2000
2 2000
3 3000
3 3000

update query ? When I use a update statement with where exists it errors out because the query returns more than one row for the join condition.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved