X

Everything about Table Scans including offload to Exadata, Big Data SQL, and External Tables

  • March 9, 2018

Create External Table as Select

Roger Macnicol
Software Architect

I was looking through a test script and saw something I didn't know you could do in Oracle. I mentioned it to an Oracle ACE and he didn't know it either. I then said to the External Table engineers "Oh I see you've added this cool new feature" and he replied dryly - "Yes, we added it in Oracle 10.1". Ouch! So just in case you also didn't know, you can create an External Table using a CTAS and the ORACLE_DATAPUMP driver.

This feature only work with the ORACLE_DATAPUMP access driver (it does NOT work with with the LOADER, HIVE, or HDFS drivers) and we can use it like this:

SQL> create table cet_test organization external
  2  (
  3    type ORACLE_DATAPUMP
  4    default directory T_WORK
  5    location ('xt_test01.dmp','xt_test02.dmp')
  6  ) parallel 2
  7  as select * from lineitem
 
Table created.

Checking the results shows us

-rw-rw---- ... 786554880 Mar 9 10:48 xt_test01.dmp 
-rw-rw---- ... 760041472 Mar 9 10:48 xt_test02.dmp

This can be a great way of creating a (redacted) sample of data to give to a developer to test or for a bug repro to give to support or to move between systems. 

Join the discussion

Comments ( 2 )
  • Ionut Preda Tuesday, November 20, 2018
    Hello Roger,

    Can you tell us if external tables accepts constraints?
    Also, would be interesting to know if, on external tables, can create index(s).

    Thank you,
    Ionut P.
  • Roger Wednesday, November 21, 2018
    Hi,

    Rely constraints on external tables are supported (since 12.2 I think) but they must be specified as DISABLE. They are supported on both regular and opn virtual columns.
    1. PRIMARY KEY
    Add Constraint XETVC_PK primary key (vc1) Rely Disable;
    2. UNIQUE
    Add Constraint XETVC_UQ unique (vc1) Rely Disable;
    3. FOREIGN KEY
    Add Foreign Key (vc1) References parent_table (c1) Rely Disable;

    Note that CHECK constraint on VCs are not supported
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.