My Oracle Support Banner

Set DDL <Options> Off Not Working (Doc ID 2913877.1)

Last updated on DECEMBER 09, 2022

Applies to:

Oracle SQLcl - Version 22.2.1 and later
Information in this document applies to any platform.

Symptoms

Downloaded SQLcl 22.2 version and testing "SET DDL <Options> OFF" command has no effect in the SQLcl.

Example: The TableName and Schema name demonstrated here is only for Educational and Testing purposes.

Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0l-22.2.1.201.1456\bin>sql <username>/<password>@<Connect_String>

SQL> show ddl
STORAGE : ON
INHERIT : ON
COLLATION_CLAUSE : NEVER
EMIT_SCHEMA : 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

SQL> set ddl storage off
DDL Option STORAGE OFF

SQL> set ddl tablespace off
DDL Option TABLESPACE OFF

SQL> set ddl segment_attributes off
DDL Option SEGMENT_ATTRIBUTES OFF

SQL> show ddl
STORAGE : OFF
INHERIT : ON
COLLATION_CLAUSE : NEVER
EMIT_SCHEMA : ON
SQLTERMINATOR : ON
OID : ON
SPECIFICATION : ON
TABLESPACE : OFF
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 : OFF

SQL> ddl emp

CREATE TABLE "<SCHEMA_NAME>"."emp"
( "EMPNO" NUMBER(4,0) NOT NULL ENABLE,
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0),
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,
FOREIGN KEY ("MGR")
REFERENCES "HR"."EMP" ("EMPNO") ENABLE,
FOREIGN KEY ("DEPTNO")
REFERENCES "HR"."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" ;

Changes

 Downloaded SQLcl 22.2 and later version

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document
Symptoms
Changes
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.