This content has been marked as final. Show 9 replies
hi how can i run an SQL script in oracle server in every first day of the month? i dont want to run that script manually every first day of the month.
Find the appropriate version and then search for DBMS_SCHEDULER, or DBMS_JOB if you're rockin' a sufficiently ancient version of Oracle.
:( sorry im new to oracle DB.
Very clearely wrote on psoug:
Im came from ancient world.
Edited by: OrakelNewbie on Nov 30, 2010 9:50 PM
For example:1 person found this helpful
create table job_log(dt date); / declare job_id number; begin dbms_job.submit( job_id, 'begin insert into job_log values(sysdate); end;', sysdate, 'add_months(sysdate,1)' ); dbms_output.put_line(job_id); end;
What OS and version are you running?
You can schedule this from the OS side as well if you don't want to use the scheduler.
In linux you can make use of the crontab:
The above script will run on the 1st day of every month at 01h00.
crontab -e #then paste the following lines with the path to your scripts #.---------------- minute (0 - 59) #| .------------- hour (0 - 23) #| | .---------- day of month (1 - 31) #| | | .------- month (1 - 12) OR jan,feb,mar,apr ... #| | | | .----- day of week (0 - 7) (Sunday=0 or 7) OR sun,mon,tue,wed,thu,fri,sat #| | | | | #* * * * * command to be executed 00 01 1 * * /<path to your scripts>/script_name.sh
your script (the one you are calling in the cron) will have to call the sql script and should look similar to this:
Hope this helps
#export all your environment variables here # for example export ORACLE_BASE=/u01/app/oracle export ORACLE_HOME=$ORACLE_BASE/product/<your path> export ORACLE_SID=ORCL export PATH=$ORACLE_HOME/bin:$PATH sqlplus user/password@DB @/path/your_sql_script.sql
Please do not post info to run stupid scripts using stupid crontab.
Dbms_scheduler is much, much, much, much more powerful than crontab.
Senior Oracle DBA
Thanks for the reply. im using windows 2003 server and oracle 10g release 2 DB.
this is the sql script that i want to execute on the server:
SELECT TO_CHAR(SYSDATE,'DD-MON-YY HH24:MI:SS') FROM DUAL;
REVOKE SELECT,ALTER ON SAMPLE_SEQ FROM PUBLIC;
DROP PUBLIC SYNONYM SAMPLE_SEQ;
DROP SEQUENCE SAMPLE_SEQ;
CREATE SEQUENCE SAMPLE_SEQ
INCREMENT BY 1
START WITH 1
CREATE PUBLIC SYNONYM SAMPLE_SEQ FOR TEST.SAMPLE_SEQ
GRANT SELECT,ALTER ON SAMPLE_SEQ TO PUBLIC;
Edited by: OrakelNewbie on Nov 30, 2010 11:23 PM
Wow Sybrand, you must always attack other peoples posts to make a point!
You will see that I have said in my post "if you don't want to use the scheduler" and not "you must use the crontab".
I am merely showing that there are more than one possibility to achieve his goal and I haven't said anywhere that the crontab is better, although I feel it has it's place when you want to use certain bash scripts.
This is a forum to learn and why not learn of all the possibilities, the more options you have the better solution you can choose at the end for your situation.