Oracle Database and SQL: Read Consistency, Row Locks and Schema Ownership?

Why the Answer Is Correct

Option C is true because Oracle implements multi-version read consistency: when a SELECT starts, the database returns data as of a single point in time (statement-level read consistency, escalated to transaction level in serializable transactions), reconstructing older versions of changed blocks from undo segments. This guarantee applies to ordinary user-created tables, not just dictionary objects, so readers never see uncommitted or half-applied changes.

Option E is true because an UPDATE first acquires a transaction lock (TX) and then locks each individual row it changes by marking the row's interested transaction list entry in the block; a second session attempting to update the same row waits until the first transaction commits or rolls back. This row-level locking is what allows Oracle to support high concurrency without escalating to table locks.

Together, C and E describe the two pillars of Oracle's concurrency model: readers do not block writers and writers do not block readers, because readers use undo snapshots while writers hold row-level locks.

Why the Other Options Are Wrong

Option A is false because ROLLBACK is scoped to the transaction of the session that made the changes; no other database user can roll back your uncommitted UPDATE. Only your own session can undo it (or the instance itself, via SMON recovery, after a session failure) — not an unrelated user issuing ROLLBACK.

Option B is false because a query can read tables owned by other schemas whenever the owner has granted the needed object privileges (or SELECT ANY TABLE / DBA rights exist); you simply qualify the name, e.g. hr.employees, or reference a synonym. Restricting a query to 'the same schema' is not an Oracle rule at all.

Option D is false because Oracle maintains a one-to-one mapping between users and schemas: 'A schema is owned by a database user and has the same name as that user.' Privileges on objects in other schemas can be granted to a user, but granting access never transfers ownership of a second schema.

Community Comment Notes

One commenter (billysunday1) supplied the decisive evidence against D by quoting Oracle Concepts: "Each user owns a single schema" and noting the schema shares the user's name — exactly the documentation-based reasoning that settles this question for C and E.

The same commenter then contradicted himself in a second comment, insisting "Answer should be C and D" and pasting a CREATE USER statement alongside a GRANT of schema names to that user; granting access to another schema's objects is not the same as owning a second schema, and CREATE USER creates one user with one implicit schema. The third commenter, archit4321, cuts straight to the point: "C and E are the most accurate".

The vote record (CE 75 versus CD 25) shows roughly a quarter of learners falling for the schema-ownership misconception in D, which is why the documentation quote from the first comment is worth memorizing rather than the vote split.