Oracle8i 分散システム, リリース8.1

Transcription

1 Oracle8i J

2 Oracle8i, 8.1 J Oracle8i Distributed Database Systems, Release 2(8.1.6) A Jason Durbin, Lance Ashdown William Creekbaum, Steve Bobrowski, Katherine Hughes, Pavna Jain, Peter Vasterd, John Bellemore, Anupam Bhide, Roger Bodamer, Jacco Draaijer, Diana Foch-Laurentz, Nina Lewis, Raghu Mani, Basab Maulik, Denise Oertel, Paul Raveling, Kendall Scott, Gordon Smith, Katia Tarkhanov, Randy Urbano, Sandy Venning, Eric Voss, and others Copyright 1996, 1999, Oracle Corporation. All rights reserved. Printed in Japan. * Oracle Corporation Oracle Corporation Restricted Rights Notice Restricted Rights Notice Programs delivered subject to the DOD FAR Supplement are "commercial computer software" and use, duplication, and disclosure of the Programs, including documentation, shall be subject to the licensing restrictions set forth in the applicable Oracle license agreement. Otherwise, Programs delivered subject to the Federal Acquisition Regulations are "restricted computer software" and use, duplication, and disclosure of the Programs shall be subject to the restrictions in FAR , Commercial Computer Software - Restricted Rights (June, 1987). Oracle Corporation, 500 Oracle Parkway, Redwood City, CA

3 ... xi Oracle8i... xii xii xiii... xiii... xiv... xv... xv... xv... xvi I / i

4 : SQL SQL SQL RPC / : ii

5 : iii

6 II : : DML : Oracle : : : : iv

7 7: ID PURGE_LOST_DB_ENTRY DBMS_TRANSACTION : : : DBA_2PC_PENDING : DBA_2PC_NEIGHBORS : : : DBA_2PC_PENDING RECO v

8 III 6 Oracle Oracle SQL Transparent Gateway Generic Connectivity Transparent Gateway Oracle Oracle : : : Oracle : SQL HS HS vi

9 DBMS_HS Generic Connectivity Generic Connectivity Generic Connectivity SQL Generic Connectivity Oracle SQL Generic Connectivity Generic Connectivity Agent ODBC OLE DB ODBC OLE DB SQL OLE DB FS OLE DB : SQL DBMS_HS_PASSTHROUGH SQL SQL OCI Oracle Oracle Tools Oracle vii

10 Oracle Server Oracle A HS_COMMIT_POINT_STRENGTH... A-2 HS_DB_DOMAIN... A-2 HS_DB_INTERNAL_NAME... A-3 HS_DB_NAME... A-3 HS_DESCRIBE_CACHE_HWM... A-3 HS_FDS_CONNECT_INFO... A-4 HS_FDS_SHAREABLE_NAME... A-5 HS_FDS_TRACE_LEVEL... A-5 HS_FDS_TRACE_FILE_NAME... A-5 HS_LANGUAGE... A-6 HS_NLS_DATE_FORMAT... A-7 HS_NLS_DATE_LANGUAGE... A-7 HS_NLS_NCHAR... A-8 HS_OPEN_CURSORS... A-8 HS_ROWID_CACHE_SIZE... A-9 HS_RPC_FETCH_REBLOCKING... A-9 HS_RPC_FETCH_SIZE... A-10 B C Generic Connectivity... C-2 Oracle... C-2... C-3... C-4... C-5 Generic Connectivity... C-6 ALL_CATALOG... C-6 ALL_COL_COMMENTS... C-6 ALL_CONS_COLUMNS... C-6 ALL_CONSTRAINTS... C-7 viii

11 ALL_IND_COLUMNS... C-7 ALL_INDEXES... C-8 ALL_OBJECTS... C-10 ALL_TAB_COLUMNS... C-11 ALL_TAB_COMMENTS... C-12 ALL_TABLES... C-12 ALL_USERS... C-14 ALL_VIEWS... C-14 DICTIONARY... C-14 USER_CATALOG... C-15 USER_COL_COMMENTS... C-15 USER_CONS_COLUMNS... C-15 USER_CONSTRAINTS... C-15 USER_IND_COLUMNS... C-16 USER_INDEXES... C-17 USER_OBJECTS... C-19 USER_TAB_COLUMNS... C-19 USER_TAB_COMMENTS... C-20 USER_TABLES... C-21 USER_USERS... C-22 USER_VIEWS... C-23 D ODBC Oracle... D-2 OLE DB Oracle... D-2 ix

12 x

13 Oracle8i Oracle8i Oracle8i Oracle8i Enterprise Edition Oracle8i Oracle8i Enterprise Edition Enterprise Edition xi

14 Oracle8i Oracle8i ODBC OLE DB Generic Connectivity 8 Generic Connectivity C Generic Connectivity D Transparent Gateway Oracle Oracle Generic Connectivity HS ODBC Oracle Oracle8i 1-27 LDAP DML DDL INSERT 4-3 DML DDL Kerberos 1-24 Oracle Advanced Security SSL Single Sign-on HS_FDS_CONNECT_INFO HS_FDS_SHAREABLE_NAME HS_FDS_TRACE HS_FDS_TRACE_FILE_NAME xii

15 Oracle Server SQL Oracle Oracle SQL Oracle Oracle Oracle SELECT Oracle 7-15 V$HS_AGENT V$HS_SESSION / I II 4 5 Oracle DBA Oracle 2 xiii

16 / III 6 Oracle 7 Transparent Gateway Oracle 8 Generic Connectivity 9 A B C Generic Connectivity D Oracle Oracle Transparent Gateway ODBC OLE DB Oracle Oracle Oracle Generic Connectivity Agent Oracle8i Oracle ODBC OLE DB Oracle Generic Connectivity C Generic Connectivity D Generic Connectivity III Oracle8i PL/SQL xiv

17 Oracle Oracle Oracle DBA Oracle8i Oracle8i Oracle Oracle 2 7 Transparent Gateway Oracle 8 Generic Connectivity 3 9 A B C Generic Connectivity D 1 6 Oracle xv

18 SQLPLUS username/password INTO TABLENAME 'table' { } VARCHAR (length) ASC DESC 1 {column_name array_def} DECIMAL (digits [, precision ]) < > SQL WHERE x <operator> x... WHERE column_1 <operator> x AND column_2 <operator> y [AND...] xvi

19 I

20

21 1 Oracle 1-1

22 Oracle 1 Oracle / / 1 Oracle 1-1 HQ MFG SALES 3 MFG Manufacturing 1 PRODUCTS HQ DEPT MFG HQ DEPT MFG SELECT * FROM dept; MFG 1-2 Oracle8i

23 1-1 MFG.ACME.COM HQ.ACME.COM Oracle Oracle Oracle. SALES.ACME.COM.... Oracle Oracle Oracle Oracle8i SQL Oracle7 1-3

24 / Oracle Oracle Oracle : : Oracle Oracle8i 1-4 Oracle8i

25 1 Oracle Oracle Oracle Oracle Oracle Oracle8i Oracle Transparent Gateway Oracle Oracle Sybase Oracle Sybase Transparent Gateway Oracle ODBC OLE DB Generic Connectivity Oracle Oracle Transparent Gateway : III HS Oracle8i Server Oracle Transparent Gateway HS Oracle Gateway Oracle Transparent Gateway : 6 Oracle Transparent Gateway Oracle Transparent Gateway Oracle Oracle Transparent Gateway Oracle Oracle Oracle Oracle Oracle SQL : Oracle Transparent Gateway 1-5

26 Generic Connectivity Generic Connectivity Oracle8i ODBC OLE DB 2 Oracle8i ODBC OLE DB Generic Connectivity Agent Generic Connectivity ODBC OLE DB : Generic Connectivity 8 Generic Connectivity 1 Oracle Oracle SQL Oracle Oracle 1 SQL Oracle SQL SQL NLS Oracle 1 Oracle Oracle SQL Oracle SQL Oracle SQL Oracle Oracle SQL Oracle PL/SQL Oracle Oracle8i 1-6 Oracle8i

27 Generic Connectivity Oracle8i ODBC OLE DB Oracle : Oracle Transparent Gateway / Oracle HQ 2 DEPT SALES EMP 1-7

28 1-2 Oracle Net8 Net8 DEPT EMP HQ SALES INSERT INTO DELETE FROM DEPT..; SELECT... FROM COMMIT;. 1-8 Oracle8i

29 1-2 HQ DEPT SELECT * FROM dept; 1-2 HQ SALES EMP SELECT * FROM emp@sales; 2 1 : 1-9

30 Oracle A A B B A B A B 1-3 SCOTT HQ.ACME.COM EMP 1-10 Oracle8i

31 1-3 SCOTT Select * FROM emp PUBLIC SYNONYM emp -> emp@hq.acme.com EMP 1 JANE SCOTT/TIGER HR JANE SCOTT JANE SCOTT HR HR 1-11

