Home » SQL & PL/SQL » Client Tools » To run a query in background in toad or sqlplus (ORACLE 10G,Version : 10.2.0.4.0)
To run a query in background in toad or sqlplus [message #492950] Thu, 03 February 2011 21:57 Go to next message
good
Messages: 27
Registered: July 2000
Junior Member
Hi,

Thanks to all for your effort to solve our problem.

The issue is that i do have a set of monthly process, where i need to run query one after the other. Some query will take one hours time to complete but some will take 10 hours or more to execute. My problem is that we have wait or machine shouldn't hang or log off during that time ...

I am executing the query in TOAD and windows SQL*Plus. Query can run Parallely at a time.

Is the there anyway to run the query in the background like what we do in the unix nohup command in toad?

Thanks
Good.

Re: To run a query in background in toad or sqlplus [message #492952 is a reply to message #492950] Thu, 03 February 2011 22:13 Go to previous messageGo to next message
BlackSwan
Messages: 26183
Registered: January 2009
Location: SoCal
Senior Member
>can run Parallely at a time.
open multiple windows/session & run single query in each.
Be warned that resource depletion may occur & bog down everything.

If you want to run queries serially just place the SQL statements in a single file; one after another.
Re: To run a query in background in toad or sqlplus [message #492953 is a reply to message #492952] Thu, 03 February 2011 22:23 Go to previous messageGo to next message
good
Messages: 27
Registered: July 2000
Junior Member
BlackSwan wrote on Thu, 03 February 2011 22:13
>can run Parallely at a time.
open multiple windows/session & run single query in each.
Be warned that resource depletion may occur & bog down everything.

If you want to run queries serially just place the SQL statements in a single file; one after another.



Thanks for your quick replay. I am doing it like that way right now, but my question is that if my get restart while query is executing our work is get lost to avoid that how to execute in background?

We have a command in unix(nohup)which is used execute the shell script in background after the session window is close also, like that do we have anything in window or toad or sqlplus ?

Thanks
Re: To run a query in background in toad or sqlplus [message #492954 is a reply to message #492953] Thu, 03 February 2011 22:28 Go to previous messageGo to next message
BlackSwan
Messages: 26183
Registered: January 2009
Location: SoCal
Senior Member
GUI & background are mutually exclusive; even nonsensical.

you could create a script/batch file then run as batch job utilizing the "AT" command.
Re: To run a query in background in toad or sqlplus [message #492956 is a reply to message #492954] Thu, 03 February 2011 22:39 Go to previous messageGo to next message
good
Messages: 27
Registered: July 2000
Junior Member
BlackSwan wrote on Thu, 03 February 2011 22:28
GUI & background are mutually exclusive; even nonsensical.

you could create a script/batch file then run as batch job utilizing the "AT" command.


Thanks. How to execute the sqlplus and execute the query, do we want create a .bat file or .sh script to use AT command in window.

Can you provide me some example?

Thanks.
Re: To run a query in background in toad or sqlplus [message #492958 is a reply to message #492956] Thu, 03 February 2011 22:47 Go to previous messageGo to next message
BlackSwan
Messages: 26183
Registered: January 2009
Location: SoCal
Senior Member
is SEARCH & GOOGLE broken for you?

http://www.orafaq.com/wiki/Scripts#MS-Windows_CMD_and_DOS_Batch_Scripts
Re: To run a query in background in toad or sqlplus [message #492962 is a reply to message #492950] Thu, 03 February 2011 23:23 Go to previous messageGo to next message
good
Messages: 27
Registered: July 2000
Junior Member
AS you said I have tried the AT command in my dos prompt, it shows "Invalid command"

What I do?
1. I have created the batch file "test.bat" in "c:\test" directory. The batch file contain,
dir c:\


2. In the command prompt typed,

at 10.45 "c:\test\test.bat" 


Press enter, It shows "Invalid command" and display AT help screen.

Could you please help me to resolve it where i am worng?

thanks.


Re: To run a query in background in toad or sqlplus [message #492965 is a reply to message #492950] Thu, 03 February 2011 23:25 Go to previous messageGo to next message
Michel Cadot
Messages: 65833
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It depends on what your script does, in addition to BlackSwan answer, you can also split your script in independant parts and create Oracle jobs that execute each part in parallel.

Regards
Michel
Re: To run a query in background in toad or sqlplus [message #492970 is a reply to message #492965] Thu, 03 February 2011 23:34 Go to previous messageGo to next message
good
Messages: 27
Registered: July 2000
Junior Member
Michel Cadot wrote on Thu, 03 February 2011 23:25
It depends on what your script does, in addition to BlackSwan answer, you can also split your script in independant parts and create Oracle jobs that execute each part in parallel.

Regards
Michel


thanks michel.

I do have a multiple script only, but if my machine goes off also the queries in the script need to be executed without break in it. I was running the script before script get completed the system goes off or some reason i need to restart the system, the script get terminatted. so that only we to run the script in the background even the that session window close.

I have tried the command AT in the windows using the batch file to execute. The batch file contain "Dir c:\" just the directory to be display, but it give the "invalid Command" and display the AT help screen. what to do?
Re: To run a query in background in toad or sqlplus [message #493095 is a reply to message #492970] Fri, 04 February 2011 16:09 Go to previous messageGo to next message
ThomasG
Messages: 3208
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Just READ the AT help screen. It should tell you the format is hours:minutes, not hours.minutes right there.
Re: To run a query in background in toad or sqlplus [message #493137 is a reply to message #493095] Sat, 05 February 2011 07:12 Go to previous messageGo to next message
good
Messages: 27
Registered: July 2000
Junior Member
ThomasG wrote on Fri, 04 February 2011 16:09
Just READ the AT help screen. It should tell you the format is hours:minutes, not hours.minutes right there.



Thanks. Let me try and let you know.
Re: To run a query in background in toad or sqlplus [message #493226 is a reply to message #493095] Sun, 06 February 2011 22:29 Go to previous message
good
Messages: 27
Registered: July 2000
Junior Member
ThomasG wrote on Fri, 04 February 2011 16:09
Just READ the AT help screen. It should tell you the format is hours:minutes, not hours.minutes right there.



thanks it works with dir command in batch file. let me try with sqlplus queries.
Previous Topic: SQL Developer migration process
Next Topic: Set up the attributes of SQL PLUS permanently.
Goto Forum:
  


Current Time: Tue Oct 16 03:41:02 CDT 2018