|
|
|
3.5 DDL::OracleThe DDL::Oracle back-end module developed by Richard Sutherland was initially designed to reverse-engineer Oracle DDL (Data Definition Language) from Oracle8i databases, although its functionality is expanding and the module now offers other additional features for Oracle DBAs. It currently resides on a SourceForge web site, but you can still get the latest tarball from the Perl CPAN site: The DDL::Oracle object-oriented module is designed for use by other scripts (such as Orac or debug.pl, as we describe later), rather than as a standalone program. The SourceForge site also provides many of the facilities you'll find useful if you start using DDL::Oracle in a serious way with your own scripts (as we hope you will), including a mailing list: 3.5.1 Installing DDL::Oracle on UnixIf you download DDL::Oracle directly, you can install it with the following steps: $ gzip -d DDL-Oracle-1.10.tar.gz $ tar xvf DDL-Oracle-1.10.tar $ cd DDL-Oracle-1.10 $ vi README The installation of DDL::Oracle follows the usual Perl pattern: $ perl Makefile.PL $ make $ make test $ make install Once DDL::Oracle is installed, you can view its documentation from within the installation directory (see Appendix A, for much more information about the perldoc program): $ perldoc DDL::Oracle 3.5.1.1 Using DDL::Oracle with OracYou can use DDL::Oracle in many different ways, though mainly through other programs that make use of its facilities. The DDL::Oracle download bundle supplies a number of example scripts that can be used as templates for your own DDL::Oraclescripts. We'll discuss some of these scripts — in particular, defrag.pl, in later sections, but first we'll show how DDL::Oracle is typically used within other programs. We'll start with Orac, which we introduced earlier in this chapter. Example 3-3 shows how the Orac program uses DDL::Oracle to drive the Devel menu; note that all of the options you see here are direct mappings from DDL::Oracle's API. DDL::Oracle can create DDL for virtually every kind of object in the database and in many different ways (e.g., CREATE or DROP statements). To illustrate its use, we'll work through some cut-down code used to create the output in Figure 3-10. Figure 3-10. DDL::Oracle driving the Orac Devel menu Example 3-3. Usage of DDL::Oracle within the Orac program# Step 1: We bind the left-hand scroll list to the right-hand text
# screen. If double-clicked, we go to the related subfunction.
$window->{text}->bind(
'<Double-1>', # Links a double-click to the command below
sub{
# Step 2: As soon as the user double-clicks, lock out
# all other commands until we're done.
$window->Busy(-recurse=>1);
$self->{Main_window}->Busy(-recurse=>1);
# Step 3: Here's the money shot. Configure DDL::Oracle,
# using its full API driven by other Perl/Tk buttons.
DDL::Oracle->configure(
dbh => $self->{Database_conn}, # database
resize => $resize, # handle
schema => $schema,
prompt => $prompt,
heading => 0,
view => $view,
blksize => $Block_Size,
version => $Oracle_Version
);
# Step 4: Create a new DDL::Oracle object dependent
# upon whatever live table or object was double-clicked.
my $obj = DDL::Oracle->new(
type => $obj_type,
list => [[ $main::v_sys,
$window->{text}->get('active'),
]] );
my $sql;
# Step 5: Depending upon the exact type of DDL required,
# use DDL::Oracle to generate the DDL and fill $sql.
if ( $action eq "drop" ){
$sql = $obj->drop;
} elsif ( $action eq "create" ){
$sql = $obj->create;
} elsif ( $action eq "resize" ){
$sql = $obj->resize;
} elsif ( $action eq "compile" ){
$sql = $obj->compile;
}
# Step 6: Output the DDL text generated and then move
# the cursor to the bottom of the text panel.
$current_index = $text->index('current'); # Current mark
$text->insert('end', $sql . "\n\n");
$self->search_text(\$text, $current_index);
$text->see( q{end linestart});
# Step 7: Remove screen lock, to choose further options.
$self->{Main_window}->Unbusy;
$window->Unbusy;
} );
Here's what's going on in this code.
3.5.2 Installing DDL::Oracle on Win32There is an ActivePerl package for DDL::Oracle. To obtain it, simply connect your PC to the Internet, as described in Chapter 2, and run ppm: C:\>ppm PPM interactive shell (2.1.5) - type 'help' for available commands. PPM> install DDL-Oracle Install package 'DDL-Oracle?' (y/N): y Installing package 'DDL-Oracle'... ... Writing C:\Perl\site\lib\auto\DDL\Oracle\.packlist PPM> quit As well as grabbing the DDL::Oracle module files, this ActivePerl installation also provides some of the most important sample scripts that come with Richard Sutherland's main source code download on Unix (see Table 3-2).
3.5.3 Using DDL::Oracle as a Batch and List ProcessorOne important thing that differentiates DDL::Oracle from other available freely available tools is its batch orientation. If you're ever in a situation where you need to create many different scripts (for backups, performance tuning, or any other purpose) for your DBA work and you find yourself cutting and pasting from one script to another, you probably need DDL::Oracle. By using DDL::Oracle in batch mode, you can concentrate on solving your problem and let DDL::Oracle do the hard work on the back end, generating the actual DDL code required. DDL::Oracle can also be used as a list processor. In this mode you can send it a list of objects or components for which to generate DDL — for example, all the tables in a particular tablespace. One of the most useful of the helpful sample scripts provided with the program is defrag.pl, which you can use for reorganizing these tablespaces. We'll take a quick look at this script in the next section. 3.5.4 defrag.plThere are many different options for running defrag.pl, all of which you can read about by issuing this command: $ perl defrag.pl --help We ran the following command to generate defragmentation scripts for our USERS tablespace: $ perl defrag.pl --user=system --password=manager --sid=orcl \ --tablespace=USERS ... defrag.pl completed successfully on Sun Mar 24 12:34:49 2002 This operation created the following list of files:
These scripts are essentially designed to export the target data, drop the objects, recreate the resized and defragmented objects, and then import the data once again. There are several different types of scripts:
|
|
|
|