THE Database FOR Network Computing
Oracle Oracle Oracle Oracle Oracle Oracle (Oracle Object for OLE Oracle Developer) SQL Oracle8 Enterprise Edition R8.0.5 for Windows NT Oracle8 Enterprise Edition R8.0.5 for Solaris Oracle8 Oracle7 Oracle,Oracle7,Oracle8
2.3.1....7 2.3.2....9 3.2.1....13 3.2.2....14 3.2.3....16 3.4.1....21 3.4.2....22 3.5.1....23 3.5.2. EXPIRE_TIME...24 3.5.3....24
1. Oracle COMMIT ROLLBACK COMMIT ROLLBACK ( ) 1Oracle 1.1. COMMIT ROLLBACK COMMIT 7499 emp ROLLBACK 7521 emp 1
Oracle COMMIT ROLLBACK DDL CREATEDROPRENAMEALTER DDL COMMIT DDL Oracle CREATE DROPALTER DDL ROLLBACK 1.2. Oracle COMMIT ROLLBACK SAVEPOINT SAVEPOINT ( ) p1 ROLLBACK ROLLBACK; UPDATE SAVEPOINT A ROLLBACK TO A; UPDATE SAVEPOINT B ROLLBACK TO B; DELETE 2 2
3 ROLLBACK 1. 2. 3. DELETE UPDATE
2. Oracle (ISAM) Oracle Oracle 2.1. 1 1. 2. 7369 3. 5. COMMIT 4
1. 7934 2. 4. COMMIT 1. 2. 4. COMMIT COMMIT ROLLBACK UPDATE INSERT ( ) SQL COMMIT 5
2.2. Oracle 100 200 100 200 200 100 100 200 3 1. 2. 2:00 100 200 2:00 100 200( 2) 100 Oracle ] 100 200( 2) Oracle 6
2.3. PM5:00 PM5:00 SERIALIZABLE Oracle7 R7.3 SET TRANSACTION 2.3.1. SET TRANSACTION READ ONLY READ WRITE READ ONLY SET TRANSACTION SET TRANSACTION COMMIT ROLLBACK ORA-01555 SET TRANSACTION SET TRANSACTION SET TRANSACTION COMMIT 7
INSERT UPDATEDELETESELECT FOR UPDATE DML 1 1. 3.B 8000 CLERK 4. B 5.COMMIT 6. 8
2.3.2. SET TRANSACTION ISOLATION LEVEL SERIALIZABLE SQL92 SERIALIZABLE SERIALIZABLE COMPATIBLE 7.3.0.0.0 READ COMMITED SET TRANSACTION DML Oracle INITRANS 3 ALTER SESSION SET ISOLATION_LEVEL=SERIALIZABLE 9
1 1. SERIALIZABLE 2. 8000 3. SERIALIZABLE 4. 8001 5. 8000 ORA-08177 DELETE 6. 7.ORA-08177 10
3. Oracle Oracle Oracle 3.1. Oracle INSERT/UPDATE/DELETE DML INSERT/UPDATE/DELETE 1 1. 9000 2. 4. SQL 9000 1 1. 9000 2. 9000 4. SQL 11
1 1. 2. 7900 100 3. 7900 5. SQL 6. 950 100 100 1150 SQL 3.2. Oracle LOCK TABLE SELECT FOR UPDATE 12
3.2.1. LOCK TABLE tablename viewname mode Oracle 22-1 EXCLUSIVE MODE SHARE MODE LOCK TABLE IN NOWAIT SHARE MODESELECT FOR UPDATE ORA-00054 NOWAIT 1 LOCK ANY TABLE LOCK TABLE SQL 1)emp emp 2)emp emp LOCK TABLE IN SHARE MODESELECT FOR UPDATE 3)emp 4)emp dept 13
DML SQL ORA-01555 LOCK TABLE IN SHARE MODESELECT FOR UPDATE LOCK TABLE IN SHARE MODE SELECT FOR UPDATE SELECT FOR UPDATE UPDATE DELETE SELECT FOR UPDATE UPDATE DELETE OLTP 3.2.2. SELECT FOR UPDATE FOR UPDATE OF OF FROM OF NOWAIT ORA-00054 NOWAIT SELECT FOR UPDATE NOWAIT NOWAIT 14
FOR UPDATE DISTINCT GROUP BY CURSOR 1) 7900 2) 7900 3) 7900 (emp ) (dept 4) 7900 (emp ) OF OF ORA-00904 emp.ename e.ename ename 15
FOR UPDATE FOR UPDATE FOR UPDATE 3.2.3. NOWAIT SELECT FOR UPDATE SELECT... FOR UPDATE(NOWAIT ) 1. 10 2. 3. 10 4.COMMIT 5. 16
SELECT... FOR UPDATE(NOWAIT ) : 1: ORA-00054: NOWAIT 1. 10 2. 10 NOWAIT ORA-00054 NOWAIT NOWAIT FOR UPDATE FOR UPDATE NOWAIT 3.3. Oracle ( ) 4 EMP DEPT SCOTT DEPT EMP DEPTNO DEPT EMP 1 N ( )DEPT ( )EMP DELETE CASCADE DELETE 17
DELETE UPDATE INSERT DELETE UPDATE INSERT/UPDATE/DELETE DELETE SQL DEPT DEPTNO=10 EMP DEPTNO=10 (EMP) SYSTEM SQL*Plus SQL DEPT EMP EMP LMODE LMODE 2 (RS,SS) 3 (RX,SX) 4 (S) 5 (SRX,SSX) 6 (X) 1 18
UPDATE (DEPTNO ) SCOTT SQL ROLLBACK deptno=40 UPDATE SYSTEM EMP DELETE EMP ( ) DELETE CASCADE EMP DEPTNO SQL DELETE SCOTT SQL 19
SYSTEM EMP UPDATE SCOTT SQL SYSTEM (EMP) 3.4. A A B C D B 5 20
ABCD DCBA A B C C C 3.4.1. Oracle SQL 1. 7369 2. 7934 3. 7934 4. 7369 5.Oracle SQL SQL SQL 6. 7. 7369 SQL 21
SQL ROLLBACK COMMIT SQL SQL INIT.ORA 30 3.4.2. SELECT FOR UPDATE NOWAIT 3.5. Oracle Oracle Oracle Enterprise Manager Instance Manager ALTER SYSTEM KILL SESSION Net8/SQL*Net expire_time 5 6 22
3.5.1. ALTER SYSTEM KILL SESSION SID 10SERIAL 344 SQL ALTER SYSTEM ALTER SYSTEM SID SERIAL V$SESSION SYSTEM V$SESSION USERNAME OSUSER PROGRAM Oracle STATUS STATUS KILLED Instance Manager Oracle Enterprise Manager Instance Manager 1.Instance Manager Oracle Enterprise Manager Instance Manager 2. [ ] 23
3. [ ][ ] 3.5.2. EXPIRE_TIME EXPIRE_TIME TCP/IP keepalive SQL*Net V2.1(Oracle7 R7.1) Windows NT Oracle7 V2.1 sqlnet.ora n [] ( 10)Windows NT n 16 keepalive TCP/IP 3.5.3. SYSTEM foo SCOTT 24
Oracle 6 1,000 10 1,000 10 1,000 5,000 1,000 7,000 2,000 (Optimistic Lock) (Pessimistic Lock) 25
4.1. (Pessimistic Lock) 10 1,000 5,000 10 6,000 6,000 4.2. (Optimistic Lock) 10 1,000 10 1,000 5,000 6,000 1,000 26
5. Oracle Oracle 5.1. Oracle 7369 SMITH : 1: ORA-00054: NOWAIT 1. 7369 NOWAIT 2. 7369 NOWAIT 3. 4.A NOWAIT 27
5.2. Oracle SELECT 2 28
1. 7369 2. 7369 3. 4.1 3 5. 6. 7.2 6 FOR UPDATE ( 2 ) NOWAIT NOWAIT NOWAIT 29
6. ( ) 6.1. ( ) SELECT / TYPE1 TYPE2 / / TYPE3 3 ( ) TYPE1 SELECT TYPE2 1. 2. 3. 4. 30
TYPE3: / 6.2. 1 ( ) ( ) / TYPE4 / SELECT TYPE5 / TYPE6 TYPE7 4 ( ) TYPE4TYPE2 TYPE5TYPE3 TYPE6 TYPE7 6.3. 6.16.2 31
7. 1998 11 24 Design & Migration Services Copyright ORACLE CORPORATION JAPAN 1998 32