본문 바로가기
IT

링크관련 SQL

by Echinacea 2021. 6. 28.
반응형

1

select * from test_bank;


select * from test_bank@test_link;



CREATE DATABASE LINK TEST_LINK_DA
CONNECT TO da2121
IDENTIFIED BY da21
USING '(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)
(HOST = 192.168.217.202)
(PORT = 1521))
(CONNECT_DATA = (SERVER = DEDICATED)
(SERVICE_NAME = KOPODA))
)';


select * from emp;

2

--성공--
CREATE DATABASE LINK TEST_LINK4
CONNECT TO scott
IDENTIFIED BY tiger
USING '(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)
(HOST = 192.168.119.119)
(PORT = 1521))
(CONNECT_DATA = (SERVER = DEDICATED)
(SERVICE_NAME = dink))
)';


connect scott@test_link4;
SELECT * FROM test_bank;

select * from test_bank@test_link;

BEGIN DISTRIBUTED TRANSACTION
SELECT * FROM REMOTE.KOPODA.TEST_BANK;
COMMIT TRANSACTION;
END;

BEGIN DISTRIBUTED TRANSACTION
UPDATE REMOTE.DINK.TEST_BANK
SET ACCOUNT = ACCOUNT+1 WHERE NAME = 'SERVER';
COMMIT;
END;


CONNECT scott@TEST_LINK;
INSERT INTO TEST_BANK VALUES('NICK', 134234);
COMMIT;


select * from dba_db_links;

select * from user_db_links;

show user;

select * from test_bank;

--성공 : orcl에 연결됨--
select * from test_bank@test_link;


CONNECT orcl@TEST_LINK;
INSERT INTO TEST_BANK VALUES('NICK', 134234);
COMMIT;


show user;


connect scott@test_link;


select * from user_db_links;

--scott@test_link

begin distributed transaction
INSERT INTO remote.dink.TEST_BANK VALUES('NICK', 134234);
COMMIT TRANSACTION;
END;


CONNECT scott@test_link;



BEGIN DISTRIBUTED TRANSACTION
SET @SQL= 'BEGIN INSERT INTO remote.dink.TEST_BANK VALUES('NICK', 134234); END;
EXEC (@SQL) AT TEST_LINK;
COMMIT

3

--성공--
CREATE DATABASE LINK TEST_LINK4
CONNECT TO scott
IDENTIFIED BY tiger
USING '(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)
(HOST = 192.168.119.119)
(PORT = 1521))
(CONNECT_DATA = (SERVER = DEDICATED)
(SERVICE_NAME = dink))
)';


connect scott@test_link4;
SELECT * FROM test_bank;

select * from test_bank@test_link;

BEGIN DISTRIBUTED TRANSACTION
SELECT * FROM REMOTE.KOPODA.TEST_BANK;
COMMIT TRANSACTION;
END;

BEGIN DISTRIBUTED TRANSACTION
UPDATE REMOTE.DINK.TEST_BANK
SET ACCOUNT = ACCOUNT+1 WHERE NAME = 'SERVER';
COMMIT;
END;


CONNECT scott@TEST_LINK;
INSERT INTO TEST_BANK VALUES('NICK', 134234);
COMMIT;


select * from dba_db_links;

select * from user_db_links;

show user;

select * from test_bank;

--성공 : orcl에 연결됨--
select * from test_bank@test_link;


CONNECT orcl@TEST_LINK;
INSERT INTO TEST_BANK VALUES('NICK', 134234);
COMMIT;


show user;


connect scott@test_link;


select * from user_db_links;

--scott@test_link

begin distributed transaction
INSERT INTO remote.dink.TEST_BANK VALUES('NICK', 134234);
COMMIT TRANSACTION;
END;


CONNECT scott@test_link;



BEGIN DISTRIBUTED TRANSACTION
SET @SQL= 'BEGIN INSERT INTO remote.dink.TEST_BANK VALUES('NICK', 134234); END;
EXEC (@SQL) AT TEST_LINK;
COMMIT

