Object Locking

Object locking is an optional Oracle database component for development databases. Do not install or enable it in production.

Use it when several developers work on the same database objects. Locking a package, procedure, table, or other supported object tells the team who is working on it and prevents another developer from changing its definition until it is unlocked or the lock expires. It does not lock the data rows in a table.

Set up an environment

Ask your DBA to connect to the environment’s development database with an account that can install the utilities.
Open Edit environment → Object locking and choose that installation connection. All environment members use the same NEXO_SQL_STUDIO utilities schema.
Open Install → Preview install script to review the SQL. Choose Install for a new setup or Update for an existing installation. New schema creation asks for a password and an existing permanent tablespace.
Select Check installation. If connections are missing access, use Copy grants and ask the DBA to run that SQL. Disconnected members are checked when they connect, or when you request the check.
Enable Object locking for this environment and save while the installation connection is connected. Saving applies protection to the environment’s application schemas. Missing grants are shown without discarding your saved settings.

After setup, developers can lock and unlock objects from the object menu. Nexo checks access in the background after connecting, so installation checks do not delay the connection.

Utilities schema

Each environment selects one Oracle installation connection. The utilities schema is always NEXO_SQL_STUDIO; its name and object names cannot be customized. A new installation must use an empty schema. Keep application objects in separate schemas to avoid collisions and reserve this schema for Nexo utilities, including future features.

Tables use NEXO_*, packages use PK_NEXO_*, triggers use TG_NEXO_*, views use V_NEXO_*, and scheduler jobs use JOB_NEXO_*. Object locking uses NEXO_OBJECT_LOCKS, PK_NEXO_LOCK_API, PK_NEXO_CORE_API, and TG_NEXO_BEFORE_DDL. Oracle may generate names for identity sequences and constraints.

The current utilities schema version is 1.2.0, stored under UTILITIES_SCHEMA_VERSION in NEXO_PARAMS. NEXO_COMPONENT_VERSIONS keeps the ordered component changeset history and SHA-256 checksums. Nexo reports installations as missing, ready, outdated, inaccessible, partial, or newer than the extension. An interrupted upgrade that has already committed Oracle DDL is reported as partial and requires DBA repair before retrying. Applied checksums are immutable: drift, partial installations, and newer versions require DBA review.

Installation and upgrades

Open Edit environment → Object locking, the final tab, and select the installation connection. Check installation, beside the Install menu, detects the utilities installation and checks member grants. The menu contains Preview install script, Install (or Update), and Documentation. Preview opens the same version-aware script that a DBA can review and run; there is no separate DBA script.

Installation requires a privileged DBA connection. The utilities owner needs permission to create tables, PL/SQL, the cleanup scheduler job, and the database DDL trigger. A fresh installation refuses a populated schema; upgrades require a recognized Nexo ledger with matching checksums.

If the utilities schema does not exist, the installer includes its creation statement and prompts for a strong password and an existing permanent tablespace. A DBA can also create the empty schema first:

create user NEXO_SQL_STUDIO identified by "<strong-password>"
  default tablespace <default-tablespace>
  quota unlimited on <default-tablespace>;

Replace both placeholders before running. Existing empty utilities schemas retain their tablespace and quota. Review storage and retention for the expected DDL volume. The history table stores full DDL as CLOB data.

If a DBA runs the installation separately, choose Preview install script, save the SQL, and run it with SQLcl or SQL*Plus against the same development database and PDB. Replace the password and tablespace placeholders when they are present. The script includes the required privileges, utilities, and grants for the selected environment members, and stops at the first error. Then return to the environment and select Check installation before enabling protection. Installing the utilities alone does not enable application schema protection.

Every member connection needs SELECT on NEXO_OBJECT_LOCKS, NEXO_COMPONENT_VERSIONS, and NEXO_PARAMS, plus EXECUTE on PK_NEXO_LOCK_API and PK_NEXO_CORE_API. Lock changes go through the package. New installation scripts grant access to environment members without adding public grants or synonyms.

Setup applies only pending changesets. A current installation has no pending database changes. Exported scripts recheck the expected ledger before changing objects and stop on errors in SQLcl or SQL*Plus. The 1.2.0 upgrade preserves existing locks, configuration, and explicit developer grants while renaming the legacy APIs, trigger, and job, and migrating NEXO_CONFIG to NEXO_PARAMS. Recheck grants after updating.

Enabling schema protection

Enable locking in the environment’s Object locking tab and save. Explicit environment configuration takes precedence over legacy connection and folder settings. Saving persists the configuration and applies database-wide protection for member application schemas through the installation connection. Keep that connection connected when saving changes to protection.

The tab lists each connection’s access status and supplies SQL for missing grants. Check installation connects members to recheck their effective access. Copy grants copies the SQL and confirms completion in a callout. Missing grants do not prevent saving: the editor remains open with the errors and saved values. Disconnected members are marked Not checked. Background checks run after connecting and do not delay connection completion or silently enable schema protection.

