Oracle DBMS_METADATA.GET_DDL: Extract Object DDL

calendar Mar 7, 2024 / · 9 min read · DDL Scripting dbms_metadata user_objects user_all_tables  ·
Share on: linkedin copy

Oracle DBMS_METADATA.GET_DDL: Extract Object DDL

Purpose

Oracle 9i introduced the DBMS_METADATA package. It can retrieve object definitions as DDL or as XML. The function GET_DDL turns that ability into a single query. You give it an object type, an object name and a schema. It returns the creation statement as a CLOB.

A DBA needs this when a table must be copied to another database, when a change must be written down before it is made, or when someone asks how an object was built. The query below returns the DDL of one table. Two SET commands come first. Without them, SQL*Plus can cut the output or wrap it in headings.

The package has existed since Oracle 9i. The function signature and the SQL*Plus settings below follow the 19c documentation.

Code

1set pagesize 0
2set long 90000
3select dbms_metadata.get_ddl('TABLE','TABLE_A','ANDY') from dual;

Three more patterns that DBAs use with the same package:

 1-- DDL for the tables, views, sequences and synonyms in your own schema
 2select dbms_metadata.get_ddl(object_type, object_name, user)
 3from   user_objects
 4where  object_type in ('TABLE','VIEW','SEQUENCE','SYNONYM');
 5
 6-- Object grants on one table, and system grants for one user
 7select dbms_metadata.get_dependent_ddl('OBJECT_GRANT','EMPLOYEES','HR') from dual;
 8select dbms_metadata.get_granted_ddl('SYSTEM_GRANT','SCOTT') from dual;
 9
10-- Leave storage clauses out of the DDL for the current session, then reset
11set long 2000000
12set pagesize 0
13execute dbms_metadata.set_transform_param(dbms_metadata.session_transform,'STORAGE',false);
14select dbms_metadata.get_ddl('TABLE',u.table_name)
15from   user_all_tables u
16where  u.nested='NO'
17and    (u.iot_type is null or u.iot_type='IOT');
18execute dbms_metadata.set_transform_param(dbms_metadata.session_transform,'DEFAULT');

Code Breakdown

The SQL*Plus settings

  • set pagesize 0 sets the page size to zero. A page size of zero suppresses all headings, page breaks, titles, the initial blank line, and other formatting information. The DDL comes out as plain text, with no column heading in the middle of it.
  • set long 90000 sets the maximum width, in bytes, that SQLPlus uses to display LONG, CLOB and similar values. GET_DDL returns a CLOB, so this setting decides how much of the DDL you see. The package documentation tells SQLPlus users to set PAGESIZE to 0 and LONG to a large number to get complete, uninterrupted output. The value 90000 is large enough for many tables. A table with many columns, partitions or constraints may need more, so raise it when the text stops early.

The GET_DDL call

select dbms_metadata.get_ddl('TABLE','TABLE_A','ANDY') from dual; has these parts.

  • dbms_metadata.get_ddl is the function. Its signature is GET_DDL(object_type, name, schema, version, model, transform), and it returns a CLOB. Only the first two arguments are required. The other four have defaults.
  • 'TABLE' is the object type. The documentation says it takes the same values as the OPEN object type parameter, and it must be a named object. Values include TABLE, VIEW, INDEX, SEQUENCE, PROCEDURE, FUNCTION, PACKAGE_SPEC, PACKAGE_BODY, TRIGGER and SYNONYM.
  • 'TABLE_A' is the object name.
  • 'ANDY' is the schema that owns the object. The documentation says the default is the current user. That is why the user_objects example above does not need a schema value.
  • from dual gives the query a table to select from. DUAL is a table created by Oracle Database with the data dictionary. It has one column, DUMMY, and one row with the value X. Because it has only one row, the result comes back once.

The other three patterns

  • The user_objects query passes the object type and name from the dictionary view into GET_DDL, so one select returns one row of DDL per object. Each call still returns a single named object. The where clause is an addition to the first draft of this query. It keeps only type names that match the GET_DDL list above. Some USER_OBJECTS type names may not match the names that GET_DDL accepts. The documentation lists package types as PACKAGE_SPEC and PACKAGE_BODY, with an underscore. If you widen the list, run the query on a test schema first and watch for INVALID_ARGVAL errors.
  • GET_DEPENDENT_DDL fetches dependent objects, such as object grants. You pass the object type, the base object name and the base object schema. GET_GRANTED_DDL fetches granted objects, such as system grants and role grants. You pass the object type and the grantee.
  • The STORAGE example uses SET_TRANSFORM_PARAM with DBMS_METADATA.SESSION_TRANSFORM, which means "for the current session". It tells the package not to return storage clauses in the DDL. The last call resets the session-level parameters to their defaults.

