CHAPTER 15

Oracle Storage

Exam Objectives

• 062.5.1    Describe the Storage of Table Row Data in Blocks

• 062.5.2    Create and Manage Tablespaces

• 062.15.1    Explain How Oracle Database Server Automatically Manages Space

• 062.15.2    Save Space by Using Compression

• 062.15.3    Proactively Monitor and Manage Tablespace Space Usage

• 062.15.4    Use the Segment Advisor

• 062.15.5    Reclaim Wasted Space from Tables and Indexes by Using the Segment Shrink Functionality

• 062.15.6    Manage Resumable Space Allocation

Users never see a physical datafile. All they see are logical segments. System administrators never see a logical segment. All they see are physical datafiles. The Oracle database provides complete abstraction of logical storage from physical. This is one of the requirements of the relational database paradigm. As a database administrator (DBA), you must be aware of the relationship between the logical and physical storage. Monitoring and administering these structures, a task often described as space management, used to be a huge part of a DBA’s workload. The facilities provided in recent releases of the database can automate space management to a certain extent, and they can certainly let the DBA set up storage in ways that will reduce the maintenance workload considerably. Also, some facilities will permit more efficient use of space, such as compression and segment reorganization through the segment shrink capability.

When a user hits a space limit, an error will be returned, and the statement that hit the limit will fail. This situation should, of course, be avoided, but if it does occur, the user can be protected by enabling the resumable space allocation facility.

Describe the Storage of Table Row Data in Blocks

At the lowest level, rows are stored in blocks. But there are several layers of storage above that, all of which must be understood before drilling down to the level of the row in the block. Start at the top and work down.

The Oracle Data Storage Model

The separation of logical from physical storage is a necessary part of the relational database paradigm. It means that programmers have no way of physically referencing an item of data, thus eliminating any dependency on a specific machine. The relational paradigm states that programmers should address only logical structures and let the database manage the mapping to physical structures. This means that physical storage can be reorganized or the whole database can be moved to completely different hardware and operating system, and the application will not be aware of any change.

Figure 15-1 shows the Oracle storage model sketched as an entity-relationship diagram, with the logical structures to the left and the physical structures to the right.

Figure 15-1    The Oracle storage model

There is one relationship, shown as a dotted line. This is a many-to-many relationship between segments and datafiles. This relationship is dotted because it shouldn’t be there. As good relational engineers, DBAs do not permit many-to-many relationships. Resolving this relationship into a normalized structure is what the storage model is all about.

The tablespace entity resolves the many-to-many relationship between segments and datafiles. One tablespace can contain many segments and be made up of many datafiles. This means that any one segment may be spread across multiple datafiles, and any one datafile may contain all of or parts of many segments. This solves many storage challenges. Some older database management systems used a one-to-one relationship between segments and files; every table or index would be stored as a separate file. This raised two dreadful problems for large systems. First, an application might well have thousands of tables and even more indexes; managing many thousands of files was an appalling task for the system administrators. Second, the maximum size of a table is limited by the maximum size of a file. Even if modern operating systems do not have any practical limits, there may be limitations imposed by the underlying hardware environment. Use of tablespaces bypasses both these problems. Tablespaces are identified by name, unique in the database.

The segment entity represents any database object that stores data and therefore requires space in a tablespace. Your typical segment is a table, but there are other segment types, notably index segments and undo segments. Any one segment can exist in only one tablespace, but the tablespace can spread it across all the files making up the tablespace. This means that the tables’ sizes are not subject to any limitations imposed by the environment on maximum file size. Because many segments can share a single tablespace, it becomes possible to have far more segments than there are datafiles. Segments are schema objects, identified by the segment name and qualified with the owning schema name. Note that programmatic schema objects (such as PL/SQL procedures, views, or sequences) are not segments; they do not store data, and they exist as structures within the data dictionary.

The Oracle block is the unit of input/output (I/O) for the database. Datafiles are formatted into Oracle blocks, consecutively numbered. The size of the Oracle blocks is fixed for a tablespace (generally speaking, it is the same for all tablespaces in the database); the default is 8KB. A row might be only a couple hundred bytes, so there could be many rows stored in one block. However, when a session wants a row, the whole block will be read from disk into the database buffer cache. Similarly, if just one column of one row has been changed in the database buffer cache, the database writer (DBWn) will (eventually) write the whole block back into the datafile from which it came, overwriting the previous version. The size of an Oracle block can range from 2KB to 16KB on Linux or Windows and to 32KB on some other operating systems. The block size is controlled by the parameter DB_BLOCK_SIZE. This can never be changed after database creation because it is used to format the datafiles that make up the SYSTEM tablespace. If it becomes apparent later that the block size is inappropriate, the only course of action is to create a new database and transfer everything into it. A block is uniquely identified by its number within a datafile; the block number alone is not enough.

Managing space one block at a time would be a crippling task, so blocks are grouped into extents. An extent is a set of consecutively numbered Oracle blocks within one datafile. Every segment will consist of one or more extents, also consecutively numbered. These extents may be in any and all of the datafiles that make up the tablespace. An extent can be identified from either the dimension of the segment (extents are consecutively numbered per segment, starting from zero) or the dimension of the datafile (every extent is in one file, starting at a certain Oracle block number).

A datafile is physically made up of a number of operating system blocks. How datafiles and the operating system blocks are structured is entirely dependent on the operating system’s file system. Some file systems have well-known limitations and are therefore not widely used for modern systems (for example, the old MS-DOS FAT file system could handle files up to only 4GB, and only 512 of them per directory). Most databases will be installed on file systems with no practical limits, such as NTFS on Windows and ext3 on Linux. The alternatives to file systems for datafile storage are raw devices or ASM. Raw devices are no longer supported by Oracle for datafile storage. ASM is described in Chapter 3.

An operating system block is the unit of I/O for your file system. A process might want to read only one byte from disk, but the I/O system will have to read an operating system block. The operating system block size is configurable for some file systems (for example, when formatting an NTFS file system, you can choose from 512 bytes to 64KB), but typically system administrators leave it at the default (512 bytes for NTFS, 1KB for ext3). This is why the relationship between Oracle blocks and operating system blocks is usually one-to-many, as shown in Figure 15-1. There is no reason not to match the operating system block size to the Oracle block size if your file system lets you do this. A configuration that should always be avoided is one where the operating system blocks are bigger than the Oracle blocks.

Segments, Extents, Blocks, and Rows

Data is stored in segments. The data dictionary view DBA_SEGMENTS describes every segment in the database. This query shows the segment types in a simple database—the counts are low because there is no real application installed:

In brief, and in the order they are most likely to concern a DBA, these segment types are as follows:

•  TABLE    These are the heap-structured table; they consist of variable-length rows in random order. Even though a typical segment is a table segment, never forget that the table is not the segment and that there are more complex table organizations that use other segment types.

•  INDEX    Indexes are sorted lists of key values, each with a pointer, the ROWID, to the physical location of the row. The ROWID specifies which Oracle block of which datafile the row is in, as well as the row number within the block.

