Re: Multiple sqlplus sessions from a single sqlplus session

Thanks much Oleksander , Martin for the great suggestions . I am going to try it out

On Mon, Jan 4, 2021 at 6:46 AM Oleksandr Denysenko <odenysenko_at_gmail.com> wrote:

> 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
>
> --
Cheers,
Kunwar

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