4

select * from help;


CREATE DATABASE LINK TEST_LINK_DA4
CONNECT TO hr2
IDENTIFIED BY hr2
USING '(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)
(HOST = 192.168.119.133)
(PORT = 1521))
(CONNECT_DATA = (SERVER = DEDICATED)
(SERVICE_NAME = XE))
)';



CREATE DATABASE LINK TEST_LINK_KOPODA
CONNECT TO da2121
IDENTIFIED BY da21
USING '(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)
(HOST = 192.168.217.202)
(PORT = 1521))
(CONNECT_DATA = (SERVER = DEDICATED)
(SERVICE_NAME = KOPODA))
)';


select * from dept@test_link_KOPODA;



BEGIN DISTRIBUTED TRANSACTION
SET @SQL= 'BEGIN INSERT INTO remote.dink.TEST_BANK VALUES('NICK', 134234); END;
EXEC (@SQL) AT TEST_LINK;
COMMIT


begin distributed transaction
INSERT INTO remote.dink.TEST_BANK VALUES('NICK', 134234);
COMMIT TRANSACTION;
END;


CONNECT orcl@TEST_LINK;
INSERT INTO TEST_BANK VALUES('NICK', 134234);
COMMIT;






select * from test@test_link_da4;


SELECT * FROM TEST;

SELECT * FROM TEST_BANK@SCOTT.TEST_LINK;

5

--성공--
CREATE DATABASE LINK TEST_LINK4
CONNECT TO scott
IDENTIFIED BY tiger
USING '(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)
(HOST = 192.168.119.119)
(PORT = 1521))
(CONNECT_DATA = (SERVER = DEDICATED)
(SERVICE_NAME = dink))
)';


select * from test_bank@test_link4;





connect scott@test_link4;
SELECT * FROM test_bank;


BEGIN DISTRIBUTED TRANSACTION
SELECT * FROM REMOTE.KOPODA.TEST_BANK;
COMMIT TRANSACTION;
END;

BEGIN DISTRIBUTED TRANSACTION
UPDATE REMOTE.DINK.TEST_BANK
SET ACCOUNT = ACCOUNT+1 WHERE NAME = 'SERVER';
COMMIT;
END;


CONNECT scott@TEST_LINK;
INSERT INTO TEST_BANK VALUES('NICK', 134234);
COMMIT;


select * from dba_db_links;

select * from user_db_links;

show user;

select * from test_bank;

--성공 : orcl에 연결됨--
select * from test_bank@test_link;


CONNECT orcl@TEST_LINK;
INSERT INTO TEST_BANK VALUES('NICK', 134234);
COMMIT;


show user;


connect scott@test_link;


select * from user_db_links;

--scott@test_link

begin distributed transaction
INSERT INTO remote.dink.TEST_BANK VALUES('NICK', 134234);
COMMIT TRANSACTION;
END;


CONNECT scott@test_link;



BEGIN DISTRIBUTED TRANSACTION
SET @SQL= 'BEGIN INSERT INTO remote.dink.TEST_BANK VALUES('NICK', 134234); END;
EXEC (@SQL) AT TEST_LINK;
COMMIT








CREATE DATABASE LINK TEST_LINK_DA2
CONNECT TO da2121
IDENTIFIED BY da21
USING '(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)
(HOST = 192.168.217.206)
(PORT = 1521))
(CONNECT_DATA = (SERVER = DEDICATED)
(SERVICE_NAME = KOPODA))
)';



select * from da2121@test_link_DA2;








CREATE DATABASE LINK TEST_LINK_DA4
CONNECT TO hr2
IDENTIFIED BY hr2
USING '(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)
(HOST = 192.168.119.133)
(PORT = 1521))
(CONNECT_DATA = (SERVER = DEDICATED)
(SERVICE_NAME = XE))
)';


