How to generate create table script without primary and foreign key in Oracle

I have lots of schemas and tables. I want to generate create script of all of my tables. I am using below statement and it is working pretty well.

SELECT DBMS_METADATA.GET_DDL('TABLE','table_name','schema') FROM DUAL

But this statement also generates all primary and foreign key scripts that belong to table. So, is there any way to not include primary and foreign keys in create table scripts

You could try the following:

set pagesize 0
set long 90000 

exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);
exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'CONSTRAINTS',false);
exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'REF_CONSTRAINTS',false);

SELECT DBMS_METADATA.GET_DDL( 'TABLE','table_name','schema') FROM DUAL;

This should result in the DDL without any indexes and foreign keys.

Reference:

• docs.oracle.com

• dba-oracle.com

StackExchange Database Administrators Q#62734, answer score: 4