With the release of Personal Oracle7 for Windows 95, Oracle has almost completely revamped the look and feel of the product:
This chapter starts by examining the new tools in greater depth.
The Navigator can be invoked by going to the Personal Oracle7 for Windows 95 program
group and selecting Personal Oracle7 Navigator. When the Navigator is invoked, you
will see a window with two frames. (See Figure 3.1.) Two folders--;Projects
and Database Connections--;and a cylindrical icon identified as Local
Database appear within the left frame of the main window.
Figure 3.1. The Personal Oracle7 Navigator
window.
If you double-click the Local Database icon and the Personal Oracle7 database is not yet running, the Navigator automatically starts the database. You will see the same windows that you see when you select the Start Database icon from the Personal Oracle7 for Windows 95 program group. Once the Personal Oracle7 database starts, the Local Database icon includes two connected plugs to denote that the Personal Oracle7 database is running and that the Navigator is connected to it. In addition, eight folders appear beneath the Local Database icon:
You can look at the contents of these folders in two ways. If you click a folder
name in the left frame, the contents of the folder are displayed in the right frame.
(See Figure 3.2.)
Figure 3.2.
Examining the contents of a Local Database folder. Alternatively, you can place
the pointer over a folder and click the right mouse button to bring up a menu with
the options Explore, New, and Properties; to see the contents of the Table
folder, select the Explore menu option. (See Figure 3.3.)
Figure 3.3.
Exploring the Table folder in the Local Database. As you can see in Figure
3.3, the contents of six of the folders--;Table, View, Index,
Synonym, Database Link, and Snapshot--;are displayed
in two columns, Name and Owner. The contents of the other two folders--;User
and Role--;are displayed in a single column labeled Name.
TipWhen you examine the contents of one of the folders in the Local Database, Personal Oracle7 displays the contents in an arbitrary order. To view the contents in alphabetical order, click the column heading that you want to use for sorting the list of items. For example, the list of tables in the Table folder is displayed in two columns: Name and Owner. To sort by Owner, click the Owner column heading, as shown in Figure 3.4. To sort by Name, click the Name column heading, as shown in Figure 3.5.
Figure 3.4. Sorting the list of tables by
owner.
Figure 3.5. Sorting the list of tables
by name.
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 item: an account that has been granted one or more database privileges. A number of features about Oracle accounts aren't obvious:
Figure 3.6. Creating database user MGILROY with the Navigator.
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 by providing the database role. 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 3.7.)
Figure 3.7. Creating the TECHNICIAN
database role with the Navigator.
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 3.8.) SQL*Plus accepts all the statements related to system and
object privilege management.
Figure 3.8. Granting system privileges
to a role with the Navigator.
The set of object privileges that can be granted to a role or a user depends on the type of object. For example, table object privileges include the following: 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.
An entirely new object introduced in Personal Oracle7 for Windows 95 is the project.
A project is a set of objects that help to support a specific purpose. These
objects might include tables, views, database connections, and pointers to external
files. When you install Personal Oracle7, two projects are automatically created:
Sample and Toolbox. You can look at a project's properties by selecting a project
and right-clicking Properties. (See Figure 3.9.)
Figure 3.9. Looking at the properties
of the Sample project.
Oracle gives you two ways to create a new project. You can select File | New |
Project from the menu bar and then specify a filename for the new project in the
Create Project dialog box (as shown in Figure 3.10), or you can select the Project
folder and right-click New. By default, all project files are stored in the Bin
directory (which is a subdirectory of Orawin95).
Figure 3.10. Specifying a filename for
a project.
Oracle also gives you several ways to add an object to a project. If the object
already exists in the database, you can copy it to the project. For example, to add
a table to the Frayed_Wires project, copy the table to the project by dragging it
from the Table folder in the Local Database to the Frayed_Wires
folder. (See Figure 3.11.) In addition, you can copy a table to a project by selecting
the table with the pointer and selecting Edit | Copy. Then select the project with
the pointer and select Edit | Paste.
Figure 3.11. Adding a table to a project.
To delete an object from a project, click the object in the Project folder
and press the Delete key. Alternatively, click the object, right-click, and select
Delete from the pop-up menu. In either case the Navigator will ask you whether the
object should be deleted from the project only or from both the project and the database.
(See Figure 3.12.) By default, the Navigator will delete the object from the project
only. If you delete an object from a project, the object will still exist in the
database.
Figure 3.12. Choosing a delete option
when deleting a table from a project.
The term export refers to the process of saving the tables that belong
to a project--;both the table definitions and the contents of the tables--;to
a file external to the Personal Oracle7 database. To export a project, right-click
the project icon and select Export. Then specify the directory in which the export
file will be created in the Save in box. (See Figure 3.13.) The default directory
is C:\Orawin95\Bin
Figure 3.13. Selecting a directory for storing
the exported project.
Once the export is complete, a message box will tell you that the export was successful.
(See Figure 3.14.) If you inspect the directory in which the export file was stored,
you'll see two files with the name of the project that you exported. The file that
has the extension .DMP is a binary file that contains the database objects
that were exported. The other file is a text file that describes the result of the
export process.
Figure 3.14. Navigator indicates a successful
export.
One way to delete a project is to select a project, right-click, and select Delete
from the pop-up menu. Alternatively, select the project to be deleted by clicking
its icon and select Edit | Delete from the top menu. In either case a message box
asks you to confirm the deletion. (See Figure 3.15.)
Figure 3.15. Deleting a project with
the Navigator.
The Navigator allows you to define one or more connections to Oracle7 databases that reside on other machines. You define a database connection by specifying a username, the password for the user, and a connect string or database alias. The database alias identifies three items:
The SQL*Net Easy Configuration tool, which is discussed near the end of this chapter, manages database aliases.
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:
To start the Personal Oracle7 database, click the Windows 95 Start button and select the Programs menu item. In the Personal Oracle7 for Windows 95 program group, select the Start Database program. The Oracle7 Startup window displays the following messages: Starting up database...
Checking security...
Oracle7 instance started...
Database mounted...
Oracle7 database started successfully (See Figure 3.16.)
Figure 3.16. Window displayed after
successful database startup.
To start the database at Windows 95 startup, follow these steps:
After performing this task, the database will start whenever you start or log
in to Windows 95.
Figure 3.17. Creating a shortcut in
the StartUp folder for Start Database.
To stop the Personal Oracle7 database, click the Windows 95 Start button and select the Programs menu item. In the Personal Oracle7 for Windows 95 program group, select the Stop Database program. The following series of messages appears in the Oracle7 Shutdown window: Checking security...
Shutting down database...
Database dismounted...
Oracle7 database is shutdown (See Figure 3.18.)
Figure 3.18. Window displayed after
successful database shutdown.
TipGoing through the shutdown process before exiting Windows 95 or turning off your computer is 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. Please refer to Chapter 17, "Defining Transactions," for more information on instance recovery.
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 3.19.) However, SQL*Plus does have a spool capability that you can use
for saving your SQL statements as well as the responses returned by the Oracle database.
Figure 3.19. Using SQL*Plus to create
the Customer table.
In addition to the Export utility, you can use Backup Manager to safeguard your
data. Backup Manager differs from Export in that it copies log files in their entirety
rather than making a copy of the contents of the database. (See Figure 3.20.)
Figure 3.20. The Backup Manager window.
Recovery Manager works in conjunction with Backup Manager to perform database
recovery. (See Figure 3.21.) Chapter 25 covers the backup and recovery process.
Figure 3.21. The Recovery Manager window.
Export and Import are utility programs that allow you to transfer data from one Oracle database to another. The Export program copies the contents of one or more tables to a binary-format file that, by default, is given the extension .DMP. The Import program reads an export file and loads the contents into an Oracle database as specified by the user.
In Personal Oracle7 for Windows 3.11, these programs were Windows based; however, in Personal Oracle7 for Windows 95, you must run these programs from an MS-DOS window. To control the action of these programs, you may either use command-line arguments or respond to the prompts displayed by the program. You also have the option of exporting tables by using the Navigator to export a project.
To run the export program, open an MS-DOS window and type exp at the
DOS prompt. If you don't specify any command-line arguments, the Export program prompts
you for the Oracle username and password to use to connect to Personal Oracle7. (See
Figure 3.22.)
Figure 3.22. Using the Export program
from an MS-DOS window.
Export also provides the option of saving all the database objects--;tables, indexes, and so on--;that a specified user owns.
A full export exports 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. 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.
SQL*Loader is a utility program that loads comma-separated values (CSVs), tab-delimited,
and other types of "flat" files into an Oracle database. It is a powerful
program that allows you to describe the format of the flat file and how you want
to process the records in that file. Again, SQL*Loader is no longer a Windows-based
program, as it was in Personal Oracle7 for Windows 3.11. Like the Export and Import
programs, you must run SQL*Loader from an MS-DOS window using command-line arguments.
(See Figure 3.23.) Also, be aware that SQL*Loader is unidirectional; it isn't capable
of extracting the contents of an Oracle table into a flat file.
Figure 3.23. Using SQL*Loader from an
MS-DOS window.
SQL*DBA is another command-line interface to the Oracle database. You can use
SQL*DBA to start up or shut down an Oracle database and to monitor the internal functioning
of the Personal Oracle7 database. (See Figure 3.24.)
Figure 3.24. Monitoring processes with
SQL*DBA.
You can simultaneously connect multiple tools or applications to a Personal Oracle7 database. Personal Oracle7 for Windows 3.1 included a utility program called Session Manager that would display the current connections to the Oracle database. Personal Oracle7 for Windows 95 no longer has this tool. However, you can use SQL*DBA to view the current users of Personal Oracle7.
Personal Oracle7 for Windows 95 no longer supplies the utility program Database
Expander that was part of the version for Windows 3.11. As you may recall, Database
Expander could examine and increase the size of an Oracle object called a tablespace
at the user's request. A tablespace is a logical area in which Oracle stores
tables, indexes, and other objects. A tablespace is composed of one or more operating
system files. The functionality provided by Database Expander is now provided by
both the Navigator and SQL*DBA. To examine the used and free space in each tablespace
with the Navigator, select the Local Database by clicking its icon. You may then
either right-click and select Properties from the menu or select File | Properties.
Select the Tablespace tab. In the drop-down Name field, select the tablespace named
USER_DATA. The Navigator will create a three-dimensional pie chart that
shows the amount of used and free space in the selected tablespace. (See Figure 3.25.)
Figure 3.25. Using the Navigator to examine
used and free space in a tablespace.
At some point, you might need to increase the size of a database. This situation
occurs for several 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 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. In Chapter 26, "Managing Space," I'll show you how you to use SQL*Plus or SQL*DBA to create a new tablespace or increase the storage capacity of an existing tablespace.
Password Manager is a tool for changing the internal password for Personal Oracle7.
In the version for Windows 3.11, Password Manager was a Windows-based program; you
must now run it from the MS-DOS prompt. (See Figure 3.26.) However, that requirement
isn't such an inconvenience because, in all likelihood, you will rarely run this
program. You should consider changing the default password after you install Personal
Oracle7--;the default password is, not surprisingly, oracle. However,
if you do change the database password, you will be prompted for the password whenever
you start or stop the database unless you also assign the password to DBA_AUTHORIZATION
in the registry.
Figure 3.26. Using Password Manager
from an MS-DOS prompt to change the database password.
NoteThe default database password is oracle.
SQL*Net version 2 uses several configuration files to specify legitimate database
service names or aliases. Unfortunately, the average user can easily make a mistake
in setting up these configuration files. To simplify this process, Personal Oracle7
for Windows 95 supplies a utility program called SQL*Net Easy Configuration. When
you first invoke the program, you'll notice that it looks much like the Personal
Oracle7 Installer. Figure 3.27 shows how to use SQL*Net Easy Configuration to specify
a database alias.
Figure 3.27. Using SQL*Net Easy Configuration
to specify a new database alias.
You can find online documentation for Personal Oracle7 for Windows 95 in two places:
Unfortunately, more online documentation was supplied with Personal Oracle7 for Windows 3.11 than is supplied with Personal Oracle7 for Windows 95.
This chapter focuses on the following key concepts:
