Oracle__15. Oracle 1Z0-051 Exam - Transactions

Question 1

What happens to a SAVEPOINT after a commit?

Answer 1

It is deleted

Question 2

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;

Answer 2

The second SAVEPOINT over writes the first SAVEPOINT because the savepoint name is the same.

Question 3

Savepoint can be used to rollback DML statement or DDL statements or both?

Answer 3

Only DML statements

Question 4

What clause can be added to a select statement which will temporary lock records from other users for updating or deleting?

Answer 4

FOR UPDATE

Question 5

Can you rollback a DELETE statement?

Answer 5

Yes

Question 6

Can you rollback a TRUNCATE statement?

Answer 6

No

Question 7

What does the following statement do? SAVEPOINT test;

Answer 7

create a savepoint in a transaction

Question 8

What does the following statement do? ROLLBACK to test;

Answer 8

ROLLSBACK the SQL statements to the test SAVEPOINT.

Question 9

Can you create more than one save point in a transaction?

Answer 9

Yes

Question 10

What happens to a transaction with several SAVEPOINT when the command ROLLBACK is executed?

Answer 10

all transactions are rolled back.

Question 11

What type of command will perform an automatic commit?

Answer 11

Any DDL statement.

Question 12

What will happen with transactions when you exit SQL *Plus?

Answer 12

There will be an Auto Commit.

Question 13

During a transaction but before the commit can the user view the results by executing a select statement?

Answer 13

Yes

Question 14

During a transaction but before the commit can another user view the results by executing a select statement?

Answer 14

No

Question 15

During a transaction but before the commit can another user make changes to the same rows that are pending a change?

Answer 15

No.The rows are locked by the first user.

Question 16

What happens to SAVEPOINT after a COMMIT?

Answer 16

They are erased

Question 17

What happens to locked rows after a COMMIT?

Answer 17

They are released

Question 18

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?

Answer 18

The user will see the data prior to any uncommitted transaction in the new session.

Question 19

What keywords can be added to a select statement to lock records during a transaction?

Answer 19

FOR UPDATE

Question 20

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?

Answer 20

NOWAIT

Question 21

Where in a SELECT statement does the FOR UPDATE clause belong.

Answer 21

Just before the ORDER BY, otherwise the very last clause.

Question 22

If you SELECT.. FOR UPDATE includes several tables are all select rows in all tables locked?

Answer 22

Yes

Question 23

What keyword can be added to the SELECT..FOR UPDATE to limit the tables if all many tables are in the select?

Answer 23

FOR UPDATE OF ColumnnameThe columnname may have an alias and identified the particular table in which the rows will be locked.

Question 24

What will this clause do in a select statement? FOR UPDATE WAIT 5

Answer 24

The select will wait 5 second before locking the row and returning control.

Question 25

Can a FOR UPDATE clause in a select which has more than one table?

Answer 25

Yes. Select rows of each table are locked

Question 26

What does the NOWAIT option for the FOR UPDATE do?

Answer 26

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.

Question 27

List 3 main Transaction Control Commands.

Answer 27

1. COMMIT2. ROLLBACK3. SAVEPOINT

Question 28

What is a PL *SQL Package?

Answer 28

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

Question 29

What is different in PL *SQL in Oracle 11g?

Answer 29

force triggers of same type to follow a sequence.

Question 30

What happens if you do a ROLLBACK to a SAVEPOINT if the SAVEPOINT does not exist?

Answer 30

It creates an error.

Question 31

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;

Answer 31

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;

Question 32

Can a DELETE or a TRUNCATE be performed on a table that is a parent of a referential integrity constraint?

Answer 32

Only if the table has an ON DELETE rule;

Question 33

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;

Answer 33

SELECT Order, nameFROM Orders JOIN CustomersUSING (Customer_id)WHERE order_total > credit limitFOR UPDATE OF (name)ORDER BY order_id;