Section 3.5. DDL::Oracle

3.5 DDL::Oracle

The DDL::Oracle back-end module developed by Richard Sutherland was initially designed to reverse-engineer Oracle DDL (Data Definition Language) from Oracle8 i 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:

http://sourceforge.net/projects/ddl-oracle

http://www.cpan.org/authors/id/R/RV/RVSUTHERL

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:

[e-mail]

3.5.1 Installing DDL::Oracle on Unix

If 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 Orac

You 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::Oracle scripts. 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.

1. The code in Example 3-3 is basically a scroll widget bind command on the left side of the screen. This is filled with a table list, and the binding is attached to a text widget on the right side of the screen. Whenever an object such as the table SCOTT.DEPT is double-clicked, the defined subcommand runs. This fills up the text widget with DDL output.

2. Following the double-click operation, we lock the program. This turns the cursor into an hourglass or watch, depending on your operating system.

3. We then configure the new DDL::Oracle object. This derives its values from the radio buttons, seen displayed at the bottom of Figure 3-10. (The database handle provided by Perl DBI was previously stored in $self->{Database_conn} by the object-oriented orac_Oracle.pm module.)

4. Next , we find out what was actually double-clicked in the left-hand scrolling screen list generated earlier by a simple piece of SQL such as the SELECT TABLE_NAME FROM USER_TABLES statement.

5. Depending upon what kind of DDL we need, (determined from the higher Perl/Tk radio button set), we take the DDL text generated from $obj and store it in a simple Perl string variable, named $sql . (In this case, we wanted to view the DDL necessary to CREATE the DEPT table.)

6. The required DDL is then pasted to the right-hand text scroller .

7. On task completion, we unlock the screen to await further user instruction.

3.5.2 Installing DDL::Oracle on Win32

There 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).

Table 3-2. DDL::Oracle download example scripts

Script	Purpose
ddl.pl	Generates various types of DDL for a single, named object.
copy_user.pl	Generates for new users, with identical privileges from other users.
defrag.pl	Creates command files to defragment Oracle tablespaces.
query.pl	Generates DDL for a specified list of objects.

3.5.3 Using DDL::Oracle as a Batch and List Processor

One 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.pl

There 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:

defrag_USERS.sh

defrag_USERS.sh1

defrag_USERS.sh2

defrag_USERS.sh3

defrag_USERS.sh4

defrag_USERS.sh5

defrag_USERS_drop_all.sql

defrag_USERS_add_tbl.sql

defrag_USERS_add_ndx.sql

defrag_USERS_exp.par

defrag_USERS_imp.par

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:

Shell scripts

Let's look at a sample from the defrag_USERS.sh2 script:

 # Step 2 -- Use SQL*Plus to run defrag_USERS_drop_all.sql #           which will drop all objects in tablespace USERS sqlplus -s / << EOF    SPOOL /u02/tools/DDL-Oracle-1.10/defrag_USERS_drop_all.log    @ /u02/tools/DDL-Oracle-1.10/  defrag_USERS_drop_all.sql  EOF ... 

SQL scripts

Let's examine part of the defrag_USERS_drop_all.sql script mentioned:

 PROMPT DROP TABLE demo.customer CASCADE CONSTRAINTS DROP TABLE demo.customer CASCADE CONSTRAINTS ; PROMPT DROP TABLE demo.department CASCADE CONSTRAINTS DROP TABLE demo.department CASCADE CONSTRAINTS ; ... 

Export/Import parameter files

Finally, here's a short sample from the defrag_USERS_imp.par file:

 log          = /u02/tools/DDL-Oracle-1.10/defrag_USERS_imp.log file         = /u02/tools/DDL-Oracle-1.10/defrag_USERS.pipe rows         = y commit       = y ignore       = y buffer       = 65535 analyze      = n recordlength = 65535 full         = y ... 

When you're ready to defragment, you simply execute the following command:

 $ ./defrag_USERS.sh 

This executes everything else needed to reorganize your tablespace. Defragtastic!