System, Oracle-maintained, APEX, and ORDS schemas are excluded from protection. The reserved utilities schema cannot be protected as an application schema. Removing a member disables protection for its schema unless another retained connection uses that schema. If the installation connection is offline or needs access repair, Nexo retains the cleanup work and retries it in the background after reconnecting. Deleting an enabled environment requires its database protection to be removed first; connect the installation connection before deleting. Disabling protection affects every tool and developer using that schema, so teams should control who may edit shared environment settings.

Using locks

Nexo sets Oracle CLIENT_IDENTIFIER to the signed-in Nexo account identity whenever a pooled connection is borrowed for an environment using locking. Without a Nexo identity, the component falls back to Oracle SESSION_USER. The first lock registers that effective identity. Lock owners can unlock their objects; configured locking administrators can force-unlock other users’ objects after confirmation.

Right-click a supported object to lock it. Locks default to 12 hours. Combined package and type actions cover the specification and body. Active locks show a red lock after the object name; unlocked objects have no lock icon. Parent badges count locked objects, and tooltips and details show the owner and expiry. Filters → Edit filters supports Invalid and Locked criteria alongside name and type rules.

Opening a foreign lock shows its owner and expiry in a dialog. Choose Open read-only or cancel; locking administrators can also request a confirmed Force unlock. Read-only source opens as an immutable document. Nexo checks again before compiling. Failed access checks appear as unavailable, never as an unlocked object.

The database trigger enforces locks for DDL from every tool, including Nexo SQL Studio, SQL Developer, SQLcl, CI, and deployment services. Another developer’s lock can block compilation or deployment.

PL/SQL API

SQL scripts and other tools can use the same locks as Nexo. The API is NEXO_SQL_STUDIO.PK_NEXO_LOCK_API. Use it in the development database where the utilities are installed, after the DBA grants access to the calling database user.

Procedure	Parameters	What it does
`set_schema_enabled`	`p_schema_name VARCHAR2`, `p_enabled CHAR`	Enables (`'Y'`) or disables (`'N'`) locking and DDL history for an application schema. Environment Save performs this step for its members.
`lock_object`	`p_owner VARCHAR2`, `p_object_name VARCHAR2`, `p_object_type VARCHAR2`, `p_minutes NUMBER DEFAULT NULL`	Locks one object. A null duration uses the configured default of 720 minutes. The application schema must already be enabled.
`unlock_object`	`p_owner VARCHAR2`, `p_object_name VARCHAR2`, `p_object_type VARCHAR2`, `p_force CHAR DEFAULT 'N'`	Unlocks an object owned by the current effective identity. Force unlock requires `'Y'` and a configured locking administrator.

These procedures commit their changes. Run them separately from uncommitted application work. Use the Oracle object type, such as TABLE, PROCEDURE, PACKAGE, or PACKAGE BODY. The package specification and body are separate database objects; call the API for each when protecting both. Nexo’s combined package action handles both for you. Object names must match the dictionary exactly, including case for quoted names.

For example, after enabling the APP schema in the environment, lock a package body for one hour:

begin
  NEXO_SQL_STUDIO.PK_NEXO_LOCK_API.lock_object(
    p_owner       => 'APP',
    p_object_name => 'PK_CUSTOMER',
    p_object_type => 'PACKAGE BODY',
    p_minutes     => 60
  );
end;
/

Unlock it from the same effective identity when the work is finished:

begin
  NEXO_SQL_STUDIO.PK_NEXO_LOCK_API.unlock_object(
    p_owner       => 'APP',
    p_object_name => 'PK_CUSTOMER',
    p_object_type => 'PACKAGE BODY'
  );
end;
/

A DBA can enable an application schema directly when managing setup outside Nexo:

begin
  NEXO_SQL_STUDIO.PK_NEXO_LOCK_API.set_schema_enabled('APP', 'Y');
end;
/

To see who holds active locks:

select object_owner, object_name, object_type, locked_by, locked_at, expires_at
from NEXO_SQL_STUDIO.NEXO_OBJECT_LOCKS
where expires_at > sysdate
order by object_owner, object_name, object_type;

The effective identity is the Oracle session’s CLIENT_IDENTIFIER when present, otherwise its SESSION_USER. Nexo supplies the signed-in account identity automatically. Other tools normally use their database username; a DBA can configure a consistent client identifier for tools that must share the same developer identity. Query NEXO_SQL_STUDIO.PK_NEXO_CORE_API.current_effective_user from dual to check the identity before changing a lock.

An existing lock raises “Object is already locked.” A foreign unlock is rejected unless the caller is a locking administrator and passes p_force => 'Y'. Administrators are configured by the DBA in NEXO_SQL_STUDIO.NEXO_LOCK_USERS with enabled = 'Y' and can_admin = 'Y'; force unlock should be an exceptional, agreed team action. An expired or already removed lock needs no further unlock.

Operations and recovery

A DBA can disable protection for an application schema, promote a trusted identity to locking administrator, remove an abandoned lock, or disable the database trigger while investigating a failure. Audit these actions and re-enable locking after the blocking condition is understood.

The hourly cleanup job removes expired locks and DDL history older than DDL_HISTORY_RETENTION_DAYS (60 days by default). Monitor scheduler failures, invalid utilities objects, tablespace growth, and changeset state. Never edit an applied changeset or its ledger checksum; apply later versioned changesets through setup.