Atlas for Oracle

What is specific to Oracle Database when Atlas manages the schema. The atlas skill covers the workflows and commands; this skill covers the rules, edge cases, and answers that differ on Oracle. Behavior is described for Oracle AI Database 26ai, release 23.26, the release of the Free image; where 19c or 21c differ, the text says so.

Files

This skill is SKILL.md plus seven reference files under references/, each served at https://atlasgo.io/skills/atlas-oracle/references/<name>. Read a reference when the task needs it:

Requires

The atlas skill, installed next to this one (../atlas/SKILL.md). If it is missing, download https://atlasgo.io/skills/atlas/SKILL.md to ../atlas/SKILL.md and each reference file listed in its Files section to ../atlas/references/<name>.

Rules

1. The Oracle driver ships in the Oracle build of Atlas and needs a login. If a command fails with The oracle driver is available only as custom build, install that build: curl -sSf https://atlasgo.sh | ATLAS_FLAVOR="oracle" sh, or use the arigaio/atlas:latest-extended image. Check the login with atlas whoami. If it shows no login, ask the user to run atlas login: logged out, every command that connects to Oracle aborts with The oracle driver is available only to Atlas Pro users. A user without an account can start a free trial with atlas login.
2. Pick the URL scope from what the schema holds:
   • In Oracle, a schema is a user. Schema scope, the default: Atlas manages the schema of the user in the URL and writes unqualified statements ("ORDERS"). Database scope, ?mode=database: Atlas covers every schema in the PDB that Oracle does not maintain and qualifies each statement ("APP"."ORDERS").
   • The URL's path is a PDB service, such as FREEPDB1, never the root container.
   • At database scope, a schema the desired state does not declare is planned for DROP USER, after its objects. Limit the run with schemas or --schema.
   • Database-scope plans can drop indexes and leave new ones out. Keep tables, indexes, code, and grants at schema scope, one env per schema, and use database scope for roles alone (Rule 11).
   • The dev URL takes the target's scope: docker://oracle/free:latest-lite, or the same with ?mode=database.
3. The dev database is the Free image, where Atlas connects to FREEPDB1 as PDBADMIN. The lite image has no USERS tablespace, and JSON and VECTOR columns fail in its SYSTEM tablespace with ORA-43853. Use a docker block with database = "FREEPDB1" and an init that creates USERS and makes it the default, plus any other tablespace the schema names (references/dev-database.md). Match the target's release and MAX_STRING_SIZE: a 26ai dev database accepts BOOLEAN and JSON columns that a 19c target rejects. Never point dev at a real environment's database.
4. Connect Atlas as the schema owner, never as an administrator. It needs the CREATE privileges for its object types, SELECT_CATALOG_ROLE, EXECUTE ON SYS.DBMS_LOCK, and a quota on its tablespace; each missing one fails with its own error (references/dev-database.md, Privileges of the Atlas User).
5. Names: Oracle folds unquoted names to uppercase, and Atlas quotes every name it writes. Write HCL labels in uppercase (table "ORDERS"): a lowercase label creates a lowercase name that every query must quote. At schema scope, the schema block's name is the URL's user. Name every primary key, unique constraint, foreign key, and check: for unnamed ones, Atlas copies the dev database's SYS_C names into the plan, which collide with names on the target (ORA-02264) and shift when the schema changes. Keep the SYS_C names inspection writes for existing constraints (references/objects.md).
6. Numbers and sequences:
   • Write integers as NUMBER(p), such as NUMBER(10) or NUMBER(19). INTEGER and NUMBER(*,0) are planned as plain NUMBER, which accepts decimals, and no later plan reports it.
   • Atlas does not plan a sequence's start, from SQL or HCL. Write START WITH in the migration file, or, in the declarative workflow, run ALTER SEQUENCE ... RESTART START WITH n once by hand. Never use min_value as the start: once the sequence is used, later plans restart it and repeat numbers.
   • Oracle treats '' as NULL: a backfill of '' leaves rows NULL.
7. Some changes never reach a plan, and the plan still reports success (references/objects.md):
   • A rename is planned as a drop and an add, which loses the data: Oracle has no renamed_from. Write renames by hand (references/migrations.md, Renames).
   • Packages, PARTITION BY, INVISIBLE, and checks of the form col IS NOT NULL are left out. Create packages, partitioned tables, and invisible columns in hand-written migration files.
   • Materialized views break planning, and existing ones inspect as tables: exclude them by name.
8. PL/SQL:
   • Code that does not compile on the dev database is left out of plans, and in a migration file Oracle creates it INVALID while Atlas reports it applied. An invalid trigger fails every statement on its table with ORA-04098. Test it (Rule 12).
   • In hand-written files, end each CREATE of PL/SQL with / on its own line, or END;;.
   • Anonymous blocks (BEGIN, DECLARE) fail with PLS-00103: use plain SQL, CALL, or a procedure the file creates, calls, and drops. SQL*Plus commands such as SET and EXEC fail too (references/migrations.md).
9. Oracle commits before and after every DDL statement, so a file or a plan that fails halfway stays half applied, and --tx-mode cannot prevent it. To recover, fix the failed statement and the ones after it and run atlas migrate hash, or undo the applied statements with atlas migrate down. Keep DDL and data changes in separate, small files (references/migrations.md).
10. Busy tables: DDL_LOCK_TIMEOUT is 0, so DDL on a table with an open transaction fails at once with ORA-00054. Set ALTER SESSION SET DDL_LOCK_TIMEOUT = 30 at the top of the file or in a hook. Atlas plans CREATE INDEX without ONLINE, which blocks writes during the build: on a populated table, write CREATE INDEX ... ONLINE by hand. Add foreign keys and checks ENABLE NOVALIDATE and validate them in a later file; back a NOVALIDATE unique constraint with a non-unique index (references/migrations.md, Locks and Online Changes).
11. Roles and grants (references/security.md): manage roles in an env of their own at database scope, with mode { schemas = false, objects = false, roles = true }, and grants in the schema-scope env with mode { permissions = true }, naming roles as strings and creating them in the dev init. Roles are database-wide and the role block has no external, so set drop_role in the skip diff policy, or other teams' roles are planned for a drop. Users, system privileges, and schema privileges are not managed.
12. Test every change before reporting it done, as a habit. Run atlas schema validate, add a test "schema" case that calls each function and procedure (SELECT ... FROM dual, CALL) and exercises each trigger, view, and check, and a test "migrate" case with seeded rows for each migration that moves or converts data. For every migration that creates PL/SQL, assert that SELECT COUNT(*) FROM user_objects WHERE status = 'INVALID' returns 0. Run them with atlas schema test and atlas migrate test (references/testing.md).
13. When no built-in analyzer covers a check, write a custom lint rule in the HCL rule language, and do the same for a convention the user states, such as an index on every foreign key, uppercase names, or character length semantics. Oracle has no analyzers of its own, so these rules carry the Oracle checks (references/custom-rules.md).

Quick Answers

Documentation

• Oracle HCL reference: https://atlasgo.io/hcl/oracle
• Oracle guides: https://atlasgo.io/guides/oracle/automatic-migrations, https://atlasgo.io/guides/oracle/vector
• Security: https://atlasgo.io/guides/oracle/security-declarative, https://atlasgo.io/guides/oracle/security-versioned
• Dev database: https://atlasgo.io/concepts/dev-database
• Lint checks: https://atlasgo.io/lint/analyzers
• Oracle SQL Language Reference: https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/
• Guides for each database: https://atlasgo.io/databases