Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

        
 

 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.


 

     

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.