Re: Running SQLs betweem RAC Instance

From: DA Morgan <damorgan_at_psoug.org>
Date: Thu, 03 Jan 2008 22:30:28 -0800
Message-ID: <1199428211.903417@bubbleator.drizzle.com>

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
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Fri Jan 04 2008 - 00:30:28 CST