32 JANE SCOTT SCOTT HR SCOTT Oracle Advanced Security Advanced Networking Option CREATE DATABASE LINK SQL MTS 4 1 MTS 1-12 Oracle8i

33 : Oracle8i Net8 A/PA/P HR A/P HR HR A/P HR HR A/P HR SCOTT SCOTT LINK$ SSL : 1-17 DB_DOMAIN DB_NAME 1-13

34 EDU COM ORG ACME_TOOLS ACME_AUTO DIVISION1 DIVISION2 DIVISION3 ASIA AMERICAS EUROPE JAPAN US MEXICO UK GERMANY HQ FINANCE SALES MFTG SALES HQ SALES SALES SALES SALES HUMAN_RESOURCES.EMP HUMAN_RESOURCES.EMP MFTG COM ACME_TOOLS DIVISION3 MFTG MFTG.DIVISION3.ACME_TOOLS.COM US.AMERICAS.ACME_ AUTO.COM UK.EUROPE.ACME_AUTO.COM SALES 1-14 Oracle8i

35 AMERICAS SALES EUROPE SALES SALES.US.AMERICAS.ACME_AUTO.COM SALES.UK.EUROPE.ACME_AUTO.COM : 2-2 SALES.US.ORACLE.COM SALES.US.ORACLE.COM GLOBAL_NAMES TRUE HQ HQ.ACME.COM GLOBAL_NAMES TRUE HQ.ACME.COM Oracle DB_DOMAIN 2-4 GLOBAL_NAMES FALSE HQ.ACME.COM FOO : Oracle Advanced Replication SALES CREATE PUBLIC DATABASE LINK sales.division3.acme.com USING 'sales1'; : GLOBAL_NAMES Oracle8i 1-15

36 Oracle DBA_DB_LINKS ALL_DB_LINKS PUBLIC DBA_DB_LINKS ALL_DB_LINKS PUBLIC DBA_DB_LINKS ALL_DB_LINKS PL/SQL PL/SQL Oracle Oracle Names Oracle PL/SQL Oracle 1-16 Oracle8i

37 Oracle Oracle Names : SYSTEM SYSTEM Oracle SYSTEM : CURRENT_USER X.509 Oracle Advanced Security : Oracle8i Advanced Security / CREATE PUBLIC DATABASE LINK hq USING 'hq'; CREATE PUBLIC DATABASE LINK hq CONNECT TO CURRENT_USER USING 'hq'; CREATE PUBLIC DATABASE LINK hq CONNECT TO jane IDENTIFIED BY doe USING 'hq'; :

38 Oracle REMOTE_OS_AUTHENT REMOTE_OS_AUTHENT REMOTE_OS_AUTHENT TRUE FALSE Oracle Advanced Security JOE SCOTT TIGER JANE JANE SCOTT/TIGER LINK$ SELECT ANY TABLE O7_DICTIONARY_ACCESSIBILITY TRUE 1-18 Oracle8i

39 : O7_DICTIONARY_ACCESSIBILITY TRUE JANE HR SCOTT/TIGER O7_ DICTIONARY_ACCESSIBILITY TRUE SELECT ANY TABLE JANE LINK$ HR SCOTT/TIGER JANE HR TIGER HR SCOTT JANE SCOTT JANE SCOTT X.509 CURRENT_USER JANE HR JANE HR SCOTT SCOTT SSL JANE SCOTT SELECT SCOTT JANE SCOTT.P SCOTT SCOTT ID JANE SCOTT.P SCOTT 1-19

40 SCOTT.P JANE JANE DB1 GUEST : : 1-24 CREATE DATABASE LINK SALES.US.AMERICAS.ACME_AUTO.COM SQL SQL CREATE DATABASE LINK sales.us.americas.acme_auto.com USING 'sales_us'; SALES_US SALES CREATE DATABASE LINK foo CONNECT TO CURRENT_USER USING 'am_sls'; AM_SLS SALES CREATE DATABASE LINK sales.us.americas.acme_auto.com CONNECT TO scott IDENTIFIED BY tiger USING 'sales_us'; SALES_US SALES TIGER SCOTT CREATE PUBLIC DATABASE LINK sales CONNECT TO scott IDENTIFIED BY tiger USING 'rev'; REV SALES TIGER SCOTT CREATE SHARED PUBLIC DATABASE LINK sales.us.americas.acme_ auto.com CONNECT TO scott IDENTIFIED BY tiger AUTHENTICATED BY anupam IDENTIFIED BY bhide USING 'sales'; SALES SALES BHIDE ANUPAM TIGER SCOTT : 2-8 CREATE DATABASE LINK Oracle8i SQL 1-20 Oracle8i

41 SQL FOO EMP SELECT * FROM emp@foo; Oracle schema.schema_object@global_database_name schema schema_object global_database_name DB_NAME DB_DOMAIN GLOBAL_NAMES FALSE SALES.DIVISION3.ACME.COM SELECT * FROM scott.emp@sales.division3.acme.com; # emp table in scott's schema SELECT loc FROM scott.dept@sales.division3.acme.com; 1-21

42 GLOBAL_NAMES FALSE SALES.DIVISION3.ACME.COM FOO SELECT name FROM # link name different from global name Oracle SELECT * FROM emp@hq.acme.com; EMP@HQ.ACME.COM EMP SELECT * FROM emp; : 2-28 Oracle Oracle 1 1 Oracle Oracle SYSTEM CONNECT system/manager@sales1 HQ.ACME.COM HQ SCOTT JANE SELECT * FROM scott.emp@hq.acme.com; INSERT INTO jane.accounts@hq.acme.com (acc_no, acc_name, balance) VALUES (5001, 'BOWER', 2000); 1-22 Oracle8i

43 UPDATE SET balance = balance + 500; DELETE FROM jane.accounts@hq.acme.com WHERE acc_name = 'BOWER'; DESCRIBE DESCRIBE ANALYZE JANE SCOTT JANE SCOTT JANE SET ROLE MTS SSL NT Oracle 1-23

44 Oracle 1 DISTRIBUTED_LOCK_TIMEOUT DISTRIBUTED_TRANSACTIONS COMMIT_ POINT_STRENGTH : 2 7 Transparent Gateway Oracle Kerberos 5 DCE 1-24 Oracle8i

45 Oracle : Oracle8i Advanced Security PUBLIC CREATE PUBLIC DATABASE LINK foo USING 'sales'; CONNECT TO AUTHENTICATED BY CREATE DATABASE LINK sales CONNECT TO scott IDENTIFIED BY tiger USING 'sales'; CREATE SHARED PUBLIC DATABASE LINK sales CONNECT TO mick IDENTIFIED BY jagger AUTHENTICATED BY david IDENTIFIED BY bowie USING 'sales'; Oracle ID/

46 ID/ 2 ID/ Kerberos end-to-end end-to-end JANE JANE 1-26 Oracle8i

47 : 2-8 Oracle SSL Net8 Oracle Advanced Security Net8 Oracle Windows NT Oracle : Oracle8i Net8 Oracle8i Advanced Security 1 SQL FRED CREATE USER fred IDENTIFIED GLOBALLY AS 'CN=fred adams,o=oracle,c=england'; FRED FRED Oracle 1-27

48 Oracle8i : JANE BILL SCOTT HR HR GUEST GUEST JANE BILL SCOTT 3 GUEST HR Oracle8i

49 1-5 LDAP SSL DB1 SSL SSL SSL DB2 SCOTT DB1 DB2 DB1 clerk1 guest bill scott DB2 clerk2 guest jane scott DB1 DB2 DB1 DB2 CREATE CREATE USER guest IDENTIFIED GLOBALLY AS ''; CREATE ROLE clerk1 GRANT select ON emp; CREATE PUBLIC DATABASE LINK db2_link CONNECT AS CURRENT_USER USING 'db2'; CREATE USER guest IDENTIFIED GLOBALLY; CREATE ROLE clerk2 GRANT select ON dept; 1-29

50 SCOTT DB2 DB1 1. SCOTT DB1 SSL 2. SCOTT SELECT e.ename, d.loc FROM emp e, d WHERE e.deptno=d.deptno 3. DB1 DB2 SSL 4. DB1 SCOTT DB1 SCOTT CLERK1 GUEST 5. DB2 SCOTT DB2 SCOTT CLERK2 GUEST 6. SCOTT CLERK2 DB2 GUEST SELECT DB1 7. DB1 DB2 SCOTT : Oracle8i Net8 Oracle8i Advanced Security Oracle Advanced Security Net8 RSA RC4 DES Oracle Advanced Security 1-30 Oracle8i

51 : Oracle Advanced Security Oracle8i Net8 Oracle8i Advanced Security SQL HR.ACME.COM JANE SCOTT HR SCOTT SCOTT HR JANE HR SCOTT : Oracle Oracle Enterprise Manager SNMP 1-31

