How to use external table to copy table data in a file to a defined directory location in Oracle

External table is sometimes very useful to copy table data based on condition into a file to a defined directory location in Oracle:

CREATE OR REPLACE DIRECTORY  DP_DIR AS  '/tmp/oracle/';


GRANT READ, WRITE ON DIRECTORY SYS.DP_DIR TO TAPADM WITH GRANT OPTION;

Below will create the file;


CREATE TABLE TAPADM.TAP_COM_EVENT_DET_EXT
  ORGANIZATION EXTERNAL
   (
     TYPE ORACLE_DATAPUMP
     DEFAULT DIRECTORY DP_DIR
     LOCATION ('TAP_company_event_detail_xt.dmp')
   )
   AS select * from TAPADM.TAP_COMPANY_EVT_DETAIL where company_evt_detail_seq in(select COMPANY_EVT_DETAIL_SEQ from TAPADM.PTED_DIFF_1);

Now to copy the data to some other location at new DB from the above file we can use the below command or syntax.

CREATE TABLE TAPADM.TAP_COM_EVENT_DET_EXT
(
  COMPANY_EVENT_DETAIL_SEQ  VARCHAR2(255 CHAR),
  VERSION                   NUMBER(19),
  CREATOR_ID                VARCHAR2(30 CHAR),
  CREATED_DATE              TIMESTAMP(6),
  MODIFIER_ID               VARCHAR2(30 CHAR),
  MODIFIED_DATE             TIMESTAMP(6),
  REALM_ID                  NUMBER(19),
  EVENT_DETAIL_SUBTYPE      VARCHAR2(100 CHAR),
  COMPANY_FK                VARCHAR2(255 CHAR)
)
ORGANIZATION EXTERNAL
  (  TYPE ORACLE_DATAPUMP
     DEFAULT DIRECTORY DP_DIR
     ACCESS PARAMETERS 
       (  )
     LOCATION (DP_DIR:'TAP_company_event_detail_xt.dmp')
  )
REJECT LIMIT 0
NOPARALLEL
NOMONITORING;