Remote DBA Plans
Remote DBA Service

Oracle Extract Schema
Oracle Tips by Burleson Consulting

Advanced Oracle Utilities: The Definitive Reference by Rampant TechPress is written by the top Oracle database experts (Bert Scalzo, Donald Burleson, and Steve Callan). The following is an excerpt from the book.

Extract Schema

Probably the most useful reverse engineering scenario in terms of frequency of use will be the schema extraction process. Most applications and/or development projects are versioned at a very high collective level. Moreover, since applications tend to operate at the schema level where a schema may equate to one or more application functional areas, it is, therefore, also very common to want to manage or version the database at the schema level as well.

Now take the object extraction example and just add all the types of objects that may need to be reverse engineered, but that would take some work. Also like before, that script might have to change over time as new schema level objects were possible. Obviously this scenario is much like the prior database extract in that a collection of objects needs to be reversed engineered, just at the schema level this time rather than the whole database. The generate_schema_ddl.sql SQL*Plus script, as shown below, generates DDL for an entire schema.

generate_schema_ddl.sql script

DEFINE schema=&1

SET FEEDBACK OFF

SET VERIFY OFF

SET PAGESIZE 0

SET LINESIZE 1024

SET TRIMOUT ON

SET TRIMSPOOL ON

SET SERVEROUTPUT ON

SET TERMOUT OFF

COLUMN sid NOPRINT new_value sid;

SELECT value sid from v$parameter

where name='db_name';

SPOOL &sid._&schema._reverse_engineer.sql

DECLARE

meta_data_handle NUMBER;

transform_handle NUMBER;

DDL CLOB;

BEGIN

meta_data_handle := DBMS_METADATA.OPEN ('SCHEMA_EXPORT');

DBMS_METADATA.set_filter (meta_data_handle, 'SCHEMA', upper('&schema'));

DBMS_METADATA.set_filter (meta_data_handle, 'INCLUDE_USER', true);

DBMS_METADATA.set_filter (meta_data_handle, 'EXCLUDE_PATH_EXPR', '=''TABLE_DATA''');

-- you can inlcude/exclude items

-- DBMS_METADATA.set_filter (meta_data_handle, 'INCLUDE_PATH_EXPR', '=''VIEW''');

-- DBMS_METADATA.set_filter (meta_data_handle, 'EXCLUDE_PATH_EXPR', '=''VIEW''');

transform_handle:= DBMS_METADATA.add_transform (meta_data_handle, 'DDL');

DBMS_METADATA.SET_TRANSFORM_PARAM (transform_handle, 'PRETTY',true);

DBMS_METADATA.SET_TRANSFORM_PARAM (transform_handle, 'SQLTERMINATOR',true);

LOOP

DDL := DBMS_METADATA.fetch_clob (meta_data_handle);

EXIT WHEN DDL IS NULL;

DBMS_OUTPUT.PUT_LINE (to_char(DDL));

END LOOP;

DBMS_METADATA.CLOSE (meta_data_handle);

EXCEPTION

WHEN OTHERS THEN

DBMS_METADATA.CLOSE (meta_data_handle);

END;

SPOOL OFF

SET TERMOUT ON

Was it noticed that the schema and database level extract scripts are 90% the same? That is because working with groups of objects with dbms_metadata always works the same by simply opening and specifying some filters differently for the various granularities. So above, one opened for a schema_export and defined a few extra filters to handle PRETTY and SQLTERMINATOR. Shown below are tables with the various dbms_metadata objects types and filters. One will have to experiment on all the many possibilities.

ASSOCIATION	MATERIALIED_VIEW	ROLE_GRANT
AUDIT	MATERIALIZED_VIEW_LOG	ROLLBACK_SEGMENT
AUDIT_OBJ	OBJECT_GRANT	SEQUENCE
CLUSTER	OPERATOR	SYNONYM
COMMENT	PACKAGE	SYSTEM_GRANT
CONSTRAINT	PACKAGE_SPEC	TABLE
CONTEXT	PACKAGE_BODY	TABLE_DATA
DB_LINK	PASSWORD_HISTORY	TABLE_STATISTICS
DEFAULT_ROLE	PASSWORD_VERIFY_FUNCTION	TABLESPACE
DIMENSION	PROCEDURE	TABLESPACE_QUOTA
DIRECTORY	PROFILE	TRIGGER
FGA_POLICY	PROXY	TRUSTED_DB_LINK
FUNCTION	REF_CONSTRAINT	TYPE
INDEX_STATISTICS	REFRESH_GROUP	TYPE_SPEC
INDEX	RESOURCE_COST	TYPE_BODY
INDEXTYPE	RLS_CONTEXT	USER
JAVA_SOURCE	RLS_GROUP	VIEW
JOB	RLS_POLICY	XMLSCHEMA
LIBRARY	ROLE

Table 6.5: Dbms_metadata Object Types

NAME	BASE_OBJECT_NAME_EXPR	SCHEMA
NAME_EXPR	EXCLUDE_BASE_OBJECT_NAME_EXPR	SCHEMA_EXPR
EXCLUDE_NAME_EXPR	BASE_OBJECT_SCHEMA_EXPR	INCLUDE_USER
SCHEMA	BASE_OBJECT_TYPE	SCHEMA
SCHEMA_EXPR	BASE_OBJECT_TYPE_EXPR	SCHEMA_EXPR
SPECIFICATION	BASE_OBJECT_TABLESPACE	NAME
BODY	BASE_OBJECT_TABLESPACE_EXPR	NAME_EXPR
TABLESPACE	GRANTEE	BEGIN_WITH
TABLESPACE_EXPR	PRIVNAME	BEGIN_AFTER
PRIMARY	PRIVNAME_EXPR	END_BEFORE
SECONDARY	GRANTEE_EXPR	END_WITH
BASE_OBJECT_NAME	EXCLUDE_GRANTEE_EXPR	INCLUDE_PATH_EXPR
BASE_OBJECT_SCHEMA	CUSTOM_FILTER	EXCLUDE_PATH_EXPR

Table 6.6: DBMS_METADATA Filters

For more details on Oracle utilities, see the book "Advanced Oracle Utilities" by Bert Scalzo, Donald K. Burleson, and Steve Callan.

You can buy it direct from the publisher for 30% off directly from Rampant TechPress.