SQL & PL/SQL :: How Exactly Oracle Updates Rows Of A Table

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


ADVERTISEMENT

Updates In Audit Table

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

SQL & PL/SQL :: When Oracle Updates SessionID?

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

Security :: Oracle Critical Patch Updates?

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

Perform Regular Updates On Several Oracle Tables

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

PL/SQL :: Track Table Data Changes (inserts / Updates / Deletes) Including Os_user

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

Server Utilities :: Pre Requisite For Oracle Critical Patch Updates

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

Update Statement Which Updates User IDs In One Table With User IDs In Another Table?

Jun 17, 2009

I am trying to write an update statement which updates the User IDs in one table with the User IDs in another table. However I need to update statement to ignore any duplicates that are in the tables.

View 4 Replies View Related

Application Express :: What Would Cause Oracle To Insert Duplicate Rows Into A Table

May 2, 2013

What would cause Oracle to insert duplicate rows into a table? Could a join of two tables in the initial query assigned to an application page cause ORacle to insert an extra row into a table when an update to data value occurs? I have no insert triggers and no foreign keys assigned to the table. I am not sure what would cause Oracle to assume that an insert of a row must occur. I want to prevent that insert.

View 9 Replies View Related

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

SQL & PL/SQL :: Count Of Updates By Week

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

When Recovery Catalog Updates Occur

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

SQL & PL/SQL :: %ROWCOUNT Returning Incorrect Value For Updates?

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

SQL & PL/SQL :: Cursor For Loop Updates Only One Record?

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

Application Express :: Tabular Form With Updates In 4.1

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

Application Express :: Cross Form Updates For Searched Record?

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

PL/SQL :: Stored Procedure Taking Long Time When Updates In Loop

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

SQL & PL/SQL :: Primary Constraint On Table Affecting Procedure To Insert Rest Of Rows In Table?

Jun 12, 2012

primary 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]...

View 2 Replies View Related

SQL & PL/SQL :: Load 10 Million Rows In Table From Another Table Based On Multiple Joins

Sep 24, 2010

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

Delete 4 Millions Of Rows From A Table Having 25 Millions Of Rows?

Dec 14, 2007

I joined the forum just today, i need some tips on deleting the millions of rows from a huge table having 25 millions of rows.

View 4 Replies View Related

PL/SQL :: How To Transpose A Table From Rows To Columns Or Columns Into Rows

Aug 22, 2012

what are the collections available in Oracle Plsql, what are concepts of collection.

How to Transpose a Table from rows to columns or columns into rows.

DDL and DML concepts.

What is the concepts of statistics in Oracle Plsql.

View 4 Replies View Related

SQL & PL/SQL :: Rows From Each Table In DB

May 5, 2011

Scenario like this , I have a dB which contain 100 tables, i want to find out each table contain how many rows excluding

system tables ..

how can i write query for this.

View 2 Replies View Related

Purging Table Which Is Having 98 M Rows

Dec 3, 2012

I want to purge a table which is having more then 98M rows...here are the details...

Purge Process I followed
---------------------------------------------
Step 1. Created backup table from Main table with required data only
create table abc_98M_purge as
select * from abc_98M where trunc(tran_date)>='01-jul-2011'
-> table created with 5325411 rows

Step 2. truncate table abc_98M

Step 3. inserted all 5325411 rows back to abc_98M from abc_98M_purge using below procedure

DECLARE
TYPE ARRROWID IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
tbrows ARRROWID;
row PLS_INTEGER;
cursor cur_insert is select rowid from abc_98M_purge order by rowid;
BEGIN
open cur_insert;
loop
[code]....

View 4 Replies View Related

SQL & PL/SQL :: How To Delete Rows From Table

Oct 19, 2011

I am using the below proc to delete some records

1)select client_id,count(*) from TCLIENT_NOTIFICATION_PACK where client_id=1620560178 group by client_id order by 2 desc;

client_id count(*)
----------- ---------
16205601785128

2)select client_id, count(*) from TCLIENT_NOTIFICATION_PACK
where client_id=1620560178
group by client_id
having count(*) > 40
order by 2 desc
client_id count(*)
----------- ---------
16205601785128

3) select client_id,clnt_notification_pack_tid
-- bulk collect into v_client_id,v_notif_tid
from (select clnt_notification_pack_tid,
client_id,
clnt_notification_pack_typ_tid,
crte_dt,

[code]....

4) Iam using the below proc to delete the rows from table, except the 4 rows returned above

declare
v_clnt_notification_pack_tid TCLIENT_NOTIFICATION_PACK.CLNT_NOTIFICATION_PACK_TID%type;
tYPE t_client_id is table of TCLIENT_NOTIFICATION_PACK.client_id%type;
tYPE t_notif_tid is table of TCLIENT_NOTIFICATION_PACK.clnt_notification_pack_tid%type;
v_client_id t_client_id;
v_notif_tid t_notif_tid;

[code]....

5) After running this procedure, i shud see 5124 records, but i see zero records.

View 23 Replies View Related

SQL & PL/SQL :: How To Display All The Rows In A Table

Sep 8, 2010

I am bit confused to display all the rows in a column using stored procedure like

In sqlserver:
create procedure test
as
begin
select * from table;
end
in sqlserver it is working perfectly.

In oracle:
create or replace procedure test
is
begin
select * from table;
end;

But in oracle i am getting an error like select * into something like that.I understand the error. i.e. i need to move the column values into variables.if it a single row then there will be no problem.i want to display all the rows in the table at one time.i can not use cursors.

View 3 Replies View Related

SQL & PL/SQL :: Rows Of A Table Updated

Jul 17, 2012

write a query find which rows of a table is updated on 2 days before?

(OR)

In table there r so many rows write a query which two rows r updated last two day before?

View 4 Replies View Related

SQL & PL/SQL :: Delete Even Rows Of A Table

Oct 25, 2006

How to delete even rows of a table or rather alternate rows of a table.

View 31 Replies View Related

SQL & PL/SQL :: How To Delete 1% Of Rows From A Table

Jun 13, 2011

I have a table, in that i have "n" no of rows from that i want to delete 1% of rows from that table

View 26 Replies View Related

PL/SQL :: Reduce Rows In A Big Table

Dec 19, 2012

I have one very big table , says employee table, I need to keep less rows to do this

1. create table employee_backup as select * from employee

2. truncate table employee

3. insert into employee select * from employee_backup where rownum < 1000

is #3 sript is OK? if I only want 1000 rows ?

View 5 Replies View Related

PL/SQL :: Fetch 1 Lac Rows From Table

Dec 15, 2012

OS : widows 2003

Oracle 9i release 2

I need to Fetch 1 lakh rows from table (all rows) in fastest way.

How to do it?

View 6 Replies View Related







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