Stupid Database Tricks – Shutdown the server from a stored procedure

From the Database Geek.

I’m not even going to try to explain why I wrote this. Let’s just say that it comes in handy on occasion.

What this very simple procedure does is use DBMS_SCHEDULER to execute a shutdown command in windows. This could easily be adapted to Unix and/or Linux.

Use at your own risk. By default you get a 30 or 60 second delay (depending on windows version, I think) before the shutdown/reboot.

If you pass FALSE in to the procedure, or don’t pass anything, the server will shutdown. If you pass in a TRUE, the server will reboot.

CREATE OR REPLACE PROCEDURE shutdown_srv(
  p_reboot IN BOOLEAN DEFAULT FALSE
)
AS
BEGIN

  IF p_reboot
  THEN
    DBMS_SCHEDULER.CREATE_JOB(
              job_name=>'REBOOT',
              job_type=>'executable',
              job_action=>'shutdown /r',
              enabled=>TRUE
    );
  ELSE
    DBMS_SCHEDULER.CREATE_JOB(
              job_name=>'REBOOT',
              job_type=>'executable',
              job_action=>'shutdown /s',
              enabled=>TRUE
    );

  END IF;

  DBMS_SCHEDULER.ENABLE('REBOOT');

END;
/

Of course, the scheduler will need to be enabled. You can start it by running services.msc and changing the scheduler from disabled to manual (or automatic) and then starting the service.

LewisC

Technorati : , ,

You can follow any responses to this entry through the RSS 2.0 feed. Both comments and pings are currently closed.

Comments are closed.