As a DBA, I find myself frequently in the situation that it would be nice to have the DDL of an object. One of the most common cases is if a database object got invalid. This invalidation could be during an application deployment or a database migration (new database, new version). It is useful to know the definition of the invalid object to find the root cause of the invalidation.
There are different ways of getting the definition of an object out of the database. Some of them will be discussed in future blog posts (data pump, sqlCL). The way I describe in this blog post is the usage of the DBMS_METADATA package. As a DBA I use this package if I’m working directly on a server and I am connected to the database via sqlplus. It is an easy way of analyzing the structure of an object.
What is DBMS_METADATA capable of?
The package is compelling to describe all the functionalities the package provides this article would be too large. With this package, you are able to gather object metadata for objects in XML, SCML or DDL format and you are also able to work with XML which would be sent to the database.
In this article, I only focus on getting DDL statements for an object.
|
1 2 3 4 5 6 7 8 |
SQL> SELECT DBMS_METADATA.GET_DDL(OBJECT_TYPE => 'TABLE', NAME => 'DUAL', SCHEMA => 'SYS') DDL FROM DUAL; DDL -------------------------------------------------------------------------------- CREATE TABLE "SYS"."DUAL" SHARING=METADATA ( "DUMMY" VARCHAR2(1) ... |
If you are logged as user SYS or as a user with the SELECT_CATALOG role you can gather the DDL for all database objects. If you are logged in with a different user, you are only able to see the metadata of your objects.
How to use DBMS_METADATA?
The subprogram for extracting DDL from any database object is GET_DDL. This subprogram requires two parameters OBJECT_TYPE and NAME. If you do not own the database object, you need the SCHEMA parameter additionally.
|
1 2 3 4 5 6 7 8 |
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; |
You can use DBMS_METADATA.GET_DDL directly in a SQL statement. The easiest way is in combination with the dual table. Be aware if you get the metadata of a table you also get the default storage options for your current installation:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
SQL> CREATE TABLE DEMO (ID NUMBER, CONTENT VARCHAR2(400)); Table created. SQL> SELECT DBMS_METADATA.GET_DDL(OBJECT_TYPE => 'TABLE', NAME => 'DEMO') DDL FROM DUAL; CREATE TABLE "SYS"."DEMO" ( "ID" NUMBER, "CONTENT" VARCHAR2(400) ) 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 "SYSTEM" |
If you would like to suppress the storage, you can set the TRANSFORM Parameters for this execution. There are many parameters you can set for the TRANSFORM of the output ( TRANSFORM documentation). To disable all storage parameters you can use (SEGMENT_ATTRIBUTES). There is also a parameter PRETTY (Default => TRUE) which formats the output with line intents and feeds.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
SQL> EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SEGMENT_ATTRIBUTES', FALSE); SQL> EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'PRETTY', TRUE); SQL> SELECT DBMS_METADATA.GET_DDL(OBJECT_TYPE => 'TABLE', NAME => 'DEMO') ddl from dual; DDL ---------------------------------------------------------------------------------------------------- CREATE TABLE "SYS"."DEMO" ( "ID" NUMBER, "CONTENT" VARCHAR2(400) ) ; |
Conclusion
If you need to extract the DDL of some statements to either compare the DDL with the one you have in your source control or to find bugs in your application, DBMS_METADATA is an easy tool for doing that.
