Thanks for the question, raj.

Asked: January 14, 2019 - 6:58 pm UTC

Last updated: August 08, 2019 - 3:26 am UTC

Version: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit

Viewed 50K+ times! This question is

You Asked

Hi Tom,

I need to get the DDLs of all the tables from a schema.

It should generate a separate file for every table.
Ex: emp.sql and dept.sql for emp & dept tables of scott schema.

The DDL script should contain the NOT NULL and other constrains of the table as well.

I don't want a separate scripts(with ALTER statement) for every constraint of the table, but the constraints should be generated within the Table's DDL script itself.

I tried with SQL Developer 4.1.1 by using the "Database Export" feature. But this tool is not generating the NOT NULL constraints within the DDL of the table.

However, it shows the NOT NULL constraints within the DDL, when i try to fetch the DDL of any table using "SQL" tab of the table definition of the SQL Developer

how to get the DDLs of all the tables of a schema in separate files including the constraints in the same DDL script of the table.

this is required for me to compare the sources with my version control system. The sources in the version control system are stored such a way that the DDL contains the NOT NULL constraints as well in the same file.

Please help.

and Chris said...

There's a whole host of options for exporting table DDL using Oracle SQL Developer.

Jeff Smith discusses them in these posts:

https://www.thatjeffsmith.com/archive/2014/06/how-to-generate-schema-ddl-scripts-with-one-file-per-table-using-sql-developer/
https://www.thatjeffsmith.com/archive/2016/05/configuring-your-generated-ddl-in-sql-developer-and-sqlcl/
https://www.thatjeffsmith.com/archive/2011/11/introducing-the-sql-developer-shopping-cart/

Note: if you load the tables into Data Modeler, you can use this to do your source control!

If none of the above helps, you can always hand-crank the DDL using dmbs_metadata.

Set the options you want using the transformations. Then call get_ddl for each table in your schema:

create table t (
  c1 int not null primary key,
  c2 int not null unique,
  c3 int references t ( c1 )
);

begin
   dbms_metadata.set_transform_param (dbms_metadata.session_transform,'STORAGE',false);
   dbms_metadata.set_transform_param (dbms_metadata.session_transform,'TABLESPACE',false);
   dbms_metadata.set_transform_param (dbms_metadata.session_transform,'SEGMENT_ATTRIBUTES', false);
   dbms_metadata.set_transform_param (dbms_metadata.session_transform,'CONSTRAINTS', true);
   dbms_metadata.set_transform_param (dbms_metadata.session_transform,'REF_CONSTRAINTS', true);
end;
/

set long 10000
select dbms_metadata.get_ddl('TABLE',table_name,user) 
from   user_tables
where  table_name = 'T';

DBMS_METADATA.GET_DDL('TABLE',TABLE_NAME,USER)                                                                                                                                                                                                                                          

  CREATE TABLE "CHRIS"."T" 
   ( "C1" NUMBER(*,0) NOT NULL ENABLE, 
 "C2" NUMBER(*,0) NOT NULL ENABLE, 
 "C3" NUMBER(*,0), 
  PRIMARY KEY ("C1")
  USING INDEX  ENABLE, 
  UNIQUE ("C2")
  USING INDEX  ENABLE, 
  FOREIGN KEY ("C3")
   REFERENCES "CHRIS"."T" ("C1") ENABLE
   ) ;

The DDL command in SQLcl does something similar:

SQL> sho ddl
STORAGE : OFF
INHERIT : 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 : OFF
BODY : ON
CONSTRAINTS_AS_ALTER : ON
SEGMENT_ATTRIBUTES : OFF
SQL>
SQL> ddl t

  CREATE TABLE "CHRIS"."T"
   (    "C1" NUMBER(*,0) NOT NULL ENABLE,
        "C2" NUMBER(*,0) NOT NULL ENABLE,
        "C3" NUMBER(*,0),
         PRIMARY KEY ("C1")
  USING INDEX  ENABLE,
         UNIQUE ("C2")
  USING INDEX  ENABLE,
         FOREIGN KEY ("C3")
          REFERENCES "CHRIS"."T" ("C1") ENABLE
   ) ;

Rating

(2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks for the inputs. It was helpful.

raj reddy, January 16, 2019 - 6:26 pm UTC

Thanks for the inputs.

aitorito, August 06, 2019 - 5:00 pm UTC

Execute this script to generate a sql with table ddl + fks + synonyms + grants... like toad app ;)

# cat aitor_ddl.sql
SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON

exec DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true);
exec DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', true);
exec DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'STORAGE', false);
exec DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SEGMENT_ATTRIBUTES', false);

spool create_table_&&table_name.sql

SELECT dbms_metadata.get_ddl('TABLE','&&table_name','&&username') from dual
union all
SELECT DBMS_METADATA.GET_DDL('INDEX',INDEX_NAME,OWNER)
FROM dba_INDEXES where OWNER='&&username' and TABLE_NAME='&&table_name'
union all
SELECT dbms_metadata.get_ddl('SYNONYM',TABLE_NAME,OWNER)
from dba_synonyms where TABLE_OWNER='&&username' and TABLE_NAME='&&table_name';

select TO_CHAR('grant '||privilege||' on '||lower(owner)||'.'|| lower(table_name)||' to '||grantee|| decode(grantable,'YES',' with grant option',NULL)|| ' ;')
from sys.dba_tab_privs where owner = '&&username' and table_name = '&&table_name' AND PRIVILEGE IN('SELECT','UPDATE','INSERT','DELETE');

spool off;

August 08, 2019 - 3:26 am UTC

nice stuff

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

Thanks for the inputs. It was helpful.

More to Explore

SQL

How to get the DDLs of all the tables of a schema?

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

Thanks for the inputs. It was helpful.

More to Explore

SQL