Oracle SQL Oracle SQL Transactions and Concurrency Control 1 — Questions and Answers
Question 1: What SQL command permanently saves all changes made in the current Oracle transaction to the database?
- COMMIT (Correct answer)
- SAVE
- PERSIST
- FLUSH
Correct answer: COMMIT
COMMIT permanently saves all pending DML changes made in the current transaction to the database.
Question 2: Which Oracle SQL statement undoes all uncommitted changes made in the current transaction?
- UNDO
- ROLLBACK (Correct answer)
- REVERT
- CANCEL
Correct answer: ROLLBACK
ROLLBACK undoes all uncommitted DML changes in the current transaction, restoring data to the last committed state.
Question 3: What is the purpose of the SAVEPOINT statement in Oracle SQL?
- To permanently save a transaction to disk
- To create a named checkpoint within a transaction that can be rolled back to (Correct answer)
- To save the current database schema state
- To create a backup of the transaction log
Correct answer: To create a named checkpoint within a transaction that can be rolled back to
SAVEPOINT creates a named point within a transaction, allowing partial rollback to that specific point rather than rolling back the entire transaction.
Question 4: Which Oracle SQL syntax correctly rolls back a transaction to a savepoint named 'SP1'?
- ROLLBACK SP1
- ROLLBACK TO SP1
- ROLLBACK TO SAVEPOINT SP1 (Correct answer)
- REVERT TO SAVEPOINT SP1
Correct answer: ROLLBACK TO SAVEPOINT SP1
The correct syntax is ROLLBACK TO SAVEPOINT savepoint_name to undo changes back to the specified savepoint.
Question 5: Which event causes an implicit (automatic) COMMIT in Oracle SQL?
- Executing a SELECT statement with ORDER BY
- Executing a DDL statement such as CREATE TABLE (Correct answer)
- Opening a new SQL*Plus session
- Executing a MERGE statement
Correct answer: Executing a DDL statement such as CREATE TABLE
DDL statements (CREATE, ALTER, DROP, TRUNCATE) in Oracle implicitly issue a COMMIT before and after executing, finalizing any pending transactions.
Question 6: What happens to all row-level locks held by a transaction when COMMIT is issued in Oracle?
- Locks are converted to shared locks
- Locks remain until the next DML operation
- All locks are immediately released (Correct answer)
- Locks are transferred to the next waiting transaction
Correct answer: All locks are immediately released
When a COMMIT is issued, Oracle releases all row-level and table-level locks acquired during the transaction, allowing other sessions to modify those rows.
Question 7: In Oracle, which of the following best describes a transaction?
- A single SQL SELECT statement that reads data
- A logical unit of work consisting of one or more SQL statements that must all succeed or all fail (Correct answer)
- A collection of stored procedures grouped by schema
- A scheduled batch job registered in the database scheduler
Correct answer: A logical unit of work consisting of one or more SQL statements that must all succeed or all fail
A transaction is a logical unit of work that groups one or more SQL statements, ensuring atomicity so all changes either commit together or roll back together.
