DBMS_METADATA – Quickstart

The DBMS_METADATA is a powerful package that offers the retrieval of metadata from the data dictionary as XML or DDL and to submit XML to re-create an object. In this post I will show you how you can quickly generate DDL statements for lots of objects in the database using some examples.

There are quite some functions in this package, but there are a couple of functions that I will focus on (and use in some examples) :

  • GET_DDL
  • GET_DEPENDENT_DDL
  • GET_GRANTED_DDL
  • OPEN
  • SET_COUNT
  • SET_FILTER
  • ADD_TRANSFORM
  • SET_TRANSFORM_PARAM
  • FETCH_CLOB
  • CLOSE

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 'DLL'
) RETURN CLOB;

This function is used to get the DDL to create named objects like tables, views, packages, tablespaces, etc.

DBMS_METADATA.GET_DEPENDENT_DDL (
  object_type         IN VARCHAR2,
  base_object_name    IN VARCHAR2,
  base_object_schema  IN VARCHAR2 DEFAULT NULL,
  version             IN VARCHAR2 DEFAULT 'COMPATIBLE',
  model               IN VARCHAR2 DEFAULT 'ORACLE',
  transform           IN VARCHAR2 DEFAULT 'DDL',
  object_count        IN NUMBER   DEFAULT 10000
) RETURN CLOB;

This function is used to get the DDL to create dependent objects (audits and object grants).

DBMS_METADATA.GET_GRANTED_DDL (
  object_type  IN VARCHAR2,
  grantee      IN VARCHAR2 DEFAULT NULL,
  version      IN VARCHAR2 DEFAULT 'COMPATIBLE',
  model        IN VARCHAR2 DEFAULT 'ORACLE',
  transform    IN VARCHAR2 DEFAULT 'DDL',
  object_count IN NUMBER   DEFAULT 10000
) RETURN CLOB;

This function is used to get the DDL to grant system privileges and roles granted to the specified user or role.

If you use this functions from an SQL*Plus session, make sure you set the pagesize to 0 and set the long size to 2000000 or something like that, otherwise you might not get a complete output.

SET PAGESIZE 0
SET LONG 2000000
SET LONGCHUNKSIZE 10000
SET LINESIZE 1000

Examples :

SQL> execute dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'SQLTERMINATOR', true);
SQL> select dbms_metadata.get_ddl('TABLESPACE','USERS') from dual;
CREATE TABLESPACE "USERS" DATAFILE
'/u02/oradata/ocmdb1/users01.dbf' SIZE 104857600
AUTOEXTEND ON NEXT 1310720 MAXSIZE 131071M
LOGGING ONLINE PERMANENT BLOCKSIZE 32768
EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO
ALTER DATABASE DATAFILE
'/u02/oradata/ocmdb1/users01.dbf' RESIZE 587202560
select dbms_metadata.get_dependent_ddl('OBJECT_GRANT','DEPARTMENTS','HR') from dual;
select dbms_metadata.get_granted_ddl('SYSTEM_GRANT','SYSTEM') from dual;

I have used this package to create automate the generation of DDL statements for tablespace creation. I needed an automated way of generating DDL to be able to create tablespaces for a database based on the tablespace definitions of an existing database. The script below will create an file named tablespaces.sql in a and oracle directory object named TEMP. There is a filter configured, that will exclude all system tablespaces, the tablespace configured as the undo tablespace and the database default temporary and permanent tablespaces.

Before using it, make sure you have configured a directory named TEMP, for example :

CREATE DIRECTORY TEMP AS '/tmp';

The script to generate the DDL statements for the creation of all (not excluded) tablespaces :

DECLARE
  CURSOR cursor_exclts IS
    SELECT
          property_value
    FROM
          database_properties
    WHERE
          property_name IN ('DEFAULT_PERMANENT_TABLESPACE','DEFAULT_TEMP_TABLESPACE');

  fHandle    UTL_FILE.FILE_TYPE;
  tsHandle   NUMBER;
  tsTHandle  NUMBER;
  tsDDL      CLOB;
  rv_excltsname   VARCHAR2(30);
  lv_excltsnames  VARCHAR2(2000);

BEGIN
  -- Open/Create a file to write DDL statement for tablespace creation
  fHandle := UTL_FILE.FOPEN ('TEMP', 'tablespaces.sql', 'w', 32760);

  -- ##########################################################################
  --            create a list of tablespace names to exclude
  -- ##########################################################################
  lv_excltsnames := '''SYSTEM'',''SYSAUX''';

  -- Get the default permanent and temporary tablespace for the database
  OPEN cursor_exclts;
  LOOP
    FETCH cursor_exclts INTO rv_excltsname;
    EXIT WHEN cursor_exclts%NOTFOUND;
    lv_excltsnames := lv_excltsnames || ',''' || rv_excltsname || '''';
  END LOOP;
  CLOSE cursor_exclts;

  -- Get the name of the current UNDO tablespace
  SELECT value INTO rv_excltsname FROM v$parameter WHERE name='undo_tablespace';
  lv_excltsnames := lv_excltsnames || ',''' || rv_excltsname || '''';

  -- Generate the clause that will be included in the filter
  lv_excltsnames := 'NOT IN (' || lv_excltsnames || ')';

  -- ##########################################################################
  --            use DBMS_METADATA packge to generate the DDL statements
  -- ##########################################################################
  tsHandle := DBMS_METADATA.OPEN('TABLESPACE');
  DBMS_METADATA.SET_COUNT (tsHandle, 1);
  DBMS_METADATA.SET_FILTER (tsHandle, 'NAME_EXPR', lv_excltsnames);
  tsTHandle := DBMS_METADATA.ADD_TRANSFORM (tsHandle, 'DDL');
  DBMS_METADATA.SET_TRANSFORM_PARAM(tsTHandle, 'SQLTERMINATOR', TRUE);

  LOOP
    tsDDL := DBMS_METADATA.FETCH_CLOB (tsHandle);
    EXIT WHEN tsDDL IS NULL;
    UTL_FILE.PUT (fHandle, tsDDL);
  END LOOP;

  DBMS_METADATA.CLOSE (tsHandle);
  UTL_FILE.FCLOSE (fHandle);

  EXCEPTION
    WHEN UTL_FILE.INVALID_PATH THEN RAISE_APPLICATION_ERROR(-20000, 'Invalid path. Create directory or set UTL_FILE_DIR parameter');
END;
/

Bugs I encountered while using DBMS_METADATA package :

9i release 2 :

  1. Tablespace creation DDL specifies the size for the datafiles with the datafile size at the time the datafile was created. This is not a problem is the datafile is autoextensible but will be if this is not the case and the datafile was manually resized with the alter database datafile ‘…..’ resize …. statement.
  2. It seems that DBMS_METADATA creates DDL statements for tablespaces that have existed in the past, but were dropped. A create tablespace …. datafile is created, but there is no datafile definition (can be expected because there are no datafiles anymore).

Both these “bugs” don’t seem to exist in oracle 10g release 2 (didn’t test it on 10g release 1). For the size problem, in 10g release 2 DBMS_METADATA package adds a seconds statement for each tablespace (that has a bigger size (or smaller) than at the time of creation) that issues the alter database datafile ….. resize … statement. The “removed” tablespace problem didn’t show up in my 10g release 2 test environment and the PL/SQL script above could be rewritten in a way that instead of excluding some tablespaces using the DBMS_METADATA.SET_FILTER function, you could also create a list of tablespaces you want the DDL to be created.

This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Your email address will not be published.

Blue Captcha Image
Refresh

*