Re: Running SQLs betweem RAC Instance

From: ibodogan <idogan_tech_at_yahoo.com>
Date: Fri, 4 Jan 2008 08:41:19 -0800 (PST)
Message-ID: <e59b8084-9353-4153-9005-6f7434af3785@e4g2000hsg.googlegroups.com>


On Jan 4, 12:30 am, DA Morgan <damor..._at_psoug.org> wrote:
> ibodogan wrote:
> > On Jan 2, 10:46 pm, DA Morgan <damor..._at_psoug.org> wrote:
> >> ibodogan wrote:
> >>> Is there a way to run a SQL in a RAC instance #1 from instance # 2 ?
> >>> thanks,
> >>> i.d.
> >> The question has no meaning. An instance != a database. All instances
> >> are connected to the exact same database.
>
> > Mr. DA,
>
> > since when you get to determine that...
>
> > your answer speaks volumes about how limited your tiny Oracle world
> > is...
>
> > keep teaching whatever "meaningful" to you in your tiny hole and leave
> > the production database admin to people like me...
>
> > See below why this may be needed, let me know if you can not grasp it:
>
> > DECLARE
> > l_sqltext  VARCHAR2(3000);
> > l_rows             PLS_INTEGER;
> > p_indentlen        PLS_INTEGER := 2;
> > p_linelen  PLS_INTEGER := 100;
> > p_linecount        PLS_INTEGER := NULL;
> > l_linestr  VARCHAR2(200);
> > l_strlen   PLS_INTEGER;
> > l_strindex PLS_INTEGER;
> > l_linecount        PLS_INTEGER;
> > l_instid    PLS_INTEGER;
> > BEGIN
>
> >  DBMS_OUTPUT.ENABLE(1000000);
> >  DBMS_OUTPUT.PUT_LINE('ACTIVE SESSIONS & SQL');
>
> >  SELECT instance_number
> >  INTO   l_instid
> >  FROM   v$instance;
>
> >  l_rows := 0;
> >  FOR l_rec IN (SELECT      s.username, TO_CHAR(s.sid) || '-' ||
> > TO_CHAR(s.serial#) AS sidserial,
> >                    s.last_call_et AS elapsed, s.machine || '-' || s.osuser AS
> > machineosuser,
> >                    s.process, p.spid, s.sql_id, s.program, s.inst_id
> >             FROM   gv$session s, gv$process p
> >             WHERE  s.paddr = p.addr AND s.status='ACTIVE' AND s.type !=
> > 'BACKGROUND' AND
> >                    s.username IS NOT NULL AND s.sid != (SELECT sid FROM v$mystat WHERE
> > rownum=1) AND              s.inst_id=p.inst_id)
> >  LOOP
> >    IF l_rows = 0 THEN
> >            DBMS_OUTPUT.PUT_LINE(RPAD('USER', 30) || ' ' || RPAD('SID-SERIAL',
> > 15) || ' ' ||
> >                         RPAD('ELAPSED', 11) || ' ' || RPAD('CLIENT
> > MACHINE - OS USER', 25) || ' ' ||
> >                         RPAD('CLIENT SPID', 11) || ' ' || RPAD('SERVER
> > SPID', 11) || ' ' || RPAD('SQL ID-PROGRAM', 20) );
> >            DBMS_OUTPUT.PUT_LINE (RPAD('~~~~', 30) || ' ' ||  RPAD('~~~~~~~~~~',
> > 15) || ' ' ||
> >                         RPAD('~~~~~~~', 11) || ' ' ||
> > RPAD('~~~~~~~~~~~~~~~~~~~~~~~', 25) || ' ' ||
> >                         RPAD('~~~~~~~~~~~', 11) || ' ' ||
> > RPAD('~~~~~~~~~~~', 11) || ' ' ||
> >                         RPAD('~~~~~~~', 20) );
> >    END IF;
>
> >    l_rows := l_rows + 1;
>
> >    DBMS_OUTPUT.PUT_LINE(RPAD(l_rec.username, 30) || ' ' ||
> > RPAD(l_rec.sidserial, 15) || ' ' ||
> >                         RPAD(l_rec.elapsed, 11) || ' ' ||
> > RPAD(l_rec.machineosuser, 25) || ' ' ||
> >                         RPAD(l_rec.process, 11) || ' ' ||
> > RPAD(l_rec.spid, 11) || ' ' ||
> >                            RPAD(l_rec.sql_id || '-' || l_rec.program, 20) );
>
> >    FOR l_rec2 IN (SELECT sql_text
> >                    FROM gv$sqltext
> >                    WHERE sql_id = l_rec.sql_id AND inst_id = l_rec.inst_id
> >                    ORDER BY piece) LOOP
>
> >            DBMS_OUTPUT.PUT_LINE('   ' || l_rec2.sql_text);
> >    END LOOP;
>
> >    IF l_instid = l_rec.inst_id THEN
> >         FOR l_rec3 IN (SELECT plan_table_output
> >                         FROM table
> > (DBMS_XPLAN.DISPLAY_CURSOR(l_rec.sql_id))    ) LOOP
> >            dbms_output.put_line(l_rec3.plan_table_output);
> >         END LOOP;
> >    ELSE
> > -- here I need to run the package in instance # X, otherwise
> > -- I can't get the execution plan, you got that Mr DA?
> >         DBMS_OUTPUT.PUT_LINE('- Run DBMS_XPLAN.DISPLAY_CURSOR in
> > INSTANCE #: ' || l_rec.inst_id);
> >    END IF;
>
> >    DBMS_OUTPUT.PUT_LINE(' ');
> >  END LOOP;
> >  DBMS_OUTPUT.PUT_LINE('   ' || l_rows || ' active sessions listed.');
>
> > END;
>
> I will be polite, something you were not, and not state what I am
> actually thinking. But you can not do it. It has no meaning. If you
> want to run on a different instance try something really cutting
> edge ... connect to that specific instance. If you don't know how
> to use services then ask.
> --
> Daniel A. Morgan
> Oracle Ace Director & Instructor
> University of Washington
> damor..._at_x.washington.edu (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -
>
> - Show quoted text -

DA,

I thought you were smart and know to shut up when you're wrong. I take it back.

it is apparent that you have no clue what I'm trying to do here but I'm glad that several people got it if you follow the post. so i'm not gonna waste my time trying to explain an Oracle instructor what production DBAs face every day for monitoring. your answer just explains how you so-called experts spin it when you don't know the answer. you just force yourself to respond almost every post out there and when you don't know the answer you just spin it or mock the question. well, you hit very wrong door this time and you got your lesson.

i.d. Received on Fri Jan 04 2008 - 10:41:19 CST

Original text of this message