PL/SQL :: Finding Commits In Plsql During Runtime?
Jul 19, 2012
In any application, we will have many commit statements issued in many places. How can we find out as to where all commits have happened during runtime.
Basically in our application, when am trying an operation, am getting the error ORA-01086: savepoint <save point name> never established.
My guess is that there is a commit somewhere because of which system is not able to rollback to that save point.
I'm working on a Java-based web application and we have unit tests that we use to test all our all code that interacts with the database or code that interacts with our DB code. The Spring framework allows us to perform some DML within a transaction before each test and then rollback the changes. For the most part, this works, however when I run the full suite of unit tests, it will randomly commit data to the database causing the rest of the tests to fail.
will Oracle's auditing let me see where this odd-ball commit is occurring? Is there another way for me to see when data is being committed?
This does not appear to be happening on any of the systems we've deployed, however this is a bit unsettling and would like to know why this is occurring so that we can prevent it from happening in production.
When a user session commit he is waiting on 'log file sync' untill LGWR sends the message back to user session after writing log buffer content into redo log file.As far as i understand this is serialize operation(one at a time).
So how come i have 7 ms average 'log file sync' wait time and i can still perform 200 commits per sec ?
update siebel.s_contact set marital_stat_cd = case when (marital_stat_cd = 'Casado') then 'Married' when (marital_stat_cd = 'Solteiro') then 'Single' when (marital_stat_cd = 'Divorciado') then 'Divorced' end
As you can see i forgot the else, so my update is wrong.
I thought i could rollback the update issuing the rollback statement, but when i have issue the rollback, the i query the table to confirm that the update was rollbacked and for my suprise the update is commited.
I didn�t issue the commit statement after the update and i confirmed that the auto-commit feature to worksheets is disabled, so i don�t understand whit the update was commited.
I have been testing out a form using 10.1.2.0.2 on a v10.2.0.1 db and in my local env. the form works correctly i.e. if I make a change and 'post' it and then exit and press NO (when asked to save changes) then it correctly leaves the value in the database as it originally was.
The process works by the user pressing a button in form A (read only form) and this opens form B (using open_mode,session,activate) and the user makes their change(s) in form B (a 'post' command is issued in a When New Rec Inst trigger on a db block when the user navigates to a new record within the same block if it is determined that the block status <> 'QUERY') before returning to form A and pressing 'NO' when prompted to save changes.
However, if I run the same process in the TEST env. using the same executable against the same database then it actually updates the database value.
I have tested this by adding a debug message at the end of form B to retrieve the db value back AFTER having issued a clear_form(no_commit) just for the sake of the test and it still returns me the 'new' i.e. amended value - which is obviously incorrect. From what I can see it would appear that the commit occurs straight after the 'post' has been issued and well before the user even exits the form.
Is this a known bug with the 'post' built-in or could it be that a parameter is set to act in this way (i.e. is there an 'autocommit' setting that is 'ON') within the application server?
We have a fact table t1 in the warehouse which has above 6 million records.There is to be an update like this where t2 has aid+bid as composite primary key. column aid repeats in t1.There's performance problem and we'v been told to break this huge update into pieces with few commits in the middle.
update t1 set t1.aid = (select t2.aid from t2 where t1.bid = t2.bid )
I've tried cursor loop with 3 commits in the middle based on if condition that evaluates on every iteration.
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'
This is a daily batch job which captures end of the day changes from a set of Oracle 10G "Source" tables in (.exp) file and another overnight batch job will read from (.exp) files and Insert into Oracle 10G "Target" Tables. The .exp files are created by Oracle 10G Data Pump utilities.In future we will be migrating Target to Teradata. All the tables will be migrated. No change in table names or structure.
The plan of action to write the later bit of PLSQL (which read from .exp files and loads it into Target tables) into Java so that the programe can be re-used on teradata.
Questions
1) Can Java read the contents with in .exp files and insert into Oracle tables? 2) Can Teradata read the contents with in .exp files and insert into Teradata tables? 3) Will we be achieving any benefit if we execute DDL (Insert) using Java rather than PLSQL. Obvious one being Platform independent.
there is no transformation of data in flight between .exp to Target tables.
I want to load lakhs of records into a table. My problem is when after loading the ¼ of records my process is abend due to the size of my rollback segment area. I don't have an option to increase it. So, Is there any way to go for intermediate commits when I am using the imp or sqlldr utilities to load the entire data without abend?
I have excel file which I am reading through plsql procedure using UTL_FILE utilities, one of the column in the excel has multiple values in the same column, I am getting the values into plsql, but when it is coming to where clause its not working.
Example: in excel the column has : 'ABC','GEH','HGT',LTP'
create or replace procedure abc(temp_col varchar2) . .... .... ... SELECT COLA, COLB, COLC FROM TABLE_TEMP WHERE TCOL IN temp_col;
This is not working, if the column in excel has one value say ('ABC') then the above sql is working, if it has more than one value its not working.
I have upgraded my Operating Syatem from Vista to Win7. I have SQL nav version but is not supported on Win7. So I have installed PLSQL developer. When I try to connect its giving the error connection not open. But when I tried the same credentials with SQLPLUS it's working fine.
we have a program that uses SQLPlus to run PLSQL scripts - this program then checks the return code of SQLPlus to determine whether the script ran successfully. Our PLSQL scripts often call other PLSQL scripts via "@@OtherScript.sql" - one problem we have is that if (due to an engineering mistake) the OtherScript.sql file is missing, SQLPlus throws an SP2 error stating that the file was not found. However, the return code of SQLPlus does not indicate any problem. I have found, by searching the internet, that I can simply add a line "whenever OS error exit failure" to our script which then allows our program to detect the failure... however, the error message is not as informative:
CODESQL*Plus: Release 11.1.0.7.0 - Production on Tue Oct 19 10:28:45 2010 Copyright (c) 1982, 2008, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options [code]....
is it possible to get BOTH types of error messages? With "oserror exit" set it does not tell you which file was not found.. which is useful information. I would like the SQL Plus session to return failure but also print out what file it did not find.
I am trying to implement a Houskeeping program for files generated in 4 different servers. This housekeeping program is run as a batch job and I need to use PLSQL to implement it. The files need to be housekept on the basis of the file creation date.
how I can go about doing this using a PLSQL stored procedure.
At PLSQL level how can we make our program re-startable such that if there is some abort after the commited update program will restart from the last commit checkpoint.
I am create procedure send the message in form bulider10g to Internet email and the procedure is work .
but now I want to modify this procedure , I want to add picture of my country ( logo ) and the original message . why cant display the image in my email ?
I have one PLSQL package that does a join of two tables of remote database instance via the datalink. I just wonder where the most calculation(the join) is done, local machine or remote machine? Is there any best practice to have a better performance for such configuration.
In my PLSQL package ...How do I invoke this class file ?
So far I have done this ...
Declare abc ORA_JAVA.JOBJECT; xyz ORA_JAVA.JOBJECT; xxx VARCHAR2(25); value ORA_JAVA.JOBJECT; BEGIN
[code]...
I am getting an error saying the object type is wrong .... I would like to know how to assign a hardcoded value to the JOBJECT just to test before I continue.
BEGIN dbms_scheduler.Create_schedule(schedule_name => 'RMAN_TICKER_STARTING', repeat_interval => 'FREQ=DAILY;BYHOUR=9; BYMINUTE=15,30,45,59', comments => 'schedule to run daily'); dbms_scheduler.Create_program (program_name => 'TICKER_PROGRAME',
[code]....
It was created successfully, but when I execute, it shows the error message.
BEGIN dbms_scheduler.Run_job ('RMAN_TICKER_JOB', TRUE); END; begin dbms_scheduler.run_job ( 'RMAN_TICKER_JOB',TRUE); end; Error at line 1 ORA-27370: job slave failed to launch a job of type EXECUTABLE ORA-27300: OS system dependent operation:accessing execution agent failed with status: 2
[code]....
But I have the CMD file in the location - "F:FEEDLGRTOOLSfeedlgr.cmd".
I will have to take the Employee Names and create a table structure. Number of employee names can vary from day to day. So, whenever I execute my procedure with Table type, I will have to build the table columns with the employee names.
I have problems with calling procedure directly from webbrowser. I have a procedure test in test_pkg package. I granted execute on it to anonymous , added it to wwv_flow_epg_include_mod_local function and still have error 404.searched this forum and found this: Does listener use wwv_flow_epg_include_mod_local
So I granted to apex_public_user and created synonym for him to my package. And it starts working. But then I wanted to add next proceudre to my package the same way, and change my test procedure and it stop working again. Now I have 404 error again.
Application Express 4.1.1.00.23 ( plus all earlier versions that I've ever used)
When using the wizard to create a Validation of type "PLSQL Function returning Boolean", why is it mandatory to enter a value in the text field "Error Message" on the screen that follows? This message is never used as the message actually displayed comes from a PLSQL return statement.