Key Points

  • One named object per GET_DDL call. GET_xxx returns a single named object. To pull DDL for a group of objects, use a select over a dictionary view, as shown above.
  • Dependent and granted objects have their own functions. GET_DEPENDENT_xxx fetches dependent objects such as audits and object grants. GET_GRANTED_xxx fetches granted objects such as system grants and role grants. For both, you can set an object count. The default count is 10000, which the documentation calls adequate in most cases.
  • Privilege decides what you see. The package treats a user connected as SYS, or a user with the SELECT_CATALOG_ROLE role, as privileged. Nonprivileged users can see the metadata of only their own objects. If a caller asks for an object it may not retrieve, no exception is raised, and the object is simply not retrieved.
  • Roles are off inside stored code. In stored procedures, functions and definer's-rights packages, roles such as SELECT_CATALOG_ROLE are disabled. Such a PL/SQL program can fetch metadata only for objects in its own schema.
  • Session transform parameters carry over. If the DDL transform is specified, session-level transform parameters are inherited. A setting you made earlier in the session therefore changes later GET_DDL results until you reset it.
  • Errors are specific. OBJECT_NOT_FOUND means the object was not found in the database. INVALID_ARGVAL means a NULL or invalid value was supplied for an input parameter, and the message names the parameter.

Insights and Best Practices

Shape the output before you spool it

If you save DDL to a script file, decide the format first. By default the output has no statement terminators. SQLTERMINATOR set to true adds a terminator (; or /) to each statement. PRETTY formats the output with indentation and line feeds, and it already defaults to true, so setting it only makes the choice explicit. Both are shown here:

1begin
2  dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'SQLTERMINATOR', true);
3  dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'PRETTY', true);
4end;
5/

Setting SEGMENT_ATTRIBUTES and STORAGE to false removes those elements from the definitions. Remove them when you plan to run the script on a database with different storage layout. Test the script on a scratch schema before you rely on it.

For the spool itself, the SQLPlus settings help. SET LINESIZE sets the total number of characters that SQLPlus displays on one line before beginning a new line. SET TRIMSPOOL ON removes blanks at the end of each spooled line. SET HEADING OFF suppresses the printing of column headings. Together with PAGESIZE 0, they give a clean text file.

Use a loop only when you need one

The user_objects query is a quick way to get the DDL of the common object types in a schema. Run it in a test schema first, because every row in the view becomes one CLOB. The documentation notes that for some object types you can use more than one function. For example, you can use GET_xxx to fetch an index by name, or GET_DEPENDENT_xxx to fetch the same index by specifying the table on which it is defined.

Keep grants with the objects

After you extract the table, extract its object grants with GET_DEPENDENT_DDL('OBJECT_GRANT', ...). Extract the system grants of the owning user with GET_GRANTED_DDL('SYSTEM_GRANT', ...). Store all three results in the same file, in that order.

Run it as the right user

If you only need your own objects, connect as the owner. The package lets nonprivileged users see the metadata of their own objects. If you need objects in other schemas, connect as a user that has SELECT_CATALOG_ROLE, and call the function from a script that you run in SQL*Plus. Do not call it from a definer's-rights procedure and expect the role to work, because roles are disabled there.

Keep a DDL history

Save the output with a date in the file name before every structural change. A text file of DDL, kept in version control, is a cheap record of what an object looked like before the change. The file also gives you a base for a rollback script.

When to Use This Check

  • Before you change a table, view or package, to keep the old definition.
  • When you copy an object to a test or development database.
  • When you document a schema for a new team member or an audit.
  • When two environments behave differently and you need to compare object definitions side by side.
  • When you need the grants that go with an object, not only the object.
  • When a developer asks how an object was created and no script exists.

Troubleshooting Common Issues

The DDL stops in the middle of a statement. The LONG setting is too small for the CLOB. Raise it, for example to 2000000, and run the query again. SET LONGCHUNKSIZE sets the size, in bytes, of the increments SQL*Plus uses to retrieve a CLOB or LONG value. It is a second setting to check.

Headings and blank lines appear in the output. Set PAGESIZE to 0 before the query. Add SET HEADING OFF if a heading is still printed.

You get OBJECT_NOT_FOUND. The object does not exist in the schema you named. Query user_objects or dba_objects for the exact name, type and owner, then try again. If you call the function from a stored procedure, remember that the procedure can fetch metadata only for objects in its own schema.

The query returns no row for an object you know exists. The package does not raise an exception when you are not privileged to retrieve an object. It simply does not retrieve it. Connect as the owner or as a user with SELECT_CATALOG_ROLE and run the query again.

INVALID_ARGVAL appears. A NULL or invalid value was passed. The error message names the parameter. Check the object type string first, since it must be a value the package accepts.

The DDL has no storage clause, or it still has one. A transform parameter set earlier in the session may be active. Reset the session transform parameters to their defaults, or open a new session.

References

Posts in this series