Setting up object locking
Published 26 July 2017
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:
GRANT ANY PRIVILEGE
CREATE ANY TABLE
CREATE ANY PROCEDURE
To set up object locking
- In the Lock objects tab, click Set up object locking.
The script to set up object locking is displayed.
- Review the script and click Run script.
- Click OK.
Object locking is now set up for the database. Before using the feature, please check that the redgate.check_locks trigger has been created successfully. If the user has insufficient privileges to add the database-level trigger, the trigger will need to be added manually by connecting as the Redgate/pass1 user and running the trigger creation SQL.
How to run the setup script manually:
Run the following SQL statements to create the necessary objects to support the object locking feature:
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
- a table named
LOCKED_OBJECTSto store lock information
- two procedures,
UNLOCK_OBJ, to insert and delete rows from
- a DDL trigger named
CHECK_LOCKSto check for a lock before allowing a drop or alter (in Oracle 12c and above you may need to connect as the Redgate user to create this object)
- a role granted to PUBLIC named
- a schema named
- 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;