Re: How to read parent session data - without forcing a commit in the parent

On 21 okt, 16:27, nis..._at_gmail.com wrote:
> On 21 Oct, 15:17, sybrandb <sybra..._at_gmail.com> wrote:
>
>
>
>
>
> > On 21 okt, 15:26, nis..._at_gmail.com wrote:
>
> > > Hi Folks,
>
> > > I have an interesting problem. Basically, I have an oracle job that I
> > > need to speed up as we are exceeding our allocated time window. The
> > > oracle job consists of a large number of small changes across multiple
> > > 'source' tables and then one of my functions is called to try and
> > > process this source data in the fastest possible time - in order to
> > > create a single huge table from the uncommitted data. If there is an
> > > error in my processing, I simply raise an exception that causes a
> > > rollback of the source data. So far so good.
>
> > > Now here's the problem. The 'huge' table that I am building consists
> > > of 2 distinct sets of SELECT queries on the uncomitted source data
> > > tables. There is no problem in doing this serially - other than it
> > > takes us 4 hours which is too long. To speed things up, I wanted to
> > > run the 2 distinct SELECT queries in parallel - and therein lies the
> > > problem. I cannot get parallel SELECT functions to run without them
> > > forcing the source data to become committed. Is there any way to get
> > > around this?? Below is a cut down version of the top level function
> > > that I used to kick off the parallel functions that build the huge
> > > table from the source tables. It works - but we cannot rollback
> > > because the parallel functions force the source data to become
> > > comitted!!! is there no way for a child session to have access to its
> > > parents uncomitted data?
>
> > > Thanks folks..
> > > Nis.
>
> > > PROCEDURE testParallel
> > >   IS
>
> > >     p_ba varchar2(100) :='CSR';
> > >     p_no number :=100;
>
> > >     v_command1 varchar2(200):= 'nis_test_parallel.func1(' || '''' ||
> > > '''' || p_ba || '''' || '''' || ',' || p_no || ')';
> > >     v_command2 varchar2(200):= 'nis_test_parallel.func2(' || '''' ||
> > > '''' || p_ba || '''' || '''' || ',' || p_no || ')';
>
> > >   BEGIN
>
> > >     -- When this function is called, then at this point we have got
> > > uncomitted data in tables. These tables are
> > >     -- need to be read by
>
> > >     -- The start_background_job command uses the
> > > dbms_scheduler.create_job command to kick off the thread
> > >     start_background_job( 'BUILD_TABLE_PART1',
> > >                           v_command1,
> > >                           'Background Job to build part1 of the
> > > results table');
>
> > >     start_background_job( 'BUILD_TABLE_PART2',
> > >                           v_command2,
> > >                           'Background Job to build part2 of the
> > > results table');
>
> > >   END testParallel;
>
> > Evidently you are trying to repair a piece of shit by creating another
> > piece of shit, aka symptom fighting, instead of resolving the issue.
> > Is that going to work? NO.
> > Your post smells you need to sit down, and try to LEARN sql, and try
> > to LEARN to program, instead of trying to hack yourself out.
> > Eventually this will result in cardiac surgery due to living
> > dangerously and on high stress levels for many years.
> > Your post has 'DISASTER AHEAD' inscribed all over it.
> > Because: WHY ON EARTH you are creating jobs on the fly in a procedure,
> > which has uncommitted data?
> > You are executing DDL, so you will commit!
> > This 'process' needs to be re-architected, SOON.
> > And hopefully by someone who does know what he is doing.
>
> > --
> > Sybrand Bakker
> > Senior Oracle DBA
>
> Mr Sybrand - I know very well what i'm doing, and very well what
> questions i'm asking. Stop ranting like a lunatic - I see that you
> have a habit of doing just that when other people have posted
> questions before. If you can't be useful member of this community - go
> and join a cult and self destruct yourself (preferably soon). Good
> luck!- Tekst uit oorspronkelijk bericht niet weergeven -
>
> - Tekst uit oorspronkelijk bericht weergeven -

Apparently you don't know what you are doing, and I'm exposing you as a fraud, and this is why reply so hurt and insulting. There have been many similar idiots like you in the more than 10 years I have been responding to questions here. You have quite some nerve to state I'm not an useful member of this community.
I would love to see you destruct yourself soon. You deserve it. Because YOU are the lunatic!

--
Sybrand Bakker
Senior Oracle DBA