•  TYPE2 UNDO    These are the undo segments (no one refers to them as “type2 undo” segments) that store the prechange versions of data necessary for providing transactional integrity: rollback, read consistency, and isolation.

•  TABLE PARTITION    A table can be divided into many partitions. If this is done, then the partitions will be individual segments, and the table itself will not be a segment at all; it exists only as the total of its partitions. Each table partition of a heap table is itself structured as a heap table in its own segment. These segments can be in different tablespaces, meaning that it becomes possible to spread one table across multiple tablespaces.

•  INDEX PARTITION    An index will be in one segment by default, but indexes can be partitioned. If you are partitioning your tables, you will usually partition the indexes on those tables as well.

•  LOBSEGMENT, LOBINDEX, LOB PARTITION    If a column is defined as a large object data type, then only a pointer is stored in the table itself; the pointer is to an entry in a separate segment where the column data actually resides. LOBs can have indexes built on them for rapid access to data within the objects, and LOBs can also be partitioned.

•  CLUSTER    A cluster is a segment that can contain several tables. In contrast with partitioning, which lets you spread one table across many segments, clustering lets you denormalize many tables into one segment.

•  NESTED TABLE    If a column of a table is defined as a user-defined object type that itself has columns, then the column can be stored in its own segment as a nested table.

•  ROLLBACK    Rollback segments should not be used in normal running from release 9i onward. Release 9i introduced automatic undo management, which is based on undo segments. There will always be one rollback segment that protects the transactions used to create a database (this is necessary because at that point, no undo segments exist), but it shouldn’t be used subsequently.

Every segment has one or more extents. When a segment is created, Oracle will allocate an extent to it in whatever tablespace is specified. Eventually, as data is entered, the extent will fill. Oracle will then allocate a second extent in the same tablespace, but not necessarily in the same datafile. If you know that a segment is going to need more space, you can manually allocate an extent. Figure 15-2 shows how to identify precisely where each extent of a segment is.

Figure 15-2    Determining the physical location of a segment’s extents

In the figure, the first command creates the table SCOTT.NEWTAB, relying completely on defaults for the storage. Then a query against DBA_EXTENTS shows that the segment consists of just one extent, extent 0. This extent is in file 6 and is eight blocks long. The first of the eight blocks is block 224. The size of the extent is 64KB, which shows that the block size is 8KB. The next command forces Oracle to allocate another extent to the segment, even though the first extent will not be full. The next query shows that this new extent, extent 1, is also in file 6 and starts immediately after extent 0. Note that it is not clear from this example whether the tablespace consists of multiple datafiles because the algorithm Oracle uses to work out where to assign the next extent does not simply use datafiles in turn. If the tablespace does consist of multiple datafiles, you can override Oracle’s choice with this syntax:

The last query in Figure 15-2 goes to the view DBA_DATA_FILES to determine the name of the file in which the extents were allocated and the name of the tablespace to which the datafile belongs. To identify the table’s tablespace, you can also query DBA_SEGMENTS.

An extent consists of a set of consecutively numbered blocks. Each block will have a header area and a data area. The header is of variable size and grows downward, if necessary, from the top of the block. Among other things, it contains a row directory that lists where in the block each row begins and row locking information. The data area fills from the bottom up. Between the two there may (or may not) be an area of free space. Events that will cause a block’s header to grow include inserting and locking rows. The data area will initially be empty and will fill as rows are inserted (or index keys are inserted, in the case of a block of an index segment). The free space does get fragmented as rows are inserted, deleted, and updated (which may cause a row’s size to change), but that is of no significance because all this happens in memory, after the block has been copied into a buffer in the database buffer cache. The free space is coalesced into a contiguous area, when necessary, and always before the DBWn writes the block back to its datafile.

Exercise 15-1: Investigate Storage Structures    In this exercise, you will run various queries to determine storage characteristics. Follow these steps:

1.  Determine the physical structures of your database.

2.  Create a table and determine where it is stored.

What size are the extents? By default, this is the size that will be used for the first 16 extents of a segment, after which extents of 128 blocks will be allocated.

3.  Move the table.

Where are the extents now?

4.  Tidy up.

Create and Manage Tablespaces

Tablespaces are repositories for schema data, including the data dictionary. All databases must have a SYSTEM tablespace and a SYSAUX tablespace, as well as (for practical purposes) a temporary tablespace and an undo tablespace. These four will usually have been created when the database was created. Subsequently, the DBA may create many more tablespaces for user data and possibly additional tablespaces for undo and temporary data.

Creating Tablespaces

To create a tablespace with Database Express, from the database home page click the Storage tab and then the Tablespaces link. Figure 15-3 shows the result for the default database.

Figure 15-3    The tablespaces in the default General Purpose template database

There are six tablespaces shown in the figure. For each tablespace, identified by name, the window shows the following:

•  Size    This is the current size of the datafile (or datafiles) assigned to the tablespace. It is based on the current size, not the maximum size to which it may be allowed to expand.

•  Free space    The space currently available within the tablespace.

•  Used (%)    This is the space occupied by segments in the tablespace that cannot be reclaimed.

•  Auto Extend    Indicates whether any files of the tablespace have the automatic extension facility enabled (they all do).

•  Maximum size    If autoextensible, to what limit? In the figure, all are Unlimited, meaning the maximum possible.

•  Status    Green indicates that the tablespace is online and therefore that the objects within it should be accessible. An offline tablespace would be indicated in red.

•  Type    An icon indicating whether the tablespace stores permanent objects, temporary objects, or undo segments.

•  Auto segment management    A check mark indicates that automatic segment space management is used by the tablespace.

You could also glean this information by querying the data dictionary views DBA_TABLESPACES, DBA_DATA_FILES, DBA_TEMP_FILES, and DBA_FREE_SPACE.

In Database Express, click the Actions button for a drop-down menu including Create to create a tablespace. The Create Tablespace window prompts for a tablespace name as well as the type of data (permanent, temporary, or undo) to be stored within it. The dialog continues with prompts for the datafile (or datafiles) definition and various attributes.

Database Express has a SHOW SQL button in most windows. This means you can use it to construct syntactically correct statements that you can save (and edit) for future, scripted use. This is a typical statement generated by Database Express (line numbers added):

Look at this command line by line.

Figure 15-4 shows a typical tablespace creation statement, as executed from the SQL*Plus command line, with a query confirming the result.

Figure 15-4    Tablespace creation and verification with SQL*Plus

The tablespace GLTABS consists of two datafiles, neither of which will autoextend. The only deviation from defaults has been to specify a uniform extent size of 4MB. The first query in the figure shows that the tablespace is not a bigfile tablespace; if it were, it would not have been possible to define two datafiles.

