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


ADVERTISEMENT

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 View Related

Data Guard - Difference Between 2 Commands?

Mar 6, 2012

Before I am going to implement data guard in my company i am experimenting it in a test server. I want to know the difference between these 2 commands mentioned below which i found from various websites.

[ START REDO APPLY ]
SQL> alter database recover managed standby database disconnect;
[ START REDO APPLY USING REAL-TIME APPLY ]
SQL> alter database recover managed standby database using current logfile disconnect;

Which of the two commands should i use in my production database.

View 1 Replies View Related

Data Guard :: Difference Between Standby And Dataguard

Jun 29, 2010

Difference between standby and dataguard. I assume standby and dataguard are same?

View 4 Replies View Related

SQL & PL/SQL :: Find Difference Between Two Table With Respect To Column?

Jul 20, 2013

I have two tables tab1 and tab2 with below data Now requirement is to find difference between two table with respect to column "serial_no" which exist only in table tab1 so as per below at serial_no 2 CC4 in tab1 but CC2 in tab2 so this is different how to dump out this data

TAB1
SQL> select * from tab1;

PARENT CHILD ROW_ID SERIAL_NO
-------------------- -------------------- ---------- ----------
P1 CC1 101 1
P1 CC4 104 2
P1 CC5 105 3
P1 CC2 102 4
P1 CC3 103 5

TAB2
SQL> select * from tab2;
PARENT CHILD PREVIOUS_ROWID ROW_ID NEXT_ROWID
-------------------- -------------------- -------------- ---------- ----------
P1 CC1 0 101 102
P1 CC2 101 102 103
P1 CC3 102 103 104
P1 CC4 103 104 105
P1 CC5 104 105 0

create table tab1(parent varchar2(20), child varchar2(20),row_id integer,serial_no integer);

insert into tab1 values('P1','CC1',101,1);
insert into tab1 values('P1','CC4',104,2);
insert into tab1 values('P1','CC5',105,3);
insert into tab1 values('P1','CC2',102,4);
insert into tab1 values('P1','CC3',103,5);

create table tab2(parent varchar2(20), child varchar2(20),previous_rowid integer,row_id integer,next_rowid integer);

insert into tab2 values('P1','CC1',0,101,102);
insert into tab2 values('P1','CC2',101,102,103);
insert into tab2 values('P1','CC3',102,103,104);
insert into tab2 values('P1','CC4',103,104,105);
insert into tab2 values('P1','CC5',104,105,0);

View 8 Replies View Related

SQL & PL/SQL :: Difference Between Table / Column And Row Level Constraints?

Dec 24, 2010

through Difference between table Level, column Level ,row level constraints.

View 8 Replies View Related

SQL & PL/SQL :: Difference Between Normal And Cluster Index On Table?

Aug 4, 2010

Difference between Normal Index on Table and Cluster Index on Table?

View -1 Replies View Related

Server Administration :: Find Out Difference Of Data For Some Tables Between Current And Previous Day

May 26, 2011

We want to find out difference of data for some tables between current day & previous day. We can use query with minus operation but it will take lot of time since table size is in range from 200 to 500 GB. We have to do this exercise every day.

View 5 Replies View Related

Performance Tuning :: Difference Between Move And Shrink Table?

Jul 22, 2011

What is different between move table and shrink table?

View 2 Replies View Related

PL/SQL :: Compare Two Rows From Same Table And Show Field And Difference?

Aug 28, 2013

How can I compare two rows from the same table and show the field and the difference?

Example:

select ename, job, mgr, hiredate, sal, deptno
from EMP t
where t.empno = 7782
or t.empno = 7788

ENAME JOB MGR HIREDATE SAL DEPTNO
1 CLARK MANAGER 7839 6/9/1981 2450.00 10
2 SCOTT ANALYST 7566 6/9/1981 3000.00 20

My report comparing fields should be:

FIELD BASE COMPARE DIFFERENCE
ENAME CLARK SCOTT --
JOB MANAGER ANALYST --
MGR 7839 7566 --
SAL 2450 3000 550 --(Show difference only for numbers)
DEPTNO 10 20 --

View 9 Replies View Related

Enterprise Manager :: What Is The Difference Between Create External Table Vs Create Table

Apr 29, 2011

