Introduction to Oracle Tools

This book covers the Oracle tools necessary to produce a working Windows and Web-based application. This book will also cover several monitoring and tuning tools used to aid in the maintenance, performance tuning, and problem solving of both the Sales Tracking application environment and the Oracle9i environment.

Oracle development tools have been in the Oracle product set since the version 4 days where forms were called Fast Forms, the equivalent of today's wizard that walks the user through building a base form. The reporting tool was a version of today's SQL*Plus, then called UFI, or user-friendly interface.

The base-reporting tool was called RPT and had some interesting undocumented features. RPT language was the procedural language of Oracle at the time. Oracle v5 brought serious enhancements to the forms and reports-based tools. The forms tool was renamed SQL*Forms v2.0 and a new report writer was added, SQL*ReportWriter v1.0. This version of forms greatly enhanced the programming and capabilities of the online forms. This package of development tools was enhanced through the years, adding a menu package and a graphics development package. Oracle v7 brought some major changes to the database as well as these tools. The tool set was renamed Developer/2000 and was much more Windows-based than the previous character mode development and screens.

The power of the Oracle application-development environment is that these tools are pretty much supported on all environments that the database itself supports. This means that the developer can develop and test an application in a PC environment and deploy it on large UNIX or even main frame computers. This portability of development has been a strength of Oracle since Oracle v4.1.

This book works with Developer v6.0, the latest version of Oracle's application development tools. This environment includes Project Builder, Form Builder, Report Builder, Graphics Builder, Translation Builder, Schema Builder, and Query Builder. This book concentrates on the forms and reports aspects of this tool.

Oracle Developer 6.i (Forms, Reports, and Graphics)

The Project Builder component is what a developer uses to keep all the pieces of the application, such as the various forms and report source code and intermediate code. Project Builder allows the developer to compile single programs or all the programs in the project. Project Builder is a convenient tool used to organize all parts of a particular application-development effort that is using Developer v6.i. Figure 1.8 shows the Project Builder main screen. Note the icons down the left side of the screen; these bring up the Form Builder, Report Builder, Graphics Builder, Procedure Builder, and Query Builder tools.

Oracle Developer is based on common elements that are used throughout the tool. Once you learn how to use the functionality in one tool, you will understand how to use it throughout the Developer environment.

Oracle Developer contains an Object Navigator that provides a hierarchical structure of all application objects. The Object Navigator provides you with two views: the Ownership view (see Figure 1.9) or the Visual view (see Figure 1.10). The Ownership view displays the objects according to the block representing rows from a table. The Visual view, on the other hand, displays objects according to how they appear on the output screen.

Form Builder is used to build and maintain form-based applications. Programming in Oracle Developer is much different from third-generation languages where the programmer used a text editor and physically wrote the program source code. Applications are built with Oracle Developer by creating onscreen objects that represent the data elements that will be retrieved from the database, visual aspects, program navigation, and so on, see Figure 1.11.

Selected activities or events are coded in triggers (or program units) that are used whenever the event occurs. Events are like screen navigation, for example, entering a field or exiting a field. A common event to code upon exiting a field would be the data-editing criteria to make sure what was entered in this particular field on the screen was the correct information. This event could be as simple as enforcing a particular date format or ensuring that this data element appears in another table (used as a reference table).

Report Builder is a wizard-based tool used to create a variety of reports from data from the database (see Figure 1.12). This tool also has a WYSIWYG layout editor. Report Builder has procedural constructs for data formatting, calculating summary information, and so on. This tool could be used to perform database maintenance, but these tasks are better performed by PL/SQL.

Graphics Builder is used to generate a variety of types of charts based on data from the database.

SQL*Plus

SQL*Plus (pronounced sequel plus) is an interactive character-mode tool for the Oracle9i RDBMS environment. SQL*Plus has a variety of functions in the Oracle environment. You can use SQL*Plus to process SQL statements one at a time or to process SQL statements interactively with end users. SQL*Plus can initiate PL/SQL for the procedural processing of SQL statements as well as to list and print query results. SQL*Plus has powerful character-mode reporting capabilities that can format query results into reports. SQL*Plus is also the character-mode or script processor for administrative functions, and has the ability to accept input from operating-system files or SQL scripts.