52 Oracle Enterprise Manager Enterprise Manager Oracle Enterprise Manager GUI Oracle Enterprise Manager Enterprise Manager Oracle Enterprise Manager Oracle Enterprise Manager Oracle Oracle Net8 SQL PL/SQL Enterprise Manager Enterprise Manager Enterprise Manager 60 Oracle 150 SNMP SNMP SNMP Oracle SNMP HP OpenView Digital POLYCENTER Manager NetView IBM NetView/6000 Novell NetWare Management System SunSoft SunNet Manager 1-32 Oracle8i

53 : SNMP Oracle SNMP 1 SQL SQL SQL SQL SQL 2 SQL 1 SALES SCOTT DEPT SELECT * FROM scott.dept@sales.us.americas.acme_auto.com; 1 SALES SCOTT DEPT UPDATE scott.dept@mktng.us.americas.acme_auto.com SET loc = 'NEW YORK' WHERE deptno = 10; 1-33

54 : 1 1 SQL SALES SELECT ename, dname FROM scott.emp e, scott.dept@sales.us.americas.acme_auto.com d WHERE e.deptno = d.deptno; 2 PL/SQL PL/SQL SALES BEGIN UPDATE scott.dept@sales.us.americas.acme_auto.com SET loc = 'NEW YORK' WHERE deptno = 10; UPDATE scott.emp SET deptno = 11 WHERE deptno = 10; END; COMMIT; 1 SQL SQL SQL SQL : SQL Oracle8i 1-34 Oracle8i

55 1 SALES 2 UPDATE scott.dept@sales.us.americas.acme_auto.com SET loc = 'NEW YORK' WHERE deptno = 10; UPDATE scott.emp@sales.us.americas.acme_auto.com SET deptno = 11 WHERE deptno = 10; COMMIT; 2 1 SALES UPDATE scott.dept@sales.us.americas.acme_auto.com SET loc = 'NEW YORK' WHERE deptno = 10; UPDATE scott.emp SET deptno = 11 WHERE deptno = 10; COMMIT; : Oracle8i Oracle 1-35

56 Oracle 2 2 DML : Oracle 2 4 SELECT * FROM joan.dept@sales.acme.com SELECT * FROM emp@mktg.us.acme.com dept sales acme.com emp mktg us.acme.com SQL SELECT * FROM scott.emp@orders.us.acme.com; Oracle ORDERS.US.ACME.COM 1-36 Oracle8i

57 1. SQL Oracle Names Server SQL SELECT * FROM emp@prod1.us.oracle.com PROD1 US.ORACLE.COM SQL DB_DOMAIN DB_NAME CONNECT scott/tiger@locdb SELECT * FROM scott.emp@orders; LOCDB US.ACME.COM ORDERS ORDERS.US.ACME.COM Oracle CONNECT scott/tiger@locdb SELECT * from scott.emp;

58 Oracle CONNECT CONNECT TO... IDENTIFIED BY CONNECT TO CURRENT_USER USING Oracle CREATE DATABASE LINK k1 USING 'prod' CREATE DATABASE LINK k2 CONNECT TO scott IDENTIFIED BY tiger USING 'prod' CREATE DATABASE LINK k3 CONNECT TO CURRENT_USER USING 'prod' CREATE DATABASE LINK k4 CONNECT TO CURRENT_USER Oracle SQL SQL 1-38 Oracle8i

59 SQL SALES.DIVISION3.ACME.COM HQ.DIVISION3.ACME.COM Oracle Names Server : EMP TSMITH SCOTT CONNECT scott/tiger@hq CREATE PUBLIC DATABASE LINK sales.division3.acme.com CONNECT TO guest IDENTIFIED BY network USING 'dbstring'; JWARD CONNECT jward/bronco@hq CREATE DATABASE LINK sales.division3.acme.com CONNECT TO tsmith IDENTIFIED BY radio; UPDATE tsmith.emp@sales.division3.acme.com SET deptno = 40 WHERE deptno = 10; 1. Oracle JWARD UPDATE 2. JWARD JWARD.SALES.DIVISION3.ACME.COM SALES 1-39

60 Oracle 3. SCOTT Oracle DBSTRING 4. TSMITH/RADIO SALES 5. EMP Oracle TSMITH EMP 6. : SALES EMP SCOTT TSMITH EMP SALES SALES TSMITH.EMP 1-40 : HQ CREATE PUBLIC DATABASE LINK sales.division3.acme.com CONNECT TO guest IDENTIFIED BY network USING 'dbstring'; HQ CONNECT scott/tiger@hq CREATE DATABASE LINK sales.division3.acme.com; DELETE FROM emp@sales WHERE empno = 4299; 1-40 Oracle8i

61 DELETE 1. Oracle SCOTT DELETE DELETE FROM WHERE empno = 4299; SCOTT Oracle / CREATE PUBLIC DATABASE LINK sales.division3.acme.com CONNECT TO guest IDENTIFIED BY network USING 'dbstring'; 4. Oracle DBSTRING 5. Oracle SCOTT/TIGER SCOTT EMP 6. EMP 7. PL/SQL 1-41

62 Oracle SALES.UK.ACME.COM HR.UK.ACME.COM 2 SALES CREATE VIEW employee_names AS SELECT ename FROM scott.emp@hr; CREATE SYNONYM employee FOR scott.emp@hr; EMPLOYEE scott.emp@hr.uk.acme.com 1: SALES.UK.ACME.COM HR.UK.ACME.COM SALES.US.ORACLE.COM HR.US.ACME.COM 1-42 Oracle8i

63 SALES SELECT * FROM employee_names SELECT * FROM employee SELECT * FROM scott.emp@hr.uk.acme.com SELECT * FROM scott.emp@hr.uk.acme.com SELECT * FROM scott.emp@hr.us.acme.com SELECT * FROM scott.emp@hr.uk.acme.com 2: SALES.UK.ACME.COM HR.UK.ACME.COM SALES.US.ORACLE.COM SALES SELECT * FROM employee_names SELECT * FROM employee SELECT * FROM scott.emp@hr.uk.acme.com SELECT * FROM scott.emp@hr.uk.acme.com SELECT * FROM scott.emp@hr.us.acme.com SELECT * FROM scott.emp@hr.uk.acme.com EMPLOYEE_NAMES EMPLOYEE HR.UK.ACME.COM 1-43

64 RPC : 3 Oracle 1 Oracle Oracle CREATE PUBLIC SYNONYM emp FOR scott.emp@sales.us.americas.acme_auto.com CREATE PUBLIC SYNONYM dept FOR scott.dept@sales.us.americas.acme_auto.com 1-44 Oracle8i

65 SELECT ename, dname FROM e, d WHERE e.deptno = d.deptno; SELECT ename, dname FROM emp e, dept d WHERE e.deptno = d.deptno; SQL COMMIT Oracle SELECT INSERT UPDATE DELETE SQL SQL COMMIT SAVEPOINT ROLLBACK 1 SCN SCN 1 SQL 1-45

66 SCN Oracle Oracle8i RPC PL/SQL RPC PL/SQL SALES DEL_ EMP 1257 BEGIN END; RPC Oracle8i SQL Oracle SQL DRIVING_SITE NO_MERGE INDEX Oracle 1-46 Oracle8i

67 : 3-5 Oracle Oracle Oracle Oracle8i NCHAR NLS HS NLS HS NLS_LANG / NLS_LANGUAGE / Oracle NLS_CHARACTERSET NLS_TERRITORY HS_LANGUAGE Oracle Transparent Gateway NLS_NCHAR HS_NLS_NCHAR Oracle Transparent Gateway 1-47

68 / / 1-6 Oracle 1-6 / NLS NLS_LANG = Oracle NLS Oracle Oracle8i

69 1-7 NLS NLS_LANG = Oracle NLS Oracle Oracle Oracle NLS 1-8 Transparent Gateway Oracle NLS Oracle NLS Transparent Gateway NLS 1-49

70 1-8 NLS NLS_LANG = Oracle NLS Oracle NLS Oracle NLS Oracle HS Transparent Gateway NCHAR Transparent Gateway NCHAR Oracle Transparent Gateway NCHAR Transparent Gateway : Oracle8i 1-50 Oracle8i

71 2 : 2-1

72 1 : 2 DB_NAME 8 SALES DB_DOMAIN US.ACME.COM 2-2 Oracle8i

73 DB_NAME DB_DOMAIN SALES AU.ORACLE.COM SALES.AU.ORACLE.COM SALES US.ORACLE.COM SALES.US.ORACLE.COM MKTG US.ORACLE.COM MKTG.US.ORACLE.COM PAYROLL NONPROFIT.ORG PAYROLL.NONPROFIT.ORG DB_DOMAIN DB_NAME DB_DOMAIN ALTER DATABASE DB_DOMAIN HQ MNFG MNFG MNFG SN1 SN2 HQ.ACME.COM HQ HQ :

