{"id":73224,"date":"2013-09-16T18:44:50","date_gmt":"2013-09-16T18:44:50","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/uncategorized\/protecting-your-changes-when-working-in-a-shared-environment\/"},"modified":"2021-07-14T13:07:38","modified_gmt":"2021-07-14T13:07:38","slug":"protecting-your-changes-when-working-in-a-shared-environment","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/oracle-databases\/protecting-your-changes-when-working-in-a-shared-environment\/","title":{"rendered":"Protecting your changes when working in a shared environment"},"content":{"rendered":"

Over the last few months, we’ve been visiting customers and helping them get up and running with Red Gate tools. Something we’ve seen over and over is that teams have to avoid stepping on each other\u2019s toes when sharing a database, because there\u2019s a constant risk of accidentally overwriting changes.<\/p>\n

A typical scenario goes something like this:<\/p>\n

    \n
  1. Alice opens a package and starts editing it.<\/li>\n
  2. Meanwhile, Bob unwittingly opens the same package, edits it, and saves it.<\/li>\n
  3. Alice saves her changes to the package.<\/li>\n<\/ol>\n

    Presto: Bob\u2019s work is gone. And what\u2019s more, Bob and Alice may not even notice. The error could make it all the way to production. We spoke to one developer who lost a week\u2019s work when their database was deployed without critical changes that had been accidentally overwritten.<\/p>\n

    There are ways to undo this kind of mistake. Some types of objects can be rescued from the recycle bin, or recovered with backups or source control; if you have Oracle Flashback Technology enabled, you can return PL\/SQL objects to a previous state. None of these methods are comprehensive or reliable, they can be time-consuming, and they depend on the problem being noticed at all.<\/p>\n

    It\u2019s better to prevent the mistake happening in the first place. To that end, some developers rely on email, IM, or text messages to warn their team when they\u2019re working on something. Others leave comments in the code or use Post-it notes. We\u2019ve even seen people shout across the office. In every case, the solution wasn’t reliable: Alice had to remember to warn Bob she was working on something, and Bob had to take notice. Some teams have a \u201cplay nice\u201d policy, and plan projects so people don\u2019t end up working on things simultaneously, but that isn’t ideal, either. It\u2019s basically a communication problem.<\/p>\n

    With Source Control for Oracle 2<\/a>, you can lock database objects before you start work on them and leave a comment explaining what you\u2019re doing. Then you can carry on working normally without worrying about someone else overwriting your work. If someone goes to edit the locked object, the server returns an error with your comment, so they know what\u2019s going on.<\/p>\n

    You can set up the Red Gate schema from the Lock objects tab in Source Control for Oracle. The tool runs a script to create a Red Gate schema on your database; it\u2019s all transparent, so you can review the script and see exactly what\u2019s going on<\/a>. Afterwards, the tab shows which objects are currently locked (by you or other people). You can see who locked the object, when they locked it, and how recently it was edited. The locking functionality works for every project connected to the database.<\/p>\n

    \"Screenshot:<\/a><\/p>\n

    When someone locks an object, information about who locked it is stored in the locked_objects<\/i> table along with their comment. When anyone executes some DDL, a system-level trigger checks they have permission to edit the referenced object. If that person locked the object themselves, they can edit it as normal. Everyone else will see an error, even if they\u2019re signed in as the same Oracle user. The error contains the comment left by the person who locked it.<\/p>\n

    \"Screenshot:<\/a><\/p>\n

    It\u2019s not totally draconian. You can still unlock other people\u2019s objects if they\u2019re off sick or on vacation or taking an extended lunch break, or if you\u2019re just feeling mischievous. We see the locking feature as a communication tool to replace the emails and Post-its. The difference is that it\u2019s easy to communicate, and impossible to miss.<\/p>\n","protected":false},"excerpt":{"rendered":"

    Over the last few months, we’ve been visiting customers and helping them get up and running with Red Gate tools. Something we’ve seen over and over is that teams have to avoid stepping on each other\u2019s toes when sharing a database, because there\u2019s a constant risk of accidentally overwriting changes. A typical scenario goes something like this: Alice opens a…<\/p>\n","protected":false},"author":316209,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143533],"tags":[],"coauthors":[],"class_list":["post-73224","post","type-post","status-publish","format-standard","hentry","category-oracle-databases"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73224","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/316209"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=73224"}],"version-history":[{"count":1,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73224\/revisions"}],"predecessor-version":[{"id":91707,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73224\/revisions\/91707"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=73224"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=73224"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=73224"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=73224"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}