0

Context

Every few weeks the database guy at work backs up the production database and then several weeks later imports it into the development database. I don't know why it takes so long between backup and restore, but anything I do in the database during that time period is lost unless I explicitly tell him to save it. Not having every little change memorized has at least once caused a data loss issue in one of my applications. So my plan is to create a table and populate it with information about the changes made to the database so I have an answer next time he asks me "what changes have you made to the database since September 5th?"

Question

I'm trying to compose this trigger to log structural changes to a table that I will query with a cron job and backup the relevant tables as needed. I don't care about data, just structural changes.

How do I access the variables needed to populate the table in this statement?

CREATE OR REPLACE TRIGGER DB_CHANGES_T 
AFTER ALTER OR CREATE OR DROP OR GRANT OR RENAME OR TRUNCATE ON DATABASE 
BEGIN

  insert into db_change_log (
    event_type, -- 'create', 'alter', 'drop', 'grant', 'rename', or 'truncate'
    affected_entity, -- the name of the table/view/package/etc that was affected  
    entity_type, -- 'table', 'view', 'index', 'package', 'procedure', 'function', etc, etc
    event_time
  ) values (?, ? , ?, sysdate);

END;
  • I know you are not in charge of this process but in general, I would say what is being done by taking production back to development shouldn't really be done on a cycle like that in the first place. If it is being done so you can test against production-like data that should be done on a different database to begin with so that your development process is not impacted by having to redo work as part of this. – Joe W Nov 14 at 19:07
  • Doesn't saving the changes to the database via a trigger make that also part of what gets lost on the refresh? I agree with the others - you have a procedural problem within your org that should be addressed. If you really can't influence that, you can make use of a source control system yourself. At the very least, why aren't you saving these changes as .sql files in a directory structure on your workstation, making it easy for you to identify and execute them as needed? – EdStevens Nov 15 at 13:32
  • Apart from Liquibase Sql Developer has built in interface for git and subversion.under Team menu – Kumar Nov 18 at 9:43
3

It really sounds like you have an XY Problem.

Your Business Requirement can be summed up with this statement:

I don't care about data, just structural changes.

I'm sure the Business Requirement to capture and manage Schema deltas is part of DevOps. There are solutions that exist for this part of the entire DevOps process. (eg Liquibase) I suggest you don't reinvent the wheel.

Oracle Tools for Schema Deltas

The latest version of Oracle SQLcl (v19.2.1 - $0) has incorporated Liquibase. This allows you to create schema deltas (and save the result in a Code Repository)

The third example in the documentation appears to be a good starting point for your needs.

  1. capture base Schema
  2. capture changes
  3. (example applies base Schema to new Schema)
  4. apply changes to base Schema

Make sure you keep the deltas in a Code Repository.

More Info?

There are a lot of articles that discuss how automate schema changes...especially with the context of DevOps. Feel free to google for more information.

2

The documentation has a list of the event attribute functions that you can access. It looks like you'd want ora_sysevent for the event, ora_dict_obj_name and ora_dict_obj_owner to identify the object in question and ora_dict_obj_type for the object type.

Taking a step back, though, you'd normally solve this sort of problem by using source control to check in your changes before they get applied. Then you'd just need to re-apply any changes from source control after the database was refreshed.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.