The second query in the figure investigates the TEMP tablespace, used by the database for storing temporary objects. It is important to note that temporary tablespaces use tempfiles, not datafiles. Tempfiles are listed in the views V$TEMPFILE and DBA_TEMP_FILES, whereas datafiles are listed in V$DATAFILE and DBA_DATA_FILES. Also note that the V$ views and the DBA views give different information. As the query shows, you can query V$TABLESPACE to find if a tablespace is a bigfile tablespace, and you can query V$TEMPFILE (or V$DATAFILE) to find how big a file was at creation time. This information is not shown in the DBA views. However, the DBA views give the detail of extent management and segment space management. The different information available in the views is because some information is stored only in the controlfile (and therefore visible only in V$ views) and some is stored only in the data dictionary (and therefore visible only in DBA views). Other information is duplicated.

Exercise 15-2: Create, Alter, and Drop Tablespaces    In this exercise, you will create tablespaces and change their characteristics. Follow these steps:

1.  Connect to the database as user SYSTEM.

2.  Create a tablespace in a suitable directory—any directory on which the Oracle owner has write permission will do. This is an example:

This command specifies the options that are the default. Nonetheless, it may be considered good practice to do this to make the statement self-documenting.

3.  Create a table in the new tablespace and determine the size of the first extent.

4.  Add extents manually and observe the size of each new extent by repeatedly executing the command

followed by the query from step 3. Note the point at which the extent size increases.

5.  Take the tablespace offline, observe the effect, and bring it back online.

6.  Make the tablespace read-only, observe the effect, and make it read-write again.

7.  Tidy up by issuing the following command:

Altering Tablespaces

These are the changes commonly made to tablespaces after creation:

•  Renaming

•  Taking online and offline

•  Flagging as read-write or read-only

•  Resizing

Rename a Tablespace and Its Datafiles

The syntax is as follows:

This is simple but can cause problems later. Many sites rely on naming conventions to relate tablespaces to their datafiles. All the examples in this chapter do just that; they embed the name of the tablespace in the name of the datafiles. Oracle doesn’t care. Internally, it maintains the relationships by using the tablespace number and the datafile (or tempfile) number. These are visible as the columns V$TABLESPACE.TS# and V$DATAFILE.FILE#. If your site does rely on naming conventions, it will be necessary to rename the files as well.

A tablespace can be renamed while it is in use, but to rename a datafile, it must be offline. This is because the file must be renamed at the operating system level, as well as within the Oracle environment, and this can’t be done if the file is open. All the file handles would become invalid. It is, however, possible to move a datafile online.

Figure 15-5 shows an example of the whole process, using the GLTBS tablespace created in Figure 15-4.

Figure 15-5    Renaming a tablespace and its datafiles

In the figure, the first command renames the tablespace. Then the tablespace is taken offline (as described in the next section), and an operating system command renames one of the datafiles in the file system. An ALTER DATABASE command changes the filename as recorded within the controlfile so that Oracle will be able to find it. Finally, the tablespace is brought back online. The last command shows the alternative approach; it physically moves the other file and renames it (both at the file system level and within the controlfile) in one operation with zero downtime.

Taking a Tablespace Online or Offline

An online tablespace or datafile is available for use; an offline tablespace or datafile exists as a definition in the data dictionary and the controlfile but cannot be used. It is possible for a tablespace to be online but one or more of its datafiles to be offline. This is a situation that can produce interesting results and should generally be avoided. Here is the syntax for taking a tablespace offline:

A NORMAL offline (which is the default) will force a checkpoint for all the tablespace’s datafiles. Every dirty buffer in the database buffer cache that contains a block from the tablespace will be written to its datafile, and then the tablespace and the datafiles are taken offline.

At the other extreme is IMMEDIATE, which offlines the tablespace and the datafiles immediately, without flushing any dirty buffers. Following this, the datafiles will be corrupted (they may be missing committed changes) and will have to be recovered by applying change vectors from the redo log before the tablespace can be brought back online. Clearly, this is a drastic operation. It would normally be done only if a file has become damaged so that the checkpoint cannot be completed. The process of recovery is detailed in Chapter 22. You cannot take a tablespace offline using IMMEDIATE unless media recovery through archive logging has been enabled.

A TEMPORARY offline will checkpoint all the files that can be checkpointed and then take them and the tablespace offline in an orderly fashion. Any damaged files will be offlined immediately. If just some of the tablespace’s datafiles have been damaged, this will limit the number of files that will need to be recovered.

Use the following command to bring the tablespace back online:

Mark a Tablespace as Read-Only

To see the effect of making a tablespace read-only, study Figure 15-6.

Figure 15-6    Operations on a read-only tablespace

The syntax is completely self-explanatory.

After a tablespace is made read-only, none of the objects within it can be changed with Data Manipulation Language (DML) statements, as demonstrated in the figure. But they can be dropped. This is a little disconcerting but makes perfect sense when you think it through. Dropping a table doesn’t actually affect the table. It is a transaction against the data dictionary that deletes the rows that describe the table and its columns; the data dictionary is in the SYSTEM tablespace, and that is not read-only.

Resizing a Tablespace

You can resize a tablespace either by adding datafiles to it or by adjusting the size of the existing datafiles. You can resize datafiles upward automatically as necessary if the AUTOEXTEND syntax was used at file creation time. Otherwise, you have to do it manually with an ALTER DATABASE command.

The M, G, and T refer to the units of size for the file (megabytes, gigabytes, and terabytes, respectively). This is an example:

From the syntax, you do not know whether the file is being made larger or smaller. An upward resize can succeed only if there is enough space in the file system, and a resize downward can succeed only if the space in the file is not already in use by extents allocated to a segment.

To add another datafile of 2GB in size to a tablespace, use the following command:

You can include clauses for automatic extension when creating the file. To enable automatic extension later, use a command such as this:

This will allow the file to double in size, increasing 100MB at a time.

A bigfile tablespace can be resized at the tablespace level, rather than the datafile level. This is because Oracle knows that the tablespace can have only one datafile, and therefore there is no ambiguity about which datafile should be resized. Figure 15-7 shows how to create and resize a bigfile tablespace.

Figure 15-7    Working with bigfile tablespaces

You might think that limiting a tablespace to one file only would limit the storage capacity of the tablespace, but this is not the case. A smallfile tablespace can consist of up to 1,022 datafiles, each of which can be up to 4 million blocks. A bigfile tablespace can be one datafile of up to 4 billion blocks, so there is really no difference.

Dropping Tablespaces

To drop a tablespace, use the DROP TABLESPACE command. The syntax is as follows:

If the INCLUDING CONTENTS keywords are not specified, the drop will fail if there are any objects in the tablespace. Using these keywords instructs Oracle to drop the objects first and then to drop the tablespace. Even this will fail in some circumstances, such as when the tablespace contains a table that is the parent in a foreign key relationship with a table in another tablespace.

If the AND DATAFILES keywords are not specified, the tablespace and its contents will be dropped, but the datafiles will continue to exist on disk. Oracle will know nothing about them anymore, and they will have to be deleted with operating system commands.

Extent Management

The extent management method is set per tablespace and applies to all segments in the tablespace. There are two techniques for managing extent usage: dictionary management and local management. The difference is clear: Local management should always be used; dictionary management should never be used. Dictionary-managed extent management is still supported, but it is just a holdover from previous releases.

Dictionary extent management uses two tables in the data dictionary. SYS.UET$ has rows describing used extents, and SYS.FET$ has rows describing free extents. Every time the database needs to allocate an extent to a segment, it must search FET$ to find an appropriate bit of free space and then carry out DML operations against FET$ and UET$ to allocate it to the segment. This mechanism causes problems with performance because all space management operations in the database (many of which could be initiated concurrently) must serialize on the code that constructs the transactions.

Local extent management was introduced with release 8i and became the default with release 9i. It uses bitmaps stored in each datafile. Each bit in the bitmap covers a range of blocks, and when space is allocated, the appropriate bits are changed from zero to one. This mechanism is far more efficient than the transaction-based mechanism of dictionary management. The cost of assigning extents is amortized across bitmaps in every datafile that can be updated concurrently, rather than being concentrated (and serialized) on the two tables.

When creating a locally managed tablespace, an important option is uniform size. If uniform is specified, then every extent ever allocated in the tablespace will be that size. This can make the space management highly efficient because the block ranges covered by each bit can be larger, namely, only one bit per extent. Consider this statement:

Every extent allocated in this tablespace will be 160MB, so there will be about 64 of them. The bitmap needs only 64 bits, and 160MB of space can be allocated by updating just one bit. This is going to be very efficient—provided that the segments in the tablespace are large. If a segment were created that needed space for only a few rows, it would still get an extent of 160MB. Small objects need their own tablespace.

Here is the alternative (and default) syntax:

When segments are created in this tablespace, Oracle will allocate an eight-block (64KB) extent. As a segment grows and requires more extents, Oracle will allocate extents of this size up to 16 extents, after which it will allocate 128-block (1MB) extents. Thus, fast-growing segments will tend to be given space in larger chunks.

It is possible that if a database has been upgraded from previous versions, it will include dictionary-managed tablespaces. Check this with the following query:

Any dictionary-managed tablespaces should be converted to local management with this PL/SQL procedure call:

Segment Space Management

The segment space management method is set per tablespace and applies to all segments in the tablespace. There are two techniques for managing segment space usage: manual and automatic. The difference is clear: Automatic management should always be used; manual management should never be used. Manual segment space management is still supported but never recommended. It is a remnant from previous releases.

Automatic segment space management was introduced with release 9i and became the default in release 11g. Every segment created in an automatic management tablespace has a set of bitmaps that describe how full each block is. There are five bitmaps for each segment, and each block will appear on exactly one bitmap. The bitmaps track the space used in bands; there is a bitmap for full blocks, and there are bitmaps for blocks that are 75 percent to 100 percent used, 50 percent to 75 percent used, 25 percent to 50 percent used, and 0 percent to 25 percent used. When searching for a block into which to insert a row, the session server process will look at the size of the row to determine which bitmap to search. For instance, if the block size is 4KB and the row to be inserted is 1,500 bytes, an appropriate block will be found by searching the “25 percent to 50 percent” bitmap. Every block on this bitmap is guaranteed to have at least 2 KB of free space. As rows are inserted, are deleted, or change size through updates, the bitmaps get updated accordingly.

The old manual space management method used a simple list, known as the freelist, that stated which blocks were available for insert but without any information on how full they were. This method could cause excessive activity because blocks had to be tested for space at insert time, and it often resulted in a large proportion of wasted space.

To see whether any tablespaces are using manual management, run this query:

The only tablespaces that should be returned by this query are SYSTEM, undo tablespaces, and temporary tablespaces. The segments in these tablespaces are managed by Oracle, which does not require assistance from any automatic mechanism. It is not possible to convert tablespaces from manual to automatic segment space management. The only way is to create a new tablespace using automatic segment space management, move the segments into it (at which point the bitmap will be generated), and drop the old tablespaces.

Oracle Managed Files

Using Oracle Managed Files (OMF) is intended to remove the necessity for the DBA to have any knowledge of the file systems. The creation of database files can be fully automated. To enable OMF, set some or all of these instance parameters:

The DB_CREATE_FILE_DEST parameter specifies a default location for all datafiles and online redo log files. The DB_CREATE_ONLINE_LOG_DEST_n parameters specify a default location for online redo log files, taking precedence over DB_CREATE_FILE_DEST. DB_RECOVERY_FILE_DEST sets up a default location for archive redo log files and Recovery Manager (RMAN) backup files; this will be discussed in later chapters on backup and recovery. As well as setting default file locations, OMF will generate filenames and (by default) set the file sizes. Setting these parameters can greatly simplify file-related operations. Once OMF has been enabled, it can always be overridden by specifying a datafile name on the CREATE TABLESPACE command.

Figure 15-8 shows an example of enabling and using OMF.

Figure 15-8    Using Oracle Managed Files

In the figure, first the parameter is set. Note that the parameter is dynamic; you can change it at any time, and all OMF files created subsequently will be in the new location. Existing OMF files are not affected by any such change. Then the tablespace OMFTS is created, relying completely on the OMF defaults. The query shows what these are.

•  File name    Generated with a leading string (o1_mf), followed by the name of the tablespace, followed by eight random characters (to generate a unique name), and then a suffix (.dbf)

•  File size    100MB

•  Autoextend    Enabled in units of 12,800 blocks (100MB), up to 32GB

The final example in the figure shows adding an OMF file to an existing tablespace, overriding the OMF default for the file size.

The combination of OMF, bigfile tablespaces, and autoextend can make space management in an Oracle database extremely easy and completely automatic.

Exercise 15-3: Change Tablespace Characteristics    In this exercise, you will create a tablespace using the nondefault manual space management to simulate the need to convert to automatic segment space management after an upgrade. Enable OMF first to ease the process. Follow these steps:

1.  Enable OMF for datafile creation. Choose any directory that exists and on which the Oracle user has read-write permissions. This is an example on Linux and then Windows:

2.  Create a tablespace, using the minimum syntax now possible.

3.  Determine the characteristics of the OMF file.

Note the file is initially 100MB, autoextensible, with no upper limit.

4.  Adjust the OMF file to have more sensible characteristics. Use whatever system-generated filename was returned by step 3.

5.  Drop the tablespace and use an operating system command to confirm that the file has indeed gone.

6.  Create a tablespace using manual segment space management. Because OMF is enabled, there is no need for any datafile clause.

7.  Confirm that the new tablespace is indeed using the manual technique.

8.  Create a table and an index in the tablespace.

9.  These segments will be created with freelists, not bitmaps. Create a new tablespace that will (by default) use automatic segment space management.

10.  Move the objects into the new tablespace.

11.  Confirm that the objects are in the correct tablespace.

12.  Drop the original tablespace.

13.  Rename the new tablespace to the original name. This is often necessary because some application software checks the tablespace names.

14.  Tidy up by dropping the tablespace, first with this command:

15.  Note the error caused by the tablespace not being empty and fix it.

Explain How Oracle Database Server Automatically Manages Space

Space is managed at three levels: the tablespace, the segment, and the block. Once these physical structures are in place, management moves to the logical level: how space is assigned to segments and how space is used within segments.

