Oracle__15. Oracle 1Z0-051 Exam - Transactions Flashcards
What happens to a SAVEPOINT after a commit?
It is deleted
What happens with this series of statements in a transaction? DELETE * FROM table1 WHERE field = 1 SAVEPOINT test DELETE * FROM table1 WHERE field = 2 SAVEPOINT test ROLLBACK TO test;
The second SAVEPOINT over writes the first SAVEPOINT because the savepoint name is the same.
Savepoint can be used to rollback DML statement or DDL statements or both?
Only DML statements
What clause can be added to a select statement which will temporary lock records from other users for updating or deleting?
FOR UPDATE
Can you rollback a DELETE statement?
Yes
Can you rollback a TRUNCATE statement?
No
What does the following statement do? SAVEPOINT test;
create a savepoint in a transaction
What does the following statement do? ROLLBACK to test;
ROLLSBACK the SQL statements to the test SAVEPOINT.
Can you create more than one save point in a transaction?
Yes
What happens to a transaction with several SAVEPOINT when the command ROLLBACK is executed?
all transactions are rolled back.
What type of command will perform an automatic commit?
Any DDL statement.
What will happen with transactions when you exit SQL *Plus?
There will be an Auto Commit.
During a transaction but before the commit can the user view the results by executing a select statement?
Yes
During a transaction but before the commit can another user view the results by executing a select statement?
No
During a transaction but before the commit can another user make changes to the same rows that are pending a change?
No.The rows are locked by the first user.
What happens to SAVEPOINT after a COMMIT?
They are erased
What happens to locked rows after a COMMIT?
They are released
During a transaction but before the commit and if the same user creates a new session, will the user in the new session we the data before the transaction or data from the uncommitted transaction?
The user will see the data prior to any uncommitted transaction in the new session.
What keywords can be added to a select statement to lock records during a transaction?
FOR UPDATE
If another user is in the process of a transaction, what option can be added to the SELECT .. FOR UPDATE so your statement is not locked until the other user performs a COMMIT or ROLLBACK?
NOWAIT
Where in a SELECT statement does the FOR UPDATE clause belong.
Just before the ORDER BY, otherwise the very last clause.
If you SELECT.. FOR UPDATE includes several tables are all select rows in all tables locked?
Yes
What keyword can be added to the SELECT..FOR UPDATE to limit the tables if all many tables are in the select?
FOR UPDATE OF ColumnnameThe columnname may have an alias and identified the particular table in which the rows will be locked.
What will this clause do in a select statement? FOR UPDATE WAIT 5
The select will wait 5 second before locking the row and returning control.
Can a FOR UPDATE clause in a select which has more than one table?
Yes. Select rows of each table are locked
What does the NOWAIT option for the FOR UPDATE do?
If any selected rows are locked the SQL statement will not wait and no lock any rows.The default is wait until all rows are released.
List 3 main Transaction Control Commands.
- COMMIT2. ROLLBACK3. SAVEPOINT
What is a PL *SQL Package?
a schema object that group logically related PL *SQL types, variables and subprograms.Have 2 parts a specification (spec) and bodyHave get and set methods
What is different in PL *SQL in Oracle 11g?
force triggers of same type to follow a sequence.
What happens if you do a ROLLBACK to a SAVEPOINT if the SAVEPOINT does not exist?
It creates an error.
Where would the FOR UPDATE clause belong in this statement? SELECT Order, name FROM Orders JOIN Customers USING (Customer_id) WHERE order_total > credit limit ORDER BY order_id;
After the WHERE and before the ORDER BY.SELECT Order, nameFROM Orders JOIN CustomersUSING (Customer_id)WHERE order_total > credit limitFOR UPDATEORDER BY order_id;
Can a DELETE or a TRUNCATE be performed on a table that is a parent of a referential integrity constraint?
Only if the table has an ON DELETE rule;
Where would the FOR UPDATE clause belong in this statement and only lock the customer table? SELECT Order, name FROM Orders JOIN Customers USING (Customer_id) WHERE order_total > credit limit ORDER BY order_id;
SELECT Order, nameFROM Orders JOIN CustomersUSING (Customer_id)WHERE order_total > credit limitFOR UPDATE OF (name)ORDER BY order_id;