SQL*Plus originated from the beginning of the Oracle RDBMS days as a tool called User Friendly Interface or UFI (pronounced U-fee). UFI was used primarily to administer the Oracle database prior to Oracle4. Early administrative tasks included adding users and managing the tablespaces. Oracle5 brought many enhancements as well as new names for many of the tools. UFI was renamed to SQL*Plus at this time. Some enhancements have been added to SQL*Plus through the years; however, most of the formatting commands and the ease of creating reports are as easy today as they were with the UFI product.

There have been additions to several of the SQL*Plus capabilities, additional ways of starting SQL*Plus, and a changed role for SQL*Plus through the major releases of the Oracle RDBMS kernel. Before Oracle6, for example, using UFI or SQL*Plus was the only way to administer the Oracle database. With Oracle6 came a new tool called SQL*DBA that took over many of the database administrative responsibilities. SQL*DBA had both a graphical mode and a character mode. In Oracle6, additional administra-tive tasks were added, such as database startup/shutdown and backup/ recovery responsibilities. The character mode was still able to run scripts from an operating-system command prompt.

Oracle7 and 8 provide a new interface called Enterprise Manager that replaced SQL*DBA as a database-management tool. The character-mode version of SQL*DBA was renamed Server Manager. Oracle9i has given these character-mode administrative functions back to SQL*Plus. Future releases of Oracle will not have the Server Manager product. SQL*Plus also exists in the world of client/server and is available with all the major graphical interfaces.

Chapter 2, “Fundamentals of the SQL Language,” concentrates on learning SQL and Chapter 6, “Building Oracle Reports,” illustrates SQL*Plus formatting commands.

SQL*Loader

Oracle Databases today are ever increasing in complexity and size. Gigabyte-sized databases are common, and data warehouses are often reaching the terabyte-sized range. With the growth of these databases, the need to populate them with external data quickly and efficiently is of paramount importance. To handle this challenge, Oracle provides a tool called SQL*Loader to load data from external data files into an Oracle database.

SQL*Loader has many functions that include the following capabilities:

• Data can be loaded from multiple input datafiles of differing file types.

• Input records can be of fixed and variable lengths.

• Multiple tables can be loaded in the same run. It can also logically load selected records into each respective table.

• SQL functions can be used against input data before loading into tables.

• Multiple physical records can be combined into a single logical record. Likewise, SQL can take a single physical record and load it as multiple logical records.

SQL*Loader can be invoked by typing in sqlload, sqlldr, or sqlldr90 at the command line. The exact command may differ depending on your operating system. Refer to your Oracle operating-system-specific manual for the exact syntax. Please note that all listings and server responses in this chapter may differ from your results based on the operating system that you are using. The sqlldr command accepts numerous command-line parameters. Invoking SQL*Loader without any parameters displays help information on all the valid parameters (see Listing 1.3).

Listing 1.3. SQL*Loader Help Information

Invoking SQL*Loader without parameters:
$ sqlldr
							The server responds with help information because SQL*Loader was invoked without parameters:
							SQL*Loader: Release 9.0.1.0.0 - Production on Sun Aug 12 14:36:04 2001
							Copyright 2001 Oracle Corporation.  All rights reserved.
							Usage: SQLLOAD keyword=value [,keyword=value,...]
							Valid Keywords:
							userid -- ORACLE username/password
							control -- Control file name
							log -- Log file name
							bad -- Bad file name
							data -- Data file name
							discard -- Discard file name
							discardmax -- Number of discards to allow          (Default all)
							skip -- Number of logical records to skip    (Default 0)
							load -- Number of logical records to load    (Default all)
							errors -- Number of errors to allow            (Default 50)
							rows -- Number of rows in conventional path bind array or between direct path data saves
							(Default: Conventional path 64, Direct path all)
							bindsize -- Size of conventional path bind array in bytes  (Default 256000)
							silent -- Suppress messages during run (header,feedback,errors,discards,partitions)
							direct -- use direct path                      (Default FALSE)
							parfile -- parameter file: name of file that contains parameter specifications
							parallel -- do parallel load                     (Default FALSE)
							file -- File to allocate extents from
							skip_unusable_indexes -- disallow/allow unusable indexes or index partitions  (Default FALSE)
							skip_index_maintenance -- do not maintain indexes, mark affected indexes as unusable  (Default FALSE)
							commit_discontinued -- commit loaded rows when load is discontinued  (Default FALSE)
							readsize -- Size of Read buffer                  (Default 1048576)
							external_table -- use external table for load; NOT_USED, GENERATE_ONLY, EXECUTE  (Default NOT_USED)
							columnarrayrows -- Number of rows for direct path column array  (Default 5000)
							streamsize -- Size of direct path stream buffer in bytes  (Default 256000)
							multithreading -- use multithreading in direct path
							resumable -- enable or disable resumable for current session  (Default FALSE)
							resumable_name -- text string to help identify resumable statement
							resumable_timeout -- wait time (in seconds) for RESUMABLE  (Default 7200)
							PLEASE NOTE: Command-line parameters may be specified either by
							position or by keywords.  An example of the former case is 'sqlldr
							scott/tiger foo'; an example of the latter is 'sqlldr control=foo
							userid=scott/tiger'.  One may specify parameters by position before
							but not after parameters specified by keywords.  For example,
							'sqlldr scott/tiger control=foo logfile=log' is allowed, but
							'sqlldr scott/tiger control=foo log' is not, even though the