Segment Space Assignment

Space is allocated to a segment in the form of an extent, which is a set of consecutive Oracle blocks. Every datafile has a bitmap that describes the state of the block in the file, whether it is free or part of an extent that has been assigned to a segment. When a segment fills up and needs to extend, Oracle will search the bitmaps of the files of the tablespace for free space, select one file, and create a new extent of an appropriate size by modifying the bitmap. The extent can then be assigned to the segment.

A segment is a container for an object, but the two are not the same thing. It is possible for the object to exist without a segment. When a segment is first created, it will have at least one extent—but it is possible for some objects to exist without a segment. Study the code in Figure 15-9. The first two queries show the tables (four) and indexes (two) in the currently logged-on schema, with the flag that shows whether a segment has been created for them. The third query shows the segments. The table BONUS exists logically but has no segment within which it can be contained.

Figure 15-9    Objects and segments

The key to understanding this situation is the instance parameter DEFERRED_SEGMENT_CREATION, which defaults to TRUE. If set to TRUE, this parameter instructs Oracle not to create a segment until the object actually contains some data. At table creation time, only the data dictionary structure describing the table is created. Only subsequently, when an attempt is made to insert a row into the table, is the segment created. It is possible to control this behavior and to override the parameter setting, with the SEGMENT CREATION clause of the CREATE TABLE statement. Figure 15-10 demonstrates this, showing that a segment is created consisting of one extent when a row is inserted into a table.

Figure 15-10    Deferred segment creation

Exercise 15-4: Manage Segment Space    In this exercise, you observe the allocation of extents to segments.

1.  Connect to the database as user SYSTEM.

2.  Create a schema to work in for this exercise and then connect to it.

3.  Check that the DEFERRED_SEGMENT_CREATION parameter is set to TRUE, which is the default, and set it if it is not.

4.  Create tables and indexes as follows:

5.  Determine what segments have been created.

6.  Insert a row into EX7A and EX7B.

7.  Run the queries from step 5 again.

Automatic Segment Space Management

A table segment consists of multiple blocks in one or more extents. When an attempt is made to insert a row into the table, Oracle must make a decision: Into which block should the row be placed? This is determined by a bitmap that reflects how full each block in the segment is. By inspecting the bitmap, Oracle can determine whether a block has sufficient space to accept the new row. The mapping is not precise but rather works in 25 percent ranges: between 0 and 25 percent free space, 25 to 50 percent free space, 50 to 75 percent free space, and 75 to 100 percent free space. In addition, the bitmap shows whether the block is not actually formatted at all (that is, it is assigned to the segment but has not yet been used) or is classed as full and is therefore not available for inserts no matter how small the new row is. As rows are inserted into blocks and deleted from blocks, the status of the block (in terms of to which of the 25 percent bands it belongs) is adjusted in the bitmap.

A problem related to the selection of a block for an insert is how to manage the situation where a row changes size. Some Oracle data types—principally, the VARCHAR2 data type—are of variable length. If a VARCHAR2 column is updated such that it becomes longer, the row will become longer. Furthermore, if any columns are NULL when a row is inserted, they will take no space at all. Therefore, when UPDATE statements are executed that expand a VARCHAR2 or populate a previously NULL column, the row will become bigger. It will therefore require more space in the block.

By default, Oracle reserves 10 percent of a block for row expansion. This is the PCTFREE (percent free) setting for the segment, set at segment creation time (though it can be modified later). A block whose usage has exceeded the PCTFREE setting for the segment is classed by the Automatic Segment Space Management (ASSM) bitmap as FULL, and therefore the block is not available for insert even though it may in fact still have some free space. So if, on average, the rows in a block increase by no more than 10 percent during their lifetime, there is no problem; enough space will be available for the new versions of the rows. If a row expands such that there is not sufficient space in the block, it must be moved to a block with sufficient space. This is known as row migration.

When a row is migrated, it is removed from the block in which it resides and inserted into a different block with sufficient space. The new block will be located by searching the ASSM bitmap. So in effect, when a user executes an UPDATE, this becomes an INSERT and DELETE, which is a more expensive operation. Furthermore, the ROWID of the row (the physical locator of the row) is not changed. The ROWID still points to the original block, which now stores no more than a “forwarding address” for the row; the forwarding address is the address of the block to which the row has been moved. The fact that the ROWID remains unchanged is good and bad. It is good because there is no need to adjust any indexes; they will still point to the original location. It is bad because when the row is retrieved through an index search, an extra I/O operation will be needed to read the row from its new location. This is, of course, transparent to SQL but may in extreme circumstances result in performance degradation.

Closely related to row migration is the issue of row chaining. A chained row is a row that is larger than the block. Clearly, if the block size is 8K and the row is 20K, then the row must be distributed across three blocks. At insert time, all three blocks will be located by searching the ASSM bitmap for blocks available for insertion, and when retrieving the row later, all three blocks may be read (depending on which columns are projected by the query). The ROWID of a chained row points to the first block of the row, as is also the case with a migrated row.

Save Space by Using Compression

Compression comes in various forms, some of which require a separate license: the Advanced Compression option. The primary purpose of compression is to reduce disk space requirements, but there is sometimes a fringe benefit in improved performance for subsequent queries. Compression may, however, cause performance degradation in some circumstances and should therefore be approached with caution. An advisor capability will estimate the space savings that compression can achieve for a table.

Compression comes in three forms:

•  Basic table compression compresses data within a block when rows are inserted through a direct load operation. Subsequent DML operations will cause the rows to be uncompressed (and possibly migrated as a result of this).

•  Advanced row compression will compress rows no matter how they are inserted and maintain the compression through DML. The compression is still on a block-by-block basis.

•  Hybrid Columnar Compression (HCC) restructures data into compression units of several megabytes and is available only on certain storage platforms.

Basic compression (which is the default type) is, in fact, de-duplication. If a repeating pattern of characters occurs within a block, the pattern is stored once only in a symbol table, with a reference to the symbol table stored in each row where the pattern occurs. Advanced row compression uses the same de-duplication technology. Either way, the compression is per block; the symbol tables are not usable outside of the block in which they exist. HCC is true compression, in that it uses compression algorithms to reduce the space needed to store data. HCC is not applied per block but across groups of blocks, which further enhances the achievable compression ratios.

The type of compression is determined at table creation time. Compression can be added or removed after creation, but any such change will not affect existing rows. To bring the change into effect, the table must be reorganized. Typically, this would be accomplished with an ALTER TABLE . . . MOVE statement.

The syntax to create a table with basic or advanced compression is a normal creation statement, with a suffix specifying the compression type.

Proactively Monitor and Manage Tablespace Space Usage

The database will automatically monitor tablespace usage through the server alert system. Figure 15-11 shows a query against the DBA_THRESHOLDS view.

Figure 15-11    Tablespace thresholds and usage

The first two lines of the output for Figure 15-11 show the system-generated thresholds for a temporary tablespace and the undo tablespace; tablespaces of these types are not monitored. This is because a simple check of free space is useless for tablespaces of these types because they are usually fully occupied by temporary or undo segments. What matters is whether the temporary or undo segments within them are full; this is a more complex metric that is not configured by default.

