0

Is there a oracle trigger or job that would send out an email if the database schema changes?

We are having a terrible time with devs changing schema and that change information not being sent downstream to the other departments. I know the simple solution is to tighten up the devs and when they make changes, but.... you know.

Is there is a job that could be run once or twice a day to compare the schema with yesterdays schema and if there are changes, these changes would be logged and sent in a notification email.

Does anyone have a solution (can be a standalone COTS application) or a script that can accomplish this task they would be willing to share, that would be great.

pithhelmet
  • 2,222
  • 6
  • 35
  • 60
  • 2
    Is that really a solution? your devs can make changes faster than consumers can adapt. Time to revoke permissions and implement change controls. Or reconsider how databases are structured, maybe you have too much in one central database and things need breaking out? – Nathan Hughes Jul 29 '21 at 15:49
  • 1
    You may use audit for this purpose or create a ddl trigger to log the changes. But better solution is to centralize your development and setup correct dev workflow. – astentx Jul 29 '21 at 15:55
  • It's possible to [log DDL changes using a trigger](http://www.dba-oracle.com/t_ddl_triggers.htm) or [export the schema DDL daily](https://stackoverflow.com/questions/10886450/how-to-generate-entire-ddl-of-an-oracle-schema-scriptable) to compare. Like the other comments, I don't recommend using them to enable your devs' bad practices. – kfinity Jul 29 '21 at 17:21

1 Answers1

0

Oracle's preferred solution for this is auditing. See Introduction to Auditing for details. As suggested in the comments, it also sounds like you need a much more formalized configuration management pipeline for introducing changes. Devs should never be making changes directly to production without independent testing and review.

pmdba
  • 6,457
  • 2
  • 6
  • 16