Richard Sutherland - 2000-10-28

DESCRIPTION

Designed for Oracle DBA's and users.  Reverse engineers database
objects (tables, indexes, users, profiles, tablespaces, roles,
constraints, etc.).  Generates DDL to *resize* tables and indexes
to the provided standard or to a user defined standard.

I originally wrote a script to defrag tablespaces, but as a DBA
I regularly find a need for the DDL of a single object or a list
of objects (such as all of the indexes for a certain table).  So
I am in the process of taking all of the DDL statement creation
logic out of defrag.pl, and putting it into the general purpose
DDL::Oracle module, then expanding it to include tablespaces,
users, roles, and all other dictionary objects.

Oracle tablespaces tend to become fragmented (now THAT's an
understatement).  Even when object sizing standards are adopted,
it is difficult to get 100% compliance from users.  And even it
you get a high degree of compliance, objects turn out to be a
different size than originally thought/planned -- small tables
grow to become large (i.e., hundreds of extents), what was thought
would be a large table ends up having only a few rows, etc.  So
the main driver for DDL::Oracle is the object management needs of
Oracle DBA's.  The "resize" method generates DDL for a list of
tables or indexes.  For partitioned objects, the "appropriate"
size of EACH partition is calculated and supplied in the generated
DDL.  The original defrag.pl will be rewritten to use DDL::Oracle,
and supplied with its distribution.

Other uses.

A hole in Oracle's Designer/2000 case tool is the DDL for changes
to a table's structure.  It produces reports of tables that change,
and handles adding columns if they are added to the end of the table.
Our data model czar has a penchant for adding columns in the MIDDLE
of the table (imagine that!).  This requires moving the data from the
old table to the new structure.  Designer/2000 supplies no assistance
for this situation.  DDL::Oracle will.

Our user management mainly consists of creating a new user with the
identical privileges of an existing user, so a "copy_user.pl" wrapper
will supply this functionality.

How about creating a copy of an instance for some reason -- a QA
database, or a new host.  Oracle's export utility can move the instance
objects if the new database exists, but it won't create the tablespaces
and their datafiles.  Our data warehouse databases have dozens of
tablespaces and hundreds of data files.  How do you create the DDL for
that?  DDL::Oracle will have this capability.

DBA's, what are your suggestions?

SYNOPSIS

use DBI;
use DDL::Oracle;

my $dbh = DBI->connect(
                        "dbi:Oracle:dbname",
                        "username",
                        "password",
                        {
                         PrintError => 0,
                         RaiseError => 1
                        }
    );

# Use default resizing and schema options.
# query default DBA_xxx tables (could use USER_xxx for non-DBA types)
DDL::Oracle->configure(
                        dbh    => $dbh,
                      );

# Create a list of one or more objects
my $sth = $dbh->prepare(
       "SELECT
               owner
             , name
        FROM
               user_tables
        WHERE
               tablespace_name = 'MY_TBLSP'    -- your millage may vary
       "
    );
$sth->execute;
my $list = $sth->fetchall_arrayref;

my $obj = DDL::Oracle->new(
                            type  => "table",
                            list  => $list,                          );
                          );

my $ddl = $obj->create;      # or $obj->resize;  or $obj->drop;  etc.

print $ddl;    # Use STDOUT so user can redirect to desired file.