74 V$PARAMETER MNFG MNFG globalnames.sql COL name FORMAT a12 COL value FORMAT a6 SELECT name, value FROM v$parameter WHERE name = 'global_names' / NAME VALUE global_names FALSE GLOBAL_ NAME SELECT * FROM global_name; GLOBAL_NAME SALES.AU.ORACLE.COM ALTER DATABASE DB_DOMAIN database domain ALTER DATABASE RENAME GLOBAL_NAME database.domain; 2-4 Oracle8i

75 1. SELECT * FROM global_name; GLOBAL_NAME SALES.AU.ORACLE.COM 2. ALTER DATABASE ALTER DATABASE RENAME GLOBAL_NAME sales.us.oracle.com; 3. GLOBAL_NAME SELECT * FROM global_name; GLOBAL_NAME SALES.US.ORACLE.COM : Oracle DB_DOMAIN 1. SALES.US.ACME.COM GLOBAL_NAME CONNECT sys/sys_pwd@sales.us.acme.com SELECT * FROM global_name; GLOBAL_NAME SALES.US.ACME.COM 2-5

76 2. V$PARAMETER DB_DOMAIN SELECT name, value FROM v$parameter WHERE name = 'db_domain'; NAME VALUE db_domain US.ACME.COM 3. HQ CREATE DATABASE LINK hq USING 'sales'; 4. USER_DB_LINKS SELECT db_link FROM user_db_links; DB_LINK HQ.US.ACME.COM US.ACME.COM 5. SALES SALES SALES.JP.ACME.COM ALTER DATABASE RENAME GLOBAL_NAME TO sales.jp.acme.com; SELECT * FROM global_name; GLOBAL_NAME SALES.JP.ACME.COM 6. V$PARAMETER DB_DOMAIN SELECT name, value FROM v$parameter WHERE name = 'db_domain'; NAME VALUE db_domain US.ACME.COM 2-6 Oracle8i

77 DB_DOMAIN ALTER DATABASE RENAME GLOBAL_NAME ALTER DATABASE DB_DOMAIN DB_ DOMAIN 7. SUPPLY USER_DB_LINKS SUPPLY CREATE DATABASE LINK supply USING 'supply'; SELECT db_link FROM user_db_links; DB_LINK HQ.US.ACME.COM SUPPLY.JP.ACME.COM JP.ACME.COM DB_DOMAIN 8. SALES JP.ACME.COM ASIA.JP.ACME.COM ALTER DATABASE RENAME GLOBAL_NAME TO sales.asia.jp.acme.com; SELECT * FROM global_name; GLOBAL_NAME SALES.ASIA.JP.ACME.COM 9. V$PARAMETER DB_DOMAIN SELECT name, value FROM v$parameter WHERE name = 'db_domain'; NAME VALUE db_domain US.ACME.COM 2 ALTER DATABASE RENAME 10. WAREHOUSE USER_DB_LINKS 2-7

78 CREATE DATABASE LINK warehouse USING 'warehouse'; SELECT db_link FROM user_db_links; DB_LINK HQ.US.ACME.COM SUPPLY.JP.ACME.COM WAREHOUSE.ASIA.JP.ACME.COM : DB_NAME DB_DOMAIN Oracle8i CREATE DATABASE LINK CREATE PUBLIC DATABASE LINK CREATE SESSION 2-8 Oracle8i

79 ROLE_SYS_PRIVS privs.sql SELECT DISTINCT privilege AS "Database Link Privileges" FROM role_sys_privs WHERE privilege IN ( 'CREATE SESSION','CREATE DATABASE LINK', 'CREATE PUBLIC DATABASE LINK') / Database Link Privileges CREATE DATABASE LINK CREATE PUBLIC DATABASE LINK CREATE SESSION 3 link_name CREATE DATABASE LINK link_name...; SQL CREATE DATABASE LINK supply.us.acme.com; SUPPLY ID/ SCOTT TIGER SCOTT/TIGER 2-9

80 SQL CREATE DATABASE LINK link_2 CONNECT TO jane IDENTIFIED BY doe USING 'us_supply'; CREATE DATABASE LINK link_1 CONNECT TO CURRENT_USER USING 'us_supply'; US_SUPPLY LINK_2 ID/ JANE/DOE US_SUPPLY LINK_1 ID/ : 1-17 Oracle Advanced Security : CREATE DATABASE LINK Oracle8i SQL PUBLIC link_name CREATE PUBLIC DATABASE LINK link_name...; SQL CREATE PUBLIC DATABASE LINK supply.us.acme.com; SUPPLY ID/ SCOTT TIGER SCOTT/TIGER 2-10 Oracle8i

81 SQL CREATE PUBLIC DATABASE LINK pu_ link CONNECT TO CURRENT_USER USING 'supply'; CREATE PUBLIC DATABASE LINK sales.us.acme.com CONNECT TO jane IDENTIFIED BY doe; SUPPLY PU_LINK ID/ : 1-17 SALES ID/ JANE/DOE : CREATE PUBLIC DATABASE LINK Oracle8i SQL Oracle Names Server Oracle8i Net8 CREATE DATABASE LINK... CONNECT TO username IDENTIFIED BY password...; 2-11

82 SQL CREATE PUBLIC DATABASE LINK supply.us.acme.com CONNECT TO scott AS tiger; CREATE DATABASE LINK foo CONNECT TO jane IDENTIFIED BY doe USING 'finance'; SUPPLY ID/ SCOTT/TIGER FINANCE FOO ID/ JANE/DOE : 1-17 CONNECT TO dblink net_service_name CREATE [SHARED] [PUBLIC] DATABASE LINK dblink... [USING 'net_service_name']; CREATE DATABASE LINK sales.division3.acme.com USING 'sales'; 2-12 Oracle8i

83 CONNECT TO CURRENT_USER Oracle Advanced Security dblink net_service_name CREATE [SHARED] [PUBLIC] DATABASE LINK dblink CONNECT TO CURRENT_USER [USING 'net_service_name']; SALES CREATE DATABASE LINK sales CONNECT TO CURRENT_USER USING 'sales'; : : Oracle8i SQL Oracle Parallel Server Oracle Server TCP/IP HQ.ACME.COM Oracle Parallel Server HQ HQ_1 HQ_2 2 HQ 2-13

84 CREATE PUBLIC DATABASE LINK USING 'string_to_hq_1'; CREATE PUBLIC DATABASE LINK USING 'string_to_hq_2'; CREATE PUBLIC DATABASE LINK hq.acme.com USING 'string_to_hq'; 2 USING 3 USING SELECT * FROM scott.emp@hq.acme.com@hq_1 : Oracle8i

85 3 /MTS MTS MTS

86 CREATE DATABASE LINK SHARED CREATE SHARED DATABASE LINK dblink_name [CONNECT TO username IDENTIFIED BY password] [CONNECT TO CURRENT_USER] AUTHENTICATED BY schema_name IDENTIFIED BY password [USING 'service_name']; SCOTT KEITH SALES CREATE SHARED DATABASE LINK link2sales CONNECT TO scott IDENTIFIED BY tiger AUTHENTICATED BY keith IDENTIFIED BY richards USING 'sales'; SHARED AUTHENTICATED BY AUTHENTICATED BY AUTHENTICATED BY : CREATE DATABASE LINK Oracle8i SQL Oracle8i

87 : SERVER=DEDICATED 2-1. Oracle Oracle Oracle

88 2-2 Oracle Oracle : Oracle8i Net Oracle8i

89 linkname ALTER SESSION CLOSE DATABASE LINK linkname; DROP PUBLIC DATABASE LINK dblink DROP [PUBLIC] DATABASE LINK dblink; 1. SQL*Plus CONNECT 2. USER_DB_LINKS SELECT db_link FROM user_db_links; DB_LINK SALES.US.ORACLE.COM MKTG.US.ORACLE.COM 2 rows selected. 3. DROP DATABASE LINK 2-19

90 DROP DATABASE LINK sales.us.oracle.com; 1. DROP PUBLIC DATABASE LINK CONNECT AS SYSDBA 2. DBA_DB_LINKS SELECT db_link FROM user_db_links WHERE owner = 'PUBLIC'; DB_LINK DBL1.US.ORACLE.COM SALES.US.ORACLE.COM INST2.US.ORACLE.COM RMAN2.US.ORACLE.COM 4 rows selected. 3. DROP PUBLIC DATABASE LINK DROP PUBLIC DATABASE LINK sales.us.oracle.com; OPEN_LINKS 1 1 SQL 3 OPEN_LINKS 3 OPEN_LINKS 4 OPEN_LINKS 0 : OPEN_LINKS Oracle8i SQL 2-20 Oracle8i

91 DBA_DB_LINKS ALL_DB_LINKS USER_DB_LINKS OWNER USER_* PUBLIC DB_LINK USERNAME NULL PASSWORD USER_* HOST CREATED USER_DB_LINKS DBA 2-21

