CHAPTER 8 ORACLE OCA-OCP ALL IN ONE -- DML AND
CONCURRENCY NEWEST UPDATE 2025-2026 EXAM
WITH ACTUAL QUESTIONS AND CORRECT VERIFIED
ANSWERS.
Data Manipulation Language (DML) - CORRECT ANSWER-5 DML command
SELECT
INSERT
UPDATE
DELETE
MERGE
INSERT command - CORRECT ANSWER-Can insert one row, with column values
specified in the command or a set of rows created by a SELECT statement. If
columns are not supplied, it relies on positional notation.
INSERT command multiple tables - CORRECT ANSWER-Can inset rows into several
tables with one statement.
UPDATE statment - CORRECT ANSWER-Used to change rows that already exist. Can
affect one row or a set of rows. Can not update rows across multiple tables.
Update statement with subquery - CORRECT ANSWER-A subquery can be used to
determine the value of a column in the SET statement but must return a scalar
value. A subquery can also be used to determine the comparison value in the
,WHERE clause. Must be scalar if using the equality, else can use the IN
predicate and return many.
DELETE Command - CORRECT ANSWER-Will remove one row or a set of rows
(columns can not be nominated--whole row) from the table, depending on the
where clause. Without a where clause, every row will be removed. No warning
will be given.
Consistency - CORRECT ANSWER-The results of a query must be consistent with
the state of the database at the time the query started. If database not
configured correctly, ORA-1555 snapshot too old.
Isolation - CORRECT ANSWER-An incomplete (uncommitted) transaction must be
invisible to the rest of the word.
Dirty read - CORRECT ANSWER-Read of uncommitted data. Oracle does not permit
this)
Durability - CORRECT ANSWER-Once a transaction completes, must be impossible
for the database to lose it.
Statement level rollback - CORRECT ANSWER-A SQL statement that fails causes the
loss only of any work it would have performed itself. It does not cause the loss
of any work that preceded it in the current transaction. If the statement is a
DDL statement, then the implicit commit that immediately preceded it is not
undone.
Execution of a select statement process on DB - CORRECT ANSWER-A staged
process where the server process executing the stmt will first check whether
the blocks containing the data are already in memory, in the database buffer
, cache. If so, execution can proceed immediately. IF not, server process must
locate them on disk and copy them into the database buffer cache.
Server Processes Read - CORRECT ANSWER-Reads blocks from datafiles into the
database buffer cache
DBWn writes - CORRECT ANSWER-Writes blocks from the database buffer cache to
the datafiles
After data blocks are in the database buffer cache, where does further
processing (sorting or aggregation) take place - CORRECT ANSWER-In the PGA of
the session. When execution is complete, the result set is returned to the user
process.
Data consistency process for query - CORRECT ANSWER-If the query encounters a
block that has been changed since the time the query started, the server
process will to the undo segment that protected the change, locate old version
and purpose of current query only, roll back the change. so that any changes
initialed after the query commenced will not be seen.
Data isolation process for query - CORRECT ANSWER-Very similar to consistency
check. If the query encounters a block that has been changed since the time
the query started, the server process will to the undo segment that protected
the change, locate old version and purpose of current query only, roll back the
change. so that any changes initialed after the query commenced will not be
seen. IF the data needed to do this roll back is no longer in the undo segments,
this mechanism will not work and will get snapshot too old.
DML operations on data blocks for undo and redo - CORRECT ANSWER-For any
DML operation, it is necessary to work on both data blocks and undo blocks,
and also to generate redo.
CONCURRENCY NEWEST UPDATE 2025-2026 EXAM
WITH ACTUAL QUESTIONS AND CORRECT VERIFIED
ANSWERS.
Data Manipulation Language (DML) - CORRECT ANSWER-5 DML command
SELECT
INSERT
UPDATE
DELETE
MERGE
INSERT command - CORRECT ANSWER-Can insert one row, with column values
specified in the command or a set of rows created by a SELECT statement. If
columns are not supplied, it relies on positional notation.
INSERT command multiple tables - CORRECT ANSWER-Can inset rows into several
tables with one statement.
UPDATE statment - CORRECT ANSWER-Used to change rows that already exist. Can
affect one row or a set of rows. Can not update rows across multiple tables.
Update statement with subquery - CORRECT ANSWER-A subquery can be used to
determine the value of a column in the SET statement but must return a scalar
value. A subquery can also be used to determine the comparison value in the
,WHERE clause. Must be scalar if using the equality, else can use the IN
predicate and return many.
DELETE Command - CORRECT ANSWER-Will remove one row or a set of rows
(columns can not be nominated--whole row) from the table, depending on the
where clause. Without a where clause, every row will be removed. No warning
will be given.
Consistency - CORRECT ANSWER-The results of a query must be consistent with
the state of the database at the time the query started. If database not
configured correctly, ORA-1555 snapshot too old.
Isolation - CORRECT ANSWER-An incomplete (uncommitted) transaction must be
invisible to the rest of the word.
Dirty read - CORRECT ANSWER-Read of uncommitted data. Oracle does not permit
this)
Durability - CORRECT ANSWER-Once a transaction completes, must be impossible
for the database to lose it.
Statement level rollback - CORRECT ANSWER-A SQL statement that fails causes the
loss only of any work it would have performed itself. It does not cause the loss
of any work that preceded it in the current transaction. If the statement is a
DDL statement, then the implicit commit that immediately preceded it is not
undone.
Execution of a select statement process on DB - CORRECT ANSWER-A staged
process where the server process executing the stmt will first check whether
the blocks containing the data are already in memory, in the database buffer
, cache. If so, execution can proceed immediately. IF not, server process must
locate them on disk and copy them into the database buffer cache.
Server Processes Read - CORRECT ANSWER-Reads blocks from datafiles into the
database buffer cache
DBWn writes - CORRECT ANSWER-Writes blocks from the database buffer cache to
the datafiles
After data blocks are in the database buffer cache, where does further
processing (sorting or aggregation) take place - CORRECT ANSWER-In the PGA of
the session. When execution is complete, the result set is returned to the user
process.
Data consistency process for query - CORRECT ANSWER-If the query encounters a
block that has been changed since the time the query started, the server
process will to the undo segment that protected the change, locate old version
and purpose of current query only, roll back the change. so that any changes
initialed after the query commenced will not be seen.
Data isolation process for query - CORRECT ANSWER-Very similar to consistency
check. If the query encounters a block that has been changed since the time
the query started, the server process will to the undo segment that protected
the change, locate old version and purpose of current query only, roll back the
change. so that any changes initialed after the query commenced will not be
seen. IF the data needed to do this roll back is no longer in the undo segments,
this mechanism will not work and will get snapshot too old.
DML operations on data blocks for undo and redo - CORRECT ANSWER-For any
DML operation, it is necessary to work on both data blocks and undo blocks,
and also to generate redo.
