Team,
I am using SQL Developer 17.3 and SQLCL 17.3 for this demo.
while doing DDL from SQLCL we got this
demo@ORA11G> show ddl
STORAGE : ON
INHERIT : ON
SQLTERMINATOR : ON
OID : ON
SPECIFICATION : ON
TABLESPACE : ON
SIZE_BYTE_KEYWORD : ON
PRETTY : ON
REF_CONSTRAINTS : ON
FORCE : ON
PARTITIONING : ON
CONSTRAINTS : ON
INSERT : ON
BODY : ON
CONSTRAINTS_AS_ALTER : ON
SEGMENT_ATTRIBUTES : ON
demo@ORA11G>
demo@ORA11G> ddl EMP table
CREATE TABLE "DEMO"."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 "EMP_PK" 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 "TS_DATA" ENABLE,
CONSTRAINT "EMP_FK" FOREIGN KEY ("DEPTNO")
REFERENCES "DEMO"."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 "TS_DATA" ;
CREATE INDEX "DEMO"."EMP_IDX" ON "DEMO"."EMP" ("ENAME")
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 "TS_DATA" ;
demo@ORA11G>
while doing export DDL from SQL Developer got this. ( have the following options got checked in SQL Developer - pretty print,terminator, storage,show schema, add force to views, add byte keyword)
--------------------------------------------------------
-- File created - Monday-October-16-2017
--------------------------------------------------------
--------------------------------------------------------
-- DDL for Table EMP
--------------------------------------------------------
CREATE TABLE "DEMO"."EMP"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10 BYTE),
"JOB" VARCHAR2(9 BYTE),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0)
) 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 "TS_DATA" ;
--------------------------------------------------------
-- DDL for Index EMP_IDX
--------------------------------------------------------
CREATE INDEX "DEMO"."EMP_IDX" ON "DEMO"."EMP" ("ENAME")
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 "TS_DATA" ;
--------------------------------------------------------
-- DDL for Index EMP_PK
--------------------------------------------------------
CREATE UNIQUE INDEX "DEMO"."EMP_PK" ON "DEMO"."EMP" ("EMPNO")
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 "TS_DATA" ;
--------------------------------------------------------
-- Constraints for Table EMP
--------------------------------------------------------
ALTER TABLE "DEMO"."EMP" ADD CONSTRAINT "EMP_PK" 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 "TS_DATA" ENABLE;
Questions:
1) constraint EMP_FK is missing in SQL Developer but available in SQLCL
2) How can i get only the FK constraints in the DDL command - tried this below command, but that doesn't help.
demo@ORA11G> DDL EMP_FK REF_CONSTRAINTS
Object REF_CONSTRAINTS EMP_FK not found
demo@ORA11G>
demo@ORA11G> DDL EMP_FK CONSTRAINTS
Object CONSTRAINTS EMP_FK not found
demo@ORA11G>
1) I can't reproduce that. I did
Tools => Database Export => Export DDL. Kept with all of the defaults
and my DDL came out with the ref constraint at the bottom
--------------------------------------------------------
-- Ref Constraints for Table EMP
--------------------------------------------------------
ALTER TABLE "SCOTT"."EMP" ADD CONSTRAINT "EMP_FK" FOREIGN KEY ("DEPTNO")
REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE;
2) looks like it might be an omission. The "old fashion" way can be used as a workaround, ie
SQL> select dbms_metadata.get_ddl('REF_CONSTRAINT','EMP_DEPT_FK') from dual;
DBMS_METADATA.GET_DDL('REF_CONSTRAINT','EMP_DEPT_FK')
--------------------------------------------------------------------------------
ALTER TABLE "HR"."EMPLOYEES" ADD CONSTRAINT "EMP_DEPT_FK" FOREIGN KEY ("DEPART
MENT_ID")
REFERENCES "HR"."DEPARTMENTS" ("DEPARTMENT_ID") ENABLE