select * from HELP@test_link_MGR;


show user;











CREATE DATABASE LINK TEST_LINK_mgr
CONNECT TO system
IDENTIFIED BY manager33
USING '(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)
(HOST = 192.168.119.119)
(PORT = 1521))
(CONNECT_DATA = (SERVER = DEDICATED)
(SERVICE_NAME = DINK))
)';


SELECT * FROM HELP@TEST_LINK_MGR;




show user; 
--HR--

select * from test@test_link_da4;











SHOW USER;
CONNECT scott@TEST_LINK;
INSERT INTO TEST_BANK VALUES('NICK', 134234);
COMMIT;

'
SELECT * FROM HANABANK;
SELECT * FROM TEST_BANK@TEST_LINK;

BEGIN
UPDATE  TEST_BANK@TEST_LINK
    SET ACCOUNT = ACCOUNT - 45
    WHERE NAME = 'TESTER';
UPDATE  HANABANK
    SET BALANCE = BALANCE + 45
    WHERE OWNER = 111;
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
    ROLLBACK;
END;










CREATE DATABASE LINK TEST_LINK_DA2
CONNECT TO da2121
IDENTIFIED BY da21
USING '(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)
(HOST = 192.168.217.206)
(PORT = 1521))
(CONNECT_DATA = (SERVER = DEDICATED)
(SERVICE_NAME = KOPODA))
)';

show user;

6

--성공--
CREATE DATABASE LINK TEST_LINK4
CONNECT TO scott
IDENTIFIED BY tiger
USING '(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)
(HOST = 192.168.119.119)
(PORT = 1521))
(CONNECT_DATA = (SERVER = DEDICATED)
(SERVICE_NAME = dink))
)';


connect scott@test_link4;
SELECT * FROM test_bank;

select * from test_bank@test_link;

BEGIN DISTRIBUTED TRANSACTION
SELECT * FROM REMOTE.KOPODA.TEST_BANK;
COMMIT TRANSACTION;
END;

BEGIN DISTRIBUTED TRANSACTION
UPDATE REMOTE.DINK.TEST_BANK
SET ACCOUNT = ACCOUNT+1 WHERE NAME = 'SERVER';
COMMIT;
END;


CONNECT scott@TEST_LINK;
INSERT INTO TEST_BANK VALUES('NICK', 134234);
COMMIT;


select * from dba_db_links;

select * from user_db_links;

show user;

select * from test_bank;

--성공 : orcl에 연결됨--
select * from test_bank@test_link;


CONNECT orcl@TEST_LINK;
INSERT INTO TEST_BANK VALUES('NICK', 134234);
COMMIT;


show user;


connect scott@test_link;


select * from user_db_links;

--scott@test_link

begin distributed transaction
INSERT INTO remote.dink.TEST_BANK VALUES('NICK', 134234);
COMMIT TRANSACTION;
END;


CONNECT scott@test_link;



BEGIN DISTRIBUTED TRANSACTION
SET @SQL= 'BEGIN INSERT INTO remote.dink.TEST_BANK VALUES('NICK', 134234); END;
EXEC (@SQL) AT TEST_LINK;
COMMIT

7

--성공--
CREATE DATABASE LINK TEST_LINK4
CONNECT TO scott
IDENTIFIED BY tiger
USING '(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)
(HOST = 192.168.119.119)
(PORT = 1521))
(CONNECT_DATA = (SERVER = DEDICATED)
(SERVICE_NAME = dink))
)';


connect scott@test_link4;
SELECT * FROM test_bank;

select * from test_bank@test_link;

BEGIN DISTRIBUTED TRANSACTION
SELECT * FROM REMOTE.KOPODA.TEST_BANK;
COMMIT TRANSACTION;
END;

BEGIN DISTRIBUTED TRANSACTION
UPDATE REMOTE.DINK.TEST_BANK
SET ACCOUNT = ACCOUNT+1 WHERE NAME = 'SERVER';
COMMIT;
END;


