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.
Oracle SQL Oracle SQL Transactions and Concurrency Control 1
What SQL command permanently saves all changes made in the current Oracle transaction to the database?