Automation task - how to retrieve SID of active session

Follow / 13 Sep 2013 at 10:23am

Hello.

I would like to make automation task that export DDL to specific directory. I would like to run it against 4 databases;

I tried to use variables %ACTIVESESSIONDB% but it return the same value as  database for which I build script (even if I run it with option - Run with connections...)

Have you solved similar problem ?

I make it on Toad For Oracle 12 64bit.

Replies

  • Follow / 13 Sep 2013 at 11:41am
    Here’s a script containing a technique that I use – it creates a script titled sid_name_parms.txt:
     
    set term off
    COL sid NOPRINT new_value sid
    SELECT instance_name sid from v$instance;
    set term on
     
    set trimout on
    set trimspool on
    set linesize 256
    set pagesize 999
    set feedback off
     
    col name   format a40   heading 'Name'
    col value  format a132  heading 'Value'
     
    spool &sid._parms.txt
     
    select NAME, VALUE
    from v$parameter
    where value not like '%\%'
      and value not like '%/%'
      and name not like 'log_archive_dest%'
    order by name;
     
    spool off
     
  • Follow / 13 Sep 2013 at 5:31pm
    I was going to say right-click on the action and create a parameter file then build out your list of comma-delimited connection names but a quick glance at the code shows that export ddl doesn't support the connection key value in the parameter file.
     
    It's not immediately apparent to me why it doesn't.
     
    Some of them do. Execute script, for instance.
     
    Maybe on some actions which have a lot of version specific parameters we made the assumption that it wouldn't likely work across databases. But even in that case it would be of value for the same database but different schemas/connections.  I think there's room for us to build those out more in time.
     
    From: przemyslaw.roznowski [mailto:bounce-przemyslawroznowski@toadworld.com]
    Sent: Friday, September 13, 2013 5:24 AM
    To: toadoracle@toadworld.com
    Subject: [Toad for Oracle - Discussion Forum] Automation task - how to retrieve SID of active session
     
    Thread created by przemyslaw.roznowski

    Hello.

    I would like to make automation task that export DDL to specific directory. I would like to run it against 4 databases;

    I tried to use variables %ACTIVESESSIONDB% but it return the same value as  database for which I build script (even if I run it with option - Run with connections...)

    Have you solved similar problem ?

    I make it on Toad For Oracle 12 64bit.

    To reply, please reply-all to this email.

    Stop receiving emails on this subject.
    Or Unsubscribe from Toad for Oracle - General notifications altogether.
    Toad for Oracle - Discussion Forum

    Flag this post as spam/abuse.


    Image removed by sender.
  • Follow / 16 Sep 2013 at 7:27am

    >>But even in that case it would be of value for the same database

    >>but different schemas/connections.  

    I need it for example to compare code from 4 databases.

    To compare I use WinMerge so  I need to export source code of packages and compare it.

    So for me it will be big help to have ability to parametrize connection string in parameter file

    I wish I will be in TOAD in future (I hope in the near future)javascript:void(0);

  • Follow / 17 Sep 2013 at 7:31pm

    Here is how to retrieve several values, available to any user, connected to database, regarding DB version:

    SELECT user,

         (SELECT sys_context('USERENV', 'INSTANCE') FROM dual) || ' ' ||

         (SELECT sys_context('USERENV', 'INSTANCE_NAME') FROM dual) instance,

          dbms_debug_jdwp.current_session_id sid,

          dbms_debug_jdwp.current_session_serial serial#,

         (SELECT sys_context('USERENV', 'SESSIONID') FROM dual) AUDSID

    FROM dual;

    USER                           INSTANCE     SID        SERIAL#    AUDSID
    ------------------------------ ------------ ---------- ---------- ------------
    SYS                            1 xe         221        137        4294967295

    Brg
    Damir Vadas