Run sqlplus SYSTEM/Oracle11 and START c:\\S2Script.sql When finished, the script

Question

Run sqlplus SYSTEM/Oracle11 and START c:S2Script.sql When finished, the script leaves you connected as Iggy/oracle.
Start a second instance of Iggy/oracle. Reduce the window size of each instance of sqlplus so that you can view them side by side.
In the LEFT instance, run DELETE FROM child WHERE child_ID=1;
In the RIGHT instance, run DELETE FROM child WHERE child_ID=2;
In the LEFT instance, run DELETE FROM parent WHERE parent_ID=1; (nothing happens in left window.)
In the RIGHT instance, run DELETE FROM parent WHERE parent_ID=2; (nothing happens in right window.)
At this point you will see the error message for the deadlock in the left window.
After creating a deadlock, run SQL Developer and start Tools|Monitor Sessions…
Locate the Contention and save a screen shot (Ctrl-PrintScreen) showing the two locked sessions to an MS Word document.
At this point you can right-click on the session showing the contention, and Kill Session. You will need to refresh the session to see that the contention has been released.
S2Script.sql
-- S2Script.sql
-- Prepare Oracle for S2 Written Assignment
-- Log into sqlplus as SYSTEM/Oracle11
-- START S2Script.sql
--
SET ECHO ON
SET SQLBLANKLINES ON

-- The next command will fail on the first run.
DROP USER Iggy CASCADE;
CREATE USER Iggy
PROFILE DEFAULT
IDENTIFIED BY oracle
DEFAULT TABLESPACE USERS
ACCOUNT UNLOCK;
GRANT CONNECT TO Iggy;
GRANT RESOURCE TO Iggy;

CONNECT Iggy/oracle

-- Create tables described on page 244 for Deadlock example
CREATE TABLE parent (
parent_ID INTEGER NOT NULL,
CONSTRAINT parent_PK PRIMARY KEY (parent_ID)
);

CREATE TABLE child (
child_ID INTEGER NOT NULL,
parent_ID INTEGER NOT NULL,
CONSTRAINT child_PK PRIMARY KEY (child_ID),
CONSTRAINT child_FK FOREIGN KEY (parent_ID) REFERENCES parent
);

INSERT INTO parent (parent_ID) VALUES (1);
INSERT INTO parent (parent_ID) VALUES (2);
INSERT INTO child (child_ID, parent_ID) VALUES (1,1);
INSERT INTO child (child_ID, parent_ID) VALUES (2,2);
COMMIT;

Explanation / Answer

Run sqlplus SYSTEM/Oracle11 and START c:S2Script.sql When finished, the script leaves you connected as Iggy/oracle.
Start a second instance of Iggy/oracle. Reduce the window size of each instance of sqlplus so that you can view them side by side.
In the LEFT instance, run DELETE FROM child WHERE child_ID=1;
In the RIGHT instance, run DELETE FROM child WHERE child_ID=2;
In the LEFT instance, run DELETE FROM parent WHERE parent_ID=1; (nothing happens in left window.)
In the RIGHT instance, run DELETE FROM parent WHERE parent_ID=2; (nothing happens in right window.)
At this point you will see the error message for the deadlock in the left window.
After creating a deadlock, run SQL Developer and start Tools|Monitor Sessions

Related Questions

Navigate

• Browse (All)
• Browse R
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.