Oracle8i 8.1 2000 2 J00927-01
Oracle8i, 8.1 J00927-01 Oracle8i Distributed Database Systems, Release 2(8.1.6) A76960-01 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 52.227-19, Commercial Computer Software - Restricted Rights (June, 1987). Oracle Corporation, 500 Oracle Parkway, Redwood City, CA 94065.
... xi Oracle8i... xii 8.1.6... xii 8.1.5... xiii... xiii... xiv... xv... xv... xv... xvi I 1... 1-2... 1-2... 1-5 /... 1-7... 1-9... 1-10... 1-13... 1-13... 1-15... 1-16... 1-17 i
:... 1-20... 1-21... 1-23... 1-23... 1-24... 1-24... 1-31... 1-31... 1-33 SQL... 1-33 SQL... 1-34 SQL... 1-34... 1-35... 1-35 2... 1-35... 1-36... 1-38... 1-41... 1-44... 1-44 RPC... 1-46... 1-46... 1-47 /... 1-48... 1-48... 1-49 2... 2-2... 2-2... 2-3... 2-4... 2-4 :... 2-5... 2-8 ii
... 2-8... 2-9... 2-11... 2-13... 2-14... 2-14... 2-16... 2-16... 2-18... 2-18... 2-19... 2-20... 2-21... 2-21... 2-24... 2-26... 2-26... 2-28... 2-30... 2-32... 2-33 :... 2-34... 2-34... 2-35... 2-35... 2-36... 2-37 3... 3-2... 3-2... 3-3 iii
... 3-3... 3-4... 3-5... 3-8... 3-10... 3-12 II 4... 4-2... 4-3... 4-4 2... 4-4... 4-5... 4-6... 4-6... 4-7... 4-7... 4-8 2... 4-11... 4-11... 4-14... 4-16... 4-16... 4-17... 4-19... 4-19 :... 4-20 1: DML... 4-20 2: Oracle... 4-22 3:... 4-22 4:... 4-23 5:... 4-24 6:... 4-24 iv
7:... 4-25 5... 5-2... 5-2... 5-4... 5-5... 5-5... 5-6 ID... 5-6... 5-9... 5-10 2... 5-11... 5-12... 5-12... 5-13... 5-14... 5-15... 5-15 PURGE_LOST_DB_ENTRY... 5-16 DBMS_TRANSACTION... 5-16 :... 5-17 1:... 5-19 2: DBA_2PC_PENDING... 5-19 3: DBA_2PC_NEIGHBORS... 5-21 4:... 5-22 5:... 5-25 6: DBA_2PC_PENDING... 5-25... 5-26... 5-26 RECO... 5-27... 5-28 v
III 6 Oracle... 6-2 Oracle... 6-2... 6-3... 6-3... 6-3 SQL... 6-4... 6-4 Transparent Gateway... 6-5 Generic Connectivity... 6-6... 6-6... 6-7... 6-8 7 Transparent Gateway Oracle Oracle... 7-2 1:... 7-2 2:... 7-2 3: Oracle... 7-4 4:... 7-4... 7-5... 7-5... 7-9... 7-9... 7-9... 7-11... 7-11... 7-12 SQL... 7-13... 7-15... 7-15 HS... 7-16 HS... 7-16 vi
DBMS_HS... 7-17... 7-17... 7-18 8 Generic Connectivity Generic Connectivity... 8-2... 8-2 Generic Connectivity... 8-3 SQL... 8-5... 8-5 Generic Connectivity... 8-5 Oracle SQL... 8-5 Generic Connectivity... 8-6 Generic Connectivity Agent... 8-6... 8-7... 8-7 ODBC... 8-9 OLE DB... 8-11 ODBC... 8-12 OLE DB SQL... 8-14... 8-14 OLE DB FS... 8-15... 8-15 OLE DB... 8-16... 8-17 9 :... 9-2 SQL... 9-2 DBMS_HS_PASSTHROUGH... 9-2 SQL... 9-3 SQL... 9-3... 9-9 OCI Oracle Oracle Tools... 9-10 Oracle... 9-11 vii
Oracle Server... 9-11... 9-11 Oracle... 9-12 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
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
x
Oracle8i Oracle8i Oracle8i Oracle8i Enterprise Edition Oracle8i Oracle8i Enterprise Edition Enterprise Edition xi
Oracle8i 8.1.6 Oracle8i 8.1.6 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 1-24 8.1.6 HS_FDS_CONNECT_INFO HS_FDS_SHAREABLE_NAME HS_FDS_TRACE HS_FDS_TRACE_FILE_NAME xii
8.1.5 8.1.5 Oracle Server SQL Oracle Oracle SQL Oracle Oracle Oracle SELECT Oracle 7-15 V$HS_AGENT V$HS_SESSION / I 1 2 3 II 4 5 Oracle DBA Oracle 2 xiii
/ 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 8.1.6 8 Generic Connectivity C Generic Connectivity D Generic Connectivity III Oracle8i PL/SQL xiv
Oracle Oracle Oracle DBA Oracle8i Oracle8i Oracle 1 4 6 Oracle 2 7 Transparent Gateway Oracle 8 Generic Connectivity 3 9 A B C Generic Connectivity D 1 6 Oracle xv
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
I
1 Oracle 1-1
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
1-1 MFG.ACME.COM HQ.ACME.COM Oracle Oracle Oracle. SALES.ACME.COM.... Oracle Oracle Oracle Oracle8i SQL Oracle7 1-3
/ Oracle Oracle Oracle : : Oracle Oracle8i 1-4 Oracle8i
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
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
Generic Connectivity Oracle8i ODBC OLE DB Oracle : Oracle Transparent Gateway / Oracle 1 1-2 HQ 2 DEPT SALES EMP 1-7
1-2 Oracle Net8 Net8 DEPT EMP HQ SALES INSERT INTO EMP@SALES..; DELETE FROM DEPT..; SELECT... FROM EMP@SALES...; COMMIT;. 1-8 Oracle8i
1-2 HQ DEPT SELECT * FROM dept; 1-2 HQ SALES EMP SELECT * FROM emp@sales; 2 1 : 1-9
Oracle A A B B A B A B 1-3 SCOTT HQ.ACME.COM EMP 1-10 Oracle8i
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
JANE SCOTT SCOTT HR SCOTT Oracle Advanced Security Advanced Networking Option CREATE DATABASE LINK SQL MTS 4 1 MTS 1-12 Oracle8i
: 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
1-4 1-4 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
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
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
Oracle Oracle Names : 2-9 2-21 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'; : 2-11 1-17
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
: 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
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
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
GLOBAL_NAMES FALSE SALES.DIVISION3.ACME.COM FOO SELECT name FROM scott.emp@foo; # 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
UPDATE jane.accounts@hq.acme.com 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
Oracle 1 DISTRIBUTED_LOCK_TIMEOUT DISTRIBUTED_TRANSACTIONS COMMIT_ POINT_STRENGTH : 2 7 Transparent Gateway Oracle Kerberos 5 DCE 1-24 Oracle8i
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/ 2 1-25
ID/ 2 ID/ Kerberos end-to-end end-to-end JANE JANE 1-26 Oracle8i
: 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
Oracle8i : JANE BILL SCOTT HR HR GUEST GUEST JANE BILL SCOTT 3 GUEST HR 1-5 1-28 Oracle8i
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
SCOTT DB2 DB1 1. SCOTT DB1 SSL 2. SCOTT SELECT e.ename, d.loc FROM emp e, dept@db2_link 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
: 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
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
: 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
: 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
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; : 1 2 1 Oracle8i Oracle 1-35
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
1. SQL 2. 3. 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 SQL @ Oracle CONNECT scott/tiger@locdb SELECT * from scott.emp; 2 1-37
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
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
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
DELETE 1. Oracle SCOTT DELETE DELETE FROM emp@sales.division3.acme.com WHERE empno = 4299; 2. 3. 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
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
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
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
SELECT ename, dname FROM scott.emp@sales.us.americas.acme_auto.com e, scott.dept@sales.us.americas.acme_auto.com 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
SCN Oracle Oracle8i RPC PL/SQL RPC PL/SQL SALES DEL_ EMP 1257 BEGIN emp_mgmt.del_emp@sales.us.americas.acme_auto.com(1257); END; RPC Oracle8i SQL Oracle SQL DRIVING_SITE NO_MERGE INDEX Oracle 1-46 Oracle8i
: 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
/ / 1-6 Oracle 1-6 / NLS NLS_LANG = Oracle NLS Oracle 1-7 1-48 Oracle8i
1-7 NLS NLS_LANG = Oracle NLS Oracle Oracle Oracle NLS 1-8 Transparent Gateway Oracle NLS Oracle NLS Transparent Gateway NLS 1-49
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
2 : 2-1
1 : 2 DB_NAME 8 SALES DB_DOMAIN US.ACME.COM 2-2 Oracle8i
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 HQ.ACME.COM@SN1 HQ.ACME.COM@SN2 : 2-13 2-3
V$PARAMETER MNFG MNFG globalnames.sql COL name FORMAT a12 COL value FORMAT a6 SELECT name, value FROM v$parameter WHERE name = 'global_names' / SQL> @globalnames 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
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
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
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
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
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') / SQL> @privs 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
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
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
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
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 DECNET Oracle @sales @ HQ.ACME.COM Oracle Parallel Server HQ HQ_1 HQ_2 2 HQ 2-13
CREATE PUBLIC DATABASE LINK hq.acme.com@hq_1 USING 'string_to_hq_1'; CREATE PUBLIC DATABASE LINK hq.acme.com@hq_2 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 2 @ : 1-12 2-14 Oracle8i
3 /MTS 100 100 MTS MTS 10 100 10 1 10 1 10 100 10 100 1 10 1 10 1 2-15
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 2-1 2-16 Oracle8i
: SERVER=DEDICATED 2-1. Oracle Oracle Oracle 2-2 2-17
2-2 Oracle Oracle : Oracle8i Net8 20 20 20 20 2-18 Oracle8i
1 20 20 linkname ALTER SESSION CLOSE DATABASE LINK linkname; DROP PUBLIC DATABASE LINK dblink DROP [PUBLIC] DATABASE LINK dblink; 1. SQL*Plus CONNECT scott/tiger@local_db 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
DROP DATABASE LINK sales.us.oracle.com; 1. DROP PUBLIC DATABASE LINK CONNECT sys/change_on_install@local_db 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
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
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
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 / SQL> @authpwd USERID PASSWORD ---------- ---------- ELLIE MAY 1 row selected. : Oracle8i Oracle8i SQL 2-23
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 / SQL> @user_and_pwd 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
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 99999 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 99999 HEADING "C_P_S" SELECT * FROM v$dblink / SQL> @dblink 2-25
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
2-27 2-3 JWARD.DEPT DEPTNO DNAME MARKETING SALES 20 30 HQ SALES SCOTT.EMP EMPNO ENAME JOB SMITH ALLEN WARD JONES 7329 7499 7521 7566 CLERK SALESMAN SALESMAN MANAGER MGR 7902 7698 7698 7839 HIREDATE 17 DEC 88 20 FEB 89 22 JUN 92 02 APR 93 SAL 800.00 1600.00 1250.00 2975.00 COMM 300.00 300.00 500.00 DEPTNO 20 30 30 20 COMPANY EMPNO ENAME DNAME SMITH ALLEN WARD JONES 7329 7499 7521 7566 MARKETING SALES SALES MARKETING
SELECT * FROM company; SQL 1 2-28 Oracle8i
CREATE [PUBLIC] synonym_name FOR [schema.]object_name[@database_link_name] 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
SCOTT SCOTT.EMP@SALES.ACME.COM 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
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
LOCAL_DB FIRE_EMP HQ.ACME.COM TERM_EMP SCOTT SALES.ACME.COM CREATE PROCEDURE fire_emp (enum NUMBER) AS BEGIN DELETE FROM emp@hq.acme.com 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
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
: 20 20 : 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
: 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
: REDWOOD SCOTT SCOTT FOX FOX FOX EMP FOX SALES.DIVISION3.ACME.COM FOX EMP CONNECT fox/mulder@local 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
: SALES SLDB CONNECT bart/simpson@local 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
: : FORD Oracle8i PL/SQL SCOTT SCOTT 2-38 Oracle8i
3 Oracle Oracle8i : Oracle8i Oracle Oracle8i 3-1
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
: Oracle ORA-02055 ORA-02067 Oracle / SALES HQ 2 DML HQ : ALTER SESSION Oracle8i SQL : Oracle8i Oracle 3-3
2 5 WHERE r1.salary + r2.salary > 50000 2 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
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 3 1. 2. 3. 3-5
SQL 1 3-8 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
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
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
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
: EXPLAIN PLAN Oracle8i SQL> @utlxplan.sql : 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
SQL PLAN_TABLE @utlxpls.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 -------------------------------------------------------------------------------- 2 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 9999999 3-11
4 PL/SQL EXCEPTION PL/SQL NO_DATA_FOUND SQL ORA-00900 ORA-02015 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, -20101); BEGIN... RAISE_APPLICATION_ERROR(-20101, 'salary is missing');... EXCEPTION WHEN null_salary THEN... END; PL/SQL ORA-06510 2 3-12 Oracle8i
II
4 Oracle8i 2 : 4-1
2 1 4-1 4-1 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
: 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
SELECT DML COMMIT ROLLBACK SAVEPOINT Oracle8i 1 2 : SQL Oracle8i SQL : 4-5 4-20 : Oracle 1 Oracle 2 4-4 Oracle8i
4-2 4-2 INSERT INTO orders...; UPDATE inventory @ warehouse...; UPDATE accts_rec @ finance...;. COMMIT; SALES.ACME.COM WAREHOUSE.ACME.COM FINANCE.ACME.COM 4-5
1 4-8 1 4-2 SALES WAREHOUSE FINANCE 4-2 SALES WAREHOUSE FINANCE SALES.ACME.COM WAREHOUSE FINANCE SALES SALES 4-6 Oracle8i
4-2 SALES WAREHOUSE FINANCE 4-2 SALES WAREHOUSE FINANCE SALES.ACME.COM SQL 4-7
1 4-3 SALES 4-3 WAREHOUSE SALES COMMIT_POINT_STRENGTH = 75 COMMIT_POINT_STRENGTH = 100 FINANCE COMMIT_POINT_STRENGTH = 50 4-8 Oracle8i
REDO 2 COMMIT_POINT_STRENGTH : COMMIT_POINT_STRENGTH Oracle8i Oracle 1. 2. 3. 4. 4-4 4-9
4-4 SALES.ACME.COM (45) HQ.ACME.COM (165) WAREHOUSE.ACME.COM (140) FINANCE.ACME.COM (45) HR.ACME.COM (45) 1 ROLLBACK 4-4 2 4-10 Oracle8i
2 : 5-5 COMMIT_POINT_STRENGTH Oracle8i 2 Oracle8i 2 2 4-8 4-11
2 REDO REDO : 5-10 3 prepared read-only abort SQL 4-12 Oracle8i
2 3 : 1 Oracle 2 : SET TRANSACTION READ ONLY : SET TRANSACTION READ ONLY 2 SCN READ ONLY 1. 2. 4-13
2 1. 2. 4-12 3. 4. REDO REDO 5. 6. 4-12 4-13 COMMIT ROLLBACK 4-16 2 4-14 Oracle8i
2 1. 2. 3. 4. 5. 6. REDO REDO 7. SCN SQL SCN Oracle SCN 1 SQL SCN : 5-28 4-15
Oracle8i SCN SCN SCN 1. 2. 3. 2 3 Oracle 2 Oracle RECO RECO Oracle 4-16 Oracle8i
Oracle LOCAL REMOTE 2 SCOTT LOCAL LOCAL REMOTE 4-5 4-5 4 3 LOCAL 2 REMOTE REMOTE COMMIT_POINT_SITE = 200 COMMIT_POINT_SITE = 100 1 SCOTT 1. SCOTT LOCAL 2. 3. REMOTE LOCAL 4. RECO 4-17
4-6 4-6 LOCAL 6 4 REMOTE 3 REMOTE 2 REMOTE 5 REMOTE COMMIT_POINT_STRENGTH = 200 COMMIT_POINT_STRENGTH = 100 1 SCOTT 1. SCOTT LOCAL 2. 3. REMOTE 4. REMOTE 5. REMOTE 6. RECO 4-18 Oracle8i
: 2 5-10 DBA_2PC_PENDING DBA_2PC_NEIGHBORS COMMIT FORCE ROLLBACK FORCE : 5-10 5-13 SCN Oracle SCN Oracle SCN REDO Oracle SCN SCN 1. 2. SCN 3. SCN 4-19
: 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 1 1 4-20 Oracle8i
: SQL 4-7 4-7 INSERT INTO orders...; UPDATE inventory @ warehouse...; INSERT INTO orders...; UPDATE inventory @ 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 2 4-21
: 2: Oracle COMMIT 4-8 SALES.ACME.COM : 4-9 4-8 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
: 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
: 5: 1. 2. 6: 1. 2. 3. REDO 4-10 SALES.ACME.COM SALES WAREHOUSE.ACME.COM 4-24 Oracle8i
: 4-10 SALES.ACME.COM Sales to Warehouse: Commit WAREHOUSE.ACME.COM 7: 1. 2. 3. 2 4. COMMIT 4-25
: 4-26 Oracle8i
5 : 5-1
DISTRIBUTED_TRANSACTIONS DISTRIBUTED_LOCK_TIMEOUT DISTRIBUTED_RECOVERY_ CONNECTION_HOLD_TIME COMMIT_POINT_STRENGTH DISTRIBUTED_TRANSACTIONS Oracle SQL ORA-02042: DISTRIBUTED_TRANSACTIONS = 10 5-2 Oracle8i
10 11 DML : DISTRIBUTED_TRANSACTIONS ORA-2042 DISTRIBUTED_TRANSACTIONS DISTRIBUTED_ TRANSACTIONS DISTRIBUTED_TRANSACTIONS SQL RECO Oracle8i : DISTRIBUTED_TRANSACTIONS Oracle8i 5-3
SQL Oracle8i DML SQL 1. 2. 3. ORA-02049: Timeout: DISTRIBUTED_LOCK_TIMEOUT 5-4 30 DISTRIBUTED_LOCK_TIMEOUT = 30 60 Oracle 30 : DISTRIBUTED_LOCK_TIMEOUT Oracle8i 5-4 Oracle8i
DML ORA-01591: identifier SQL ID 2 DISTRIBUTED_RECOVERY_CONNECTION_HOLD_TIME 200 0 1800 1800 COMMIT_POINT_STRENGTH 5-5
0 255 200 1 COMMIT_POINT_STRENGTH = 200 : 4-8 PC PC ID ID 5-6 Oracle8i
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
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 / SQL> @pending_txn_script LOCAL_TRAN_ID GLOBAL_TRAN_ID STATE MIX HOST COMMIT# ------------- ------------------------------ -------- --- ---------- ---------- 1.15.870 HQ.ACME.COM.ef192da4.1.15.870 commit no dlsun183 115499 1.15.870 1 LOCAL_TRAN_ID GLOBAL_TRAN_ID 5-8 Oracle8i
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
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 SQL> @neighbors_script LOCAL_TRAN_ID IN_OUT DATABASE DBUSER_OWNER INT ------------- ------ ------------------------- --------------- --- 1.15.870 out SALES.ACME.COM SYS C SALES 1.15.870 SALES SALES 2 5-10 Oracle8i
Oracle 2 Oracle : 4-16 2 2 ORA-02050: ID ORA-02051: ORA-02054: ID 1 Oracle8i 5-11
ORA-01591 ID ID DBA_ 2PC_PENDING DBA_ROLLBACK_SEGS 2 Oracle8i DBA_2PC_PENDING DBA_2PC_PENDING TRAN_COMMENT COMMIT COMMENT 5-12 Oracle8i
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 emp@hq... ; /*advice to commit at HQ */ ALTER SESSION ADVISE ROLLBACK; DELETE FROM emp@sales... ; /*advice to roll back at SALES*/ ALTER SESSION ADVISE NOTHING; FORCE ID COMMIT ROLLBACK : STATE 5-13
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 1.45.13 SQL COMMIT FORCE '1.45.13'; SCN SCN SCN SCN SCN 5-14 Oracle8i
ID SALES.ACME.COM.55d1c563.1.93.29 DBA_2PC_ PENDING SCN SCN SCN 829381993 COMMIT FORCE 'SALES.ACME.COM.55d1c563.1.93.29', 829381993; FORCE TRANSACTION FORCE ANY TRANSACTION SQL ROLLBACK FORCE 'transaction_id'; transaction_id DBA_2PC_PENDING LOCAL_ TRAN_ID GLOBAL_TRAN_ID 2.9.4 ID ROLLBACK FORCE '2.9.4'; : RECO DBA_ 2PC_PENDING.STATE COLLECTING COMMITTED PREPARED COMMIT FORCE ROLLBACK FORCE FORCED COMMIT FORCED ROLLBACK 5-15
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'); 1.44.99 SQL*Plus EXECUTE DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('1.44.99'); 2 TPMonitor : DBMS_TRANSACTION Oracle8i PL/SQL DBMS_TRANSACTION 5-16 Oracle8i
: 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 : 5-1 5-17
: 5-1 SALES.ACME.COM WAREHOUSE.ACME.COM HQ.ACME.COM WAREHOUSE SALES HQ 1. 2. DBA_2PC_PENDING ID 3. DBA_2PC_NEIGHBORS 4. 1: 2: DBA_2PC_PENDING 3: DBA_2PC_NEIGHBORS 4: 5-18 Oracle8i
: 5: 6: DBA_2PC_PENDING 1: ORA-01591: 1.21.17 1.21.17 ID ID 2: DBA_2PC_PENDING SQL*Plus WAREHOUSE DBA_2PC_PENDING CONNECT sys/sys_pwd@warehouse.acme.com SELECT * FROM sys.dba_2pc_pending WHERE local_tran_id = '1.21.17'; Column Name Value ---------------------- -------------------------------------- LOCAL_TRAN_ID 1.21.17 GLOBAL_TRAN_ID SALES.ACME.COM.55d1c563.1.93.29 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
: 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 1.21.17 GLOBAL_TRAN_ID... 1.93.29 STATE prepared WAREHOUSE TRAN_COMMENT Sales/New Order/Trans_type 10B 5-20 Oracle8i
: 3: DBA_2PC_NEIGHBORS DBA_2PC_NEIGHBORS WAREHOUSE CONNECT sys/sys_pwd@warehouse.acme.com SELECT * FROM dba_2pc_neighbors WHERE local_tran_id = '1.21.17' ORDER BY sess#, in_out; Column Name Value ---------------------- -------------------------------------- LOCAL_TRAN_ID 1.21.17 IN_OUT in DATABASE SALES.ACME.COM DBUSER_OWNER SWILLIAMS INTERFACE N DBID 000003F4 SESS# 1 BRANCH 0100 DBA_2PC_NEIGHBORS IN_OUT = inin_out = out IN_OUT in out DATABASE DBUSER_OWNER 5-21
: 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.55d1c563.1.93.29 ; Column Name Value ---------------------- -------------------------------------- LOCAL_TRAN_ID 1.93.29 GLOBAL_TRAN_ID SALES.ACME.COM.55d1c563.1.93.29 STATE prepared MIXED no 5-22 Oracle8i
: 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-91 31-MAY-91 SWILLIAMS TWA139: system1 SWILLIAMS SALES SALES DBA_2PC_NEIGHBORS SELECT * FROM dba_2pc_neighbors WHERE global_tran_id = 'SALES.ACME.COM.55d1c563.1.93.29' ORDER BY sess#, in_out; 3 WAREHOUSE HQ WAREHOUSE Column Name Value ---------------------- -------------------------------------- LOCAL_TRAN_ID 1.93.29 IN_OUT OUT DATABASE WAREHOUSE.ACME.COM DBUSER_OWNER SWILLIAMS INTERFACE N DBID 55d1c563 SESS# 1 BRANCH 1 HQ 5-23
: Column Name Value ---------------------- -------------------------------------- LOCAL_TRAN_ID 1.93.29 IN_OUT OUT DATABASE HQ.ACME.COM DBUSER_OWNER ALLEN INTERFACE C DBID 00000390 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.55d1c563.1.93.29'; Column Name Value ---------------------- -------------------------------------- LOCAL_TRAN_ID 1.45.13 GLOBAL_TRAN_ID SALES.ACME.COM.55d1c563.1.93.29 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# 129314 5-24 Oracle8i
: ID STATE COMMIT COMMIT# 129314 5: SALES ID SQL> CONNECT sys/sys_pwd@sales.acme.com SQL> COMMIT FORCE SALES.ACME.COM.55d1c563.1.93.29 ; WAREHOUSE ID SQL> CONNECT sys/sys_pwd@warehouse.acme.com SQL> COMMIT FORCE SALES.ACME.COM.55d1c563.1.93.29 ; 6: DBA_2PC_PENDING Oracle8i 2 DBA_2PC_PENDING 5-1 DBA_2PC_PENDING DBA_2PC_PENDING.MIXED MIXED=yes RECO 5-25
RECO RECO RECO COMMIT COMMENT 2 COMMENT COMMIT COMMENT 'ORA-2PC-CRASH-TEST-n'; n 1 n 1 2 3 4 5 6 7 8 9 10 5-26 Oracle8i
COMMIT COMMENT 'ORA-2PC-CRASH-TEST-7'; ORA-02054: 1.93.29 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
: 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
III
6 Oracle Oracle Oracle 6-1
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
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
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
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
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
6-2 Oracle Oracle "X" Oracle"Y" Oracle8i Oracle 3 Oracle SQL Oracle Oracle Oracle 6-7