The third line of the query’s output shows the database-wide default alerts for all tablespaces that do not have an alert explicitly configured. The warning alert is set at greater than or equal to 85 percent, and the critical alert is set at greater than or equal to 97 percent.

In addition to the alert system, which will inform you of issues according to preconfigured thresholds, Oracle maintains a history of tablespace usage. This is stored in the Automatic Workload Repository (AWR), the information being gathered as part of the AWR snapshots created by the manageability monitor (MMON) process. You can see this information in the DBA_HIST_TBSPC_SPACE_USAGE view. The second query in Figure 15-11 joins this view to the V$TABLESPACE view (the join is necessary to retrieve the tablespace name) and shows the history of space usage for each tablespace. Note that there is one row per tablespace per snapshot. You can see that the snapshot frequency has been set to every 15 minutes and that (in the few lines displayed) there was no change in the usage within the EXAMPLE tablespace.

Use the Segment Advisor

The Segment Advisor attempts to generate recommendations regarding reorganizing segments to reclaim space. The issue is that over time, in some circumstances, table and index segments may become larger than is necessary for the amount of data contained within them.

An obvious example is many rows having been deleted. Deletion frees up space within the segment, but the segment itself remains the same size. This will affect the table segment and all associated index segments. In most cases, if space has ever been assigned to a segment, then even if it is not needed now, it will be needed again. For example, the process of loading data into data warehouse tables often involves inserting many rows into a staging table, processing them, and deleting them. But even though the table may be empty at the end of the day’s run, all the space will be needed again the next day.

It is therefore not enough to examine the current state of a table to determine whether it is excessively large; you must also consider the history of space usage. The Segment Advisor can do this. It considers data in the AWR as well as the current state of the objects. The recommendations are based on a sampled analysis of the object and historical information used to predict future growth trends.

The Segment Advisor runs, by default, every night as an autotask scheduled job. The autotask does not attempt to analyze every segment. It selects segments on these criteria:

•  Segments in tablespaces that have crossed a space usage threshold

•  Segments that have had the most activity

•  Segments that have the highest growth rate

To see the results of the autotask, use the DBMS_SPACE.ASA_RECOMMENDATIONS function. This function returns a table with the results of the last run. Figure 15-12 shows an example of querying the result of the Segment Advisor autotask, followed by the commands that implement its recommendations.

Figure 15-12    Retrieving and implementing the Segment Advisor’s autotask advice

Reclaim Wasted Space from Tables and Indexes by Using the Segment Shrink Functionality

When a row is deleted, the space it was occupying in its block becomes available for reuse when another row is inserted. However, the nature of the activity against a table can result in a significant amount of wasted space within the table. This could be reclaimed with a MOVE operation. Following a move, all the blocks will be consecutively full of freshly reinserted rows. But during the move, the table is locked, and following it all the indexes must be rebuilt. For many environments, this makes using MOVE to reorganize tables impossible. The SHRINK command avoids these problems. It can be run without any impact on end users. A limitation is that the table’s tablespace must have been created to use automatic segment space management. Tables in tablespaces that use the older freelist technique for managing segment space usage cannot be shrunk because (unlike the new bitmap method) the freelist does not include sufficient information for Oracle to work out how full each block actually is.

The underlying implementation of a table shrink is to relocate rows from the end of the table into blocks toward the beginning of the table, by means of matched INSERT and DELETE operations, and then, when all possible moves have been done, to bring the high water mark of the table down to the last currently used block and release all the space above this point. There are two distinct phases. The compact phase moves the rows in a series of small transactions, through normal DML that generates both undo and redo and uses row locks. The second phase is a Data Definition Language (DDL) command. As with any DDL command, this is a transaction against the data dictionary; it will execute almost instantaneously but will require a short table lock. This last step is often referred to as “relocating the high water mark (HWM) of the segment.”

The syntax of the SHRINK SPACE command is as follows:

Using the keyword COMPACT carries out the first phase but not the second; in other words, the rows are relocated, but the space is not actually released from the segment. The reason for using this is that while the compaction can occur during normal running hours (though it may take many hours to complete on a large table), it is possible that the DDL at the end will hang because of concurrency with other transactions, and it will also invalidate parsed SQL in the library cache. So, it may be necessary to shrink the table with the COMPACT keyword first and then again without COMPACT during a maintenance period. It will be fast because the compaction will have already been done. The CASCADE keyword instructs Oracle also to shrink dependent objects, such as indexes.

Before a table can be shrunk, you must enable row movement for the table.

Enabling row movement is necessary because the nature of the operation means that row IDs will be changing. The same row (no change to the primary key) will be in a different physical location and will therefore have a different row ID. This is something that Oracle will not permit unless row movement has been enabled.

Figure 15-12 shows enabling row movement for a table, followed by a SHRINK SPACE that reclaims space from both the table and its index.

Manage Resumable Space Allocation

Many operations can fail for reasons of inadequate space. This typically shows up as an inability to add another extent to a segment, which itself can have several causes. A datafile could be full, an auto-extensible datafile or tempfile could be on a disk that is full, an undo segment could be in an undo tablespace that is full, an operation requiring temporary space could be using a temporary tablespace that is full, or a user could have reached their quota limit on a tablespace. Whatever the reason, space-related errors tend to be dreadfully time consuming.

Consider an exercise to load data into a data warehouse. The first time you attempt this, it fails because the destination tablespace runs out of space. The data that did go in must be rolled back (which may take as long as the insert), the tablespace extended, and the load done again. Then it fails because of inadequate undo space; therefore, you roll back, increase the undo tablespace, and try again. Then it fails during index rebuilding because of a lack of temporary space. And so on. Exercises such as this are the bane of many DBAs’ lives. The resumable space allocation feature can be the solution.

If you enable resumable space allocation, when an operation hits a space problem (any space problem at all), rather than failing with an error (and in many cases rolling back what it did manage to do), the operation will be suspended. To the user, this will show as the session hanging. When the error condition is resolved, it will continue. All suspended sessions (currently suspended and previously suspended but now running again) are listed in the view DBA_RESUMABLE.

To enable resumable space allocation at the session level, the command is as follows:

The TIMEOUT option lets you specify for how long the statement should hang. If this time is reached without the problem being resolved, the error is returned, and the statement fails. If there is no specified TIMEOUT, the session will hang indefinitely.

It is also possible to enable resumable space for all sessions by setting an instance parameter. This is a dynamic parameter. For example, here is how to set a timeout of one minute:

This will cause all sessions that hit a space problem to be suspended for up to one minute.

There is little point in enabling resumable space allocation for a session or the instance if you don’t do anything about the problem that caused a session to be suspended. Suspended sessions will, by default, be reported through the server alert system, be displayed by Database Control, and be listed in the DBA_RESUMABLE data dictionary view. Having spotted a problem, you can fix it interactively from another session. Or you can create an AFTER SUSPEND ON DATABASE trigger, which will run whenever a session is suspended. This trigger could report the problem (perhaps by generating an e-mail), or it could include code to investigate the problem and fix it automatically.