92 COL owner FORMAT a10 COL username FORMAT a8 HEADING "USER" COL db_link FORMAT a30 COL host FORMAT a7 HEADING "SERVICE" SELECT * FROM dba_db_links / SQL>@link_script OWNER DB_LINK USER SERVICE CREATED SYS TARGET.US.ACME.COM SYS inst1 23-JUN-99 PUBLIC DBL1.UK.ACME.COM BLAKE ora51 23-JUN-99 PUBLIC RMAN2.US.ACME.COM inst2 23-JUN-99 PUBLIC DEPT.US.ACME.COM inst2 23-JUN-99 JANE DBL.UK.ACME.COM BLAKE ora51 23-JUN-99 SCOTT EMP.US.ACME.COM SCOTT inst2 23-JUN-99 6 rows selected. USER_DB_LINKS DBA LINK$ DBA SELECT ANY TABLE O7_DICTIONARY_ACCESSIBILITY TRUE : LINK$ DBA O7_ DICTIONARY_ACCESSIBILITY FALSE SQL*Plus col userid format a10 col password format a10 SELECT userid,password FROM sys.link$ WHERE password IS NOT NULL / SQL>@linkpwd 2-22 Oracle8i

93 USERID PASSWORD SYS ORACLE BLAKE TYGER SCOTT TIGER 3 rows selected. DBA LINK$ AUTHENTICATED BY... IDENTIFIED BY... DBA SELECT ANY TABLE O7_DICTIONARY_ACCESSIBILITY TRUE : LINK$ DBA O7_ DICTIONARY_ACCESSIBILITY FALSE SQL*Plus col authusr format a10 col authpwd format a10 SELECT authusr as userid, authpwd as password FROM sys.link$ WHERE password IS NOT NULL / USERID PASSWORD ELLIE MAY 1 row selected. : Oracle8i Oracle8i SQL 2-23

94 COL owner FORMAT a8 COL db_link FORMAT a15 COL username FORMAT a8 HEADING "CON_USER" COL password FORMAT a8 HEADING "CON_PWD" COL authusr FORMAT a8 HEADING "AUTH_USER" COL authpwd format a8 HEADING "AUTH_PWD" COL host FORMAT a7 HEADING "SERVICE" COL created FORMAT a10 SELECT DISTINCT d.owner,d.db_link,d.username,l.password, l.authusr,l.authpwd,d.host,d.created FROM dba_db_links d, sys.link$ l WHERE password IS NOT NULL AND d.username = l.userid / OWNER DB_LINK CON_USER CON_PWD AUTH_USE AUTH_PWD SERVICE CREATED JANE DBL.ACME.COM BLAKE TYGER ELLIE MAY ora51 23-JUN-99 PUBLIC DBL1.ACME.COM SCOTT TIGER ora51 23-JUN-99 SYS TARGET.ACME.COM SYS ORACLE inst1 23-JUN-99 SYSDBA V$DBLINK GV$DBLINK IN_TRANSACTION YES Oracle Parallel Server 2-24 Oracle8i

95 1 DB_LINK OWNER_ID LOGGED_ON HETEROGENEOUS NO YES PROTOCOL OPEN_CURSORS IN_TRANSACTION UPDATE_SENT COMMIT_POINT_ STRENGTH INST_ID GV$DBLINK COL db_link FORMAT a25 COL owner_id FORMAT HEADING "OWNID" COL logged_on FORMAT a5 HEADING "LOGON" COL heterogeneous FORMAT a5 HEADING "HETER" COL protocol FORMAT a8 COL open_cursors FORMAT 999 HEADING "OPN_CUR" COL in_transaction FORMAT a3 HEADING "TXN" COL update_sent FORMAT a6 HEADING "UPDATE" COL commit_point_strength FORMAT HEADING "C_P_S" SELECT * FROM v$dblink / 2-25

96 DB_LINK OWNID LOGON HETER PROTOCOL OPN_CUR TXN UPDATE C_P_S INST2.ACME.COM 0 YES YES UNKN 0 YES YES 255 EMP DEPT 2 CREATE VIEW company AS SELECT a.empno, a.ename, b.dname FROM emp a, dept@hq.acme.com b WHERE a.deptno = b.deptno; 2-26 Oracle8i

97 JWARD.DEPT DEPTNO DNAME MARKETING SALES HQ SALES SCOTT.EMP EMPNO ENAME JOB SMITH ALLEN WARD JONES CLERK SALESMAN SALESMAN MANAGER MGR HIREDATE 17 DEC FEB JUN APR 93 SAL COMM DEPTNO COMPANY EMPNO ENAME DNAME SMITH ALLEN WARD JONES MARKETING SALES SALES MARKETING

98 SELECT * FROM company; SQL Oracle8i

99 CREATE [PUBLIC] synonym_name FOR PUBLIC synonym_name schema object_name database_link_name CREATE PUBLIC SYNONYM object_name object_name : HQ SCOTT.EMP CREATE PUBLIC SYNONYM emp FOR scott.emp@hq.acme.com; SCOTT.EMP@HQ.ACME.COM SQL EMP EMP HQ HR 2-29

100 SCOTT EMP SCOTT SCOTT SALES EMP PL/SQL 3 SQL CREATE PROCEDURE fire_emp (enum NUMBER) AS BEGIN DELETE FROM emp@hq.acme.com WHERE empno = enum; END; 2-30 Oracle8i

101 FIRE_EMP 2 CREATE SYNONYM emp FOR emp@hq.acme.com; FIRE_EMP CREATE PROCEDURE fire_emp (enum NUMBER) AS BEGIN DELETE FROM emp WHERE empno = enum; END; EMP@HQ DML SCOTT LOCAL_DB CONNECT scott/tiger@local_db CREATE PROCEDURE fire_emp (enum NUMBER) AS BEGIN EXECUTE term_emp@hq.acme.com; END; SCOTT CONNECT scott/tiger@hq.acme.com CREATE PROCEDURE term_emp (enum NUMBER) AS BEGIN DELETE FROM emp WHERE empno = enum; END; 2-31

102 LOCAL_DB FIRE_EMP HQ.ACME.COM TERM_EMP SCOTT SALES.ACME.COM CREATE PROCEDURE fire_emp (enum NUMBER) AS BEGIN DELETE FROM WHERE empno = enum; END; PEGGY SUPPLY.ACME.COM SCOTT SALES SQL> CONNECT peggy/hill@supply SQL> CREATE PUBLIC SYNONYM emp FOR scott.fire_emp@sales.acme.com; SUPPLY SALES EXECUTE DML SELECT INSERT UPDATE DELETE SELECT... FOR UPDATE LOCK TABLE 2-32 Oracle8i

103 SELECT... FOR UPDATE 2 SELECT e.empno, e.ename, d.dname FROM scott.emp@sales.division3.acme.com e, jward.dept@hq.acme.com d WHERE e.deptno = d.deptno; UPDATE INSERT DELETE LOCK TABLE JWARD EMP SCOTT.SALES EMP INSERT INTO scott.emp@sales.division3.acme.com SELECT * FROM jward.emp; SQL LONG LONG RAW Oracle DBMS_SQL DDL CREATE ALTER DROP DBMS_SQL.PARSE@link_name(crs, 'drop table emp', v7); DDL ANALYZE LIST CHAINED ROWS SYSDATE USER UID USERENV SQL : USERENV 2-33

104 : : DBMS_SQL Oracle8i PL/SQL Oracle 5-28 : JANE SALES SCOTT SLDB CONNECT jane/doe@local CREATE PUBLIC DATABASE LINK sales.division3.acme.com CONNECT TO SCOTT IDENTIFIED BY TIGER USING 'sldb'; SALES.DIVISION3.ACME.COM SCOTT 2-34 Oracle8i

105 : SCOTT EMP SQL SELECT * FROM emp@sales.division3.acme.com; DANA SALES SLDB SCOTT SCOTT CONNECT dana/sculley@local CREATE SHARED PUBLIC DATABASE LINK sales.division3.acme.com CONNECT TO scott IDENTIFIED BY tiger AUTHENTICATED BY scott IDENTIFIED BY tiger USING 'sldb'; MTS SALES SCOTT 1 SALES.DIVISION3.ACME.COM LARRY SLDB CONNECT larry/oracle@local CREATE PUBLIC DATABASE LINK redwood USING 'sldb'; 2-35

106 : REDWOOD SCOTT SCOTT FOX FOX FOX EMP FOX SALES.DIVISION3.ACME.COM FOX EMP CONNECT SELECT * FROM emp@redwood; NEIL SALES SLDB ID/ CRAZY/HORSE CONNECT neil/young@local CREATE SHARED PUBLIC DATABASE LINK sales.division3.acme.com AUTHENTICATED BY crazy IDENTIFIED BY horse USING 'sldb'; 1 SALES.DIVISION3.ACME.COM 2-36 Oracle8i

