If you haven't yet upgraded from Windows 3.1 to Windows 95, the information in this chapter describes the Database Administration Tools for Windows 3.1 supplied with Personal Oracle7 for Windows.
Before the introduction of Personal Oracle7 and the Oracle Workgroup Server, if you managed an Oracle database, you had two choices for tools (excluding any third-party products): SQL*DBA and SQL*Plus. Both of these interfaces relied upon the user entering the correct SQL and data manipulation commands.
With the advent of Personal Oracle7 and the Oracle Workgroup Server, the functionality of SQL*DBA and SQL*Plus has been partitioned into the Database Administration Tools, a set of Windows-based programs that provide a friendlier interface with a familiar look and feel for PC users. The purpose of this chapter is to introduce this toolset.
At this point you need to have a basic understanding of the Oracle architecture. You can think of the Oracle database as having three states:
Database Manager is a tool that simplifies the operation of a Personal Oracle7 database.
To start up the Personal Oracle7 database, double-click the Database Manager icon and type the database password in the pop-up window. Unless you have changed the password, type in oracle. (See Figure 4.1.)
Entering the database password during database startup. After a short time, the green light on the signal should appear, indicating that the database has started successfully. (See Figure 4.2.)
The process for shutting down the database is very similar. Double-click the Database Manager icon. Type the password in the pop-up window and then confirm that you want to shut down the database at the prompt. Within a short time, the red light on the signal should appear, indicating that the database has shut down successfully. (See Figure 4.3.)
NoteGoing through the shutdown process before exiting Windows or turning off your computer is always a good practice. This routine forces Oracle to close all the database files. However, if you turn off the computer without shutting down Personal Oracle7, Oracle will perform what is called instance recovery the next time Personal Oracle7 is started.
The function of Object Manager is to create or modify database objects: tables, indexes, views, and database links. Object Manager has an intuitive interface and is quite easy to operate.
At times, however, you will find that using SQL*Plus makes more sense than using Object Manager. As you well know, developing software is an iterative process. Programmers typically write scripts and programs and continue to modify and refine them during the development process. Object Manager is a great "quick and dirty" tool for building database objects. It cannot, however, record or process scripts. (See Figure 4.4.)
SQL*Plus is a command-line interface to the Oracle database. Thousands of Oracle users and programmers have been using this tool for many years. Even with the release of Personal Oracle7 for Windows 95, almost all Oracle database installations will continue to use SQL*Plus, regardless of hardware platform or operating system. People will continue to use SQL*Plus for at least two reasons:
SQL*Plus doesn't provide a point-and-click interface. It is old-fashioned in the sense that you have to type in each SQL statement that you want Oracle to process. (See Figure 4.5.) SQL*Plus has a spool capability that you can use for saving your SQL statements as well as the responses returned by the Oracle database.
Like SQL*Plus, SQL*DBA is a command-line interface to the Oracle database. Unlike SQL*Plus, SQL*DBA can be used to start up or shut down an Oracle database. All of the critical capabilities of SQL*DBA are now available in Database Manager. (See Figure 4.6.)
You might need to increase the size of a database for the following reasons:
Unlike some database systems, Oracle relies on preallocated files for storage. If you need to store 20MB of records in an Oracle database, you must specify the name and size of each MS-DOS file that you want Oracle to use. Increasing the size of the database really means allocating additional MS-DOS files for the database's use.
Before the release of Personal Oracle7 (and its related product, the Oracle Workgroup Server), you had to use SQL*DBA or SQL*Plus and enter a command to allocate additional space. Database Expander simplifies this procedure and nearly eliminates the opportunity to make any errors. However, if you have existing SQL*Plus scripts, you can still expand a Personal Oracle7 database by using the CREATE TABLESPACE or ALTER TABLESAPCE statement.
Figure 4.7 shows the Database Expander adding space to the default Personal Oracle7 database that's created during installation.
Although Personal Oracle7 is not designed to support concurrent multiuser access, the Oracle7 database server is designed for this purpose. To support this feature, Oracle provides two database objects: the role and the user. (Roles and users aren't important to a standalone Oracle database, but they are very useful to a developer who is trying to learn how Oracle works.)
Throughout this book (and in other sources), the terms Oracle user, database user, Oracle owner, and Oracle account are used interchangeably. Essentially, they all refer to the same thing: an account that has been granted one or more database privileges. However, a number of features about Oracle accounts aren't obvious:
The individuals who work in an organization usually have well-defined roles. For example, in a marketing organization, typical roles could be director, manager, analyst, and clerk. Whether or not an organization uses an automated information system, each role possesses a defined set of information access privileges. For example, a director has the right to retrieve, add, change, or destroy any information that exists in his or her domain--;whether it is stored in file cabinets or on disk drives. On the other hand, a clerk must follow specific procedures to make changes to information.
Oracle supports this environment through database roles. A database role is a set of privileges that serve as a template that can be assigned to individual users. For example, a database role named TECHNICIAN might have select, insert, and update privileges (the capability to read, write, and modify records) on the Product table but have only the select privilege on the Budget table. After a database role has been defined, it can be assigned to individual users. Using database roles simplifies the work of a database administrator and reduces the odds that a hole may exist in the database's security scheme. (See Figure 4.9.) ***04DPO09***
Database privileges can be divided into two categories: system privileges and object privileges. An example of a system privilege is the right to create a table or an index. An example of an object privilege is the right for an Oracle user to select data from or insert data into a table owned by another Oracle user.
Oracle version 6 had only three system privileges:
For the purpose of compatibility with Oracle version 6, these three privileges have been transformed into three predefined roles in the Oracle7 database. In addition, Personal Oracle7 has two other predefined roles that concern the privilege to perform a full export or import (discussed in Chapter 25, "Database Backup and Recovery"):
User Manager enables the user to assign a subset of the system privileges to database roles. (See Figure 4.10.) SQL*Plus offers the full array of system and object privilege management that you would need in a multirole, multiuser environment.
The set of object privileges that can be granted to a role or a user depend upon the type of object. For example, table object privileges include Select: the capability to read records from a table
Insert: the right to add records to a table
Delete: the right to remove records from a table
Update: the right to modify existing rows in a table
Even though a database user has been granted a role, you can still grant an additional database privilege to a specific user. If at all possible, though, you should grant these "special privileges" sparingly; in no time at all, keeping track of special privileges can become an administrative nightmare.
Because the Database Administration Tools are also part of Oracle's bundle of products referred to as Workgroup/2000, some of the tools are really intended for a multiuser environment. Session Manager falls into this category.
You can simultaneously connect multiple tools or applications to a Personal Oracle7 database. However, because MS-DOS is not a multitasking operating system, only one tool can be active at any given time. For instance, if you use SQL*Plus, Session Manager will display two sessions: SYS (the account that manages Personal Oracle7) and the Oracle account running SQL*Plus. (See Figure 4.11.)
You've finally developed your killer Personal Oracle7 application. After much blood, sweat, and tears, you've assembled a comprehensive set of demo data that you want to distribute to other Personal Oracle7 users. Export and Import are the tools to use for this purpose.
Export and Import work together. Export copies the contents of one or more tables to a binary-format MS-DOS file. Import reads an export file and loads the contents as specified by the user.
Export can save the contents of a single table to a binary file specified by the user. (See Figure 4.12.) You can export a table definition without its contents or produce an export file in which the tables have been compressed.
A second type of export option saves all the database objects--;tables, indexes, and others--;that belong to a specified user. (See Figure 4.13.)
At some point, you may want to export the entire contents of your Personal Oracle7 database--;the contents of each table and its definition, all index definitions, all view definitions, and every other database object definition. (See Figure 4.14.) Such an export is referred to as a full export. The time required to perform a full export depends on two factors: the size of the database and the percentage of database storage that is used. A full export of a very large but mostly empty database won't take very long to complete. If it doesn't take long, a full export of your database provides the most flexibility and safety of all database backup strategies.
NoteA crossover point exists between performing a full export and performing a backup of the database. If your goal is simply to safeguard your data, the backup certainly accomplishes that. However, if you also need to move portions of the data to another Oracle7 database, you're much better off using the Export utility.
In addition to the Export utility, you can use Backup Manager to safeguard your data. Backup Manager differs from Export in that it copies database files in their entirety rather than copying a portion of the database. (See Figure 4.15.)
Recovery Manager works in conjunction with Backup Manager to perform database recovery. (See Figure 4.16.) Chapter 25 covers the backup and recovery of a Personal Oracle7 database.
When you start up or shut down a Personal Oracle7 database, Oracle prompts you for a password. This password is distinct from a user password because it applies to the database as a whole. Password Manager enables you to change this password. (See Figure 4.17.) Changing the password is one of the first things that you should do when you install Personal Oracle7.
NoteThe default database password is oracle.
You'll want to use SQL*Loader when you have a comma-separated values (CSV) file, tab-delimited file, or other flat file containing data that you want to load into an Oracle table. SQL*Loader is a sophisticated utility that enables you to describe the data and how it should be processed. Be aware that SQL*Loader is unidirectional; it isn't capable of extracting the contents of a table into a flat file.
Like SQL*DBA and SQL*Plus, SQL*Loader is a legacy tool that has been remodeled for use in a Windows environment. (See Figure 4.18.)
The Personal Oracle7 Windows program group contains two sets of help files: Oracle7 Documentation and Database Tools User's Guide. Both files use the Windows Help format.
The Oracle7 Documentation Help consists of the entire set of Oracle7 Documentation:
Although it's comprehensive, the documentation is also massive--;as broad and as deep as the sea. Placing it in an online format does make it more accessible.
NoteYou can't search a topic across manuals within the Oracle7 Documentation Help. You must select a single manual before the Search button is enabled.
This chapter focuses on the following key concepts: