Automatic Resizing of Files

One feature in Oracle8 that became available with Oracle 7.3 is the automatic resizing of data files option. One of the biggest problems for database administrators in the past was running out of space for a new extent within a tablespace. You had to be constantly vigilant to ensure that you had both enough space and large enough blocks of contiguous space to accommodate table and index growth. The difficulty was that you could never tell when a particular table or index was going to need another extent.

This was a serious problem in that applications would crash and data entered could be lost when you ran out of space. For data warehouses, this problem usually occurred when data was being loaded in the middle of the night causing the DBA to be awakened to fix the problem. Believe me, this was a serious problem for married DBAs whose spouses did not like to be awakened every week with phone calls. It also meant that the database might not be available on time for users in the morning while the problems were being corrected.

The solution to this was to specify in the SQL syntax that it would be okay for the tablespace to add more space to a given data file. This space would be consumed on the same disk drive. Of course, you would still want some control over this resizing, so the SQL syntax has to take this into account. The following is an example of how you would specify the autoexend option (which is not the default):

Alter database

datafile ‘/u03/oradata/devel01.ora’

autoexend on

next 50M

maxsize 500M;

Distribution of Data for Tuning

What I want to do here is point you to Chapter 24, “Oracle8 Database Tuning,” which discusses database tuning. Some of you may just be reading this chapter preparing to lay out your data files without thinking of the impact on tuning. True, if you just have a 20MB database in mind, you can do almost whatever you want when it comes to file layout. However, if you are talking about storing and reading through gigabytes worth of information, you probably want to spend some time scattering the data and software intelligently between the disk drives to improve performance. This is often the cheapest way to improve the performance of an Oracle system.

Standard File Locations

Before going into detail on the various types of files associated with the Oracle system, let’s step back and provide an overview of how these files are arranged on the disk drives of your system. If you had Oracle6, it was hard to predict precisely where files were going to be stored. Most installations did not have sound guidance and used whatever the DBA at the time preferred. Starting with Oracle7, Oracle issued guidance as to how to lay out your files in what they refer to as the Optimal Flexible Architecture (OFA). You may wish to do things slightly different than the OFA would. Note, too, that the configuration of the Personal Oracle7 product that runs under Microsoft Windows is slightly different from those of the other products.

The Optimal Flexible Architecture was designed by the Oracle consulting staff for installations that it performed for customers. Oracle liked it so much it presents it in its Installation and Configuration Guide as a recommendation. This architecture is also the default for its Oracle installer scripts on most computer systems. Figure 11.3 shows the configuration of Oracle7 for UNIX. This figure also shows the general concepts of the OFA, although the names of the directories may vary on other operating systems.

Figure 11.3.  The Optimal Flexible Architecture (OFA) for UNIX.

The starting point for this architecture is known as the Oracle base. There are three key directories under Oracle base. Admin stores log files and other information for the DBA. Data is designed to store data files. Product stores the Oracle application software and the configuration files for the Oracle home instances. The other two directories are less frequently used, at least in my experience: TAR is designed to hold tape downloads, and Local is designed for items you create and may want to store with the Oracle software.

This architecture has a place for everything that you will find in a typical Oracle database. As with most standards, once you learn where things are placed in it, you can find those same things in any other Oracle databases. The large number of directories helps in that each lower-level directory has a specific focus and limited number of files that you have to sort through (from a few files to a few dozen if you keep things cleaned out properly). There is a single directory that contains all the executable files that you will typically need. This makes it easy to set your path so that you can access the Oracle applications. Finally, this architecture is designed to easily accommodate having multiple versions of the RDBMS software on the system at the same time. This is especially useful when converting over instances. You can bring up the new release of the RDBMS on your test database to see how it works while maintaining production on the existing release.

Very large installations usually alter the storage of data files to maximize performance at some point. Configuration alternatives are explored more thoroughly in the installation planning discussions in Chapter 5. For now, just try to get a good feel for the types of data files that you have. Each type of file will be discussed to provide you with an understanding of what it can do for you.

In almost all PC-based installations, creating a number of directories that can be split across multiple disk drives is not worth the effort. Most PCs have a single disk drive, and most DOS/Windows users are used to having software products installed at a single directory hierarchy located off the root directory. Part of the balancing act a product has to do when porting between platforms is maintaining the same command structure and interfaces yet adapting to the customers of the host environment. In Oracle’s case, they have implemented the directory structure shown in Figure 11.4.

Figure 11.4.  Directory of Personal Oracle under Microsoft Windows 95.