107 : SALES SLDB CONNECT CREATE PUBLIC DATABASE LINK sales.division3.acme.com CONNECT TO CURRENT_USER USING 'sldb'; : SCOTT EMP FIRE_EMP FORD CONNECT scott/tiger@local_db CREATE PROCEDURE fire_emp (enum NUMBER) AS BEGIN DELETE FROM emp@sales.division3.acme.com WHERE empno=enum; END; GRANT EXECUTE ON FIRE_EMP TO FORD; FORD SCOTT CONNECT ford/fairlane@local_db EXECUTE PROCEDURE scott.fire_emp (enum 10345); FORD SCOTT.FIRE_EMP SCOTT FORD SCOTT SCOTT FORD 2-37

108 : : FORD Oracle8i PL/SQL SCOTT SCOTT 2-38 Oracle8i

109 3 Oracle Oracle8i : Oracle8i Oracle Oracle8i 3-1

110 SQL SELECT COMMIT ROLLBACK SELECT * FROM emp@sales; COMMIT; CLOSE DATABASE LINK ALTER SESSION SALES ALTER SESSION CLOSE DATABASE LINK sales; ALTER SESSION 3-2 Oracle8i

111 : Oracle ORA ORA Oracle / SALES HQ 2 DML HQ : ALTER SESSION Oracle8i SQL : Oracle8i Oracle 3-3

112 2 5 WHERE r1.salary + r2.salary > SELECT SELECT SELECT SELECT e.empno, e.ename, d.deptno, d.dname FROM (SELECT empno, ename from emp@orc1.world) e, dept d; Oracle 3-4 Oracle8i

113 CREATE TABLE CREATE TABLE AS ( SELECT l.a, l.b, r1.c, r1.d, r1.e, r2.b, r2.c FROM local l, remote1 r1, remote2 r2 WHERE l.c = r.c AND r1.c = r2.c AND r.e > 300 ); CREATE TABLE AS ( SELECT l.a, l.b, v.c, v.d, v.e FROM ( SELECT r1.c, r1.d, r1.e, r2.b, r2.c FROM remote1 r1, remote2 r2 WHERE r1.c = r2.c AND r1.e > 300 ) v, local l WHERE l.c = r1.c ); v SELECT

114 SQL Oracle OPTIMIZER_MODE CHOOSE COST OPTIMZER_MODE ALTER SESSION OPTIMIZER_MODE ALTER SESSION OPTIMIZER_MODE = CHOOSE; ALTER SESSION OPTIMIZER_MODE = COST; : OPTIMZER_MODE Oracle8i 3-6 Oracle8i

115 1 ANALYZE EMP DEPT ANALYZE TABLE emp COMPUTE STATISTICS; ANALYZE TABLE dept COMPUTE STATISTICS; DBA/ALL/USER_TABLES DBA/ALL/USER_TAB_COL_STATISTICS DBA/ALL/USER_TAB_COLUMNS DBA/ALL/USER_TAB_HISTOGRAMS DBA/ALL/USER_PART_HISTOGRAMS DBA/ALL/USER_SUBPART_HISTOGRAMS DBA/ALL/USER_USTATS : ANALYZE ANALYZE TABLE remote@remote.com COMPUTE STATISTICS; ANALYZE : ANALYZE Oracle8i SQL Oracle8i 3-7

116 DBMS_ STATS ANALYZE GATHER_INDEX_STATS GATHER_TABLE_STATS GATHER_SCHEMA_STATS GATHER_DATABASE_STATS SCOTT.DEPT BEGIN DBMS_STATS.GATHER_TABLE_STATS ('scott', 'dept'); END; : DBMS_STATS Oracle8i PL/SQL CPU NO_MERGE SQL NO_ MERGE 3-8 DRIVING_SITE 3-8 Oracle8i

117 NO_MERGE NO_MERGE SQL 3-8 SELECT SELECT /* with argument */ SELECT /*+NO_MERGE(v)*/ t1.x, v.avg_y FROM t1, (SELECT x, AVG(y) AS avg_y FROM t2 GROUP BY x) v, WHERE t1.x = v.x AND t1.y = 1; /* in query block */ SELECT t1.x, v.avg_y FROM t1, (SELECT /*+NO_MERGE*/ x, AVG(y) AS avg_y FROM t2 GROUP BY x) v, WHERE t1.x = v.x AND t1.y = 1; : NO_MERGE Oracle8i DRIVING_SITE DRIVING_SITE DRIVING_SITE SELECT SELECT /*+DRIVING_SITE(dept)*/ * FROM emp, dept@remote.com WHERE emp.deptno = dept.deptno; : DRIVING_SITE Oracle8i 3-9

118 : EXPLAIN PLAN Oracle8i : utlxplan.sql utlxplan.sql PLAN_ TABLE SQL EXPLAIN PLAN FOR EXPLAIN PLAN FOR SELECT d.dname FROM dept d WHERE d.deptno IN (SELECT deptno FROM emp@orc2.world GROUP BY deptno HAVING COUNT (deptno) >3 ) / 3-10 Oracle8i

119 SQL : utlxpls.sql utlxpls.sql SELECT Plan Table Operation Name Rows Bytes Cost Pstart Pstop SELECT STATEMENT NESTED LOOPS VIEW REMOTE TABLE ACCESS BY INDEX RO DEPT INDEX UNIQUE SCAN PK_DEPT SQL SELECT SELECT other FROM plan_table WHERE operation = 'REMOTE'; SELECT DISTINCT "A1"."DEPTNO" FROM "EMP" "A1" GROUP BY "A1"."DEPTNO" HAVING COUNT("A1"."DEPTNO")>3 : OTHER SET LONG

120 4 PL/SQL EXCEPTION PL/SQL NO_DATA_FOUND SQL ORA ORA RAISE_APPLICATION_ERROR() BEGIN... EXCEPTION WHEN ZERO_DIVIDE THEN /*... handle the exception */ END; WHEN RAISE_APPLICATION_ERROR PRAGMA_EXCEPTION_INIT DECLARE null_salary EXCEPTION; PRAGMA EXCEPTION_INIT(null_salary, ); BEGIN... RAISE_APPLICATION_ERROR(-20101, 'salary is missing');... EXCEPTION WHEN null_salary THEN... END; PL/SQL ORA Oracle8i

121 II

122

123 4 Oracle8i 2 : 4-1

124 DEPT Net8 HQ EMP SALES Net8 MAINT BLDG SCOTT SCOTT SALES HQ MAINT UPDATE scott.dept@hq.us.acme.com SET loc = 'REDWOOD SHORES' WHERE deptno = 10; UPDATE scott.emp SET deptno = 11 WHERE deptno = 10; UPDATE scott.bldg@maint.us.acme.com SET room = 1225 WHERE room = 1163; COMMIT; 4-2 Oracle8i

125 : 1 2 DML DDL DML DDL DML DDL CREATE TABLE AS SELECT DELETE INSERT LOCK TABLE SELECT SELECT FOR UPDATE DML DDL INSERT SELECT INSERT UPDATE DELETE table_expression_clause DML/DDL XA OCI 4-3

126 SELECT DML COMMIT ROLLBACK SAVEPOINT Oracle8i 1 2 : SQL Oracle8i SQL : : Oracle 1 Oracle Oracle8i

127 INSERT INTO orders...; UPDATE warehouse...; UPDATE finance...;. COMMIT; SALES.ACME.COM WAREHOUSE.ACME.COM FINANCE.ACME.COM 4-5

128 SALES WAREHOUSE FINANCE 4-2 SALES WAREHOUSE FINANCE SALES.ACME.COM WAREHOUSE FINANCE SALES SALES 4-6 Oracle8i

129 4-2 SALES WAREHOUSE FINANCE 4-2 SALES WAREHOUSE FINANCE SALES.ACME.COM SQL 4-7

130 1 4-3 SALES 4-3 WAREHOUSE SALES COMMIT_POINT_STRENGTH = 75 COMMIT_POINT_STRENGTH = 100 FINANCE COMMIT_POINT_STRENGTH = Oracle8i

131 REDO 2 COMMIT_POINT_STRENGTH : COMMIT_POINT_STRENGTH Oracle8i Oracle

132 4-4 SALES.ACME.COM (45) HQ.ACME.COM (165) WAREHOUSE.ACME.COM (140) FINANCE.ACME.COM (45) HR.ACME.COM (45) 1 ROLLBACK Oracle8i

133 2 : 5-5 COMMIT_POINT_STRENGTH Oracle8i 2 Oracle8i

134 2 REDO REDO : prepared read-only abort SQL 4-12 Oracle8i

135 2 3 : 1 Oracle 2 : SET TRANSACTION READ ONLY : SET TRANSACTION READ ONLY 2 SCN READ ONLY

136 REDO REDO COMMIT ROLLBACK Oracle8i

137 REDO REDO 7. SCN SQL SCN Oracle SCN 1 SQL SCN :

138 Oracle8i SCN SCN SCN Oracle 2 Oracle RECO RECO Oracle 4-16 Oracle8i

