Copying data with data pump

Oracle has a brilliant feature to export / import data at great speed. The trick is to use the data pump facility. Let us first introduce the export via data pump.

We may export data via data pump as:

CREATE TABLE stu_details_ext
ORGANIZATION EXTERNAL
( TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY EXTERNORA
LOCATION ('stu_details_ext_table.dmp')
)
AS SELECT * FROM nationaliteit;

Note that two items are created: a new table with the original details, along with a file that is stored in an external directory.

Subsequently, the file can be read via:

CREATE TABLE stu_details_ext_table2
("COLUMN1" VARCHAR2(26 BYTE), 
    "COLUMN2" VARCHAR2(128 BYTE), 
    "COLUMN3" NUMBER(38,0), 
    "COLUMN4" NUMBER(38,0), 
    "COLUMN5" VARCHAR2(26 BYTE))
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY EXTERNORA LOCATION ('STU_DETAILS_EXT_TABLE.DMP')
);

Now, the data are read from the dump file with a specification that is derived from the original table.

Door tom