oracle 抽数,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> updatesetempno=9999where 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 fromwhere 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 tablerename to yoon.yoonbak;

alter tablerename 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) */ * fromt;