Two-Minute Drill

Describe the Storage of Table Row Data in Blocks

•  One tablespace can span many datafiles.

•  One tablespace can have many segments.

•  One segment is one or more extents.

•  One extent is many consecutive blocks in one datafile.

•  One Oracle block should be one or more operating system blocks.

•  The Oracle block is the granularity of database I/O.

•  A SMALLFILE tablespace can have many datafiles, but a BIGFILE tablespace can have only one.

•  Tablespaces default to local extent management and automatic segment space management, but not to a uniform extent size.

•  OMF datafiles are automatically named, are initially 100MB, and can autoextend without limit.

•  A tablespace that contains segments cannot be dropped unless an INCLUDING DATAFILES clause is specified.

•  Tablespaces can be online or offline and read-write or read-only.

•  Any one tablespace can store only one type of object: permanent objects, temporary objects, or undo segments.

Create and Manage Tablespaces

•  Tablespaces can be resized by adding datafiles or by extending existing datafiles.

•  Local extent management tracks extent allocation with bitmaps in each datafile.

•  The UNIFORM SIZE clause when creating a tablespace forces all extents to be the same size.

•  The AUTOALLOCATE clause lets Oracle determine the next extent size, which is based on how many extents are being allocated to a segment.

•  Automatic segment space management tracks the free space in each block of an extent using bitmaps.

•  It is possible to convert a tablespace from dictionary extent management to local extent management but not from freelist segment management to automatic management.

Explain How Oracle Database Server Automatically Manages Space

•  Space is allocated to segments on demand in the form of extents. Extent usage is tracked by bitmaps.

•  Space usage within a segment is tracked in 25 percent bands by bitmaps.

Save Space by Using Compression

•  Basic compression de-duplicates data when inserted through a direct load.

•  Advanced row compression can maintain the de-duplication compression through conventional DML.

Proactively Monitor and Manage Tablespace Space Usage

•  The server alert system is preconfigured to raise alerts when a tablespace is 85 percent full (warning) and 97 percent full (critical).

•  Alerts are not raised for temporary or undo tablespaces.

•  Alert thresholds can be configured at any value for any tablespace individually.

Use the Segment Advisor

•  The Segment Advisor runs every night as an autotask.

•  The advice will be to shrink a table if doing so would release a significant amount of space.

•  The Segment Advisor considers historical usage as well as the current usage.

Reclaim Wasted Space from Tables and Indexes by Using the Segment Shrink Functionality

•  A table shrink operation relocates rows toward the front of the segment and (by default) releases free space at the end.

•  A shrink is an online and in-place operation; it requires no additional space while running, and the table is not locked against other DML.

Manage Resumable Space Allocation

•  Resumable space allocation can be enabled for a session or for the instance.

•  If a session hits a space error, it will hang until the problem is fixed—or until a timeout expires.

•  A database trigger can be configured to fire whenever a session is suspended.

Self Test

1.  Examine the exhibit:

The exhibit shows the Oracle storage model, with four entities having letters for names. Match four of the following entities to the letters A, B, C, and D:

A.  Datafile

B.  Extent

C.  Oracle block

D.  Row

E.  Segment

F.  Table

2.  What statements are correct about extents? (Choose all correct answers.)

A.  An extent is a grouping of several Oracle blocks.

B.  An extent is a grouping of several operating system blocks.

C.  An extent can be distributed across one or more datafiles.

D.  An extent can contain blocks from one or more segments.

E.  An extent can be assigned to only one segment.

3.  Which of these are types of segments? (Choose all correct answers.)

A.  Sequence

B.  Stored procedure

C.  Table

D.  Table partition

E.  View

4.  If a tablespace is created with the syntax

which of these characteristics will it have? (Choose all correct answers.)

A.  The datafile will autoextend, but only to double its initial size.

B.  The datafile will autoextend with MAXSIZE UNLIMITED.

C.  The extent management will be local.

D.  Segment space management will be with bitmaps.

E.  The file will be created in the DB_CREATE_FILE_DEST directory.

5.  How can a tablespace be made larger? (Choose all correct answers.)

A.  Convert it from a SMALLFILE tablespace to a BIGFILE tablespace.

B.  If it is a SMALLFILE tablespace, add files.

C.  If it is a BIGFILE tablespace, add more files.

D.  Resize the existing file (or files).

6.  Which of these commands can be executed against a table in a read-only tablespace? (Choose the best answer.)

A.  DELETE

B.  DROP

C.  INSERT

D.  TRUNCATE

E.  UPDATE

7.  What operation cannot be applied to a tablespace after creation? (Choose the best answer.)

A.  Convert from dictionary extent management to local extent management.

B.  Convert from manual segment space management to automatic segment space management.

C.  Change the name of the tablespace.

D.  Reduce the size of the datafile (or datafiles) assigned to the tablespace.

E.  All the above operations can be applied.

8.  When the database is in mount mode, what views can be queried to find what datafiles and tablespaces make up the database? (Choose all correct answers.)

A.  DBA_DATA_FILES

B.  DBA_TABLESPACES

C.  DBA_TEMP_FILES

D.  V$DATABASE

E.  V$DATAFILE

F.  V$TABLESPACE

9.  Which views could you query to find out about the temporary tablespaces and the files that make them up? (Choose all correct answers.)

A.  DBA_DATA_FILES

B.  DBA_TABLESPACES

C.  DBA_TEMP_TABLESPACES

D.  DBA_TEMP_FILES

E.  V$DATAFILE

F.  V$TABLESPACE

G.  V$TEMPTABLESPACE

H.  V$TEMPFILE

10.  Which statements are correct about extents? (Choose all correct answers.)

A.  An extent is a consecutive grouping of Oracle blocks.

B.  An extent is a random grouping of Oracle blocks.

C.  An extent can be distributed across one or more datafiles.

D.  An extent can contain blocks from one or more segments.

E.  An extent can be assigned to only one segment.

11.  Which of these are types of segments? (Choose all correct answers.)

A.  Sequence

B.  Stored procedure

C.  Table

D.  Table partition

E.  View

12.  Which form of compression uses compression algorithms rather than de-duplication algorithms? (Choose the best answer.)

A.  Compression implemented with COMPRESS BASIC.

B.  Compression implemented with ROW STORE COMPRESS ADVANCED.

C.  Hybrid columnar compression.

D.  All Oracle compression methods use compression algorithms.

E.  All Oracle compression methods use de-duplication algorithms.

13.  You receive an alert warning you that a tablespace is nearly full. What action could you take to prevent this becoming a problem, without any impact for your users? (Choose two correct answers.)

A.  Purge all recycle bin objects in the tablespace.

B.  Shrink the tables in the tablespace.

C.  Shrink the indexes in the tablespace.

D.  Move one or more tables to a different tablespace.

E.  Move one or more indexes to a different tablespace.

14.  Which process is responsible for sending the alert when a tablespace usage critical threshold is reached? (Choose the best answer.)

