Oracle DBLINK 抽數以及DDL、DML操作

DB :  11.2.0.3.0

原庫例項orcl：
SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
orcl

 
目標庫例項yoon：
SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
yoon

建立表空間
SQL> create tablelspace yoon datafile '/u01/app/oracle/oradata/yoon/yoon01.dbf' size 100m;

建立使用者
SQL> create user yoon identified by yoon default tablespace yoon;

授權
SQL> grant dba to yoon;

配置tnsname.ora （一臺伺服器安裝了兩個例項,tnsname.ora共用）
[root@db01 admin]# vi tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = db01)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
      (SERVER = DEDICATED)
    )
  )

 

YOON =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = db01)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = yoon)
      (SERVER = DEDICATED)
    )
  )

 原庫例項orcl：
SQL> show user
USER is "SYS"

建立DB_LINK（dblink_yoon）：        
SQL> create database link dblink_yoon connect to yoon identified by yoon using 'YOON';

Database link created.

dblink_yoon：建立的dblink名
yoon：使用者名稱
yoon：密碼
YOON：tnsname.ora中配置的服務名

目標庫例項yoon
 SQL> conn yoon/yoon
Connected.

SQL> show user
USER is "YOON"

建立測試表：
SQL> create table yoon as select * from scott.emp;

Table created.

SQL> select table_name from user_tables;

TABLE_NAME
------------------------------
YOON

原庫例項orcl：
SQL> show user
USER is "SYS"

SQL> select * from ;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

SQL> insert into (empno,ename) values (7777,'KKKK');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from ;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
      7777 KKKK

15 rows selected.

SQL> update set empno=9999 where empno=7777;

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from ;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
      9999 KKKK

15 rows selected.

SQL> delete from where yoon.yoon.empno=9999;

1 row deleted.

SQL> commit;

Commit complete.

SQL> select * from ;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

SQL> alter table rename to yoon.yoonbak;
alter table rename to yoon.yoonbak
                      *
ERROR at line 1:
ORA-02021: DDL operations are not allowed on a remote database

SQL> drop table ;
drop table
                     *
ERROR at line 1:
ORA-02021: DDL operations are not allowed on a remote database

總結：DBLINK支援DML操作、不支援DDL操作(除了寫儲存過程)


抽數：
1、先建立好表結構

2、透過insert into抽數
SQL> insert /*+APPEND*/ into YOON.YOON_TABLE   select /*+ parallel（t 8） */ * from   t;