Schema Objects and Data Management
This domain tests the objects you create and the DDL and DML behavior around them: tables and data types, constraints, indexes, views, sequences, synonyms, temporary tables, and partitioning basics.
Objects and their rules
A heap table stores rows unordered; an index-organized table stores rows in the primary key index. Character data uses VARCHAR2 (4000 bytes by default, longer with extended string size), CHAR is blank-padded, NUMBER is variable length, and CLOB and BLOB store large objects. Constraints are NOT NULL, CHECK, UNIQUE, PRIMARY KEY, and FOREIGN KEY, the last supporting ON DELETE CASCADE or SET NULL. A UNIQUE or PRIMARY KEY constraint creates a unique index if no suitable index exists. Sequences support CACHE, NOCACHE, CYCLE, and ORDER. Synonyms are private or PUBLIC. Global temporary tables keep their definition in the data dictionary while their rows are private to a session or transaction.
Worked example
TRUNCATE TABLE app.child fails with ORA-02266 even though all child rows were deleted earlier. Reasoning: TRUNCATE checks that no enabled foreign key references the table; it does not inspect row counts. The fix is to disable the referencing constraint, truncate, then re-enable it, or use DELETE instead. Note that TRUNCATE is DDL, resets the high-water mark, does not fire DELETE triggers, and cannot be undone with a rollback.
Traps that cost marks
- Confusing DELETE (DML, generates undo, can be rolled back) with TRUNCATE (DDL, cannot).
- Expecting cached sequences to be gap-free; a cache is discarded on instance failure.
- Assuming a view is updatable when it contains joins, aggregates, or DISTINCT.
- Adding a NOT NULL column with a default to a large table in modern releases is metadata-only, so it does not rewrite every row.