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 :
- 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.
- 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.