What is the difference between CREATE EXTERNAL TABLE Vs CREATE TABLE .?

Is CREATE EXTERNAL TABLE included in CREATE TABLE?

View 3 Replies View Related

PL/SQL :: Static Table Creation In Oracle - Difference Between Static And Dynamic Table

Dec 11, 2012

Static Tables Creation In oracle & Diff Between Static table ,Dynamic table

View 2 Replies View Related

SQL & PL/SQL :: Count Of Date Difference Using Pending State Value In Table Field

Feb 2, 2012

Find the date difference. I need to find that how many days the task is pending, if ACT_NAME field switching from 'SET PENDING%' to 'RESUME PENDING%' by using ACTIONTAKENDATETEXT field in the History table.

Example as needed:
NoPendingDays = 23 (8+15)

I have attached Create table and Insert table values sample as SQL file.

View 10 Replies View Related

PL/SQL :: Calculate Difference By Which Day Count Of Difference Is Going?

Oct 17, 2012

Detail table will look like below:

Product_id issue_date action_date Force_date
1 10/10/2012 10/10/2012 10/10/2012
2 10/10/2012 10/10/2012 10/10/2012
3 10/10/2012 13/10/2012 15/10/2012
[code]....

Need the data like

Issue_date count_action_date count_Force_date (diff(action_date,force_date) 1 2 3 4 5 6(days since over)

10/10/2012 3 4 1 4 2 1 0 0

How to get the data like this? automatically how to get 123.... and how to calculate the difference by which day the count of difference is going?

View 3 Replies View Related

Sessions Value Always Seems To Be Higher Than Set Value

Jul 23, 2010

I have set my processes and session value in my database as 1000 and 1248 respectively. I am using 11.2.0.1 but when I restart my DB it showing me sessions=1524...its too high value then what I set.I don't know why it showing higher value than the value I set...

ALTER SYSTEM SET processes=1000 SCOPE=SPFILE;
ALTER SYSTEM SET sessions=1248 SCOPE=SPFILE;

Starting up:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options.
Using parameter settings in server-side spfile /u02/app/oracle/product/11.2.0/db_1/dbs/spfileeng11g02.ora
System parameters with non-default values:
processes = 1000
sessions = 1524

And one more thing is there any recommended formula for calculate sessions in 11g? [ I found one formual from google:
SESSIONS derived: (1.1 * PROCESSES) + 5

View 3 Replies View Related

Sessions Based On SQL ID

Aug 31, 2012

One of our DB server is high on CPU utilization, as per the AWR we found couple of queries are running many times and taking huge CPU, hence we need to know from which user these queries are runnings.find the session information as per the sql id?

View 9 Replies View Related

Inactive Sessions In Database?

Dec 12, 2011

In our databace there are around 300 sessions were in inactive state.

Database is not releas the sessions.

View 2 Replies View Related

INACTIVE Sessions In Oracle?

Apr 22, 2011

our db shows more than 200 INACTIVE Sessions ; and the DBA plans to reboot the db to get rid of these sessions . Can we not KILL these sessions and avoid the reboot ?

View 4 Replies View Related

Possible To Limit Sessions In These Views

Jan 17, 2013

I'm using Toad 11.6, I can see about 156 sessions for the database in the session browser but when I do a select from v$session, only 40 sessions are showing up. I was able to look at all the sessions until yesterday. I tried several views like v$session, v$open_cursor etc, but only 60 sessions show up. I'm connected to the database using same user login yesterday and this morning.. May be this user had some privileges revoked last night??If so is it possible to limit sessions in these views (v$session etc) but it's strange that I can see them in the session browser in Toad. 'm very confident that there are 156 sessions in the database but it's just that I'm not able to see them in the v$session etc views. I need to troubleshoot a session but it is not showing up in any views.

View 9 Replies View Related

Sessions Processes And Transactions

Nov 20, 2012

Oracle database 11g Expression Edition 11.2.0.2

I want to change the the number of processes to 150 (default is 100) I did this by issuing the below command.

alter system set processes=150 scope=spfile;But why that modified value is not being reflected?

select name, value from v$parameter where name in ('sessions','processes','transactions');

NAME                 VALUE
-------------------- -----
processes            100
sessions             172
transactions         189

View 8 Replies View Related

Processes / Sessions Connected To DB After ORA 20 Error?

Dec 28, 2010

Is there any way to tell what processes/sessions were connected to a DB after I get an ORA 20error. As you can see from the query results below, I did hit the max value of processes but I would like to know when it happen and who was connected.

I realize I can up the value, but before I do that I want to see what caused this to happen.

SQL> select * from v$resource_limit where resource_name in ('sessions', 'processes');
RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_AL
LIMIT_VALU
----------
processes 221 1000 1000
1000
sessions 223 1020 1105
1105

View 6 Replies View Related

Security :: Limit Sessions Per User In RAC

May 6, 2011

The following link states

Quote:
SESSIONS_PER_USER
Each instance maintains its own SESSIONS_PER_USER count. If SESSIONS_PER_USER is set to 1 for a user, the user can log on to the database more than once as long as each connection is from a different instance.

[URL].....

Of course the following is not working even when resource_limit is TRUE

ALTER PROFILE DEFAULT LIMIT SESSIONS_PER_USER 2;

How can I restrict a user to have limited sessions say 2 sessions across 4 node cluster? Presently I am checking the sessions logged in using sql+ and no connection pooling of front end etc. is involved.

View 5 Replies View Related

SQL & PL/SQL :: Disconnect User Who Are Blocking Sessions

Apr 11, 2011

I have an assignment, i am suppose to disconnect all the users that are blocking sessions for over three hours. Below is my code which is not working

Create or replace procedure b_user IS
Declare
ssid number;
serial number;
Begin
[code]......

View 7 Replies View Related

Performance Tuning :: Locking Of Two Sessions?

Apr 27, 2012

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.

View 3 Replies View Related

Client Tools :: 2 Sessions For One Connection?

Oct 30, 2010

why are there in the Administration --> --> Session 2 entries for 1 database connection (here with benthic golden).But if I login in a browser, there is only 1 entry for this database connection

View 3 Replies View Related

SQL & PL/SQL :: Number Of Active Sessions With Same Sentence?

Oct 20, 2011

I have a RAC on 9i and I want to know the number of active sessions that it's executing the same sql sentence.I'm using this sql but I'm not sure that this query give me te correct result.

SELECT se.inst_id,se.status,se.SQL_HASH_VALUE,se.USERNAME,substr(sq.SQL_TEXT,1,50) SQL,count(*)
FROM GV$SESSION se, gv$sql sq
WHERE se.STATUS='ACTIVE'
and se.username is not null

[code]...

View 2 Replies View Related

SQL & PL/SQL :: Kill One Users Sessions Using A Procedure?

Mar 1, 2012

I want to create a procedure that kills the sessions of the user TEST.

I have coded the below, but that dont work.

CREATE OR REPLACE PACKAGE BODY SYSTEM.test AS procedure killsessions IS
v_sessions_sql varchar2(40);
--v_sessions_sql2 varchar2(40);

[Code].....

View 4 Replies View Related

How To Kill Invalid Sessions Automatically

Jul 3, 2013

On the oracle 11g, we see too many invalid sessions. Because of which, login is getting restricted. What do i need to do to kill those invalid session periodically, which will avoid of restarting of system.

View 4 Replies View Related

SQL & PL/SQL :: DBMS Job Commit Other Sessions Automatically

Apr 14, 2011

I am calling one procedure through job and after submitting the job i am using commit. problem is when job close the session it commit the code which is inside procedure and as per my understanding in another session also, which i want to avoid .

my procedure is

Create My_proc is
Begin
Insert into my_table values(1,2,3);
End;

And my job proc is

Create my_job_proc is
Begin
dbms_job.submit(jobno,'my_proc;',sysdate,null,null,false);
commit;
End;

now i am calling my job proc is

Declare
Begin
my_job_proc;
End;

Now problem is once it finish, it will insert the data into my_table which i don't want until I call Commit.I already try 'Set autocommit off'

View 9 Replies View Related

Slave Sessions In DBMS-PARALLEL-EXECUTE?

Oct 4, 2012

I need to identify, whether my current session is one of slave sessions scheduled as Oracle scheduler job via DBMS_PARALLEL_EXECUTE or not. I already succeeded using join of dba_scheduler_running_jobs, user_parallel_execute_chunks and v$session, but I feel that this is not optimal approach.

View 1 Replies View Related







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