SQL & PL/SQL :: Missing Records While Performing Parallel Inserts
Jun 29, 2010
I have prepared shell scripts to do the parallel inserts on my DB table (LEGACY_SYSTEM).
There is a trigger (AFTER INSERT ON EACH ROW) associated with the above table. I am calling a package.function inside the trigger to do the required operation and finally it will insert records into my target table (PRICE_CHANGE).
Expectation:
------------
If I insert 10 rows into LEGACY_SYSTEM table, it should do few updates and finally insert 10 rows into PRICE_CHANGE table.
Result:
-------
10 rows got inserted into LEGACY_SYSTEM. All the updates are successful but I could see only 4 rows in PRICE_CHANGE table. If I run it for the second or third time, all the results will be perfect.
Instead of these shell script, if I insert one by one rows manually into LEGACY_SYSTEM table, I am getting all the expected results and the results are consistent. If you look at my scripts below, you will understand the problem better..
DRIVING SCRIPT(test_global.sh):
---------------
#!/bin/sh
./test_script.sh &
./test_script2.sh &
exit;
test_script.sh:
---------------
#!/bin/sh
./test.sh 100052051 10974 &
./test.sh 100052051 8649 &
./test.sh 100052060 10974 &
./test.sh 100052060 8649 &
./test.sh 100052078 10974 &
./test.sh 100052078 8649 &
./test.sh 100052086 10974 &
./test.sh 100052086 8649 &
exit;
test_script2.sh:
---------------
#!/bin/sh
./test.sh 100052051 11698 &
./test.sh 100052051 11777 &
./test.sh 100052060 11698 &
./test.sh 100052060 11777 &
./test.sh 100052078 11698 &
./test.sh 100052078 11777 &
./test.sh 100052086 11698 &
exit;
test.sh:
--------
#!/bin/sh
sqlplus HRUAMGR/HRUAMGR@MALDEV_DW1<<EOF
ALTER SESSION ENABLE PARALLEL DML;
insert into legacy_system (product, item, supp, type, g_ind, d_ind, o_num) values (7896544,$1,$2,'TEST',NULL,NULL,NULL);
commit;
exit;
EOF
I am calling test_global.sh through the UNIX session and all the records got inserted into LEGACY_SYSTEM table and few rows are missing from PRICE_CHANGE table.
If I remove the '&' symbol and execute, the results are perfect. But the requirement is not to remove the '&' symbol. I have been facing this problem for the past 1 month.
View 8 Replies
ADVERTISEMENT
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 1, 2011
Ten thousand (10000) records ,but in the TABLE that has a uniqure sequence of column named seq, find only 9 thousand (9000) records. I want to find out the missing records. The following is my approach, but it is not the best way
1.CREATE TABLE my_public_seq (seq number(10));
2.
DECLARE
vn_seq NUMBER(10);
BEGIN
FOR i IN 1..10000
LOOP
INSERT INTO my_public_seq VALUES(i);
END LOOP;
COMMIT;
END;
3. run sql
SELECT seq FROM my_public_seq
MINUS
SELECT seq FROM my_original_table_that_missing_some_records;
How to get the missing records using only one SQL statement.
View 1 Replies
View Related
Mar 7, 2010
I have two tables A with columns a.key, a.location_code, a.status and a.first_name and table B with cols b.key, b.location_code, b.status and b.first_name.
I want to find the missing records between the two tables and as well check whether each column is populated correctly. That is if u take a record with id 1 check if loc_code is same in both the tables and if they are different, insert the key and first record column and second record column into a new table. And similarly if there is no record wiht that particular id in the second table, insert the record.
For missing records in the sense for records which are present in A but not in B, am using
Select a.key_no, a.loc_code, b.loc_code
from A,B
where a.key_no=b.key_no(+)
and b.key_no IS NULL
But the problem is I need to put some constraints on the B table like b.status='Married'and b.loc_code='CA'. When am using this condition in the above query, it's throwing me error saying cannot use outer join operator in and or or.And I could not figure out how to check for the columns being populated correctly between the two tables and at the same time check for missing ones
View 5 Replies
View Related
Jun 8, 2012
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - ProductionSET DEFINE OFF;
[code]....
10 rows selected.I want the output like as follows, all those missing date i need to carry on the last one's number
NBR_OF_S NBR_OF_C S_DATE
---------- ---------- ---------
34 40 01-MAY-12
27 29 01-APR-12
27 29 01-MAR-12
21 23 01-FEB-12
21 23 01-JAN-12
21 23 01-DEC-11
[code]....
The date value I have created for this sample is monthly, based on the condition the data value I may need to generate weekly also. That's Monthly or weekly either one.
View 9 Replies
View Related
Apr 30, 2012
If we have not set parallel degree for a table then we can ( try to ) force parallel execution on a table using a parallel hint Does this 'parallelism' works on the index search in the query as well?
In which situations non-parallel non-partitioned table but parallel index (degree>2) will facilitate a query?
View 5 Replies
View Related
Apr 1, 2011
are there any possibility to attack oracle databases while performing database backup!
View 3 Replies
View Related
Jul 23, 2013
while try to expdp on network drive. getting below error.how can we perform the expdp on network drive
Network location: \\tsclient\p\expdp
1.)SQL> show user
USER is "SYS"
SQL> create or replace directory exp as '\\tsclient\p\expdp';Directory created.
SQL>Grant read,write on directory exp to system;
2.)expdp system/xxxxx@orcl directory=exp dumpfile=EXP_orcl_072013.dmp logfile=EXP_72013_1.log schemas=('IIMS','CMMN')
Export: Release 11.2.0.2.0 - Production on Tue Jul 23 13:48:07 2013
Copyright © 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Release 11.2.0.2.0 - 64bit Production
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation
View 2 Replies
View Related
Mar 11, 2010
I have a type to table where only few columns are SCD 2. Like address, city, zip.I wrote a code using cursor. But now the requirement is to replace the cursor .how to perform type 2 using Merge in oracle 10g or any better way to perform it without using cursor. Look the code which i have attached with cursor.
CREATE OR REPLACE PROCEDURE Proc_cp_jci_contract_dim_hist
IS
l_fun_ret_value NUMBER;
v_src_id NUMBER;
rec_jci_contract_dim jci_contract_dim%ROWTYPE;
rec_jci_contract_dim_hist jci_contract_dim_hist%ROWTYPE;
err_row VARCHAR2(4000);
ind NUMBER(1) := 1;
[code]....
View 2 Replies
View Related
Oct 16, 2012
Iam usin 11.2.0.3 database on linux 64 bit machine
I have got the report from the users that the dataload operation which they perform in the database is taking normal than the expected time
I rebooted the server and started the database i can see that the database startup is even taking more time
The memory allocated is sufficient and when i tried to perform the logswitch the database simply hangs
alertlog contents after restart
---------------------------------------
minact-scn: useg scan erroring out with error e:12751
Tue Oct 16 11:02:03 2012
minact-scn: useg scan erroring out with error e:12751
Suspending MMON action 'Block Cleanout Optim, Undo Segment Scan' for 82800 seconds
Tue Oct 16 11:12:47 2012
Active Session History (ASH) performed an emergency flush. This may mean that ASH is undersized. If emergency flushes are a recurring issue, you may consider increasing ASH size by setting the value of ASHSIZE to a sufficiently large value. Currently, ASH size is 50331648 bytes. Both ASH size and the total number of emergency flushes since instance startup can be monitored by running the following query:
select total_size,awr_flush_emergency_count from v$ash_info;
Tue Oct 16 11:17:53 2012
Suspending MMON action 'undo usage' for 82800 seconds
Tue Oct 16 11:28:05 2012
Suspending MMON action 'metrics monitoring' for 82800 seconds
View 3 Replies
View Related
Feb 25, 2011
I was just wondering how Oracle manages multiple sessions in a database performing DML. I believe this is related to 'Read Consistency' and I tried to search for the same but could not get any satisfactory online documents.
CASE 1:
user A logs in to a database1
issues select on table A and then inserts 4 rows
user B logs in to databse1
issues select on table A and then inserts 5 rows
issues rollback
user C logs in to a database1
issues select on table A and then inserts 6 rows
issues commit
How many rows can user C see in the table A when he issues select?
CASE 2:
user A logs in to a database1
issues select on table A and then inserts 4 rows
user B logs in to databse1
issues select on table A and then inserts 5 rows
user C logs in to a database1
user B issues rollback
user C issues select on table A and then inserts 6 rows
issues commit
How many rows can user C see in the table A when he issues select?
NOTE: All the users are currently logged in to the same database and none has logged out.
View 6 Replies
View Related
Mar 10, 2013
In my form i have a multi record block which is based on a table in which i am performing insertion, deletion, updation.
My requirement is as below,
While saving a records i want to check distinct value of specific item can not be greater than some value say 2. Then only my records should be saved. I am planning to populate those values in a collection table type and take the distinct values from it. How to populate record values in a collection table type.
View 2 Replies
View Related
Sep 4, 2008
We are facing serious problem while refreshing materialized views using fast refresh option in ORACLE..For the very first time we are performing Complete refresh of data from DB1 to DB2 for few tables.Ongoing we are performing Fast Refresh.Sometimes the fast refresh works fine without any error and sometimes it fails with the below error.
ERROR at line 1:
ORA-32320: REFRESH FAST of "CIR"."C_BO_COMM" unsupported after
cointainer table
PMOPs
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 803
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 860
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 841
ORA-06512: at line 1
let me know when will this error occur during Fast Refresh.
View 4 Replies
View Related
Sep 4, 2009
i have multiple inserts to make in a table that is in an Oracle database...i already try several ways to do it but it always giving erros... how to make multiple inserts at same time.
View 2 Replies
View Related
Dec 18, 2012
We have two databases one localdb with user rakdb and another one remotely remotedb with user rakdb .We need to be in sync with data in one table called om_item, where the users are inserting data on daily basis and the user sends us the insert script everday to run it on local databse to insert the new records in local database.I managed to create a file which records all the inserts into one text file in one directory.Can we have a scheduler to pick this text file from the specified folder and send mail using utl_mail.
CREATE TABLE ITEM (IT_CODE VARCHAR2(12),IT_NAME VARCHAR2(20));
INSERT INTO ITEM VALUES ('A','AAA');
CREATE OR REPLACE DIRECTORY MY_DIR AS 'C:TEMP';
CREATE OR REPLACE PROCEDURE it_status
[Code]..
Procedure created.
EXEC it_status
HOST TYPE c: empaaaa.txt
INSERT INTO ITEM (IT_CODE, ITEM_NAME) VALUES ('A','AAA');
COMMIT;
View 4 Replies
View Related
Apr 1, 2013
I'm on 11.2 DB and need to create an audit table that will be populated by DB triggers of other tables (after Insert,Update and Delete). The triggers will only ever be inserting data into the audit table. I have read that for insert only tables, you should define the 'pctfree' as 0. Is this correct? Do I need to set any other params (like pct_used) for tables only ever being inserted into?
View 2 Replies
View Related
Jan 19, 2011
I want to load 10 millions records from staging table to master table.One logic must be take during the load, the logic is rows already present in master table means, we need to update corresponding rows in master table otherwise rows insert in target table.
I have been using bulk collect and forall method to load data. it shows better performance compare then cursor row by row process. As per oracle doucmentation, we cannot use SELECT statements inside FORALL condition so we could not use logic inside the forall condition.
View 2 Replies
View Related
Aug 15, 2012
I have a data in one table with 6 columns where user may be updating values in all of these 6 columns or he may enter 3 or 4 columns based on that inserts should take place, this is similar to my previous thread , i am using if condition to check column for null if its not null then i will make a insert , but is there any other easier way to do this.
CREATE TABLE ot_inspect_head
(inh_sys NUMBER,i_txn_code VARCHAR2(12),i_no NUMBER,i_ref_txn VARCHAR2(20),i_ref_no NUMBER);
CREATE SEQUENCE inh_sys START WITH 1;
CREATE TABLE ot_inspect_item
(ii_inh_sys NUMBER , ii_pi_sys NUMBER,ii_sys NUMBER,ii_item_cd VARCHAR2(12),
ii_grade VARCHAR2(12),ii_qty number, ii_flex_01 VARCHAR2(12),ii_flex01_qty number,
ii_flex_02 VARCHAR2(12),ii_flex02_qty number,ii_flex_03 VARCHAR2(12),ii_flex03_qty number);
CREATE SEQUENCE ii_sys START WITH 1;
insert into ot_po values ('ss-po',1,ph_sys.nextval);
insert into ot_inspect_head values (inh_sys.nextval,'ss-ins',1,'ss-po',1);
commit;
select * from ot_inspect_item
II_INH_SYS II_PI_SYS II_SYS II_ITE II_GRADE II_QTY II_FLEX_01 II_FLEX01_QTY II_FLEX_02 II_FLEX02_QTY
---------- --------- ------ ------ -------- ------ ---------- ------------- ---------- -------------
2 1 2 HEA100 A 100
--Now if the inspection user issues the update statement , it will delete this row
--from ot_inspect_item and reinserts the values with values based on
--ii_flex_01,ii_flex_02,ii_flex_03
[code]...
View 5 Replies
View Related
Feb 18, 2010
I have a plsql Proc, which accepts a few parameters and inevitably loops through a cursor and runs a bunch of insert statements. With quite a few IF conditions.
Each insert statement has a value which i want to increment by (+1) every time an insert statement is executed in the same loop.. This is for a student housing database and this is for their room preferences so 1 is the first, 2 is there second preference e.t.c.
Please take a look at the code below: in the Insert values() I have put a? Where I want the number to increment from.
There are a lot more inserts which I haven't put below. I hope I have made myself clear as this has been quite difficult to explain. So for example if the 2nd two inserts are run, then I was the first one to insert with a 1 and the second with a 2.
BEGIN
FOR rec IN c1
LOOP
IF c1%FOUND THEN
INSERT INTO table (PK_A, fk_rms_id, application_type, application_person_type) VALUES (NULL, rec.pk_rms_id, app_type, app_person_type) RETURNING PK_APPLICATION_NO INTO x;
[Code] ........
View 2 Replies
View Related
Oct 20, 2011
I am trying to create a procedure that inserts parameters into a table and then returns the number of rows inserted back to calling block. the procedure is compiling fine but is not returning the number of rows inserted. My code is as follows;
HOST VARIABLE DECLARATION
VARIABLE g_CarMasterInsertCount NUMBER;
STORED PROCEDURE
CREATE OR REPLACE PROCEDURE CarMasterInsert_sp (
registration IN VARCHAR2,
model_name IN VARCHAR2,
car_group_name IN VARCHAR2,
date_bought IN DATE,
cost IN NUMBER,
miles_to_date IN NUMBER,
miles_last_service IN NUMBER,
status IN CHAR,
rowsInserted OUT NUMBER)
[code]....
I think im close just that my syntax is off.
View 8 Replies
View Related
Aug 9, 2013
Is there any way I could find the number of inserts happened every day to a particular table.
View 10 Replies
View Related
Apr 7, 2011
this is my problem,
insert into t1
select *
from t2
where condition;
Select * from t2 where condition retrieves in example 1000000 records but I only need to insert into t1 the first 100 records.
Is there anyway to abort/limit the insert into t1 to 100 records?
This can´t be used because only applies the condition to the first 100 rows of t2 and it's not valid.
insert into t1
select *
from t2
where condition
where rowcount<100;
View 16 Replies
View Related
Nov 2, 2012
I'm running 11.2.0I am looking at tuning a sql statement, and the question was brought up as to the max inserts per transactions in 11g, and if it exceeds 1000.I haven't found a solid answer yet, but I thought that 10g was higher than 1000.
My first thought was to implement a commit loop on every 1000 rows, as that is how things were handled in the past.But I found an article that talks about redo logs and performance and how it's a horrible practice to do the commit loop.
What I haven't found is what is the better methodology in doing this?My scenario could encounter inserts as much as 20,000 at a time.
View 9 Replies
View Related
Oct 10, 2013
I am trying to improve a procedure which is looping through a query to make inserts.
FOR P IN (
SELECT O.TYPEID
,o.KEY
,O.ID
,O.NAME
,O.LGNUM
,O.LGNAME
[code]....
View 12 Replies
View Related
Feb 20, 2010
I am running a custom script that creates about 100,000 rows of demo data.
The table I am loading in to is fairly wide (100 columns), and only has about 10,000 rows at the moment.
The script goes really fast for the first 10K rows (100 inserts per second), and then incrementally gets slower. By 20,000 rows it is doing about 1 row per second. At this rate, it will never finish!.
Each insert is a separate statement, using bind variables and wrapped in a single transaction. I've tried dropping the indexes first but it didn't make a difference.
OEM shows it's 100% CPU bottleneck with no other information I can glean.
View 32 Replies
View Related
Oct 29, 2011
Tape streaming is not happening while performing RMAN tape backup. On investigation, you find that it is not because of the incremental backup or the empty file backup and that RMAN is sending data blocks to the tape drive fast enough.What could be a solution to make tape streaming happen during the backup?
A.Configure backup optimization
B.Configure the channel to increase MAXOPENFILES
C.Configure the channel to increase the capacity with the RATE parameter
D.Configure the channel to adjust the tape buffer size with the BLKSIZE option
View 7 Replies
View Related
Aug 21, 2012
I have a table named student_details with columns "NAME","ADDRESS","COURSE" with several rows of data already insertedI have to add one more column "ID" which increments automatically.
I tried to do this using SEQUENCE but no values got inserted for already existing rows in "ID". how to write a script that automatically increments and inserts values for already existing rows also.
View 12 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
Aug 13, 2010
I have a Bash script that counts the rows of a csv file, extracts the fields and makes inserts in a sql file. Then it logs into SqlPlus and calls the insert file. The sql file looks like this:
WHENEVER SQLERROR EXIT
INSERT INTO SCHEMA.TABLE1 (FIELD1, FIELD2, FIELD3)
VALUES (VALUE1, VALUE2, VALUE3);
INSERT INTO SCHEMA.TABLE (FIELD1, FIELD2, FIELD3)
VALUES (VALUE1, VALUE2, VALUE3);
INSERT INTO SCHEMA.TABLE (FIELD1, FIELD2, FIELD3)
VALUES (VALUE1, VALUE2, VALUE3);
[code]....
I rely on "WHENEVER SQLERROR EXIT" for things to go the right path. However sometimes because of the contents of the CVS files (which I can't control) some rows don't get inserted but SqlPlus doesn't see that as an error, doesn't exit and I end up with the wrong number of rows being informed in the second insert.Is there some kind of "if-then-else" construct in Sql? After all the inserts are made, do a "select count (*)" and compare that number to the one informed by the script. If they match, make the final insert and commit; else exit.
View 9 Replies
View Related
Sep 30, 2010
I have a query which has 5 unions, each clause of the union takes 1 hr to run and query results come back in 5 hrs, Is there any way I can make these clause to run in parallel?
View 13 Replies
View Related