The DBMS_METADATA package is a powerful tool for obtaining the complete definition of a schema object. It enables you to obtain all of the attributes of an object in one pass. The object is described as DDL that can be used to (re)create it.

The GET_DDL function is used to fetch the DDL for all tables in the current schema, filtering out nested tables and overflow segments. The SET_TRANSFORM_PARAM (with the handle value equal to DBMS_METADATA.SESSION_TRANSFORM meaning “for the current session”) is used to specify that storage clauses are not to be returned in the SQL DDL.

Afterward, the session-level transform parameters are reset to their defaults. Once set, transform parameter values remain in effect until specifically reset to their defaults.

For example, if you have created a table :

create table idx3_tab (
   name  varchar2(30),
   id    number,
   addr  varchar2(100),
   phone varchar2(30)) tablespace users;

And then wanted to generate the table creation script, run the following query:

sql> select dbms_metadata.get_ddl('TABLE','IDX3_TAB') from dual;

The output would be:

CREATE TABLE "TEST"."IDX3_TAB"
   (    "NAME" VARCHAR2(30),
        "ID" NUMBER,
        "ADDR" VARCHAR2(100),
        "PHONE" VARCHAR2(30)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0
  FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS"

To get the create table definition without the storage clause you could do as follows:

EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);

The output should be PL/SQL procedure successfully completed. And then if you run:

set long 100000
select dbms_metadata.get_ddl('TABLE','IDX3_TAB') from dual;

would return:

CREATE TABLE "TEST"."IDX3_TAB"
  (    "NAME" VARCHAR2(30),
       "ID" NUMBER,
       "ADDR" VARCHAR2(100),
       "PHONE" VARCHAR2(30)
  ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING TABLESPACE "USERS"

Another example would be:

create type person as object (
  name varchar2(20),
  age number);
/
create type v0 as varray(5) of person;
/
create type n1 as table of v0;
/
create type n2 as object (n2_c1 n1);
/

create table tab11 (
  c1 n2)
nested table c1.n2_c1 store as tab11_c1_n1 (
  varray column_value store as lob tab11_c1_v1)
RETURN AS LOCATOR;
set long 100000
select dbms_metadata.get_ddl('TABLE','TAB11') from dual;

would show an output like:

CREATE TABLE "TEST"."TAB11"
  (    "C1" "TEST"."N2"
  ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING TABLESPACE "USERS"
NESTED TABLE "C1"."N2_C1" STORE AS "TAB11_C1_N1"
(PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING
VARRAY "COLUMN_VALUE" STORE AS LOB "TAB11_C1_V1"
 (ENABLE STORAGE IN ROW CHUNK 4096 PCTVERSION 10
 CACHE )) RETURN AS LOCATOR

This tool would avoid the work of writing a select statement which would combine data dictionary views to get the desired output. If we want the definition of all the objects in the database we could get the definition from the export dump.

Below are some other examples which you can use to get DDL of other objects in Oracle database.

Get The DDL For All Tables In A Specific Schema

Connect as the user for which the table DDLs are to be fetched and execute the following statement.

SET LONG 2000000
SET PAGESIZE 0
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);
SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name)
FROM USER_TABLES u
WHERE u.nested='NO'
AND (u.iot_type is null or u.iot_type="IOT");
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'DEFAULT');

Get DDL for all indexes in a schema

Connect as the schema user for which the index DDLs are to be fetched and execute the following code:

SET LONG 2000000
SET PAGESIZE 0
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);
SELECT DBMS_METADATA.GET_DDL('INDEX',u.index_name)
FROM USER_INDEXES u ;
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'DEFAULT');

For example:

SET LONG 2000000
SET PAGESIZE 0
SQL> EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);

PL/SQL procedure successfully completed.
SQL> SELECT DBMS_METADATA.GET_DDL('INDEX',u.index_name)FROM USER_INDEXES u ;


CREATE INDEX "SCOTT"."TEST_PK" ON "SCOTT"."TEST" ("COL")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
TABLESPACE "USERS"

EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'DEFAULT');
How to get the DDL for indexes using dbms_metadata

Get DDL for tablespaces

You can generate all tablespaces get ddl (create script) as follows.

set head off echo off
select 'select dbms_metadata.get_ddl(''TABLESPACE'','''
|| tablespace_name || ''') from dual;' from dba_tablespaces;

You can get the Users tablespace DDL (create script) as follows:

SQL> select dbms_metadata.get_ddl('TABLESPACE','USERS') from dual;

CREATE TABLESPACE "USERS" DATAFILE
SIZE 68717379584
AUTOEXTEND ON NEXT 68717379584 MAXSIZE 65535M,
SIZE 64424509440
AUTOEXTEND ON NEXT 64424509440 MAXSIZE 65535M,
SIZE 10737418240
AUTOEXTEND ON NEXT 10737418240 MAXSIZE 65535M
LOGGING ONLINE PERMANENT BLOCKSIZE 16384
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
COLUMN STORE COMPRESS FOR QUERY HIGH NO ROW LEVEL LOCKING SEGMENT SPACE MANAGEMENT AUTO;

Get DDL for Users

Extract User DDL and all privileges granted including roles, system and object privileges, tablespace quota and non-default profile using dbms_metadata.get_granted_ddl. Please refer below post for more information.

How to get DDL for User including Privileges and Roles Using dbms_metadata.get_ddl