Posted by Ask an Oracle practitioner what DBMS_METADATA is used for, and they’ll (correctly) tell you it used to retrieve the DDL for an object.
In fact, if you’re using any of the standard Oracle tools – SQL Developer, SQLcl, DataPump etc, you’ll quickly recognise the structure and format of the output. Just about every tool I know of that can give you the DDL is going to use DBMS_METADATA to get that DDL, making it a consistent “one-stop-shop” for getting the DDL.
SQL> select DBMS_METADATA.GET_DDL('TABLE','EMP','SCOTT') from dual;
DBMS_METADATA.GET_DDL(UPPER('TABLE'),UPPER('EMP'),UPPER('SCOTT'))
----------------------------------------------------------------------------
CREATE TABLE "SCOTT"."EMP"
( "EMPNO" NUMBER(4,0) NOT NULL ENABLE,
"ENAME" VARCHAR2(10) COLLATE "USING_NLS_COMP",
"JOB" VARCHAR2(9) COLLATE "USING_NLS_COMP",
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0),
CONSTRAINT "EMP_PK" PRIMARY KEY ("EMPNO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
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" ENABLE,
CONSTRAINT "EMP_FK" FOREIGN KEY ("DEPTNO")
REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE
) DEFAULT COLLATION "USING_NLS_COMP" 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"
Note: If you’re not happy with the verbose output for DDL, check out my Clean DDL post in order to get a nicer, more lean output.
But DBMS_METADATA also a partnering package that provides another very useful facility. It can be used to generate the DDL to migrate the structure of one object to another. Hopefully you are using source code control and/or facilities like Liquibase to manage your database objects, but as a backup to that robust solution that I am sure you already have in place 😆, you can use DBMS_METADATA_DIFF to examine two objects. I’ll start with two tables EMP and EMP2 which are identical.
SQL> create table emp as select * from scott.emp;
Table created.
SQL> create table emp2 as select * from scott.emp;
Table created.
But now I’ll make some changes to EMP2 so that it is now slightly different to EMP.
SQL> alter table emp2 pctfree 20;
Table altered.
SQL> alter table emp2 add newcol number;
Table altered.
The COMPARE_ALTER function in DBMS_METADATA_DIFF can then be used to generate the required DDL to transform the EMP table so that it would now match the EMP2 table.
SQL> select dbms_metadata_diff.compare_alter('TABLE','EMP','EMP2')
2 from dual;
DBMS_METADATA_DIFF.COMPARE_ALTER('TABLE','EMP','EMP2')
----------------------------------------------------------------------------------------------------------------------------------
ALTER TABLE "MCDONAC"."EMP" ADD ("NEWCOL" NUMBER)
ALTER TABLE "MCDONAC"."EMP" PCTFREE 20
ALTER TABLE "MCDONAC"."EMP" RENAME TO "EMP2"
Note: Although I am doing this demo in a single schema, the COMPARE_ALTER function can also be done across schemas and across database links, so it can easily be used to (for example) compare the same object between (say) Development and Test environments.
FUNCTION COMPARE_ALTER RETURNS CLOB
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
OBJECT_TYPE VARCHAR2 IN
NAME1 VARCHAR2 IN
NAME2 VARCHAR2 IN
SCHEMA1 VARCHAR2 IN DEFAULT
SCHEMA2 VARCHAR2 IN DEFAULT
NETWORK_LINK1 VARCHAR2 IN DEFAULT
NETWORK_LINK2 VARCHAR2 IN DEFAULT
One thing not immediately apparent from the output above is that the response is returned as a CLOB. If I alter my ‘feedback’ setting on SQL*Plus, you can see that only a single row has been selected, even though it appears to be 3 ALTER commands.
SQL> set feedback 1
SQL> select dbms_metadata_diff.compare_alter('TABLE','EMP','EMP2')
2 from dual;
DBMS_METADATA_DIFF.COMPARE_ALTER('TABLE','EMP','EMP2')
----------------------------------------------------------------------------------------------------------------------------------
ALTER TABLE "MCDONAC"."EMP" ADD ("NEWCOL" NUMBER)
ALTER TABLE "MCDONAC"."EMP" PCTFREE 20
ALTER TABLE "MCDONAC"."EMP" RENAME TO "EMP2"
1 row selected.
This can be a little annoying because you might want to omit some of the commands, augment them in some way, or run them one at a time, and this would then mean digging around within the CLOB with INSTR and SUBSTR functions to locate each statement.
However, DBMS_METADATA_DIFF also has a function COMPARE_ALTER_XML function which will return the DDL as an XML structure. And if we have XML, then we can easily pick it apart with XMLTABLE. Here’s a simple wrapper around the original query (but now using COMPARE_ALTER_XML instead of COMPARE_ALTER) and notice that we now get a row outputted for each line of DDL.
SQL> with t as
2 (
3 select dbms_metadata_diff.compare_alter_xml('TABLE','EMP','EMP2') xml
4 from dual
5 )
6 select xt.txt||';'
7 from t,
8 xmltable(xmlnamespaces(default 'http://xmlns.oracle.com/ku'), '/ALTER_XML/ALTER_LIST/ALTER_LIST_ITEM/SQL_LIST/SQL_LIST_ITEM'
9 passing xmltype(t.xml)
10 columns
11 txt varchar2(255) path 'TEXT'
12 ) xt;
XT.TXT||';'
----------------------------------------------------------------------------------------------------------------------------------
ALTER TABLE "MCDONAC"."EMP" ADD ("NEWCOL" NUMBER);
ALTER TABLE "MCDONAC"."EMP" PCTFREE 20;
ALTER TABLE "MCDONAC"."EMP" RENAME TO "EMP2";
3 rows selected.
So there is another tool in your DDL toolbox for ensuring that your deployments and migrations remain low risk. Enjoy!
Learning is not a spectator sport 
Nice!