Re: Running SQLs betweem RAC Instance

From: ibodogan <idogan_tech_at_yahoo.com>
Date: Thu, 3 Jan 2008 08:56:47 -0800 (PST)
Message-ID: <ad3d634a-9e8b-492e-98ea-d6a107442937@l1g2000hsa.googlegroups.com>

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; Received on Thu Jan 03 2008 - 10:56:47 CST