Problem with getting an Oracle table ddl but with out the extra detail from the current schema

Question

When I use

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

I get this ↓

  CREATE TABLE "SCOTT"."EMP"
   (    "EMPNO" NUMBER(4,0),
        "ENAME" VARCHAR2(10),
        "JOB" VARCHAR2(9),
        "MGR" NUMBER(4,0),
        "HIREDATE" DATE,
        "SAL" NUMBER(7,2),
        "COMM" NUMBER(7,2),
        "DEPTNO" NUMBER(2,0),
         CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"  ENABLE,
         CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
          REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"

-- the question is: Is there any way to get the main DDL statement without the extra details?

Answer 1

Yes, it's possible.

create table t (
    id int primary key, val varchar(16), flag char (1) check (flag in ('y','n')))
/
set long 9999

select dbms_metadata.get_ddl ('TABLE', 'T', user) t_ddl from dual; 

T_DDL
--------------------------------------------------------------------------------

  CREATE TABLE "DB"."T" 
   (    "ID" NUMBER(*,0),  
    "VAL" VARCHAR2(16),  
    "FLAG" CHAR(1),  
     CHECK (flag in ('y','n')) ENABLE,  
     PRIMARY KEY ("ID") 
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 
  TABLESPACE "USERS"  ENABLE
   ) SEGMENT CREATION DEFERRED 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  TABLESPACE "USERS" 

See SET_TRANSFORM_PARAM for the list of all parameters:

begin
    dbms_metadata.set_transform_param (
        dbms_metadata.session_transform, 'SQLTERMINATOR', true);
    dbms_metadata.set_transform_param (
        dbms_metadata.session_transform, 'PRETTY', true);
    dbms_metadata.set_transform_param (
        dbms_metadata.session_transform, 'SEGMENT_ATTRIBUTES', false);
    dbms_metadata.set_transform_param (
        dbms_metadata.session_transform, 'CONSTRAINTS_AS_ALTER', false);
    dbms_metadata.set_transform_param (
        dbms_metadata.session_transform, 'STORAGE', false);
end;
/

select dbms_metadata.get_ddl ('TABLE', 'T', user) t_ddl from dual; 

T_DDL
--------------------------------------------------------------------------------

  CREATE TABLE "DB"."T" 
   (    "ID" NUMBER(*,0),  
    "VAL" VARCHAR2(16),  
    "FLAG" CHAR(1),  
     CHECK (flag in ('y','n')) ENABLE,  
     PRIMARY KEY ("ID") 
  USING INDEX  ENABLE
   ) ;