Home » SQL & PL/SQL » Client Tools » sqlplus timeout
sqlplus timeout [message #265081] Wed, 05 September 2007 06:35 Go to next message
coolbalaga
Messages: 24
Registered: September 2006
Junior Member
Hello,

Need your help.

Is there something like a timeout tunable parameter that can be set for the sqlplus operation ?

like if suppose I am executing the sqlplus "select * from .." operation and if it taking a more time than the timeout period, then it should abort instead of the hang.

Thanks in advance.
Re: sqlplus timeout [message #265104 is a reply to message #265081] Wed, 05 September 2007 07:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Not in SQL*Plus but you can create a profile with limited and assign it to your user.
Then if something exceeds a resource limit, it is stopped (and the session killed).

Regards
Michel
Re: sqlplus timeout [message #265261 is a reply to message #265104] Wed, 05 September 2007 22:58 Go to previous messageGo to next message
coolbalaga
Messages: 24
Registered: September 2006
Junior Member
Thanks for the response.

what is the max time the sqlplus operation should take ?

Any docs are welcome on this.
Re: sqlplus timeout [message #265263 is a reply to message #265081] Wed, 05 September 2007 23:01 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>what is the max time the sqlplus operation should take ?
It depends upon actual SQL, how many rows must be scanned & how many rows being returned (plus how much/how little hardware that can be applied to the query)
Re: sqlplus timeout [message #266129 is a reply to message #265263] Sun, 09 September 2007 23:04 Go to previous messageGo to next message
coolbalaga
Messages: 24
Registered: September 2006
Junior Member
then how to identify if the sqlplus is in the hung state ....?
Re: sqlplus timeout [message #266130 is a reply to message #265081] Sun, 09 September 2007 23:16 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>then how to identify if the sqlplus is in the hung state ....?
If you know the SID of the "hung" session, then you can repeatedly query V$SESS_IO & see if the number of I/O operations are increasing.
If any metric reported by V$SESS_IO is increasing, progress is being made.
Re: sqlplus timeout [message #266152 is a reply to message #266129] Mon, 10 September 2007 00:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can also query v$session_wait to see if it is not waiting on something or activate a sql/10046 trace to follow the execution.

Regards
Michel
Re: sqlplus timeout [message #266320 is a reply to message #265081] Mon, 10 September 2007 09:05 Go to previous messageGo to next message
KCee
Messages: 16
Registered: October 2006
Junior Member
You can use other software like 'perl'.
In perl you can use function 'alarm()' to kill a command within a defined time. Works nice.

Re: sqlplus timeout [message #266321 is a reply to message #266320] Mon, 10 September 2007 09:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
How do you use this with SQL*Plus?

Regards
Michel
Re: sqlplus timeout [message #266324 is a reply to message #265081] Mon, 10 September 2007 09:35 Go to previous messageGo to next message
KCee
Messages: 16
Registered: October 2006
Junior Member
  my ( $l_cmd, $l_line, $l_timeout );

  $l_cmd     = "sqlplus /nolog \@pwd2.sql 2>&1";
  $l_timeout = 5;

  my $pid = 0;
  eval
  {
    local $SIG{ALRM} = sub 
    {
      printf "\n%%'sqlplus' Action took more than '$l_timeout' seconds !\n";
      printf "($l_cmd); pid=$pid\n\n";
      kill( 15, $pid )  if $pid > 1;
      exit 1;
    };
    alarm( $l_timeout );
    $pid = open( PH, "$l_cmd |" ) || printf( "Could not execute '$l_cmd': $!\n" );
    while ( my $line = <PH> )
    {
      push @output, $line;
    }
    alarm( 0 );
  };
  print "pid = $pid\n\n";
  eval { alarm(0) };  # The alarm reset must be outside the eval{}.
}

[Updated on: Mon, 10 September 2007 10:47] by Moderator

Report message to a moderator

Re: sqlplus timeout [message #266338 is a reply to message #266324] Mon, 10 September 2007 10:45 Go to previous message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You don't interrupt the operation, you kill the program. And you no more have an interactive session, just batch.
Maybe it is sufficient for OP.

In addition,
Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.

Regards
Michel

[Updated on: Mon, 10 September 2007 10:48]

Report message to a moderator

Previous Topic: Loading data from Excel to the oracle database
Next Topic: sql Loader in Toad
Goto Forum:
  


Current Time: Wed Dec 07 22:42:24 CST 2016

Total time taken to generate the page: 0.23657 seconds