Re: Multiple sqlplus sessions from a single sqlplus session

Hi Kunwar,

you have to at least use
   SET DEFINE OFF
before using & in sqlplus

in any case, it will be better to use separate sql script to run commands in "background" ls -l *ground_run.*

    -rw-r--r--. 1 oracle oinstall  52 Jan  4 12:51 background_run.sql
    -rwxr--r--. 1 oracle oinstall  91 Jan  4 12:59 foreground_run.sh
    -rw-r--r--. 1 oracle oinstall 121 Jan  4 13:01 foreground_run.sql

cat background_run.sql

    select count(*) from all_objects,all_objects;     exit

cat foreground_run.sql

    SET DEFINE OFF
    PROMPT STARTing: background_run.sql
    !sqlplus -s / as sysdba _at_background_run.sql &     SET DEFINE ON
    PROMPT STARTed: background_run.sql

    !ps -ef | grep sqlplus | grep ground_run | grep -v grep

    exit

cat foreground_run.sh

    #!/bin/bash

    echo "STARTing: sqlplus -s / as sysdba _at_foreground_run.sql"     sqlplus -s / as sysdba _at_foreground_run.sql     echo "EXITed from foreground_run.sql"

    ps -ef | grep sqlplus | grep ground_run | grep -v grep

./foreground_run.sh

    STARTing: sqlplus -s / as sysdba _at_foreground_run.sql     STARTing: background_run.sql

    STARTed: background_run.sql
    oracle   24148 24147  4 13:31 pts/3    00:00:00 sqlplus -s   as sysdba _at_foreground_run.sql     oracle   24152     1  0 13:31 pts/3    00:00:00 sqlplus -s   as sysdba _at_background_run.sql

    EXITed from foreground_run.sql
    oracle   24152     1  2 13:31 pts/3    00:00:00 sqlplus -s   as sysdba _at_background_run.sql

I would suggest additional script, like background_runner.sh, that will save PID of background sqlplus, so you may later use it for kill -9

Best Regards,

     Oleksandr Denysenko

02.01.2021 2:18, kunwar singh ïèøåò:
> Hi Listers,
> Happy new year!
>
> Small academic question.
>
>
> Is there a way we can run multiple sqlplus sessions from a single sqlplus session.
>
> What I want to be able to do is: Run a separate long-running session from my sqlplus and monitor
> it from the current session.
>
>
> Example:
>
> Session 1 :
> test_user_at_TESTDB> !nohup sqlplus test_user/test_user <<EOF
> select  /* *very long running query* */ <few columns> from big_table ;
>
> EOF
> &
>
> --If the above runs successfully, try to monitor it. Currently, above approach  throws an error 
>  or directly connects to the sqlplus and run the query, but doesn't get control back to Session 1
>
> Session 1 :
> test_user_at_TESTDB> select *from v$session where username='TEST_USER';
> test_user_at_TESTDB> <and few others queries like on v$sql , v$sql_plan_monitor etc >
>
>
> --
> Cheers,
> Kunwar

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