When, Why And How To Use DBMS_METADATA Oracle Package

Never in DBA history has there been a better time than now. There are so many GUI tools available for DBAs to use, free and paid, it is hard to even choose from.

One might think that the use of DBMS_METADATA package is obsolete, or old school. Before completely dismissing the idea of using this package think again.

It seems very easy to open up Toad, SQL Developer or a GUI tool of your choice, and just click through the menu to get to the object you are interested in. Retrieving the object DDL this way is totally fine.

Imagine working for a client that has no GUI tools. (I know this sounds disturbing for many). You need to use SQLPLUS.

This is actually reality, there are clients who do not have GUI tools available for DBAs to use, and DBAs are expected to deliver.

So what do you do, if you need to get the DDL for a table, or a user, or any object?

You do your DBA Magic. Pull out your script and say the magic words: DBMS_METADATA.

Let’s explore the following today:

1.When To Use DBMS_METADATA?
2.Why Use DBMS_METADATA?
3.How To Use DBMS_METADATA?

1.When To Use DBMS_METADATA?

Use DBMS_METADATA anytime you need to extract the DDL of an object, of a user or role.
Let me give you an example: you are changing the password for an application schema in the database. Prior to the password change you want to backup the user’s encrypted password. One way to do this and script it is to extract the user DDL, with DBMS_METADATA.

Use DBMS_METADATA during database refreshes, to extract permissions, users’ passwords and other things, prior to the refresh. The generated scripts can be applied after the refresh, to preserve permissions, passwords and other settings.

Use DBMS_METADATA during database upgrades, if you upgrade using export – import method, to port over users, roles, permissions and other things.

Use DBMS_METADATA when you need to backup certain objects that are going to be changed or dropped.
Some of you might think it is not your responsibility to backup object DDL.
Remember my mantra: “A Good DBA Never Assumes“?
Do not assume that others have backups of object DDL, even if they have backups, might not be the latest version.

2.Why Use DBMS_METADATA?

My favorite three reasons to use DBMS_METADATA:

1. fast, especially if you have your script ready for extraction. No need to click through GUI tools and wait until the objects load.
2. no special software is required. You only need to have SQLPLUS. It is 100% guaranteed SQLPLUS is always available on the database server, no matter what.
3. you can script the DDL extraction, automate it, spool the results. It is less prone to errors, more prone to success.

3.How To Use DBMS_METADATA

I will not go through all the subprograms and requirements of DBMS_METADATA package. You can ready through Oracle documentation for that purpose.

A good script that extract object DDL, needs to have the following features:

– be dynamic, can be run for different objects, without making changes to the script
– output of the script needs to be clean, readable, aligned, without split lines.
– generated statements need to be terminated with “;”
– no heading and no feedback
– generated statements can be run without any errors in the database.

I’ll share with you two of the scripts I use: get_table_ddl.sql, to get table and associated indexes ddl, and get_user_ddl.sql to get the user create statements and granted roles and permissions.

You get the idea on how to use this package and create your scripts.

SQL>@get_table_ddl.sql OWNER TABLE_NAME

/*
Script name: get_table_ddl.sql
 
Example:     @get_table_ddl.sql OWNER TABLE_NAME
 
Created By   Diana Robete
Copyright:   @2016 dbaparadise.com
*/
 
set feedback off;
set verify off;
set lines 256;
set pages 0;
set heading off;
set trimspool on;
set define '&'
set long 50000
column line format A254 word
 
exec dbms_output.enable(2000000);
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SQLTERMINATOR',TRUE);
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'STORAGE',FALSE);
 
define tbl_owner=&1
define tbl_name=&2
 
spool &tbl_owner..&tbl_name._table_ddl.sql
 
select '-- Table DDL for &tbl_owner..&tbl_name' from dual;
 
select dbms_metadata.get_ddl('TABLE',upper('&tbl_name'),upper('&tbl_owner')) line
from dual
/
 
select '-- Indexes for &tbl_owner..&tbl_name' from dual;
 
select dbms_metadata.get_dependent_ddl('INDEX',upper('&tbl_name'),upper('&tbl_owner')) line 
from dual
/
spool off
********************************************************************************************
Sample Output
SQL> @get_table_ddl.sql SCOTT DEPT
-- Table DDL for SCOTT.DEPT
CREATE TABLE "SCOTT"."DEPT"
(	"DEPTNO" NUMBER(2,0),
"DNAME" VARCHAR2(14),
"LOC" VARCHAR2(13),
CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
TABLESPACE "USERS"  ENABLE
) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  TABLESPACE "USERS" ;
 
-- Indexes for SCOTT.DEPT
CREATE UNIQUE INDEX "SCOTT"."PK_DEPT" ON "SCOTT"."DEPT" ("DEPTNO")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
TABLESPACE "USERS" ;
********************************************************************************************

SQL>@get_user_ddl.sql USERNAME

/*
Script name: get_user_ddl.sql
 
Example:     @get_user_ddl.sql USERNAME
 
Created By   Diana Robete
Copyright:   @2016 dbaparadise.com
*/
 
set feedback off;
set verify off;
set lines 256;
set pages 0;
set heading off;
set trimspool on;
set define '&'
set long 50000
column line format A254 word
 
exec dbms_output.enable(2000000);
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SQLTERMINATOR',TRUE);
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'STORAGE',FALSE);
 
define username=&1
 
spool &username._ddl.sql
 
select '-- User DDL for &username' from dual;
 
select dbms_metadata.get_ddl('USER',upper('&username')) line
from dual
/
 
select '-- Role Grants for &username' from dual;
select dbms_metadata.get_granted_ddl('ROLE_GRANT',upper('&username')) line 
from dual
/
 
select '-- System Grants for &username' from dual;
 
select dbms_metadata.get_granted_ddl('SYSTEM_GRANT',upper('&username')) line 
from dual
/
 
select '-- Object Grants for &username' from dual;
 
select dbms_metadata.get_granted_ddl('OBJECT_GRANT',upper('&username')) line 
from dual
/
 
spool off
********************************************************************************************
--Sample Output
 
SQL> @get_user_ddl.sql SCOTT
-- User DDL for SCOTT
CREATE USER "SCOTT" IDENTIFIED BY VALUES 'S:821416645D197C9CA8ECC1AD50B183F96B516AA7523DB1FB972E59AAF6C4;H:63EC639D32F2C215B68C56321EE236FE;F894844C34402B67'
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP"
PASSWORD EXPIRE
   ACCOUNT LOCK;
 
-- Role Grants for SCOTT
GRANT "CONNECT" TO "SCOTT";
GRANT "RESOURCE" TO "SCOTT";
 
-- System Grants for SCOTT
GRANT UNLIMITED TABLESPACE TO "SCOTT";
-- Object Grants for SCOTT
GRANT SELECT ON "SYS"."DBA_TABLES" TO "SCOTT";
********************************************************************************************

If you enjoyed this article, and would like to learn more about databases, please sign up below, and you will receive
The Ultimate 3 Step Guide To Find The Root Cause Of The Slow Running SQL!

-Diana