Query to get object DDL in Oracle database: 

1. DBA will most commonly get request to provide the metadata information of a object in a database

2. DBA will most commonly get a request a create object on development database similar to object which are in PROD database.
 

These requests may be for various purpose like

auditing,

migration,

backup purposes etc

 

Query to GET DDL structure:

set long 9999999

set lines 200 pages 2000

col METADATA format a180 word_wrapped

select dbms_metadata.get_ddl(upper('&Object_type'),upper('&Object_name'),upper('&Owner')) "METADATA" from dual;

 

Provide input:

Enter value for object_type: INDEX/TABLE/VIEW etc 

Enter value for object_name: NAME_OF_OBJECT 

Enter value for owner: OWNER_OF_OBJECT

 

Example:

1. Connect to database as mallik user and create an dummy table T1

 

[oracle@node1 ~]$ env |grep ORA

ORACLE_SID=DEVDB1

ORACLE_BASE=/u01/app/oracle

ORACLE_HOME=/u01/app/oracle/product/19.0.0.0/dbhome_1

[oracle@node1 ~]$

[oracle@node1 ~]$ sqlplus mallik/mallik

 

SQL*Plus: Release 19.0.0.0.0 - Production on Tue May 13 00:18:05 2025

Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Last Successful login time: Tue May 13 2025 00:15:56 +05:30

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

 

SQL> select * from T1;

 

      STNO STNAME          DOA             FEES

---------- --------------- --------- ----------

         1 MALLIK          12-SEP-25        300

         1 MALLIK          12-SEP-25        300

 

SQL> describe T1;

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 STNO                                               NUMBER(3)

 STNAME                                             VARCHAR2(15)

 DOA                                                DATE

 FEES                                               NUMBER(3)

 

SQL>

 

2. Connect to sys user and get the DDL definition of that dummy table T1 belongs to mallik user

 

[oracle@node1 ~]$ sqlplus / as sysdba

 

SQL*Plus: Release 19.0.0.0.0 - Production on Tue May 13 00:18:38 2025

Version 19.3.0.0.0

 

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

 

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

 

SQL> set long 9999999

SQL> set lines 200 pages 2000

SQL> col METADATA format a180 word_wrapped

SQL> select dbms_metadata.get_ddl(upper('&Object_type'),upper('&Object_name'),upper('&Owner')) "METADATA" from dual;

 

Enter value for object_type: TABLE

Enter value for object_name: T1

Enter value for owner: MALLIK

old   1: select dbms_metadata.get_ddl(upper('&Object_type'),upper('&Object_name'),upper('&Owner')) "METADATA" from dual

new   1: select dbms_metadata.get_ddl(upper('TABLE'),upper('T1'),upper('MALLIK')) "METADATA" from dual

 

METADATA

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

CREATE TABLE "MALLIK"."T1"

(       "STNO" NUMBER(3,0),

"STNAME" VARCHAR2(15),

"DOA" DATE,

"FEES" NUMBER(3,0)

) SEGMENT CREATION IMMEDIATE

PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

NOCOMPRESS LOGGING

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

  TABLESPACE "USERS"

 

SQL> exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

[oracle@node1 ~]$