A.  Database Control

B.  The DBMS_SERVER_ALERT package

C.  MMON, the manageability monitor process

D.  The server process of the session that detected the problem

E.  DBWn, the database writer, when it detects the problem

15.  When will the Segment Advisor run? (Choose two correct answers.)

A.  Every night, as an autotask

B.  On demand

C.  Automatically when a tablespace crosses a threshold for space usage

D.  Automatically when a session is suspended by the resumable space allocation mechanism

16.  Which of the following commands will shrink space in a table or index segment and relocate the HWM? (Choose the best answer.)

A.  ALTER TABLE EMPLOYEES SHRINK SPACE COMPACT HWM;

B.  ALTER TABLE EMPLOYEES SHRINK SPACE HWM;

C.  ALTER TABLE EMPLOYEES SHRINK SPACE COMPACT;

D.  ALTER TABLE EMPLOYEES SHRINK SPACE CASCADE;

E.  ALTER TABLE EMPLOYEES SHRINK SPACE;

17.  What is required before shrinking a table? (Choose all that apply.)

A.  Triggers must be disabled.

B.  Indexes must be dropped.

C.  Row movement must be enabled.

D.  Automatic segment space management must be enabled.

E.  LOB columns must be dropped.

18.  How can you enable the suspension and resumption of statements that hit space errors? (Choose all correct answers.)

A.  Issue an ALTER SESSION ENABLE RESUMABLE command.

B.  Issue an ALTER SYSTEM ENABLE RESUMABLE command.

C.  Set the instance parameter RESUMABLE_STATEMENTS.

D.  Set the instance parameter RESUMABLE_TIMEOUT.

E.  Use the DBMS_RESUMABLE.ENABLE procedure.

19.  If a statement is suspended because of a space error, what will happen when the problem is fixed? (Choose the best answer.)

A.  After the resumable timeout has expired, the statement will continue executing from the point it had reached.

B.  After the resumable timeout has expired, the statement will start executing from the beginning again.

C.  The statement will start executing from the beginning immediately after the problem is fixed.

D.  The statement will continue executing from the point it had reached immediately after the problem is fixed.

Self Test Answers

1.      A is SEGMENT, B is EXTENT, C is ORACLE BLOCK, and D is DATAFILE.
    Neither ROW nor TABLE is included in the model.

2.      A and E. One extent is several consecutive Oracle blocks, and one segment consists of one or more extents.
    B, C, and D are incorrect. They misinterpret the Oracle storage model.

3.      C and D. A table can be a type of segment, as can a table partition (in which case the table itself will not be a segment).
    A, B, and E are incorrect. They exist only as objects defined within the data dictionary. The data dictionary itself is a set of segments.

4.      C and D. Local extent management and automatic segment space management are enabled by default.
    A, B, and E are incorrect. A and B are incorrect because, by default, autoextension is disabled. E is incorrect because providing a filename will override the OMF mechanism.

5.      B and D. A smallfile tablespace can have many files, and all datafiles can be resized upward.
    A and C are incorrect. A is incorrect because you cannot convert between SMALLFILE and BIGFILE. C is incorrect because a BIGFILE tablespace can have only one file.

6.      B. Objects can be dropped from read-only tablespaces.
    A, C, D, and E are incorrect. All these commands will fail because they require writing to the table, unlike a DROP, which writes only to the data dictionary.

7.      B. It is not possible to change the segment space management method after creation.
    A, C, D, and E are incorrect. A and C are incorrect because a tablespace can be converted to local extent management or renamed at any time. D is incorrect because a datafile can be resized downward, although only if the space to be freed up has not already been used. E is incorrect because you cannot change the segment space management without re-creating the tablespace.

8.      E and F. Joining these views will give the necessary information.
    A, B, C, and D are incorrect. A, B, and C are incorrect because these views will not be available in mount mode. D is incorrect because there is no relevant information in V$DATABASE.

9.      B, D, F, and H. V$TABLESPACE and DBA_TABLESPACE will list the temporary tablespaces, and V$TEMPFILE and DBA_TEMP_FILES will list their files.
    A, C, E, and G are incorrect. A and E are incorrect because V$DATAFILE and DBA_DATA_FILES do not include tempfiles. C and G are incorrect because there are no views with these names.

10.      A and E. One extent is several consecutive Oracle blocks, and one segment consists of one or more extents.
    B, C, and D are incorrect. They misinterpret the Oracle storage model.

11.      C and D. A table can be a type of segment, and so can a table partition.
    A, B, and E are incorrect. These exist only as objects defined within the data dictionary. The data dictionary itself is a set of segments.

12.      C. Hybrid columnar compression is true compression.
    A, B, D, and E are incorrect. A and B are incorrect because both BASIC and ADVANCED compression are, in fact, based on de-duplication. D and E are incorrect because Oracle can use either technique.

13.      A and B. Both purging dropped objects and shrinking tables will release space immediately, with no downtime.
    C, D, and E are incorrect. An index can be shrunk, but this will release space within the index, not return it to the tablespace. Relocating either indexes or tables has implications for the availability of the data.

14.      C. The MMON background process raises alerts.
    A, B, D, and E are incorrect. A is incorrect because although Database Control reports alerts, it does not raise them. B is incorrect because the DBMS_SERVER_ALERT API is used to configure the alert system—it does not implement it. D and E are incorrect because foreground and background processes will encounter problems, not warn of their imminence.

15.      A and B. Unless the autotask has been disabled, it will run in every maintenance window. It can also be invoked on demand.
    C and D are incorrect. C is incorrect because although a tablespace usage alert will cause the autotask to analyze all objects in the tablespace in the next maintenance window, this does not happen when the alert is raised. D is incorrect because the only action triggered by suspension of a session is running the AFTER SUSPEND ON DATABASE trigger.

16.      D. SHRINK SPACE both compacts the data and moves the HWM. While the HWM is being moved, DML operations on the table are blocked.
    A, B, C, and E are incorrect. A, B, and E are syntactically incorrect. C is incorrect because COMPACT only performs the shrink operation but does not move the HWM after shrinking the segment.

17.      C and D. Row movement is necessary because a shrink will change row IDs. ASSM is needed to give the necessary information on how full a block is.
    A, B, and E are incorrect. A is incorrect because triggers will not fire for the operation; they will continue to fire for any other DML. B is incorrect because indexes are maintained during a shrink. E is incorrect because LOB segments will not block a table shrink. They will be shrunk themselves if CASCADE is specified.

18.      A and D. These are the only two methods to enable resumable space allocation.
    B, C, and E are incorrect. B and C are incorrect because resumable space allocation is enabled at the system level with the instance parameter RESUMABLE_TIMEOUT. E is incorrect because although there is a package called DBMS_RESUMABLE, it does not (rather annoyingly) include a procedure to enable resumable space allocation.

19.      D. As “suspended” implies, the statement will continue from the point at which it stopped.
    A, B, and C are incorrect. The timeout controls how long the suspension can last before returning an error; it is the period during which the problem can be fixed.