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
ADVERTISEMENT
Oct 16, 2012
Is there a way to perform a pattern check on a value ?
For example: 654321HD9
The pattern is 6 numbers, followed by 2 letters, followed by 1 number. The data type for the attribute is a string.
Examples of right or wrong
654321HD9 - correct value
654321HD - wrong value
654321111 - wrong value
HD1111111 - wong value
The pattern has to be as i mentioned above (6 number, 2 letters, 1 number) otherwise its wrong. My pattern does not cover all cases.
select REGEXP_SUBSTR('654321HD9', '[0-9]+[A-Za-z]+[0-9]+') from dual;
View 6 Replies
View Related
May 18, 2010
I want to maintain the logical backup of all the application tables At regular intervals. Which option would i use?
View 1 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
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
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
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
Apr 13, 2013
What is the use of Regular Expression?
View 1 Replies
View Related
Oct 14, 2013
I would like to write a select that would return all places in DB that are commiting transaction.
E.g. package for testing:
CREATE OR REPLACE PACKAGE test.TEST_COMMIT AS
PROCEDURE THE_ONLY_COMMIT_IN_DB ;
END TEST_COMMIT;--not a match
CREATE OR REPLACE PACKAGE BODY test.TEST_COMMIT AS
[code]....
The select should return 4 rows with --ok.
SELECT *
FROM ALL_SOURCE ASO
WHERE
REGEXP_LIKE(ASO.TEXT,'commit(s*);','i' )
AND name = 'TEST_COMMIT'
View 18 Replies
View Related
Oct 14, 2013
code to extract email id (full email id) from a string/text using Oracle Regular Expressions.
Example -
"This is my String with email abc.efg@hij.com to test" O/p - abc.efg@hij.com
View 9 Replies
View Related
Dec 2, 2010
Is it possible to perform any operation using oracle like addition and division in csv file before loading data in oracle. and after the operation changes must save.
Is it possible or not.
View 2 Replies
View Related
Jul 30, 2012
i want to perform full export + import of an oracle 11g database as fast as possible. i was thinking to perform the exp+imp on the same command.in exp i can perform something like this :
mknod /oracle/migration/exp_pipe p
exp '/ AS SYSDBA' file= /oracle/migration/exp_pipe full=y | imp system/***@oracle_db file= /oracle/migration/exp_pipe full=y
i know that i can do both action in impdp when using a dblink, but the problem is that some objects in the database cannot be copied via a dblink. the question is if there's a corresponding datapump command to the old exp+imp command i presented.
View 5 Replies
View Related
Apr 15, 2013
I am new in RAC scenario though i have some system to look after. I want to know, like single instance there is alert log,trace log where we should investigate to check if there is any problem. For RAC system which are the logs that i can find the monitor the incident or regular health checkup under grid and oracle user?
View 3 Replies
View Related
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
Sep 3, 2012
I came across an implementation where data from DB2 tables are moved to Oracle tables, for BI solutioning, using some oracle procedures called from MS SQL DTS packages which are scheduled jobs.Just being curious, can this be done using OWB or ODI rather than the above detour. I suppose there are some changes being done in those procedures before the data is being loaded into Oracle tables, can't this be done using OWB/ODI? Can it be scheduled too as jobs using OWB/ODI?
View 1 Replies
View Related
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
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
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
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
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
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
Apr 22, 2009
How to use a table type variable with a regular query? For example, in my PL/SQL proc I have a table of employee names.
v_emp_tbl; --contains 'John','Sally','Ted'
Then I want to get names in a table called tbl_employees which are not in the pl/sql table. So if the table contains 'John','Sally','Ted',and 'Don' then I want to see 'Don'.
Not sure how to do this. Essentially I want something like:
Select emp_name into v_single_emp
from tbl_employees where emp_name NOT IN v_emp_tbl;
I know I can loop through the pl/sql table but I wanted to see if there is some other way.
View 2 Replies
View Related
Jan 21, 2011
I have a following table,
CREATE TABLE checkdata
( col1 VARCHAR2(2000)
);
INSERT
INTO checkdata VALUES
[code]......
I need output as ,
INT8144925446-20110118T123723Z
INT8144925448-20110118T123730Z
How do i use Substr and Regular expression here ?
View 11 Replies
View Related
Jul 16, 2012
I am needing to select GUIDS from a table and for this, I need the Regular Expression. My Perl is not good and not good Regular Expression. My database is Oracle 11.2.0.2.0 and the Operating System of the Machine is Linux (Oracle Version 6).
View 4 Replies
View Related
Jun 6, 2012
Sample Data:
SELECT 'A/BCD/CCE/DFFFF' test from dual
Expected Output:
SELECT 'A' A,
'BCD' B,
'CCE' C,
'DFFFF' D
FROM DUAL;
View 6 Replies
View Related
Mar 2, 2011
I have this remote database A and database B. DB A has 10 views and DB B has 10 tables. I have to pull out data from views of DB A and load into tables of DB B at regular intervals. How do I do this job?
View 3 Replies
View Related
Nov 8, 2012
I'm trying to match all sentences that contain words starting with given search tokens at least once. For example: if the given search token words are one and two then only sentences like "one plus one is two" should match. And should not match sentences like "one plus three is four". I was able to come up with this but I need a AND condition which I'm unable to get it right.
select count(*) from dual where regexp_like('one plus one is two', '(^|s)one|three', 'i');
Currently this gives a count of 1. But needs to give a count of 0 when the regexp is fixed.
search words : one two
one is less than two -> match
two is greater than one -> match
onetwo is union of two numbers -> match
onetwo is union of 2 numbers -> not a match as 'two' is not at the beginning of a word
one is less than three -> not a match as two is not present.
View 7 Replies
View Related
