
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:
- fast, especially if you have your script ready for extraction. No need to click through GUI tools and wait until the objects load.
- 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.
- 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
1 Comment
Leave a Reply Cancel reply
This site uses Akismet to reduce spam. Learn how your comment data is processed.
Thanks for the share