2 Replies Latest reply: Sep 26, 2012 11:25 AM by Kodiak_Seattle RSS

    dbms_scheduler for regular users ?

    Kodiak_Seattle
      I am just an average user within our Department, I don't have any Admin rights. I have some SQL that execute first thing Monday morning and was wondering if I could schedule that using a *.sql file ? that has all the sql that I normally execute as script ?

      I am on 11G R2, and use SQL Developer and noticed that in version 3.2.1 there is a Scheduler Wizard feature ?

      Basically I would issue 6 drop table statements; and then I would create my 6 tables in the proper order tacked in the .sql file ?

      I read some documentation and various howto and wiki, but that is the part that I am not sure, if I can do that too ?

      Once the job is created and scheduled, is that stored on the server end or does my SQL Developer always need to run ?

      Thanks!
        • 1. Re: dbms_scheduler for regular users ?
          spajdy
          There are at least two ways haw to do it.
          1/ run your sql script using SQL*Plus from command line. Use cron on Unix/Linux or Task schedule(at) on Windows to schedulre when to run.
          Sample:
          a.sql
          -- connct to instance
          connect user/password@alias
          -- do you work
          select * from v$instance;
          -- exit script
          exit

          cmdline
          sqlplus /nolog @a.sql

          2/ create a job directly in DB. When you use ORACLE Scheduler you can create job of type executable.This type of job is able to run any shell script so you can user sql script from example 1/.
          Or create stored procedure that do same as sql script and run this procedure in job.
          • 2. Re: dbms_scheduler for regular users ?
            Kodiak_Seattle
            Great, thanks... I asked the Admin to grant me permission to create a table at the user level and not at the Role level.

            I did a simple test using a stores pl/sql proc and I was able to create a table and then drop it.

            So now I will write my PL/SQL and test it manually and then off to learn the Scheduler, so cool!

            :-)

            thanks for the tips!