You want the create script for a table.
AND you want the create script for ALL of your tables.
BUT also I want want the tablespace.
Forget the constraints.
How do you configure this in our tools?
SQL Developer: The Preferences & the Wizards
Go to the preferences.
Database, utilities, export.
SQLcl: SET DDL
HELP SET DDL
SET DDL
And now run the DDL command.
HR@orcl >help set ddl
SET DDL
SET DDL [[ PRETTY | SQLTERMINATOR | CONSTRAINTS | REF_CONSTRAINTS |
CONSTRAINTS_AS_ALTER|OID | SIZE_BYTE_KEYWORD | PARTITIONING |
SEGMENT_ATTRIBUTES | STORAGE | TABLESPACE | SPECIFICATION |
BODY | FORCE | INSERT | |INHERIT | RESET] {on|off}
]| OFF ]
HR@orcl >set ddl constraints off
DDL Option CONSTRAINTS off
HR@orcl >set ddl ref_constraints off
DDL Option REF_CONSTRAINTS off
HR@orcl >set ddl tablespace off
DDL Option TABLESPACE off
HR@orcl >set ddl segment_attributes off
DDL Option SEGMENT_ATTRIBUTES off
HR@orcl >ddl locations
CREATE TABLE "HR"."LOCATIONS"
( "LOCATION_ID" NUMBER(4,0),
"STREET_ADDRESS" VARCHAR2(40),
"POSTAL_CODE" VARCHAR2(12),
"CITY" VARCHAR2(30),
"STATE_PROVINCE" VARCHAR2(25),
"COUNTRY_ID" CHAR(2)
) ;
COMMENT ON COLUMN "HR"."LOCATIONS"."LOCATION_ID" IS 'Primary key of locations table';
COMMENT ON COLUMN "HR"."LOCATIONS"."STREET_ADDRESS" IS 'Street address of an office, warehouse, or production site of a company.
Contains building number and street name';
COMMENT ON COLUMN "HR"."LOCATIONS"."POSTAL_CODE" IS 'Postal code of the location of an office, warehouse, or production site
of a company. ';
COMMENT ON COLUMN "HR"."LOCATIONS"."CITY" IS 'A not null column that shows city where an office, warehouse, or
production site of a company is located. ';
COMMENT ON COLUMN "HR"."LOCATIONS"."STATE_PROVINCE" IS 'State or Province where an office, warehouse, or production site of a
company is located.';
COMMENT ON COLUMN "HR"."LOCATIONS"."COUNTRY_ID" IS 'Country where an office, warehouse, or production site of a company is
located. Foreign key to country_id column of the countries table.';
COMMENT ON TABLE "HR"."LOCATIONS" IS 'Locations table that contains specific address of a specific office,
warehouse, and/or production site of a company. Does not store addresses /
locations of customers. Contains 23 rows; references with the
departments and countries tables. ';
CREATE INDEX "HR"."LOC_CITY_IX" ON "HR"."LOCATIONS" ("CITY")
;
CREATE INDEX "HR"."LOC_COUNTRY_IX" ON "HR"."LOCATIONS" ("COUNTRY_ID")
;
CREATE INDEX "HR"."LOC_STATE_PROVINCE_IX" ON "HR"."LOCATIONS" ("STATE_PROVINCE")
;
HR@orcl >
Don’t want the quoted identifiers?
Use the Data Modeler.
5 Comments
Currently using Oracle SQL Developer 21.4.3
Why is it not possible to generate DDL with the Database Export that contains the NOT NULL constraints within the CREATE TABLE scripts.
The DDL Preview when viewing the table shows CREATE TABLE including NOT NULL and for example, indexes are in the ALTER TABLE.
The included Data Modeler can also do this, but I prefer the Database Export as it can save the scripts per type, whereas the modeler saves a single file or multiple files, there is no type possibility.
What you’re seeing is how database engine itself sees your table, it expresses the DDL the way we print it. We just ask the DB for your DDL.
Here are your options –
SQL> help set ddlSET DDL
SET DDL [[ PRETTY | SQLTERMINATOR | CONSTRAINTS | REF_CONSTRAINTS |
CONSTRAINTS_AS_ALTER|OID | SIZE_BYTE_KEYWORD | PARTITIONING |
SEGMENT_ATTRIBUTES | STORAGE | TABLESPACE | SPECIFICATION |
BODY | FORCE | INSERT | |INHERIT | RESET] {on|off}
] | ON | OFF ]
SQL>
editionable bug:
When I export packages and procedures from a 12c database, using SQL Developer, the default statement is
CREATE OR REPLACE EDITIONABLE PROCEDURE
But this fails as all users in dba_users are with EDITIONS_ENABLED=’N’
I just opened SR SR 3-14571041491 : sqldeveloper export ddl adds “editionable” to all packages
and they says those 2 bugs are closed as not a bug!!!
It means export DDL of sqldeveloper is UNUSABLE for 12c!!!
and it looks like nobody cares!!!!
Is there a list somewhere of the object types that the ddl command supports?
I saw somewhere that it is using dbms_metadata.get_ddl behind the scenes, but attempting to run the command below fails.
ddl job_name PROCOBJ
but
dbms_metadata.get_ddl(‘PROCOBJ’, ‘JOB_NAME’)
works just fine
Hello Jeff.
I would like to give a try to sqlcl, but one thing confuses me.
I have the latest SQLDeveloper package installed (named ‘Windows 64-bit with JDK 8 included’)
But sqlcl is not mapped to this ‘internal’ JDK in any way and asks me to locate another one (JDK7 as I remember)
Can it be improved out the box?
Such inconsistency is weird and I don’t want to waste my system with multiple JDK instances