CODEWITHSUNDEEP
databaseoracle-databasealter-table
whiz
whiz

Reputation: 1073

notification when alter occurs on oracle database

We have a database that many persons have to have access to. I am looking for a way that will allow us to get notification whenever "alter" occurs on this database, so other parties can be aware of it. Please advise.

Upvotes: 2

Views: 487

Answers (5)

whiz
whiz

Reputation: 1073

Seems 110g put it into consideration:

http://sysdba.wordpress.com/2007/10/16/ddd-logging-in-the-alert-log-in-11g/

Upvotes: 1

BQ.
BQ.

Reputation: 9413

Regarding your comment above:

It is development database, so we need many people to get access to it. – whizmaven (8 hours ago)

@Jonathan Leffler is correect: Many people may have access to it, but the group of people who have DDL rights (CREATE, DROP, ALTER, etc.) should be very limited.

The better solution is for any such changes to be done locally (either on a locally hosted DB, or as a different user account on a shared test DB). Only once the changes are verified there should they be rolled out to a fully shared environment. And that rollout/change-control process should be handled in a much more formal manner.

At a bare minimum, upon that rollout, an email notification of the changes and ramifications of those changes (new testing needed, updates, etc.) should be used.

Upvotes: 2

Gary Myers
Gary Myers

Reputation: 35401

ALTER what ?

ALTER SESSION may be a very common command (especially ALTER SESSION SET NLS_DATE_FORMAT or CURRENT_SCHEMA).

More commonly you'd be want to track ALTER schema_object, maybe ALTER SYSTEM and ALTER DATABASE

Upvotes: 0

cagcowboy
cagcowboy

Reputation: 30828

You can create TRIGGERs to catch a number of database events...

http://www.psoug.org/reference/ddl_trigger.html

...including before/after an ALTER on a schema.

Upvotes: 5

Jonathan Leffler
Jonathan Leffler

Reputation: 753585

Many persons should NOT have ALTER permission on a production database - very few people should have ALTER permission on a production database. Actually, you probably shouldn't have many people empowered to make changes in test databases, either.

You can consider whether auditing is the correct solution - in some DBMS, it would be one way of detecting changes, but alerting would be harder (other than by some variation on the theme of 'grepping the audit log'). There may be other ways in Oracle.

Upvotes: 4

Related Questions