CONNECT scott@TEST_LINK;
INSERT INTO TEST_BANK VALUES('NICK', 134234);
COMMIT;


select * from dba_db_links;

select * from user_db_links;

show user;

select * from test_bank;

--성공 : orcl에 연결됨--
select * from test_bank@test_link;


CONNECT orcl@TEST_LINK;
INSERT INTO TEST_BANK VALUES('NICK', 134234);
COMMIT;


connect scott@test_link;


select * from user_db_links;

--scott@test_link

begin distributed transaction
INSERT INTO remote.dink.TEST_BANK VALUES('NICK', 134234);
COMMIT TRANSACTION;
END;


CONNECT scott@test_link;



BEGIN DISTRIBUTED TRANSACTION
SET @SQL= 'BEGIN INSERT INTO remote.dink.TEST_BANK VALUES('NICK', 134234); END;
EXEC (@SQL) AT TEST_LINK;
COMMIT TRANSACTION


BEGIN tran
remove $xxx from Account A
add $xxx to Account B
COMMIT tran
if something goes back with either one of these two actions :
ROLLBACK tran;



begin distributed transaction;
INSERT INTO remote.dink.TEST_BANK VALUES('NICK', 134234);
COMMIT TRANSACTION;
END;


begin distributed transaction;
select * from test_bank@test_link;
COMMIT TRANSACTION;
END;



BEGIN TRANSACTION;




CREATE TABLE TEST_BANK AS SELECT * FROM SCOTT@TEST_LINK;


SELECT * FROM USER_DB_LINKS;

SHOW USER;

8

--성공--
CREATE DATABASE LINK TEST_LINK4
CONNECT TO scott
IDENTIFIED BY tiger
USING '(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)
(HOST = 192.168.119.119)
(PORT = 1521))
(CONNECT_DATA = (SERVER = DEDICATED)
(SERVICE_NAME = dink))
)';


select * from test_bank@test_link4;





connect scott@test_link4;
SELECT * FROM test_bank;


BEGIN DISTRIBUTED TRANSACTION
SELECT * FROM REMOTE.KOPODA.TEST_BANK;
COMMIT TRANSACTION;
END;

BEGIN DISTRIBUTED TRANSACTION
UPDATE REMOTE.DINK.TEST_BANK
SET ACCOUNT = ACCOUNT+1 WHERE NAME = 'SERVER';
COMMIT;
END;


CONNECT scott@TEST_LINK;
INSERT INTO TEST_BANK VALUES('NICK', 134234);
COMMIT;


select * from dba_db_links;

select * from user_db_links;

show user;

select * from test_bank;

--성공 : orcl에 연결됨--
select * from test_bank@test_link;


CONNECT orcl@TEST_LINK;
INSERT INTO TEST_BANK VALUES('NICK', 134234);
COMMIT;


show user;


connect scott@test_link;


select * from user_db_links;

--scott@test_link

begin distributed transaction
INSERT INTO remote.dink.TEST_BANK VALUES('NICK', 134234);
COMMIT TRANSACTION;
END;


CONNECT scott@test_link;



BEGIN DISTRIBUTED TRANSACTION
SET @SQL= 'BEGIN INSERT INTO remote.dink.TEST_BANK VALUES('NICK', 134234); END;
EXEC (@SQL) AT TEST_LINK;
COMMIT








CREATE DATABASE LINK TEST_LINK_DA2
CONNECT TO da2121
IDENTIFIED BY da21
USING '(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)
(HOST = 192.168.217.206)
(PORT = 1521))
(CONNECT_DATA = (SERVER = DEDICATED)
(SERVICE_NAME = KOPODA))
)';



select * from da2121@test_link_DA2;