139 Oracle LOCAL REMOTE 2 SCOTT LOCAL LOCAL REMOTE LOCAL 2 REMOTE REMOTE COMMIT_POINT_SITE = 200 COMMIT_POINT_SITE = SCOTT 1. SCOTT LOCAL REMOTE LOCAL 4. RECO 4-17

140 LOCAL 6 4 REMOTE 3 REMOTE 2 REMOTE 5 REMOTE COMMIT_POINT_STRENGTH = 200 COMMIT_POINT_STRENGTH = SCOTT 1. SCOTT LOCAL REMOTE 4. REMOTE 5. REMOTE 6. RECO 4-18 Oracle8i

141 : DBA_2PC_PENDING DBA_2PC_NEIGHBORS COMMIT FORCE ROLLBACK FORCE : SCN Oracle SCN Oracle SCN REDO Oracle SCN SCN SCN 3. SCN 4-19

142 : SCN SCN SCN : Oracle8i SALES.ACME.COM WAREHOUSE.ACME.COM SALES WAREHOUSE 1: DML SQL*Plus SQL SALES WAREHOUSE CONNECT scott/tiger@sales.acme.com...; INSERT INTO orders...; UPDATE inventory@warehouse.acme.com...; INSERT INTO orders...; UPDATE inventory@warehouse.acme.com...; COMMIT; SQL SQL Oracle8i

143 : SQL INSERT INTO orders...; UPDATE warehouse...; INSERT INTO orders...; UPDATE warehouse...; COMMIT; SALES.ACME.COM SQL WAREHOUSE.ACME.COM SALES SALES.ACME.COM SALES SALES.ACME.COM WAREHOUSE.ACME.COM SALES.ACME.COM WAREHOUSE.ACME.COM SQL SQL

144 : 2: Oracle COMMIT 4-8 SALES.ACME.COM : SALES.ACME.COM WAREHOUSE.ACME.COM 3: 1. Oracle WAREHOUSE.ACME.COM 2. WAREHOUSE.ACME.COM REDO SALES.ACME.COM WAREHOUSE.ACME.COM 3. WAREHOUSE.ACME.COM SALES.ACME.COM 4-22 Oracle8i

145 : 4-9 SALES.ACME.COM 1. Sales to Warehouse Please prepare 2. Warehouse to Sales Prepared WAREHOUSE.ACME.COM 4: 1. SALES.ACME.COM WAREHOUSE.ACME.COM 2. REDO WAREHOUSE.ACME.COM 4-23

146 : 5: : REDO 4-10 SALES.ACME.COM SALES WAREHOUSE.ACME.COM 4-24 Oracle8i

147 : 4-10 SALES.ACME.COM Sales to Warehouse: Commit WAREHOUSE.ACME.COM 7: COMMIT 4-25

148 : 4-26 Oracle8i

149 5 : 5-1

150 DISTRIBUTED_TRANSACTIONS DISTRIBUTED_LOCK_TIMEOUT DISTRIBUTED_RECOVERY_ CONNECTION_HOLD_TIME COMMIT_POINT_STRENGTH DISTRIBUTED_TRANSACTIONS Oracle SQL ORA-02042: DISTRIBUTED_TRANSACTIONS = Oracle8i

151 10 11 DML : DISTRIBUTED_TRANSACTIONS ORA-2042 DISTRIBUTED_TRANSACTIONS DISTRIBUTED_ TRANSACTIONS DISTRIBUTED_TRANSACTIONS SQL RECO Oracle8i : DISTRIBUTED_TRANSACTIONS Oracle8i 5-3

152 SQL Oracle8i DML SQL ORA-02049: Timeout: DISTRIBUTED_LOCK_TIMEOUT DISTRIBUTED_LOCK_TIMEOUT = Oracle 30 : DISTRIBUTED_LOCK_TIMEOUT Oracle8i 5-4 Oracle8i

153 DML ORA-01591: identifier SQL ID 2 DISTRIBUTED_RECOVERY_CONNECTION_HOLD_TIME COMMIT_POINT_STRENGTH 5-5

154 COMMIT_POINT_STRENGTH = 200 : 4-8 PC PC ID ID 5-6 Oracle8i

155 DBA_2PC_PENDING ID Oracle8i 5-1 DBA_2PC_PENDING LOCAL_TRAN_ID GLOBAL_TRAN_ID STATE MIXED HOST COMMIT# integer.integer.integer : LOCAL_TRAN_ID GLOBAL_TRAN_ID global_db_name.db_hex_id.local_tran_id db_hex_id 8 16 ID : LOCAL_TRAN_ID GLOBAL_TRAN_ID 5-2 DBA_2PC_PENDING STATE YES 5-2 DBA_2PC_PENDING STATE collecting 5-7

156 5-2 DBA_2PC_PENDING STATE prepared committed forced commit forced abort 1 DBA_2PC_PENDING COL local_tran_id FORMAT a13 COL global_tran_id FORMAT a30 COL state FORMAT a8 COL mixed FORMAT a3 COL host FORMAT a10 COL commit# FORMAT a10 SELECT local_tran_id, global_tran_id, state, mixed, host, commit# FROM dba_2pc_pending / LOCAL_TRAN_ID GLOBAL_TRAN_ID STATE MIX HOST COMMIT# HQ.ACME.COM.ef192da commit no dlsun LOCAL_TRAN_ID GLOBAL_TRAN_ID 5-8 Oracle8i

157 DBA_2PC_NEIGHBORS ID ID Oracle8i 5-3 DBA_2PC_NEIGHBORS LOCAL_TRAN_ID IN_OUT DATABASE DBUSER_OWNER integer.integer.integer : LOCAL_TRAN_ID GLOBAL_TRAN_ID.DBA_ 2PC_PENDING IN OUT 5-9

158 5-3 DBA_2PC_NEIGHBORS INTERFACE C N IN_OUT OUT C N IN_OUT IN C N DBA_2PC_PENDING COL local_tran_id FORMAT a13 COL in_out FORMAT a6 COL database FORMAT a25 COL dbuser_owner FORMAT a15 COL interface FORMAT a3 SELECT local_tran_id, in_out, database, dbuser_owner, interface FROM dba_2pc_neighbors / SQL> CONNECT sys/sys_pwd@hq.acme.com LOCAL_TRAN_ID IN_OUT DATABASE DBUSER_OWNER INT out SALES.ACME.COM SYS C SALES SALES SALES Oracle8i

159 Oracle 2 Oracle : ORA-02050: ID ORA-02051: ORA-02054: ID 1 Oracle8i 5-11

160 ORA ID ID DBA_ 2PC_PENDING DBA_ROLLBACK_SEGS 2 Oracle8i DBA_2PC_PENDING DBA_2PC_PENDING TRAN_COMMENT COMMIT COMMENT 5-12 Oracle8i

161 COMMIT COMMENT 'Finance/Accts_pay/Trans_type 10B'; DBA_2PC_PENDING ADVICE SQL ALTER_SESSION ADVISE DML EMP EMP SQL ALTER SESSION ADVISE COMMIT; INSERT INTO ; /*advice to commit at HQ */ ALTER SESSION ADVISE ROLLBACK; DELETE FROM ; /*advice to roll back at SALES*/ ALTER SESSION ADVISE NOTHING; FORCE ID COMMIT ROLLBACK : STATE 5-13

162 FORCE TRANSACTION FORCE ANY TRANSACTION ID SQL COMMIT FORCE 'transaction_id'; transaction_id DBA_2PC_PENDING LOCAL_ TRAN_ID GLOBAL_TRAN_ID DBA_2PC_PENDING ID LOCAL_TRAN_ID SQL COMMIT FORCE ' '; SCN SCN SCN SCN SCN 5-14 Oracle8i

163 ID SALES.ACME.COM.55d1c DBA_2PC_ PENDING SCN SCN SCN COMMIT FORCE 'SALES.ACME.COM.55d1c ', ; FORCE TRANSACTION FORCE ANY TRANSACTION SQL ROLLBACK FORCE 'transaction_id'; transaction_id DBA_2PC_PENDING LOCAL_ TRAN_ID GLOBAL_TRAN_ID ID ROLLBACK FORCE '2.9.4'; : RECO DBA_ 2PC_PENDING.STATE COLLECTING COMMITTED PREPARED COMMIT FORCE ROLLBACK FORCE FORCED COMMIT FORCED ROLLBACK 5-15

164 DBA_2PC_ PENDING MIXED YES ID DBMS_TRANSACTION PURGE_LOST_DB_ ENTRY PURGE_LOST_DB_ENTRY trans_id DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('trans_id'); SQL*Plus EXECUTE DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY(' '); 2 TPMonitor : DBMS_TRANSACTION Oracle8i PL/SQL DBMS_TRANSACTION 5-16 Oracle8i

165 : STATE Collecting Rolled back Rolled back PURGE_LOST_DB_ ENTRY Committed Committed Committed PURGE_LOST_DB_ ENTRY Prepared Unknown Prepared Forced commit Forced rollback Forced commit Forced rollback Unknown Committed PURGE_LOST_DB_ ENTRY Unknown Rolled back PURGE_LOST_DB_ ENTRY Mixed Committed PURGE_MIXED Mixed Rolled back PURGE_MIXED : DBMS_TRANSACTION Oracle8i PL/SQL :

