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 Object
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 Object

Remember the example table level DDL generation script from a few pages back? It was over 60 lines long, and yet it handled only the most basic scenarios of table DDL extraction. If that script had to be augmented for every possible permutation of table level options, that script would easily grow to a few thousand lines long. Plus, if Oracle changed either the CREATE or ALTER table syntax, then that script would have to be revisited to make sure it remained accurate for anything new. That is just too much work to tackle – especially when there is an alternative.

 

So examine the exact same table generation process instead using the Oracle dmbs_metadata PL/SQL package as shown in the new_generate_table_ddl.sql SQL*Plus script:

 

new_generate_table_ddl.sql script

 

set echo off

set heading off

set feedback off

set verify off

set pagesize 0

set linesize 132

 

define schema=&1

 

EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'PRETTY',true);

EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true);

 

SELECT to_char(DBMS_METADATA.GET_DDL ('TABLE', table_name, owner))

FROM   dba_tables

WHERE  owner=upper('&1');

 

In just 12 short lines, a script is created to reverse engineer all the tables for a given schema and for every possible Oracle option or feature those tables use. Furthermore, now the task of keeping such a script current is now on Oracle’s shoulders. So even if Oracle adds new table options or parameters like extends or changes to the CREATE/ALTER table syntax, the script is not affected. Additionally, this DDL generation script can be extended to change or add additional objects types because it is very straightforward and easy. For example, if one wanted to switch to or add indexes, just substitute or add this command.

 

SELECT to_char(DBMS_METADATA.GET_DDL ('INDEX', index_name, table_owner))

FROM   dba_indexes

WHERE  table_owner=upper('&1');


Now compare the actual table CREATE TABLE DDL generated from the new_generate_table_ddl.sql SQL*Plus script, shown next, to the earlier output from the old_generate_table_ddl.sql SQL*Plus script. Note that check constraints, primary keys and unique keys have been picked up along with their storage clauses. Furthermore, even the table storage clause is more accurate with items such as NOCOMPRESS, NOLOGGING and BUFFER_POOL now covered.

 

new_generate_table_ddl.sql output

 

 

  CREATE TABLE "MOVIES"."CUSTOMER"

   (    "CUSTOMERID" NUMBER(10,0) NOT NULL ENABLE,

        "FIRSTNAME" VARCHAR2(20) NOT NULL ENABLE,

        "LASTNAME" VARCHAR2(30) NOT NULL ENABLE,

        "PHONE" CHAR(10) NOT NULL ENABLE,

        "ADDRESS" VARCHAR2(40) NOT NULL ENABLE,

        "CITY" VARCHAR2(30) NOT NULL ENABLE,

        "STATE" CHAR(2) NOT NULL ENABLE,

        "ZIP" CHAR(5) NOT NULL ENABLE,

        "BIRTHDATE" DATE,

        "GENDER" CHAR(1),

         CHECK (Gender in ('M','F')) ENABLE,

         CHECK (CustomerId > 0) ENABLE,

         CONSTRAINT "CUSTOMER_PK" PRIMARY KEY ("CUSTOMERID")

  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS

  STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)

  TABLESPACE "USERS"  ENABLE,

         CONSTRAINT "CUSTOMER_UK" UNIQUE ("FIRSTNAME", "LASTNAME", "PHONE")

  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS

  STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)

  TABLESPACE "USERS"  ENABLE

   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING

  STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)

  TABLESPACE "USERS" ;

Extract Database

On occasion, DBAs may find the need to reverse engineer or extract an entire database. An example might be that responsibility is inherited for a pre-existing database and there are no DDL scripts that anyone knows of or is sure are accurate. Another example might be that a duplicate database needs to be created for test or development. So there are occasions where one needs to reverse engineer more than one object type or construct at a time.

 

Now a much more complex aspect of working with dbms_metadata is being introduced: extracting logical groupings of items. Start with the concept of reverse engineering or extracting the DDL for everything in the largest logical grouping of items, which is the entire database. The generate_database_ddl.sql SQL*Plus script generates DDL for the entire database.

 

generate_database_ddl.sql script

 

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._reverse_engineer.sql

 

DECLARE

   meta_data_handle  NUMBER;

   transform_handle  NUMBER;

   DDL               CLOB;

BEGIN

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

 

  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', '=''DIRECTORY''');

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

 

  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

 

The generate_database_ddl.sql SQL*Plus script demonstrates that in order to work on collections of objects with dbms_metadata, one must use the dbms_metadata.open function to define a handle. In this case, the collection is for a database export. Then simply loop through the items contained by that handle and print the DDL line one by one. Note that a filter was set to exclude the table data itself which would have generated INSERT statements for all the data. Additional filters can be added to specify an almost unlimited combination of extract scenarios.


Fo
r 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.