Re: Multiple sqlplus sessions from a single sqlplus session
Date: Wed, 13 Jan 2021 17:04:54 +0000
Message-ID: <DM6PR01MB592940A35AF3B315A1C42EA2CEA90_at_DM6PR01MB5929.prod.exchangelabs.com>
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_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of kunwar singh <krishsingh.111_at_gmail.com> Sent: Wednesday, January 13, 2021 4:42 AM To: Oleksandr Denysenko <odenysenko_at_gmail.com> Cc: ORACLE-L <oracle-l_at_freelists.org> 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_at_gmail.com<mailto: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
Received on Wed Jan 13 2021 - 18:04:54 CET