Sunday, August 12, 2018

How to Create a External Table in Oracle



CREATE TABLE "APPS"."TEST_FILESYSTEM"
   ( "FILESYSTEM" VARCHAR2(40 BYTE),
"TOTAL_GB" VARCHAR2(10 BYTE),
"USED_GB" VARCHAR2(10 BYTE),
"AVAILABLE_GB" VARCHAR2(10 BYTE),
"USED_PERCENTAGE" VARCHAR2(10 BYTE),
"MOUNTED_ON" VARCHAR2(25 BYTE)
   )
   ORGANIZATION EXTERNAL
    ( TYPE ORACLE_LOADER
      DEFAULT DIRECTORY "DUMP"
      ACCESS PARAMETERS
      ( records delimited by newline
fields terminated by ' '
    )
      LOCATION
       ( 'Test_filesystem.txt'
       )
    )
   REJECT LIMIT UNLIMITED ;

Note:

* Dump - Directory should be available in dba_directory table.
* Location - txt file should be available in specific location or Path
* use All_external_tables view to check the external table information.

No comments:

Post a Comment