CREATE DATABASE LINK TEST_LINK_DA4
CONNECT TO hr2
IDENTIFIED BY hr2
USING '(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)
(HOST = 192.168.119.133)
(PORT = 1521))
(CONNECT_DATA = (SERVER = DEDICATED)
(SERVICE_NAME = XE))
)';


select * from HELP@test_link_MGR;


show user;











CREATE DATABASE LINK TEST_LINK_mgr
CONNECT TO system
IDENTIFIED BY manager33
USING '(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)
(HOST = 192.168.119.119)
(PORT = 1521))
(CONNECT_DATA = (SERVER = DEDICATED)
(SERVICE_NAME = DINK))
)';


SELECT * FROM HELP@TEST_LINK_MGR;




show user; 
--HR--

select * from test@test_link_da4;











SHOW USER;
CONNECT scott@TEST_LINK;
INSERT INTO TEST_BANK VALUES('NICK', 134234);
COMMIT;

'
SELECT * FROM HANABANK;
SELECT * FROM TEST_BANK@TEST_LINK;

BEGIN
UPDATE  TEST_BANK@TEST_LINK
    SET ACCOUNT = ACCOUNT - 45
    WHERE NAME = 'TESTER';
UPDATE  HANABANK
    SET BALANCE = BALANCE + 45
    WHERE OWNER = 111;
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
    ROLLBACK;
END;










CREATE DATABASE LINK TEST_LINK_DA2
CONNECT TO da2121
IDENTIFIED BY da21
USING '(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)
(HOST = 192.168.217.206)
(PORT = 1521))
(CONNECT_DATA = (SERVER = DEDICATED)
(SERVICE_NAME = KOPODA))
)';

show user;

9

SELECT * FROM EMP@DBLK_ORCL10;SELECT * FROM DBA_DB_LINKS;CREATE DATABASE LINK DBLK_ORCL10CONNECT TO SCOTTIDENTIFIED BY TIGERUSING 'ORCL=(DESCRIPTION= (ADDRESS_LIST=  (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.119.119)(PORT=1521)) ) (CONNECT_DATA= (SERVICE_NAME=DINK) ))';SET XACT_ABORT ONBEGIN DISTRIBUTED TRANSACTIONselect * from openquery(DINK,'select SYSDATE FROM DUAL')COMMIT TRANSET XACT_ABORT OFF;CREATE DATABASE LINK DBLK_ORCL10CONNECT TO SCOTTIDENTIFIED BY TIGERUSING 'ORCL=(DESCRIPTION= (ADDRESS_LIST=  (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.119.119)(PORT=1521)) ) (CONNECT_DATA= (SERVICE_NAME=DINK) ))';--안되는코드--CREATE DATABASE LINK HRTESTCONNECT TO HRIDENTIFIED BY 1234USING 'XE=(DESCRIPTION= (ADDRESS_LIST=  (ADDRESS=(PROTOCOL=TCP)(HOST=LOCALHOST)(PORT=1521)) ) (CONNECT_DATA= (SERVICE_NAME=XE) ))';SELECT * FROM EMP@SELECT * FROM USER_DB_LINKS;select * from all_db_links;SELECT DB_LINK, USERNAME, HOST FROM ALL_DB_LINKS ;select * from emp@dblk_orcl;SELECT * FROM EMP@NEWLINK;CREATE DATABASE LINK NEWLINKCONNECT TO SCOTTIDENTIFIED BY TIGERUSING 'XE=(DESCRIPTION= (ADDRESS_LIST=  (ADDRESS=(PROTOCOL=TCP)(HOST=LOCALHOST)(PORT=1521)) ) (CONNECT_DATA= (SERVICE_NAME=XE) ))';SELECT * FROM JOBS;SELECT * FROM JOBS@NEWLINK;

10

select * from dba_db_links;select * from user_db_links;SELECT * FROM emp@dblk_orcl;select * from openquery(dink, 'select sysdate from dual'); SELECT instance FROM v$thread; SELECT DB_LINK, USERNAME, HOST FROM ALL_DB_LINKS ;SELECT * FROM DBA_DB_LINKS;SELECT * FROM EMP@xe;

