DDL::Oracle A DDL generator for Oracle databases
Download

DDL::Oracle Ranking & Summary

DDL::Oracle Tags

Perl module generator Oracle Oracle Databases DDL generator DDL

DDL::Oracle Description

A DDL generator for Oracle databases DDL::Oracle is a Perl module 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, then expanded that 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 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, etc. So the main driver for 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.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) DDL::Oracle->configure( dbh = > $dbh, ); # 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; etc. print $ddl; # Use STDOUT so user can redirect to desired file. # Here's another example, this time for type 'components'. This type # differs from the norm, because it has no owner and no name. my $obj = DDL::Oracle- >new( type = > 'components', list = > ] ); my $ddl = $obj- >create; print $ddl; Requirements: · Perl

DDL::Oracle Related Software