You want the create script for a table.
You want the create script for ALL of your tables.
You want the tablespace.
But not 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.
[email protected]? >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 ] [email protected]? >SET ddl constraints off DDL OPTION CONSTRAINTS off [email protected]? >SET ddl ref_constraints off DDL OPTION REF_CONSTRAINTS off [email protected]? >SET ddl tablespace off DDL OPTION TABLESPACE off [email protected]? >SET ddl segment_attributes off DDL OPTION SEGMENT_ATTRIBUTES off [email protected]? >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") ; [email protected]? >
3 Comments
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