position of the parameter 'log' is correct.

External Tables

External tables is a new facility that makes flat files look like relational tables for the purpose of loading. External tables builds where SQL*Loader leaves off. Because Oracle9i looks at the flat file as a table, data transformation can easily occur via SQL, parallel loading is available, and direct-path loading with index support is possible. This new facility is also completely compatible with SQL*Loader, reading the SQL*Loader control file, making the correct DDL for the new object type. Notice the similarities from the following external table syntax to that you are familiar with in SQL*Loader: both can have field delimiters, record delimiters, identifies bad and log files, and so on.

CREATE TABLE emp_load (empno NUMBER, ename VARCHAR(30), ...)
  ORGANIZATION EXTERNAL
  (TYPE ORACLE_LOADER
      DEFAULT DIRECTORY '/Oracle_data_load'
      ACCESS PARAMETERS  -- Specific to the current access driver
      (   fields terminated by ','
          records delimited by newline
          badfile 'bad/bademp_load.dat'
          logfile 'log/logemp_load.dat'
          (empno     integer(4), ename char(10), ...)
          )
          LOCATION ('dat/emp_load111900.dat')
          ...
    )
PARALLEL 4
REJECT LIMIT 100;

The big difference is that syntax like INSERT INTO EMP SELECT * FROM EMP_LOAD can allow for a single pass of the data when loading Oracle tables. External tables support things a bit more complex like:

INSERT INTO EMP
SELECT * FROM EMP_LOAD
WHERE EMP_LOAD.EMPNO NOT IN (
    SELECT EMPNO
    FROM EMP);

Of course, you would have the full power of the SQL language such as DECODE to transform the data while it was being loaded. This new feature allows you to do parallel loading from a single file, perform data transformation/conversion, and so on; things that would have necessitated a second pass of a worktable using PL/SQL.

External tables also support an API that is capable of creating DDL and XML for currently defined objects. The new package DBMS_METADATA has two options: GET_DDL and GER_XML. This will facilitate the loading and manipulation of all kinds of objects, not just the traditional relational tables.

Net8

Net8 (called SQL*Net in Oracle 7 and before) is the basis of the Oracle client/server technology. Net8 allows you, the end user, to transparently work with about any version of Oracle on any computer in about any environment without having to program or specifically handle any of the network or connectivity issues. Net8 simplifies Oracle programming because programs (such as Oracle Forms, SQL*Plus, and so on) can be developed and tested on one computer environment and run in a totally different computer environment without any modifications. In the client/server environment (see Figure 1.13), Net8 allows programs to reside and execute on inexpensive PC computers and access data on larger/faster computers. Net8 can also handle thousands of users accessing an Oracle9i environment with the connection manager and multithreaded technology (illustrated in Figure 1.14) component installed.

An in-depth study of Net8 is beyond the scope of this book. Net8 is a flexible networking protocol that can handle various networking topologies. This allows for Net8 to be able to communicate between the users and the Oracle9i database no matter what the network in place is. Net8 hides the complexities of the network from the programmer and the end user, simplifying the programming of Oracle applications, making Oracle applications very portable, and so on.

Log Miner

Log Miner was introduced in Oracle8.1 and is a tool that is capable of seeing into the online redo and archive log files. This information can then be used to create REDO SQL used to repeat a transaction on another system and UNDO SQL useful to undo a user mistake. Log Miner is also useful with database auditing and statistical analysis. The Oracle8i and 9i versions of the tool utilizes SQL*Plus and a series of PL/SQL procedures. Oracle9i also supports a Log Miner GUI available through Oracle Enterprise Manager.

