Data Files

Data files perform perhaps the most important function in an Oracle system—storing data in a retrievable format. These files cannot be read directly from operating system utilities such as More in UNIX or Notepad under Microsoft Windows. You access their contents via SQL queries, which brings up an important point: You have little control over where things are stored within a given tablespace or data file. Instead, when working with SQL commands, you specify logical structures such as tablespaces and tables. The physical structures are Oracle’s responsibility. Then you, as the DBA, map the two together (see Figure 11.1).

Figure 11.1.  Physical versus logical data structures.

Remember that each data file is associated with only one tablespace. A tablespace has one or more data files. The DBA has to make a tradeoff when determining the number of data files associated with a given tablespace. One large tablespace with all the data tables is easier to administer, and you do not have a table running out of space in one tablespace when there is a large amount of space in other tablespaces that it cannot use. However, because you cannot control the placement of a table or index within data files in a tablespace, you are unable to split the input/output load between disks. There also are situations where you would rather a user be stopped by filling up a tablespace before taking up all the space available to the system. An example of this is where you allocate space between various projects sharing a single database. It takes system resources to keep track of the large number of data files, so you usually want to keep the number of files down to a reasonable value.

Let’s explore what these data files look like internally, at a conceptual level. Stored within every Oracle data file is a record of the latest change number (SCN in Oracle terms) that has been applied to the system. In this manner, when you restore a backup data file from tape, Oracle can determine which changes need to be applied to the file to bring it up to date. Another point to remember about data files is that they are preallocated by the operating system to the full size that you specify in the creation command. If you allocate a file for a tablespace to 100MB, the data file takes up 100MB even though it contains no tables or data. This preallocation concept applies to the database objects, such as tables that you create within the tablespace, and their data files. If you create a table with an initial extent of 10MB, there will be a 10MB section of the tablespace reserved for that table even though it does not contain any rows of data.

The following summarizes other facts that you should know about your data files:

•  There are two general types of data files—raw and cooked. (Don’t you just love these computer terms?) The normal operating system files that you work with are considered to be cooked because they use the file system utilities provided by your operating system. In some older operating systems, especially older UNIX implementations, the overhead associated with the operating system file management utilities slowed Oracle down unnecessarily, so Oracle enhanced its software to talk directly to the disk drives. These were referred to as raw disks. With most modern operating system implementations, the speed difference between raw and cooked disk drives is small and there are cases wherein the cooked disk drives can provide better performance.

•  One of the primary benefits of having a number of tablespaces is to balance the input/output load across several disk drives or disk controllers. Generally speaking, you want to split tables and their indexes into separate tablespaces located on separate disks to maximize performance (at least in larger Oracle instances). You also want to separate the rollback and temporary segments (see Chapter 10, “Oracle Memory Areas”) from the table and index data files to prevent input/output to these tablespaces from competing with each other.

•  Because every write transaction to a data file is mirrored with a transaction to a redo log file, you usually want to locate your data files on separate disk drives from those of your redo log files.

•  Finally, Oracle provides an interesting feature—the read-only tablespace. Tablespaces designated read-only are assumed to be up-to-date without having to check their latest update number (the SCN). This finally enables you to place tablespaces on such devices as CD-ROMs (to which you could not write to record SCN updates). It also can save time for tablespaces that contain reference data that you do not update. If one of these data files is lost, you copy the data file from the backup tape and bring it directly on line. Oracle understands that it does not have to try to apply redo and archive log transactions to bring the file up-to-date.

This information should start you on the way to understanding Oracle data files. Later chapters discuss the objects that you place within a data file and maintenance of the tablespaces and data file. For now, be sure that you understand what these data files are and their relationship with tablespaces. The following is the SQL query that enables you to determine the location of your data files:

SQL> select * from sys.dba_data_files;



FILE_NAME

----------------------------------------------------------------------

  FILE_ID TABLESPACE_NAME          BYTES    BLOCKS STATUS

--------- -------------------- --------- --------- ---------

C:\ORAWIN\DBS\wdbrbs.ora

        3 ROLLBACK_DATA          3145728      1536 AVAILABLE



C:\ORAWIN\DBS\wdbtemp.ora

        4 TEMPORARY_DATA         2097152      1024 AVAILABLE



C:\ORAWIN\DBS\wdbuser.ora

        2 USER_DATA              3145728      1536 AVAILABLE



C:\ORAWIN\DBS\wdbsys.ora

        1 SYSTEM                10485760      5120 AVAILABLE



SQL>

You might be wondering what else you can do with data files. Actually, there is very little to do with data files after you create them. This is a good thing in that you focus your maintenance efforts on the database objects as opposed to file structures and other technical details that are managed by the Oracle DBMS software. However, the following actions might be of interest to you in some rare circumstances:

•  Taking a specific data file offline or online (alter database datafile ‘filename’ online or alter database datafile ‘filename’ offline).

•  Renaming or moving a datafile (alter database rename file ‘filename_old’ to ‘filename_new’). Note that this makes a copy of the data file but does not delete the old data file, which you have to do manually.