I would just open two sessions. I would use one to identify the other then run
the code I wanted to monitor in the second.
Depending on exactly what and why you want to monitor the session you might
just be able to turn SQL trace on in the one session and run the target code
and then access the resulting trace file.
Mark Powell
Database Administration
(313) 592-5148
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on behalf
of kunwar singh <krishsingh.111@xxxxxxxxx>
Sent: Wednesday, January 13, 2021 4:42 AM
To: Oleksandr Denysenko <odenysenko@xxxxxxxxx>
Cc: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
Subject: 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@xxxxxxxxx<mailto:odenysenko@xxxxxxxxx>> 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 @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 @foreground_run.sql"
sqlplus -s / as sysdba @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 @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
@foreground_run.sql
oracle 24152 1 0 13:31 pts/3 00:00:00 sqlplus -s as sysdba
@background_run.sql
EXITed from foreground_run.sql
oracle 24152 1 2 13:31 pts/3 00:00:00 sqlplus -s as sysdba
@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@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@TESTDB> select *from v$session where username='TEST_USER';
test_user@TESTDB> <and few others queries like on v$sql , v$sql_plan_monitor
etc >
--
Cheers,
Kunwar
--
Cheers,
Kunwar
