Introduction to DBMS_Metadata.get_ddl
DBMS_metadata is a utility package that helps display the Data Definition Language and any stored procedures from the data dictionary wherein any object names can be easily obtained is called DBMS_metadata. We can retrieve the metadata from the dictionary either as an XML file or a DDL file that helps create XML objects from scratch. For example, Dbms_metadata.get_ddl is used to get all the DDL entries from the tables, and this output can be entered into SQL script by managing with queries. This is mostly used in Oracle, and we can use it in other servers with slight changes.
Dbms_metadata.get_ddl Overviews
1. Metadata Retrieval: We can retrieve either an object specifically or a collection of object types with this package. Selective options are also available such as user names or any specific table names that can be retrieved easily. Whenever queries return objects, the objects can be parsed easily with the help of the dbms_metadata package. We can also transform the input with the help of StyleSheet language, where XML can be transformed into SQL DDL or any required languages depending on the requirement. This is done with the help of Extensible StyleSheet Language Transformation scripts or XSLT. Retrieval interfaces are available such as OPEN, CLOSE, CONVERT, PUT, etc. There are separate queries available for SQL, such as GET_XML and GET_DDL.
GET_DEPENDENT_XML, GET_GRANTED_XML, GET_DEPENDENT_DDL, GET_GRANTED_DDL are other interfaces for grouped objects.
2. XML Submission: Type of object and transformation parameters must be specified while doing XML submission so that the package can easily find out the object and do the necessary changes automatically. Also, we should specify whether the operation must be carried out or just wants to return the DDL queries. Parse items also should be specified in this criteria, and OPENW, CONVERT, CLOSE, PUT, ADD_TRANSFORM, SET_REMAP_PARAM, SET_TRANSFORM_PARAM, and SET_ITEM_PARAM are the queries to be used while doing XML submission.
Generating create scripts through dbms_metadata package
Create scripts of tables, indexes, packages, and all others are always required by developers, and the dbms_metadata package helps to get all the required scripts easily. Get DDL script is used to retrieve all the required metadata of the table.
DBMS_METADATA.GET_DDL (
object_type IN VARCHAR2,
name IN VARCHAR2,
schema IN VARCHAR2 DEFAULT NULL,
version IN VARCHAR2 DEFAULT 'COMPATIBLE',
model IN VARCHAR2 DEFAULT 'ORACLE',
transform IN VARCHAR2 DEFAULT 'DDL')
RETURN CLOB;
We can get the schema of any object in this package using the following script.
select dbms_metadata.get_ddl('USER', du.username) AS DDL_SCRIPT
from dba_users du
where du.username = 'TYPE_USER_NAME'
union all
select dbms_metadata.get_granted_ddl('TABLESPACE_QUOTA', dtq.username) AS DDL_SCRIPT
from dba_ts_quotas dtq
where dtq.username = 'TYPE_USER_NAME'
and rownum = 1
union all
select dbms_metadata.get_granted_ddl('ROLE_GRANT', drp.grantee) AS DDL_SCRIPT
from dba_role_privs drp
where drp.grantee = 'TYPE_USER_NAME'
and rownum = 1
union all
select dbms_metadata.get_granted_ddl('SYSTEM_GRANT', dsp.grantee) AS DDL_SCRIPT
from dba_sys_privs dsp
where dsp.grantee = 'TYPE_USER_NAME'
and rownum = 1
union all
select dbms_metadata.get_granted_ddl('OBJECT_GRANT', dtp.grantee) AS DDL_SCRIPT
from dba_tab_privs dtp
where dtp.grantee = 'TYPE_USER_NAME'
and rownum = 1
union all
select dbms_metadata.get_granted_ddl('DEFAULT_ROLE', drp.grantee) AS DDL_SCRIPT
from dba_role_privs drp
where drp.grantee = 'TYPE_USER_NAME'
and drp.default_role = 'YES'
and rownum = 1;
This can be explained with an example.
CREATE USER "EMPLOYEES" IDENTIFIED BY VALUES 'S:DFDE2EB3729B9D88FC8F7492942D2EA6476BF291FECB0DC56F2A64867F17;T:7D2C79E56B7427C7D5E4E2F209FE4F35524316F9116D15803A5AB1688CAB6DBB1BAC36145075F68C4521E1EA744F2BD53FA8BC08F4BC384B5A0E04D832D49E813BC849BF01C7F38DC780E9BC3C9391E2;8855019455223980'
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP"
PROFILE "PERSONAL";
GRANT "DBA" TO " EMPLOYEES ";
GRANT UNLIMITED TABLESPACE TO "EMPLOYEES ";
ALTER USER " EMPLOYEES " DEFAULT ROLE ALL;
SQL>
Get DDL of a table
Table create script can be written like this.
select dbms_metadata.get_ddl( 'TABLE', 'TABLE_NAME','SCHEMA_NAME' ) from dual
We are creating a new table from this script by giving the table name as Employees.
select dbms_metadata.get_ddl('TABLE', 'OFFER_CLASS','EMPLOYEES') from dual
SQL> set echo off
SQL> set pagesize 0
SQL> set verify off
SQL> set feedback off
SQL> select dbms_metadata.get_ddl('TABLE', 'OFFER_CLASS','EMPLOYEES') from dual;
CREATE TABLE "EMPLOYEES"."OFFER_CLASS"
( "OFFER_ID" VARCHAR2(30 CHAR) NOT NULL ENABLE,
"OFFER_CLASS_SCHEME_CD" VARCHAR2(16 CHAR) NOT NULL ENABLE,
"VALID_FROM_DT" DATE NOT NULL ENABLE,
"VALID_UNTIL_DT" DATE NOT NULL ENABLE,
"OFFER_CLASS_VALUE_CD" VARCHAR2(40 CHAR) NOT NULL ENABLE,
"SOURCE_SYSTEM_CD" VARCHAR2(5 CHAR) NOT NULL ENABLE,
"INSERT_DT" DATE,
"UPDATE_DT" DATE
) SEGMENT CREATION IMMEDIATE
PCTFREE 0 PCTUSED 40 INITRANS 1 MAXTRANS 255
COLUMN STORE COMPRESS FOR QUERY HIGH NO ROW LEVEL LOCKING NOLOGGING
STORAGE(INITIAL 81920 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"
PARALLEL 96
SQL>
Any schema’s tables can be fetched easily in this package that also provides all table’s DDL.
SELECT DBMS_METADATA.get_ddl ('TABLE', table_name, owner)
FROM all_tables
WHERE owner = UPPER('&1')
Index Create Script
It is easy to create scripts for indexes using the dbms_metadata package. This is an example of index creation.
select dbms_metadata.get_ddl( 'INDEX', 'INDEX_NAME','SCHEMA_NAME' ) from dual
Another example of creating scripts of indexes with EMPLOYEES.PK_OFFER_CLASS is as follows.
SQL> set long 100000
SQL> set head off
SQL> set echo off
SQL> set pagesize 0
SQL> set verify off
SQL> set feedback off
SQL> select dbms_metadata.get_ddl('INDEX','PK_OFFER_CLASS',' EMPLOYEES ') from dual;
CREATE UNIQUE INDEX " EMPLOYEES"."PK_OFFER_CLASS" ON "EMPLOYEES"."OFFER_CLASS" ("OFFER_CLASS_SCHEME_CD", "VALID_FROM_DT", "OFFER_ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TBS_USER" ;
SQL>
Functions – dbms_metadata.get_ddl
All views can be taken from the package with the script’s help.
SELECT DBMS_METADATA.GET_DDL(‘VIEW’,VIEW_NAME) FROM USER_VIEWS
Functions and procedures can also be fetched from the server with the package.
SELECT DBMS_METADATA.GET_DDL('FUNCTION', OBJECT_NAME) FROM USER_PROCEDURES WHERE OBJECT_TYPE = 'FUNCTION'
Also, any package’s DDL script can be fetched easily from the server.
SQL> SELECT DBMS_METADATA.GET_DDL('PACKAGE','OBJECT_NAME','SCHEMA_NAME') FROM DUAL
SQL> SELECT DBMS_METADATA.GET_DDL('PACKAGE_BODY','OBJECT_NAME','SCHEMA_NAME') FROM DUAL
We can set constraints to the table with the help of queries
SQL> SELECT DBMS_METADATA.GET_DEPENDENT_DDL ('REF_CONSTRAINT','OBJECT_NAME','SCHEMA_NAME') from dual
We can grant access to the system or user with the help of the DBMS_metadata package.
SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','SCHEMA_NAME') from dual
SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','SCHEMA_NAME') from dual
Conclusion
This function helps sort out all the DDL functions in one go, which is faster than other queries. We can do many applications with this query, and also we can set access and grant permissions in this query. Moreover, constraints can be set clearly in this package.
Recommended Articles
This is a guide to DBMS_Metadata.get_ddl. Here we discuss the definition, overviews, Generating create scripts through dbms_metadata package for better understanding. You may also have a look at the following articles to learn more –