166 : 5-1 SALES.ACME.COM WAREHOUSE.ACME.COM HQ.ACME.COM WAREHOUSE SALES HQ DBA_2PC_PENDING ID 3. DBA_2PC_NEIGHBORS 4. 1: 2: DBA_2PC_PENDING 3: DBA_2PC_NEIGHBORS 4: 5-18 Oracle8i

167 : 5: 6: DBA_2PC_PENDING 1: ORA-01591: ID ID 2: DBA_2PC_PENDING SQL*Plus WAREHOUSE DBA_2PC_PENDING CONNECT SELECT * FROM sys.dba_2pc_pending WHERE local_tran_id = ' '; Column Name Value LOCAL_TRAN_ID GLOBAL_TRAN_ID SALES.ACME.COM.55d1c STATE prepared MIXED no ADVICE TRAN_COMMENT Sales/New Order/Trans_type 10B FAIL_TIME 31-MAY-91 FORCE_TIME RETRY_TIME 31-MAY-91 OS_USER SWILLIAMS OS_TERMINAL TWA139: HOST system1 DB_USER SWILLIAMS COMMIT# 5-19

168 : ID ID ID global_database_name.hhhhhhhh.local_transaction_id global_database_name hhhhhhh local_transaction_id 16 ID ID ID WAREHOUSE LOCAL_TRAN_ID GLOBAL_TRAN_ID STATE prepared WAREHOUSE TRAN_COMMENT Sales/New Order/Trans_type 10B 5-20 Oracle8i

169 : 3: DBA_2PC_NEIGHBORS DBA_2PC_NEIGHBORS WAREHOUSE CONNECT SELECT * FROM dba_2pc_neighbors WHERE local_tran_id = ' ' ORDER BY sess#, in_out; Column Name Value LOCAL_TRAN_ID IN_OUT in DATABASE SALES.ACME.COM DBUSER_OWNER SWILLIAMS INTERFACE N DBID F4 SESS# 1 BRANCH 0100 DBA_2PC_NEIGHBORS IN_OUT = inin_out = out IN_OUT in out DATABASE DBUSER_OWNER 5-21

170 : DATABASE IN_OUT WAREHOUSE SALES IN_OUT DATABASE in SALES.ACME.COM WAREHOUSE DBUSER_OWNER SWILLIAMS DBUSER_OWNER SWILLIAMS INTERFACE INTERFACE N INTERFACE WAREHOUSE 4: ID 2 3 : 2 3 SALES HQ 2 3 SALES SALES DBA_2PC_PENDING SQL> CONNECT sys/sys_pwd@sales.acme.com SQL> SELECT * FROM sys.dba_2pc_pending > WHERE global_tran_id = SALES.ACME.COM.55d1c ; Column Name Value LOCAL_TRAN_ID GLOBAL_TRAN_ID SALES.ACME.COM.55d1c STATE prepared MIXED no 5-22 Oracle8i

171 : ADVICE TRAN_COMMENT FAIL_TIME FORCE_TIME RETRY_TIME OS_USER OS_TERMINAL HOST DB_USER COMMIT# Sales/New Order/Trans_type 10B 31-MAY MAY-91 SWILLIAMS TWA139: system1 SWILLIAMS SALES SALES DBA_2PC_NEIGHBORS SELECT * FROM dba_2pc_neighbors WHERE global_tran_id = 'SALES.ACME.COM.55d1c ' ORDER BY sess#, in_out; 3 WAREHOUSE HQ WAREHOUSE Column Name Value LOCAL_TRAN_ID IN_OUT OUT DATABASE WAREHOUSE.ACME.COM DBUSER_OWNER SWILLIAMS INTERFACE N DBID 55d1c563 SESS# 1 BRANCH 1 HQ 5-23

172 : Column Name Value LOCAL_TRAN_ID IN_OUT OUT DATABASE HQ.ACME.COM DBUSER_OWNER ALLEN INTERFACE C DBID SESS# 1 BRANCH 1 SALES ID ID 2 SALES HQ 1 HQ HQ DBA_2PC_PENDING SELECT * FROM dba_2pc_pending WHERE global_tran_id = 'SALES.ACME.COM.55d1c '; Column Name Value LOCAL_TRAN_ID GLOBAL_TRAN_ID SALES.ACME.COM.55d1c STATE COMMIT MIXED NO ACTION TRAN_COMMENT Sales/New Order/Trans_type 10B FAIL_TIME 31-MAY-91 FORCE_TIME RETRY_TIME 31-MAY-91 OS_USER SWILLIAMS OS_TERMINAL TWA139: HOST SYSTEM1 DB_USER SWILLIAMS COMMIT# Oracle8i

173 : ID STATE COMMIT COMMIT# : SALES ID SQL> CONNECT SQL> COMMIT FORCE SALES.ACME.COM.55d1c ; WAREHOUSE ID SQL> CONNECT sys/sys_pwd@warehouse.acme.com SQL> COMMIT FORCE SALES.ACME.COM.55d1c ; 6: DBA_2PC_PENDING Oracle8i 2 DBA_2PC_PENDING 5-1 DBA_2PC_PENDING DBA_2PC_PENDING.MIXED MIXED=yes RECO 5-25

174 RECO RECO RECO COMMIT COMMENT 2 COMMENT COMMIT COMMENT 'ORA-2PC-CRASH-TEST-n'; n 1 n Oracle8i

175 COMMIT COMMENT 'ORA-2PC-CRASH-TEST-7'; ORA-02054: ORA-02059: ORA-2PC-CRASH-TEST-7 DBA_2PC_PENDING RECO RECO Oracle8i RECO RECO RECO RECO RECO ENABLE/DISABLE DISTRIBUTED RECOVERY ALTER SYSTEM 2 RECO RECO ALTER SYSTEM DISABLE DISTRIBUTED RECOVERY; RECO ALTER SYSTEM ENABLE DISTRIBUTED RECOVERY; : MS-DOS PC RECO 5-27

176 : Oracle Oracle SCN SCN Oracle SCN SCN SQL 2 SCN SCN SCN SCN SCN 2 SCN 1 2 SELECT DML 2 UPDATE SELECT SELECT 2 SCN SCN SELECT * FROM dual@remote SCN 5-28 Oracle8i

177 III

178

179 6 Oracle Oracle Oracle 6-1

180 HS Oracle8i Oracle Oracle Oracle SQL Oracle Oracle Oracle Oracle Oracle Oracle API Oracle Oracle Oracle Transparent Gateway ODBC OLE DB Generic Connectivity Oracle Oracle : Oracle SQL Oracle Oracle Oracle Oracle Oracle Oracle Oracle CREATE DATABASE LINK link_name CONNECT TO user IDENTIFIED BY password USING 'non_oracle_system'; Oracle HS SQL PL/SQL Oracle Oracle Oracle Oracle SELECT * FROM EMP@non_oracle_system; 6-2 Oracle8i

181 Oracle SQL Oracle SQL SQL Oracle8i Server Informix Sybase Oracle Oracle Transparent Gateway 8 Oracle Transparent Gateway 1 Generic Connectivity Oracle Transparent Gateway Generic Connectivity Agent : Oracle Transparent Gateway 7 Transparent Gateway Oracle Generic Connectivity Agent 8 Generic Connectivity SQL Oracle Oracle Oracle Oracle Oracle Oracle Oracle 1 Oracle Oracle Oracle 2 Oracle Oracle 2 Oracle Oracle Oracle 6-3

182 SQL SQL Oracle : 7-12 SQL SQL Oracle SQL Oracle HS 1. Oracle SQL Oracle SQL 2. Oracle 3. Oracle SQL Oracle SQL Oracle SQL Oracle SQL Oracle Oracle Oracle HS Oracle Oracle Oracle Transparent Gateway Generic Connectivity 6-4 Oracle8i

183 Transparent Gateway Oracle8i Server Oracle Transparent Gateway Transparent Gateway Oracle Oracle Oracle Generic Connectivity Oracle dbase dbase Sybase Sybase Transparent Gateway Transparent 6-1 Oracle Oracle8i Server Net8 Oracle8i Server Oracle Oracle8i Server Oracle Net8 Oracle 6-5

184 6-1 Oracle Oracle8i Oracle"X" Generic Connectivity Generic Connectivity Oracle Oracle ODBC OLE DB Transparent Gateway Oracle : Generic Connectivity 8 Generic Connectivity Oracle8i Server Oracle Oracle 6-2 Oracle8i Server Oracle Oracle8i Server Oracle SQL Oracle8i 6-6 Oracle8i

185 6-2 Oracle Oracle "X" Oracle"Y" Oracle8i Oracle 3 Oracle SQL Oracle Oracle Oracle 6-7