| Automate sql executions [message #684513] |
Mon, 21 June 2021 14:43  |
JackBauer
Messages: 24
Registered: February 2021
|
Junior Member |
|
|
Hello,
Any tool that you can recommend for executing scripts on the databases?
For example, we have daily modifications of tables, inserts in other tables and so on...
And we have 700-800 DBs in total.
Something that could be scheduled beforehand even by an operator.
Thank you
|
|
|
|
| Re: Automate sql executions [message #684521 is a reply to message #684513] |
Tue, 22 June 2021 09:38  |
 |
EdStevens
Messages: 1376
Registered: September 2013
|
Senior Member |
|
|
Quote:And we have 700-800 DBs in total.
What is your definition of 'database'? Methinks you are using the mssql definition, which in oracle terms is a schema within a database. I just have a hard time imagining an organization having 700-800 _oracle_ databases --- and I used to work for the US government.
As for the basic question, my preference is to schedule the script through either the OS scheduling system, or within the database, using dbms_scheduler.
If it has to be on demand, and you want it submitted by an essentially non-technical "operator", then no matter what you use there will be some training involved; not only in how to submit it, but how to recognize errors and respond to them. So to that, there is a point at which it doesn't really matter the tool, because it's going to require training anyway.
|
|
|
|
| Re: Automate sql executions [message #684527 is a reply to message #684521] |
Wed, 23 June 2021 08:55 |
JackBauer
Messages: 24
Registered: February 2021
|
Junior Member |
|
|
My definition of a database is a cluster database with 2-4 instances each.
I work for a bank, and we create a new DB for each app, not just a schema, a DB in Exadata nodes, then inside we create a schema.
We have in house scripts for running scripts, but they are so complicated to use that we're looking for some other alternatives...
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Automate sql executions [message #684804 is a reply to message #684546] |
Mon, 23 August 2021 10:36 |
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
|
Senior Member |
|
|
JackBauer wrote on Sun, 27 June 2021 23:03Let's say we have nothing as our scripts are very bad.
There are different products in the market, most of them cost money and all of them require training.
I suggest that you start building your own script repository.
That will also make you familiar with options and techniques to implement them.
Start with a main variable script that defines which database you work on, and the credentials such as username, connection string ( preferably no plain text passwords )
Then write a script to execute scripts on the location and other conditions, according to the variables set.
When you have something that is working to your satisfaction you can try to implement it in Windows task scheduler, or Oracle's dbms_scheduler
Step by step... good luck
Andrey
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