11

select * from T@another_database;create synonym T for T@another_database;update local_table set x = 5;update remote_table@another_database set y = 10;commit;SELECT * FROM DBA_DB_LINKS;SELECT * FROM DEPT@SYS_HUB;GRANT CREATE PUBLIC DATABASE LINK TO HR;CREATE DATABASE LINK test_server     CONNECT TO scott IDENTIFIED BY tiger USING 'testdb';GRANT CREATE DATABASE LINK TO SCOTT;SELECT * FROM ALL_DB_LINKS;create public database link test2 using 'remote_2';SELECT * FROM EMP@testdb;testdb =  (DESCRIPTION =    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))    (CONNECT_DATA =      (SERVER = DEDICATED)      (SERVICE_NAME = testdb)    );        ORCL =  (DESCRIPTION =    (ADDRESS_LIST =       (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.0.1) (PORT = 1521))    )    (CONNECT_DATA =       (SID = ORA9)    )  );  CREATE DATABASE LINK DBLK_ORCL5CONNECT TO SCOTTIDENTIFIED BY TIGERUSING 'ORCL';select * from all_Db_links;SELECT * FROM tbl@LINK10;CREATE DATABASE LINK DBLK_ORCL10CONNECT TO SCOTTIDENTIFIED BY TIGERUSING 'ORCL=(DESCRIPTION= (ADDRESS_LIST=  (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.119.119)(PORT=1521)) ) (CONNECT_DATA= (SERVICE_NAME=DINK) ))';SELECT * FROM DBA_DB_LINKS;SELECT * FROM USER_DB_LINKS;SELECT * FROM TBL@DBLK_ORCL2;SELECT * FROM TBL@DBLK_ORCL;select * from tbl@DBLK_ORCL3;select * from tbl@DBLK_ORCL5;select * from EMP@DBLK_ORCL10;DROP PUBLIC DATABASE LINK TESTDB;select state, local_tran_ID, Global_tran_Id from dba_2pc_pending where state = "prepared";BEGIN DISTRIBUTED TRANSACTION;USE ORCL;GOBEGIN DISTRIBUTED TRANSACTION;-- Delete candidate from local instance.-- Delete candidate from remote instance.SELECT * FROM TBL;COMMIT TRANSACTION;GOUPDATE 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;SELECT * FROM TBL(SCOTT, 'SELECT SYSDATE FROM DUAL');SELECT * FROM EMP@;select * from dba_db_links;select * from user_db_links;

*.ora

SELECT * FROM DBA_DB_LINKS;GRANT CREATE PUBLIC DATABASE LINK TO HR;CREATE DATABASE LINK TEST_LINKCONNECT TO HRIDENTIFIED BY 1234USING '(DESCRIPTION =    (ADDRESS_LIST =      (ADDRESS =        (PROTOCOL = TCP)        (HOST = LOCALHOST)        (PORT = 1521)      )    )    (CONNECT_DATA =      (SERVICE_NAME = TEST)    )  )';
반응형
저작자표시

'IT' 카테고리의 다른 글

[Link Only] 이클립스 한글 깨질때 해결방법  (0) 2021.07.01
SQLPLUS 권한 부여하는방법  (0) 2021.06.29
giphy download  (0) 2021.06.28
자바 난수 만드는 코드  (0) 2021.06.28
[Link Only] 윈도우10 iso 파일 다운받는 방법  (0) 2021.06.28
이클립스에서 클래스 다이어그램 만드는법  (0) 2021.06.28
[Link Only] ORACLE JOIN 설명 잘 나온 블로그  (0) 2021.06.28
[Link Only] 특정 컬럼 이름 조회 및 테이블의 소유자 확인  (0) 2021.06.28

관련글

댓글

티스토리툴바