| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> ANNOUNCE: DDL::Oracle v0.32
Release of version 0.32 of DDL::Oracle
NAME
DDL::Oracle - a DDL generator for Oracle release 7.3, 8.0 and 8i
databases.
CHANGES Modified the CREATE TABLESPACE statement to allow for 8i's specialized CREATE TEMPORARY TABLESPACE feature.
Modified the 'resize' method for tables to include ALTER INDEX REBUILD statements for affected indexes. Such indexes become UNUSABLE during the move of a table and/or its [SUB]PARTITIONs.
Included new wrapper script 'query.pl', which prompts for the name of a file. The file should contain a query which returns OWNER and NAME of objects, via any arbitrary WHERE clause. This lets the user without knowledge of Perl generate DDL for lists of objects of any type.
DESCRIPTION
Overview
Designed for Oracle DBA's and users. It reverse engineers database objects (tables, indexes, users, profiles, tablespaces, roles, constraints, etc.). It generates DDL to *resize* tables and indexes to the provided standard or to a user defined standard.
We originally wrote a script to defrag tablespaces, but as DBA's we 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 we took all of the DDL statement creation logic out of defrag.pl, and put it into the general purpose DDL::Oracle module. DDL::Oracle was then expanded to include tablespaces, users, roles, and all other dictionary objects. The completely revised program 'defrag.pl' is included with the distribution.
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 if 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. And so forth. So the main driver for creating DDL::Oracle was 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.
Initialization and Constructor
configure
The configure method is used to define the DBI connection and to set several session level options. These are:
dbh A reference to a valid DBI connection (obtained via
DBI->connect). This is a mandatory argument.
NOTE: The user connecting MUST have SELECT privileges
on the following (in addition to the DBA or USER
views):
V$INSTANCE
V$PARAMETER
schema Defines whether and what to use as the scema for DDL
on objects which use this syntax. "1" means use the
owner of the object as the schema; "0" means omit the
schema syntax; any other arbtrary string will be
imbedded in the DDL as the schema. The default is "1".
resize Defines whether and what to use in resizing segments.
"1" means resize segments using the default algorithm;
"0" means keep the current INITIAL and NEXT values; any
other string will be interpreted as a resize definition.
The default is "1".
view Defines which Dictionary views to query: DBA or USER
(e.g., DBA_TABLES or USER_TABLES). The default is DBA.
new
The new method is the object constructor. The two mandatory object definitions are defined by calling this method, to wit:
type The type of object (e.g., TABLE, INDEX, SYNONYM, table
family, etc.).
For 'table family', supply the name(s) of tables -- the
DDL will include the table and its:
Comments (Table and Column)
Indexes
Constraints
Triggers
list An arrayref to an array of arrayrefs (as in the DBI's
"fetchall_arrayref" method) containing pairs of owner and
name.
Object methods
create
The create method generates the DDL to create the list of Oracle objects.
drop
The drop method generates the DDL to drop the list of Oracle objects.
resize
The resize method generates the DDL to resize the list of Oracle objects. The 'type' defined in the 'new' method is limited to 'index' and 'table'. For tables, this generates an ALTER TABLE MOVE statement; for indexes, it generates an ALTER INDEX REBUILD statement. If the table or index is partitioned, then a statement for each partition is generated.
To generate DDL for a single partition of an index or table, define the 'name' as a colon delimited field (e.g., 'name:partition').
compile
The compile method generates DDL to compile the list of Oracle objects. The 'type' defined in the 'new' method is limited to 'function', 'package', 'procedure', 'trigger' and 'view'.
SYNOPSIS
use DBI;
use DDL::Oracle;
my $dbh = DBI->connect(
"dbi:Oracle:dbname",
"username",
"password",
{
PrintError => 0,
RaiseError => 1
}
);
# Use default resize and schema options.
# query default DBA_xxx tables (could use USER_xxx for non-DBA types)
::Oracle->configure(
dbh => $dbh,
resize => 1
schema => 1
view => 'dba'
);
# Create a list of one or more objects
my $sth = $dbh->prepare(
"SELECT
owner
, table_name
FROM
dba_tables
WHERE
tablespace_name = 'MY_TBLSP' -- your mileage 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;
print $ddl; # Use STDOUT so user can redirect to desired file.
FILES
ddl.pl
defrag.pl
copy_user.pl
copy_user.sh
README
README.defrag
AUTHOR
Richard V. Sutherland
rvsutherland_at_yahoo.com
COPYRIGHT
Copyright (c) 2000, Richard V. Sutherland. All rights reserved.
This module is free software. It may be used, redistributed,
and/or modified under the same terms as Perl itself. See:
http://www.perl.com/perl/misc/Artistic.html
AVAILABILITY DDL::Oracle has been uploaded to the CPAN and is also available from:
http://sourceforge.net/projects/ddl-oracle
 |
 |