Oracle: Sequences, Global Temporary Tables, Database Links, Synonyms, CHECK Constraints, and More

Atlas now fully inspects, diffs, and migrates Oracle SEQUENCE objects, GLOBAL TEMPORARY TABLE with ON COMMIT semantics, DATABASE LINK (private and public), SYNONYM (private and public), VARCHAR2/CHAR length semantics, primary keys backed by pre-created indexes, and value CHECK constraints.

This release delivers a broad set of additions to the Oracle driver. Atlas now handles the full lifecycle for seven previously unsupported or incorrectly handled Oracle constructs.

Sequence Objects

Standalone SEQUENCE objects are now inspected, diffed, and managed as first-class schema objects. A sequence "…" { … } block is emitted in HCL with all standard options: start, increment, min_value, max_value, cache, cycle, and order. Previously, inspecting a schema containing sequences returned an empty block.

sequence "my_seq" {  schema    = schema.app  start     = 1000  increment = 1  cache     = 20  cycle     = false}
table "orders" {  schema = schema.app  column "id" {    null    = true    type    = NUMBER    default = sql("\"my_seq\".NEXTVAL")  }}

The migration planner generates CREATE SEQUENCE, ALTER SEQUENCE, and DROP SEQUENCE statements. NEXTVAL defaults on columns that reference a managed sequence are preserved and normalized across schema-qualified and unqualified forms.

Global Temporary Tables

GLOBAL TEMPORARY TABLE is now correctly round-tripped. Previously, such a table was inspected as an ordinary permanent table and re-applying the HCL would silently recreate it as permanent. Atlas now captures the temporary nature and the ON COMMIT behavior in a temporary { } sub-block:

table "session_data" {  schema = schema.app  column "id" {    null = true    type = NUMBER  }  temporary {    on_commit = PRESERVE_ROWS  }}
table "work_items" {  schema = schema.app  column "id" {    null = true    type = NUMBER  }  temporary {    on_commit = DELETE_ROWS  }}

The corresponding DDL is generated correctly:

-- Add new table named "session_data"CREATE GLOBAL TEMPORARY TABLE "session_data" (  "id" NUMBER) ON COMMIT PRESERVE ROWS;
-- Add new table named "work_items"CREATE GLOBAL TEMPORARY TABLE "work_items" (  "id" NUMBER) ON COMMIT DELETE ROWS;

Database Links

DATABASE LINK objects, both private (schema-scoped) and PUBLIC (realm-level), are now inspected and managed. All four authentication modes are supported:

• Connectionless (no CONNECT TO clause)
• CONNECT TO CURRENT_USER
• Fixed-user: CONNECT TO user IDENTIFIED BY password
• Shared: AUTHENTICATED BY …

Passwords are write-only inputs: they are used to generate the CREATE DATABASE LINK statement (which is marked atlas:sensitive) but are not surfaced during inspection. Since Oracle has no CREATE OR REPLACE DATABASE LINK, a modification is implemented as DROP + CREATE.

# Connectionless linkdatabase_link "L1" {  schema = schema.app  host   = "remote_db"}
# Current-user linkdatabase_link "L2" {  schema = schema.app  host   = "remote_db"  connect {    current_user = true  }}
# Fixed-user link (password is write-only: not surfaced on inspect)database_link "L3" {  schema = schema.app  host   = "remote_db"  connect {    user     = "HR"    password = "secret"  }}
# SHARED link with AUTHENTICATED BYdatabase_link "L4" {  schema = schema.app  host   = "remote_db"  connect {    user     = "HR"    password = "secret"  }  authenticated_by {    user     = "AUTH_USER"    password = "auth_secret"  }}

-- Create database link "L1"CREATE DATABASE LINK "L1" USING 'remote_db';-- Create database link "L2"CREATE DATABASE LINK "L2" CONNECT TO CURRENT_USER USING 'remote_db';-- Create database link "L3"-- atlas:sensitiveCREATE DATABASE LINK "L3" CONNECT TO "HR" IDENTIFIED BY "secret" USING 'remote_db';-- Create database link "L4"-- atlas:sensitiveCREATE SHARED DATABASE LINK "L4" CONNECT TO "HR" IDENTIFIED BY "secret"  AUTHENTICATED BY "AUTH_USER" IDENTIFIED BY "auth_secret" USING 'remote_db';

Synonyms

SYNONYM objects (private and public) are now inspected and managed. Local targets (a managed table or sequence) are expressed as typed HCL references: object = table.T. Remote targets use a string object plus a db_link reference. Dependency ordering ensures the backing database link is always created before its synonyms.

# Synonym pointing to a local tablesynonym "orders_syn" {  schema = schema.app  object = table.orders}
# Synonym pointing to a local sequencesynonym "seq_syn" {  schema = schema.app  object = sequence.my_seq}
# Synonym pointing to a remote object via a database linksynonym "remote_syn" {  schema  = schema.app  object  = "\"REMOTE_TABLE\""  db_link = database_link.L1}
# Public synonym pointing to a remote objectsynonym "pub_syn" {  object  = "\"REMOTE_TABLE\""  db_link = database_link.pub_link}

