Oracle 11gR2 to schedule to run sql script

I need to run SQL script at particular time using SQL developer, will I be able to do that without DBA privileges? Also, when I increase the buffer size, do I need to shutdown/restart the database?
lium1Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
>>I need to run SQL script at particular time using SQL developer

You cannot.

You can create a .sql file and use sqlplus and schedule it to run in the OS.

Depending on the script, you might be able to run it inside the database using dbms_scheduler.

>>Also, when I increase the buffer size, do I need to shutdown/restart the database?

You really need to ask one question per question.

What "buffer size" are you increasing?
0
lium1Author Commented:
Ok, I will do one question at a time!
Can you be a little more specific about how to schedule it and run in OS using sqlplus or using dbms_scheduler?
0
slightwv (䄆 Netminder) Commented:
>>Can you be a little more specific about how to schedule it and run in OS using sqlplus or using dbms_scheduler?

using sqlplus:
create a file with all the commands.  Normally these have a .sql extension.

For example: some_file.sql

Make sure the last line of the file has:
exit

The create a BAT file with:
sqlplus some_user/password@somedatabase @some_file.sql

Test the BAT file from the command line.

Then use Windows task scheduler to set a time for it to run.


To use using dbms_scheduler you 'can' run OS scripts but if I had a BAT script, I would use task scheduler.  If you can get all the commands in a PL/SQL block, then I would suggest dbms_scheduler as a possible solution.

To know for sure, we need to know what the script does.
0
lium1Author Commented:
Hi slightwv,

On my previous question you said that I won't need a DBA privileges to schedule a job.
But, here is what I got when I create dbms_scheduler.create_job:

Insufficient privileges

Any suggestion!

Thanks,
Maurice
0
slightwv (䄆 Netminder) Commented:
You need rights on dbms_scheduler.  That does not mean DBA privs.

Check out:
http://docs.oracle.com/cd/E11882_01/server.112/e17120/schedadmin.htm#ADMIN13306

Have the DBA:
grant create job to your_db_user;
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.