Log Miner now supports objects with chained and migrated rows, direct-path inserts additional data types such as longs, lobs, and some object types, including DDL.

The SQL generated by the REDO and UNDO features includes primary keys now (Oracle8i version used ROWID) for easier usage on other target Oracle databases. The user can also drill and get REDO and UNDO for specific users, specific objects, or even activity on a specific column.

This is accomplished with two new columns to the V$LOGMNR_CONTENTS: redo_value and undo_value. Also added to aid in log mining are four new PL/SQL functions to the DBMS_LOGMNR package: COLUMN_PRESENT, MINE_VARCHAR, MINE_NUMBER, and MINE_DATE. These new columns allows the user to access and compare values in the logs.

The column_present function allows the user to view information in the log for specific columns in the SQL. This might make a nice audit facility allowing the DBA to see specific updates to specific columns of specific tables, all without the overhead of the Oracle Audit function.

Log Miner will be covered in depth in Chapter 18.

Export/Import

Export and Import are two character-mode utilities supplied by Oracle Corporation as early as Oracle4. The utilities perform a function that their name implies: export creates operating-system files of data from Oracle tables and import reads these operating-system files and creates the tables and loads the data back into the tables. The two utilities are used together primarily to back up and restore data, move data to other Oracle databases, and migrate data from earlier releases of Oracle to newer releases.

The common utilities Export and Import also have some serious enhancements. Export and Import now support all objects in a particular tablespace as well as wild-card selection when selecting table objects for export.

Export and Import can perform many important tasks in the Oracle9i environment. Export can be used to store data in archives, removing rows that are not being used but can easily be added with Import if the need exists.

Export and Import can be used to create test environments; they have the ability to capture all of a particular user's tables, indexes, and data and re-create in another Oracle instance. Export and Import can also play an important role in database tuning by eliminating certain kinds of fragmentation. Oracle fragmentation and using Export and Import for backup and recovery are beyond the scope of this book.

The operation of the Import and Export utilities is quite straightforward. Export writes the DDL (table definitions, index definitions, privileges, and so on) as well as the data itself. There are many options available to both Export and Import, such as just capturing the DDL information and not the data. Export then saves this information to named operating-system files. The operating-system files that Export creates are known as dump files. The dump files, which are in an Oracle proprietary format, are only useful to the Import utility. These dump files can be given specific names (operating-system dependent) or allowed to default to a preassigned name of EXPDAT.DMP.

Listing 1.4 shows the various parameters available for Export, and Listing 1.5 shows the various parameters available for Import.

Listing 1.4. SQL*Export Help Information

Export: Release 9.0.1.0.0 - Production on Sat Aug 11 15:38:22 2001

 Copyright 2001 Oracle Corporation.  All rights reserved.


You can let Export prompt you for parameters by entering the EXP
command followed by your username/password:

     Example: EXP SCOTT/TIGER

Or, you can control how Export runs by entering the EXP command followed
by various arguments. To specify parameters, you use keywords:
     Format:  EXP KEYWORD=value or KEYWORD=(value1,value2,...,valueN)
     Example: EXP SCOTT/TIGER GRANTS=Y TABLES=(EMP,DEPT,MGR)
               or TABLES=(T1:P1,T1:P2), if T1 is partitioned table

USERID must be the first parameter on the command line.

Keyword    Description (Default)      Keyword      Description (Default)
--------------------------------------------------------------------------
USERID     username/password          FULL         export entire file (N)
BUFFER     size of data buffer        OWNER        list of owner usernames
FILE       output files (EXPDAT.DMP)  TABLES       list of table names
COMPRESS   import into one extent (Y) RECORDLENGTH length of IO record
GRANTS     export grants (Y)          INCTYPE      incremental export type
INDEXES    export indexes (Y)         RECORD       track incr. export (Y)
DIRECT     direct path (N)            TRIGGERS     export triggers (Y)
LOG        log file of screen output  STATISTICS   analyze objects (ESTIMATE)
ROWS       export data rows (Y)       PARFILE      parameter filename
CONSISTENT cross-table consistency    CONSTRAINTS  export constraints (Y)

