Source Control for Oracle 3

Setting up object locking

Object locking is only available in Source Control for Oracle 2 and later.

This feature isn't necessary for teams where each developer has their own database.

Object locking is set up per database, not per project. You'll be able to lock objects in all schemas linked to the project.

To set up object locking, you need these database privileges:

  • CREATE USER
  • CREATE ROLE
  • GRANT ANY PRIVILEGE
  • CREATE ANY TABLE
  • CREATE ANY PROCEDURE

To set up object locking

  1. In the Lock objects tab, click Set up object locking.
    The script to set up object locking is displayed.
  2. Review the script and click Run script.
  3. Click OK.

Object locking is now set up for the database.

Alternatively, run the setup script manually

  1. View the SQL script:

    Object locking setup script

    CREATE USER redgate
    IDENTIFIED BY pass1;
    GRANT UNLIMITED TABLESPACE TO redgate;
    CREATE TABLE redgate.locked_objects (
    session_user     VARCHAR2(30),
    os_user     VARCHAR2(30),
    locked_date      DATE,
    owner         VARCHAR2(30),
    object_name   VARCHAR2(128),
    object_type   VARCHAR2(18),
    comm VARCHAR2(500),
    CONSTRAINT unique_locks UNIQUE (owner, session_user, os_user, object_name, object_type));
    COMMENT ON COLUMN redgate.locked_objects.session_user IS 'the oracle user who owns the lock';
    COMMENT ON COLUMN redgate.locked_objects.os_user IS 'the os user who owns the lock';
    CREATE OR REPLACE PROCEDURE redgate.lock_obj (
    owner  IN       VARCHAR2,
    object_name IN  VARCHAR2,
    object_type IN VARCHAR2,
    comment IN VARCHAR2)
    IS
    BEGIN
      INSERT INTO redgate.locked_objects
      VALUES ( USER, SYS_CONTEXT('USERENV', 'OS_USER'),  SYSDATE, owner, object_name, object_type, comment); 
      COMMIT;
    END lock_obj;
    /
    CREATE OR REPLACE PROCEDURE redgate.unlock_obj (
    obj_owner  IN       VARCHAR2,
    obj_name IN  VARCHAR2,
    obj_type IN VARCHAR2)
    IS
    BEGIN
      DELETE FROM redgate.locked_objects 
      WHERE  redgate.locked_objects.owner = obj_owner 
      AND redgate.locked_objects.object_name = obj_name 
      AND redgate.locked_objects.object_type = obj_type; 
    COMMIT;
    END unlock_obj;
    /
    CREATE OR REPLACE TRIGGER redgate.check_locks
    BEFORE DDL
    ON DATABASE
    DECLARE
    cnt NUMBER;
    reason varchar2(80);
    session_locker varchar2(30);
    os_locker varchar2(30);
    current_os_user varchar2(30);
    locked_at date;
    BEGIN
      SELECT SYS_CONTEXT('USERENV', 'OS_USER') INTO current_os_user FROM dual;
     
      SELECT count(*) into cnt
      FROM redgate.locked_objects  
      WHERE redgate.locked_objects.owner  = ora_dict_obj_owner 
      AND redgate.locked_objects.object_name = ora_dict_obj_name
      AND redgate.locked_objects.object_type = ora_dict_obj_type
      AND  (redgate.locked_objects.session_user != USER OR redgate.locked_objects.os_user != current_os_user ) --allow alters by the user who owns the lock
      AND ROWNUM=1;
       IF (cnt>0) THEN
          
          SELECT comm, session_user, os_user, locked_date  into reason, session_locker, os_locker, locked_at
          FROM redgate.locked_objects  
          WHERE redgate.locked_objects.owner  = ora_dict_obj_owner 
          AND redgate.locked_objects.object_name = ora_dict_obj_name
          AND redgate.locked_objects.object_type = ora_dict_obj_type
          AND  (redgate.locked_objects.session_user != USER OR redgate.locked_objects.os_user != current_os_user );
          
          RAISE_APPLICATION_ERROR(-20078,'You can''t ' || ora_sysevent || ' ' || ora_dict_obj_owner || '.' || ora_dict_obj_name || ', it was locked by ' || 
                                session_locker || '/' || os_locker || ' on ' || locked_at || ' with the comment ''' || reason || '''');
      END IF;  
    END check_locks;
    /
    CREATE ROLE object_locker;
    GRANT SELECT ON redgate.locked_objects TO object_locker;
    GRANT EXECUTE ON redgate.lock_obj TO object_locker;
    GRANT EXECUTE ON redgate.unlock_obj TO object_locker;
    GRANT object_locker to PUBLIC;

    The script will create:

    • a schema named REDGATE
    • a table named LOCKED_OBJECTS to store lock information
    • two procedures, LOCK_OBJ and UNLOCK_OBJ, to insert and delete rows from LOCKED_OBJECTS
    • a DDL trigger named CHECK_LOCKS to check for a lock before allowing a drop or alter
    • a role granted to PUBLIC named OBJECT_LOCKER
  2. Run the script on the database.
  3. Restart Source Control for Oracle.

Object locking is now set up for the database.

Removing object locking

To remove the object locking functionality, run this script on the database:

DROP USER redgate CASCADE;
DROP ROLE object_locker;

Didn't find what you were looking for?