Unloading and Loading Data with the ORACLE_DATAPUMP Access Driver。
卸载和加载数据的oracle_datapump访问驱动器。
案例：
总公司有一个表，只给分公司看，不允许操作 ；两套系统。
在总公司把数据导出一个文件，拷贝过去，然后在目标做一个外部表（只读）
总公司数据库orcl
分公司数据库ocp
数据泵必须先有目录对象
mkdir u01/app/oracle/zon
sqlplus as sysdba
create directory dzong as '/u01/app/oracle/zon';
grant all on directory dzong to public;
假设这个表是scott.emp 表
CREATE TABLE scott.emp2
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY dzong
LOCATION ('emp.dmp')
)
AS SELECT * FROM scott.emp ;
scott.emp2是一个外部表，数据来源emp.dmp ， 只读的。
SQL> delete from scott.emp2;
delete from scott.emp2
*
ERROR at line 1:
ORA-30657: operation not supported on external organized table
实际上已经完成数据的卸载：把scott.emp 导出到emp.dmp
set long 1000
select dbms_metadata.get_ddl('TABLE','EMP2','SCOTT') from dual;
CREATE TABLE "SCOTT"."EMP2"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0)
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY "DZONG"
LOCATION
( 'emp.dmp'
)
)
export ORACLE_SID=ocp
sqlplus as sysdba
建目录对象
mkdir u01/app/oracle/fen
create directory fen as '/u01/app/oracle/fen';
grant all on directory fen to public;
cp emp.dmp /u01/app/oracle/fen
拷贝至总公司scott.emp2的DDL语句
CREATE TABLE scott.empw
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0)
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY "FEN"
LOCATION
( 'emp.dmp'
)
)
select * from scott.empw;
SQL> update scott.empw set sal=0;
update scott.empw set sal=0
*
ERROR at line 1:
ORA-30657: operation not supported on external organized table
