Setting up object locking
Published 05 August 2013
Object locking is only available in Source Control for Oracle 2.
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
- 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.
Alternatively, run the setup script manually
If you prefer, you can run the setup script on your database manually.
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
andUNLOCK_OBJ
, to insert and delete rows fromLOCKED_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
- a schema named
- Run the script on the database.
- 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;