Using Oracle Data Pump to create an external table
来源:互联网 发布:java配置文件中文乱码 编辑:程序博客网 时间:2024/04/26 20:57
一,Create an external table and generate dump file
1, Create a directory and assign related privilege(create any directory)
SQL> create directory test as '/home/oracle';
Directory created.
SQL> grant read,write on directory test to system;
Grant succeeded.
2,Create external table
SQL>create table emp_ext
organization external
(type oracle_datapump
default directory test
location ('emp_ext.dump')
)
as select * from emp;
Table created.
这个时候就会在/home/oracle目录下生成一个emp_ext.dump的文件
注意
a,外部表是没有segment的,
SQL> select SEGMENT_NAME,SEGMENT_TYPE from user_segments where segment_name='EMP_EXT';
no rows selected
但是他在数据字典里是有信息的
SQL> select TABLE_NAME from user_tables where table_name='EMP_EXT';
TABLE_NAME
------------------------------
EMP_EXT
b,如果你把/home/oracle下的emp_ext.dump文件删除了,就查询不到了
SQL> select * from emp_ext;
select * from emp_ext
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-11010: unable to open at least one dump file for load
ORA-06512: at "SYS.ORACLE_DATAPUMP", line 19
这个时候你如果要是重新创建同样名字的外部表则需要删除原表
SQL> drop table emp_ext;
Table dropped.
二、Using the existing dump file to create an external table
1、把产生的dmp文件复制到其它主机上
[oracle@vmoel5u4 ~]$ scp emp_ext.dump 192.168.92.200:/home/oracle
2、创建directory,需要有 create any directory权限:
SQL> create directory test as '/home/oracle';
Directory created.
3、创建外部表:
SQL>CREATE TABLE emp_ext2
(
EMPLOYEE_ID NUMBER(6,0),
FIRST_NAME VARCHAR2(20),
LAST_NAME VARCHAR2(25) ,
EMAIL VARCHAR2(25) ,
PHONE_NUMBER VARCHAR2(20),
HIRE_DATE DATE ,
JOB_ID VARCHAR2(10) ,
SALARY NUMBER(8,2),
COMMISSION_PCT NUMBER(2,2),
MANAGER_ID NUMBER(6,0),
DEPARTMENT_ID NUMBER(4,0)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY test
LOCATION ('emp_ext.dump')
20 );
Table created.
查询验证下:
SQL> select count(*) from emp_ext2;
COUNT(*)
----------
107
注意:外部表不能做DML,例如:
SQL> delete from emp_ext2;
delete from emp_ext2
*
ERROR at line 1:
ORA-30657: operation not supported on external organized table
SQL> update emp_ext2 set salary=salary*1.1;
update emp_ext2 set salary=salary*1.1
*
ERROR at line 1:
ORA-30657: operation not supported on external organized table
SQL> insert into emp_ext2 select * from emp_ext2;
insert into emp_ext2 select * from emp_ext2
*
ERROR at line 1:
ORA-30657: operation not supported on external organized table
- Using Oracle Data Pump to create an external table
- Using SQL*Loader to create an external table
- Moving Data from HDFS to Hive Using an External Table
- create an external table
- Using CREATE TABLE AS SELECT (CTAS) to Reorganize Oracle Tables
- Exporting and Importing Objects Using Oracle Data Pump(1.expdp)
- Exporting and Importing Objects Using Oracle Data Pump(2.impdp)
- 102.You want to import schema objects of the HR user using Oracle Data Pump from the development dat
- oracle data pump 应用
- Oracle Data Pump
- Oracle Data Pump
- Excel--Create an Excel Pivot Table report using VBA
- Data Pump Import --ORACLE SG
- oracle数据泵Data Pump
- Oracle Data Pump (expdp, impdp)
- 11g to 12c Upgrade/Migration Using Full Transportable TableSpace & Data Pump
- Using Dialog Templates to create an InputBox() in C++
- 100.In which situations does the Oracle Data Pump use external tables and not the direct path load w
- linux设备驱动归纳总结(十二):简单的数码相框
- How to get a 64-bit compiler under windows to use with matlab
- java基础--连载4
- Android TextWatcher监控EditText,TextView
- CentOS的自动补全
- Using Oracle Data Pump to create an external table
- 定时任务:Java中Timer和TimerTask的使用
- linux设备驱动归纳总结(十三):1.触摸屏与ADC时钟
- sizeof _countof _tcslen的比较
- IOS 基础开发之CGFloat和float的区别
- MySQLdb 使用简介
- 排序 -- 直接插入排序
- android WebView总结
- Nginx服务器的安装与配置