VARCHAR2 / CHAR Length Semantics

Oracle distinguishes VARCHAR2(n CHAR) (character-length semantics) from VARCHAR2(n) / VARCHAR2(n BYTE) (byte-length semantics, the default). Previously both were inspected identically as VARCHAR2(n), which silently lost the character-length qualifier. This makes a meaningful difference in multi-byte character sets.

Atlas now detects the character-length semantics during inspection and preserves them on the column type. The DDL formatter emits the full VARCHAR2(n CHAR) form in this case:

table "t_charsem" {  schema = schema.app  column "c_char" {    null = true    type = sql("VARCHAR2(100 CHAR)")   # CHAR semantics preserved  }  column "c_byte" {    null = true    type = VARCHAR2(100)               # BYTE semantics (default)  }  column "c_nchar" {    null = true    type = sql("NVARCHAR2(50)")  }}

-- Previously: both columns produced VARCHAR2(100)-- Now: CHAR semantics are preserved in the generated DDLCREATE TABLE "t_charsem" (  "c_char"  VARCHAR2(100 CHAR),  "c_byte"  VARCHAR2(100),  "c_nchar" NVARCHAR2(50));

Primary Keys Backed by Pre-created Indexes

When a primary key is created with ADD CONSTRAINT … PRIMARY KEY … USING INDEX <name>, Oracle records the constraint as using a pre-existing index rather than auto-creating one. Previously, Atlas only recognized primary keys backed by auto-created indexes, so such a primary key was demoted to a plain unique index and the primary key semantic was lost entirely.

-- Pre-created unique indexCREATE UNIQUE INDEX ACTIONS_PK ON ACTIONS (APPID, GATEID, ACTIONID);
-- PK constraint reusing the pre-created indexALTER TABLE ACTIONS  ADD CONSTRAINT ACTIONS_PK  PRIMARY KEY (APPID, GATEID, ACTIONID)  USING INDEX ACTIONS_PK ENABLE;

Atlas now matches the constraint to its pre-created index, correctly identifying and naming the primary key after its constraint (which may differ from the backing index name):

# Previously: reported as a plain unique indexindex "ACTIONS_PK" {  unique  = true  columns = [column.APPID, column.GATEID, column.ACTIONID]}
# Now: reported as the table's primary keyprimary_key "ACTIONS_PK" {  columns = [column.APPID, column.GATEID, column.ACTIONID]}

Dropping such a primary key now also drops its backing pre-created index with DROP CONSTRAINT … DROP INDEX; without this, Oracle would retain the pre-created index as an orphaned object:

-- Dropping a PK backed by a pre-created index-- also drops its backing index (DROP INDEX), so Oracle does not retain itALTER TABLE "ACTIONS" DROP CONSTRAINT "ACTIONS_PK" DROP INDEX;

CHECK Constraints

Value CHECK constraints are now inspected, diffed, and applied. Oracle stores NOT NULL constraints internally as CHECK constraints; these continue to be modeled as null = false on the column. Only value checks, such as age > 0 or doc IS JSON, are surfaced as check { } blocks:

CREATE TABLE t_check_demo (  doc  CLOB,  age  NUMBER,  CONSTRAINT doc_is_json  CHECK (doc IS JSON),  CONSTRAINT age_positive CHECK (age > 0));

table "T_CHECK_DEMO" {  schema = schema.app  column "DOC" { null = true, type = CLOB }  column "AGE" { null = true, type = NUMBER }  check "DOC_IS_JSON"  { expr = "DOC IS JSON" }  check "AGE_POSITIVE" { expr = "AGE > 0" }}

The full lifecycle is supported:

• Add: Emits an out-of-line CONSTRAINT … CHECK (…) clause in CREATE TABLE, or ALTER TABLE … ADD CONSTRAINT for an existing table.
• Modify: Implemented as DROP + ADD (Oracle cannot alter a check expression in place).
• Rename: Uses ALTER TABLE … RENAME CONSTRAINT, including renaming an Oracle-generated (SYS_C…) name to a user-defined one.
• Anonymous checks: An unnamed ADD CHECK is non-reversible (Oracle auto-names it with a non-deterministic name, so no reversible DROP can be generated).

-- Modify a check (DROP + ADD, Oracle cannot ALTER in place)ALTER TABLE "T_CHECK_DEMO" DROP CONSTRAINT "AGE_POSITIVE";ALTER TABLE "T_CHECK_DEMO" ADD CONSTRAINT "AGE_POSITIVE" CHECK (AGE >= 18);
-- Rename a check (same expression, new name — uses RENAME CONSTRAINT)ALTER TABLE "T_CHECK_DEMO" RENAME CONSTRAINT "AGE_POSITIVE" TO "AGE_NON_NEGATIVE";

Compatibility

All of these features work on supported Oracle versions. Inspect, diff, and apply all work with both the declarative workflow (atlas schema apply) and versioned migrations (atlas migrate diff).