Using Oracle Data Pump to create an external table

Using Oracle Data Pump to create an external table 1, Create an external table and generate dump file1, 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 tableSQL> create table emp_ext organization external (type oracle_datapump default Directory test location ('emp_ext. dump ') as select * from emp; Table created. at this time, an emp_ext.dump file will be generated in the/home/oracle directory. Note a that external tables do not have segment, and SQL> select SEGMENT_NAME, SEGMENT_TYPE from user_segments where segment_name = 'emp_ext '; no rows selected, but it contains information in the data dictionary SQL> select TABLE_NAME from user_tables where table_name = 'emp_ext '; TABLE_NAME------------------------------EMP _ EXTb, if you put emp_ext.dump under/home/oracle When the file is deleted, the SQL> select * from emp_ext; select * from emp_ext * ERROR at line 1: ORA-29913: error in executing ODCIEXTTABLEOPEN calloutORA-29400: data cartridge errorKUP-11010: unable to open at least one dump file for loadORA-06512: at "SYS. ORACLE_DATAPUMP ", line 19 at this time, if you re-create an external table with the same name, you need to delete the original Table SQL> drop table emp_ext; Table dropped. ii. Using the existing dump file to create an external table1. Repeat the generated dmp file To other hosts [oracle @ vmoel5u4 ~] $ Scp emp_ext.dump 192.168.92.200:/home/oracle2. To create a directory, you must have the create any directory permission: SQL> create directory test as '/home/oracle'; Directory created.3. create an External table: 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. query verification: SQL> select count (*) from emp_ext2; COUNT (*) ---------- 107 Note: External tables cannot be DML, for example, 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 * 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