FEEDBACK             display progress every x rows (0)
FILESIZE             maximum size of each dump file
FLASHBACK_SCN        SCN used to set session snapshot back to
FLASHBACK_TIME       time used to get the SCN closest to the specified time
QUERY                select clause used to export a subset of a table
RESUMABLE            suspend when a space related error is encountered(N)
RESUMABLE_NAME       text string used to identify resumable statement
RESUMABLE_TIMEOUT    wait time for RESUMABLE
TTS_FULL_CHECK       perform full or partial dependency check for TTS
VOLSIZE              number of bytes to write to each tape volume
TABLESPACES          list of tablespaces to export
TRANSPORT_TABLESPACE export transportable tablespace metadata (N)
TEMPLATE template name which invokes iAS mode export

Export terminated successfully without warnings.

Listing 1.5. SQL*Import Help Information

Import: Release 9.0.1.0.0 - Production on Sat Aug 11 15:38:44 2001

 Copyright 2001 Oracle Corporation.  All rights reserved.
You can let Import prompt you for parameters by entering the IMP
command followed by your username/password:

     Example: IMP SCOTT/TIGER

Or, you can control how Import runs by entering the IMP command followed
by various arguments. To specify parameters, you use keywords:

     Format:  IMP KEYWORD=value or KEYWORD=(value1,value2,...,valueN)
     Example: IMP SCOTT/TIGER IGNORE=Y TABLES=(EMP,DEPT) FULL=N
               or TABLES=(T1:P1,T1:P2), if T1 is partitioned table

USERID must be the first parameter on the command line.

Keyword  Description (Default)       Keyword      Description (Default)
--------------------------------------------------------------------------
USERID   username/password           FULL         import entire file (N)
BUFFER   size of data buffer         FROMUSER     list of owner usernames
FILE     input files (EXPDAT.DMP)    TOUSER       list of usernames
SHOW     just list file contents (N) TABLES       list of table names
IGNORE   ignore create errors (N)    RECORDLENGTH length of IO record
GRANTS   import grants (Y)           INCTYPE      incremental import type
INDEXES  import indexes (Y)          COMMIT       commit array insert (N)
ROWS     import data rows (Y)        PARFILE      parameter filename
LOG      log file of screen output   CONSTRAINTS  import constraints (Y)
DESTROY                overwrite tablespace data file (N)
INDEXFILE              write table/index info to specified file
SKIP_UNUSABLE_INDEXES  skip maintenance of unusable indexes (N)
FEEDBACK               display progress every x rows(0)
TOID_NOVALIDATE        skip validation of specified type ids
FILESIZE               maximum size of each dump file
STATISTICS             import precomputed statistics (always)
RESUMABLE              suspend when a space related error is encountered(N)
RESUMABLE_NAME         text string used to identify resumable statement
RESUMABLE_TIMEOUT      wait time for RESUMABLE
COMPILE                compile procedures, packages, and functions (Y)
VOLSIZE                number of bytes in file on each volume of a file on tape

The following keywords only apply to transportable tablespaces
TRANSPORT_TABLESPACE import transportable tablespace metadata (N)
TABLESPACES tablespaces to be transported into database
DATAFILES datafiles to be transported into database
TTS_OWNERS users that own data in the transportable tablespace set

Import terminated successfully without warnings.

There are several database administrative and tuning tasks that are associated with any computer-based application: addition or deletion (when employment is terminated) of users that have access to the system, starting up and shutting down the database, adding additional disk space for the relational tables, monitoring how busy the computer is, and monitoring how well the database is performing.

The first item, DBA Studio (see Figure 1.15), is the main administrative tool that comes with the Oracle9 v9.0.1 software. Quest Software has an easy-to-use tool, Spotlight on Oracle (see Figure 1.16), which helps identify and explain various issues in the Oracle9i database environment. You also learn how to use Quest SQLab/Xpert (see Figure 1.17), a popular tool used to tune SQL statements. The popular PL/SQL development tool TOAD (see Figure 1.18) is covered in Chapter 16, “Using TOAD in the Development Arena,” and Figure 1.19 illustrates a new administrative type tool, Quest Central for Oracle. Software availability and trial keys for the Oracle and Quest software are in Appendix B.

DBA Studio from Oracle Corporation, Figure 1.15, is the tool to use to start/stop the Oracle9i environment, add/modify/delete users, add space to the Oracle9i tablespaces, and make quick changes to the Sales Tracking tables (changes such as adding a column to a table or adding/dropping indexes). You learn how to use this tool in Chapter 6, “Building Oracle Reports.”