Re: flushing cursor on remote instance

From: Laurentiu Oprea <laurentiu.oprea06_at_gmail.com>
Date: Wed, 19 Apr 2023 10:33:04 +0300
Message-ID: <CA+riqSXU5rb=UL_0eUY=w2pB6tgKr9XyZ_jXe4+mzCYd+xaWZQ_at_mail.gmail.com>

Just a crappy first version that is working :)

WITH
    FUNCTION flush_sqlid (

        hv IN NUMBER
    ) RETURN NUMBER IS
    BEGIN

        FOR record IN (
            SELECT
                address,
                hash_value
            FROM
                gv$sqlarea
            WHERE
                sql_id IN (
                    SELECT
                        sql_id
                    FROM
                        mytable
                )
        ) LOOP
        begin
            sys.dbms_shared_pool.purge(record.address
                                       || ','
                                       || record.hash_value, 'C');
           exception
           when others then
           goto end_loop;
        end;

<<end_loop>>
null;

        END LOOP;
RETURN 1;     EXCEPTION

        WHEN OTHERS THEN
            RETURN 1;

    END; SELECT
    *
FROM
    TABLE ( gv$(CURSOR(

        SELECT
            flush_sqlid(1)
        FROM
            v$database

    )) );

În mie., 19 apr. 2023 la 08:09, Laurentiu Oprea <laurentiu.oprea06_at_gmail.com> a scris:

> Thanks a lot Timur for the Idea.
>
> I think is working, I did a rudimentary test and apparently it flushed the
> cursor on all nodes, I`ll refine the code now and most definitely share it
> next days,
>
> În mar., 18 apr. 2023 la 17:52, Timur Akhmadeev <timur.akhmadeev_at_gmail.com>
> a scris:
>
>> I'd try to mix a function in WITH clause (
>> https://oracle-base.com/articles/12c/with-clause-enhancements-12cr1#functions)
>> and calling it via GV$ function (
>> https://oraganism.wordpress.com/2012/05/20/gv_function/)
>> Please share the code if it works for you :-)
>>
>> On Tue, Apr 18, 2023 at 5:28 PM Laurentiu Oprea <
>> laurentiu.oprea06_at_gmail.com> wrote:
>>
>>> Dear all,
>>>
>>> Does anyone know a method to flush a cursor on a remote node in a RAC DB?
>>>
>>> Much appreciated,
>>> Laurentiu.
>>>
>>
>>
>> --
>> Regards
>> Timur Akhmadeev
>>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Apr 19 2023 - 09:33:04 CEST