Where we can find ddl script of external table in oracle(any system table)

Question

I tried with below code but unable to get the DDL script,

  SELECT DBMS_METADATA.get_ddl ('TABLE', 'TABLE_NAME', 'OWNER') FROM dual 

Is there any oracle system table where we can see definition of External table.

Answer 1

DBMS_METADATA.GET_DDL will work for external tables just fine, eg

SQL> select dbms_metadata.get_ddl('TABLE','SALES_EXT') from dual;

DBMS_METADATA.GET_DDL('TABLE','SALES_EXT')
----------------------------------------------------------------------------

  CREATE TABLE "MCDONAC"."SALES_EXT"
   (    "CUST_ID" VARCHAR2(10) COLLATE "USING_NLS_COMP" NOT NULL ENABLE,
        "PRODUCT_ID" NUMBER(*,0) NOT NULL ENABLE,
        "AMT" NUMBER,
        "DTE" DATE,
        "AMT10" NUMBER(*,0) GENERATED ALWAYS AS ("AMT"*10) VIRTUAL
   )  DEFAULT COLLATION "USING_NLS_COMP"
   ORGANIZATION EXTERNAL
    ( TYPE ORACLE_LOADER
      DEFAULT DIRECTORY "TEMP"
      ACCESS PARAMETERS
      ( records delimited by newline
    ...
    ...

You can also get additional information from xxx_EXTERNAL_TABLES