C H A P T E R  4

Tablespaces and Datafiles

As you saw in Chapter 2, when you create a database, typically five tablespaces are created when you execute the CREATE DATABASE statement:

• SYSTEM
• SYSAUX
• UNDO
• TEMP
• USERS

A tablespace is a logical container that allows you to manage groups of datafiles. A tablespace has one more datafiles associated with it. A datafile can be associated with only one tablespace. In other words, a datafile can't be shared between two tablespaces. A datafile is the physical file that is created on disk. The datafile stores data for database objects such as tables and indexes. Figure 4–1 shows the logical constructs used to manage space in a database.

Figure 4–1. Relationships of logical storage objects and physical datafiles

The previously listed five tablespaces—SYSTEM, SYSAUX, UNDO, TEMP, and USERS—are the minimal set of storage containers you need to operate a database. As you open a database for use, you should quickly create additional tablespaces to store application data. This chapter discusses the purpose of the standard set of tablespaces, the need for additional tablespaces, and how to manage these critical database storage containers.

Understanding the First Five

The SYSTEM tablespace provides storage for the Oracle data-dictionary objects. This tablespace is where all objects owned by the SYS user are stored. The SYS user should be the only user that owns objects created in the SYSTEM tablespace.

Starting with Oracle Database 10g, the SYSAUX (system auxiliary) tablespace is created when you create the database. This is an auxiliary tablespace used as a data repository for Oracle database tools such as Enterprise Manager, Statspack, LogMiner, Logical Standby, and so on.

The UNDO tablespace stores the information required to roll back uncommitted data. This tablespace contains information about data as it existed before an insert, update, or delete statement (this is sometimes referred to as a before image copy of the data). This information is used to roll back uncommitted data in the event of a crash recovery and to provide read consistency for SQL statements.

Some Oracle SQL statements require a sort area, either in memory or on disk. For example, the results of a query may need to be sorted before being returned to the user. Oracle first uses memory to sort the query results; and when there isn't sufficient room in memory, the TEMP tablespace is used as a sorting area on disk. When you create a database, typically you create the TEMP tablespace and specify it to be the default temporary tablespace for any users you create.

The USERS tablespace is often used as a default permanent tablespace for table and index data for users. As shown in Chapter 2, you can create a default permanent tablespace for users when you create the database.

Understanding the Need for More

Although you could put every database user's data in the USERS tablespace, this usually isn't scalable or maintainable for any type of serious database application. Instead, it's more efficient to create additional tablespaces for application users. You typically create at least two tablespaces specific for each application using the database: one for the application table data and one for the application index data. For example, for the APP user, you can create tablespaces named APP_DATA and APP_INDEX for table and index data, respectively.

DBAs used to separate table and index data for performance reasons. The thinking was that separating table data from index data would reduce I/O contention. This is because each tablespace and associated datafiles could be placed on different disks with separate controllers.

With modern storage configurations that have multiple layers of abstraction between the application and the underlying physical storage devices, it's debatable whether you can realize any performance gains by creating multiple separate tablespaces. But there still are valid reasons for creating multiple tablespaces for table and index data:

• Backup and recovery requirements may be different for the tables and indexes.
• Indexes may have different storage requirements than the table data.

In addition to separate tablespaces for data and indexes, you sometimes create separate tablespaces for objects of different sizes. For example, if an application has very large tables, you can create an APP_DATA_LARGE tablespace that has a large extent size, and a separate APP_DATA_SMALL tablespace that has a smaller extent size.

Depending on your requirements, you should consider creating separate tablespaces for each application using the database. For example, for an inventory application, create INV_DATA and INV_INDEX; and for an HR application, create HR_DATA and HR_INDEX. Here are some reasons to consider creating separate tablespaces for each application using the database:

• Applications may have different availability requirements. Separate tablespaces let you take tablespaces offline for one application while not affecting another application.
• Applications may have different backup and recovery requirements. Separate tablespaces let tablespaces be backed up and recovered independently.
• Applications may have different storage requirements. Separate tablespaces allow for different settings for extent sizes and segment management.
• You may have some data that is purely read only. Separate tablespaces let you put a tablespace that contains only read-only data into read-only mode.

This chapter focuses on the most common and critical tasks associated with creating and maintaining tablespaces and datafiles. The next section discusses creating tablespaces, and the chapter progresses to more advanced topics such as moving and renaming datafiles.

Creating Tablespaces

You use the CREATE TABLESPACE statement to create tablespaces. The Oracle SQL reference manual contains more than 12 pages of syntax and examples for creating tablespaces. In most scenarios, you need to use only a few of the features available, namely locally managed extent allocation, and automatic segment space management. The following code snippet demonstrates how to create a tablespace that utilizes the most common features:

You need to modify this script for your environment. For example, the directory path, datafile size, and uniform extent size should be changed per environment requirements.

You create tablespaces as locally managed by using the EXTENT MANAGEMENT LOCAL clause. A locally managed tablespace uses a bitmap in the datafile to efficiently determine whether an extent is in use. The storage parameters NEXT, PCTINCREASE, MINEXTENTS, MAXEXTENTS, and DEFAULT aren't valid for extent options in locally managed tablespaces.

As you add data to objects in tablespaces, Oracle automatically allocates more extents to an associated tablespace datafile as needed to accommodate the growth. You can instruct Oracle to allocate a uniform size for each extent via the UNIFORM SIZE [size] clause. If you don't specify a size, then the default uniform extent size is 1MB.

The uniform extent size that you use varies depending on the storage requirements of your tables and indexes. I usually create several tablespaces for a given application. For example, you can create one tablespace for small objects that has a uniform extent size of 512KB, one tablespace for medium-sized objects that has a uniform extent size of 4MB, one tablespace for large objects with a uniform extent size of 16MB, and so on.

Alternatively, you can specify that Oracle determine the extent size via the AUTOALLOCATE clause. Oracle allocates extent sizes of 64KB, 1MB, 8MB, or 64MB. Using AUTOALLOCATE is appropriate when you think objects in one tablespace will be of varying sizes.

The SEGMENT SPACE MANAGEMENT AUTO clause instructs Oracle to manage the space within the block. When you use this clause, there is no need to specify parameters such as PCTUSED, FREELISTS, and FREELIST GROUPS. The alternative to AUTO space management is MANUAL. When you use MANUAL, you can adjust the previously mentioned parameters depending on the needs of your application. I recommend that you use AUTO and not MANUAL. Using AUTO vastly reduces that number of parameters you'd otherwise need to configure and manage.

When a datafile fills up, you can instruct Oracle to automatically increase the size of the datafile with the AUTOEXTEND feature. I recommend that you don't use this feature. Instead, you should monitor tablespace growth and add space as necessary. Manually adding space is preferable to having a runaway SQL process that accidentally grows a tablespace until it has consumed all space on a mount point. If you inadvertently fill up a mount point that contains a control file or the Oracle binaries, you can hang your database.

If you do use the AUTOEXTEND feature, I recommend that you always specify a corresponding MAXSIZE so that a runaway SQL process doesn't accidentally fill up a tablespace that in turn fills up a mount point.

Here's an example of creating an autoextending tablespace with a cap on its maximum size:

When you're using CREATE TABLESPACE scripts in different environments, it's useful to be able to parameterize portions of the script. For example, in development, you may size the datafiles at 100MB, whereas in production the datafiles may be 1000GB. Use ampersand & variables to make CREATE TABLESPACE scripts more portable among environments.

The next listing defines ampersand variables at the top of the script, and those variables determine the sizes of datafiles created for the tablespaces:

Using ampersand variables allows you to modify the script once and have the variables reused throughout the script. You can parameterize all aspects of the script, including datafile mount points and extent sizes.

You can also pass the values of the ampersand variables in to the CREATE TABLESPACE script from the SQL*Plus command line. This lets you avoid hard-coding a specific size in the script and instead provide the sizes at runtime. To accomplish this, first define at the top of the script the ampersand variables to accept the values being passed in:

Now you can pass variables in to the script from the SQL*Plus command line. The following example executes a script named cretbsp.sql and passes in two values that set the ampersand variables to 5G and 500M, respectively:

Table 4–1 summarizes the best practices for creating and managing tablespaces.

Table 4–1. Best Practices for Managing Tablespaces

Best Practice	Reasoning
Create separate tablespaces for different applications using the same database.	If a tablespace needs to be taken offline, it affects only one application.
For an application, separate table data from index data in different tablespaces.	Table and index data may have different storage requirements.
Don't use the AUTOALLOCATE feature for datafiles. If you do use AUTOALLOCATE, specify a maximum size.	Specifying a maximum size prevents a runaway SQL statement from filling up a storage device.
Create tablespaces as locally managed. You shouldn't create a tablespace as dictionary managed.	This provides better performance and manageability.
For a tablespace's datafile naming convention, use a name that contains the tablespace name followed by a two-digit number that's unique within datafiles for that tablespace.	Doing this makes it easy to identify which datafiles are associated with which tablespaces.
Try to minimize the number of datafiles associated with a tablespace.	You have fewer datafiles to manage.
In tablespace CREATE scripts, use ampersand variables to define aspects such as storage characteristics.	This makes scripts more reusable among various environments.

Renaming a Tablespace

Sometimes you need to rename a tablespace. You may want to do this because a tablespace was initially erroneously named, or you may want the tablespace name to better conform to your database naming standards. Use the ALTER TABLESPACE statement to rename a tablespace. This example renames a tablespace from FOOBAR to USERS:

When you rename a tablespace, Oracle updates the name of the tablespace in the data dictionary, control files, and data headers. Keep in mind that renaming a tablespace doesn't rename any associated datafiles. Renaming datafiles is covered later in this chapter.

Controlling the Generation of Redo

For some types of applications, you may know beforehand that you can easily re-create the data. An example might be a data-warehouse environment where you perform direct path inserts or use SQL*Loader to load data. In these scenarios, you can turn off the generation of redo for direct path loading. You use the NOLOGGING clause to do this:

If you have an existing tablespace and want to alter its logging mode, use the ALTER TABLESPACE statement:

You can confirm the tablespace logging mode by querying the DBA_TABLESPACES view:

The generation of redo logging can't be suppressed for regular INSERT, UPDATE, and DELETE statements. For regular Data Manipulation Language (DML) statements, the NOLOGGING clause is ignored. The NOLOGGING clause does apply, however, to the following types of DML:

• Direct path INSERT statements
• Direct path SQL*Loader

The NOLOGGING clause also applies to the following types of DDL statements:

• CREATE TABLE … AS SELECT
• ALTER TABLE … MOVE
• ALTER TABLE … ADD/MERGE/SPLIT/MOVE/MODIFY PARTITION
• CREATE INDEX
• ALTER INDEX … REBUILD
• CREATE MATERIALIZED VIEW
• ALTER MATERIALIZED VIEW … MOVE
• CREATE MATERIALIZED VIEW LOG
• ALTER MATERIALIZED VIEW LOG … MOVE

Be aware that if redo isn't logged for a table or index, and you have a media failure before the object is backed up, then you can't recover the data. You receive an ORA-01578 error indicating that there is logical corruption of the data.

Changing a Tablespace's Write Mode

In environments such as data warehouses, you may need to load data into tables and then never modify the data again. To enforce that all objects in a tablespace can't be modified, you can alter the tablespace to be read-only. To do this, use the ALTER TABLESPACE statement:

One advantage of a read-only tablespace is that you only have to back it up once. You should be able to restore the datafiles from a read-only tablespace no matter how long ago the backup was made.

If you need to modify the tablespace out of read-only mode, you do so as follows:

Make sure you re-enable backups of a tablespace after you place it in read/write mode.

In Oracle Database 11g and above, you can modify individual tables to be read-only. For example:

While in read-only mode, you can't issue any insert, update, or delete statements against the table. Making individual tables read/write can be advantageous when you're doing maintenance (such as a data migration) when you want to ensure that users don't update the data.

This example modifies a table back to read/write mode:

Dropping a Tablespace

If you have a tablespace that is unused, it's best to drop it so it doesn't clutter your database, consume unnecessary resources, and potentially confuse DBAs who aren't familiar with the database. Before dropping a tablespace, it's a good practice to first take it offline:

You may want to wait to see if anybody screams that an application is broken because it can't write to a table or index in the tablespace to be dropped. When you're sure the tablespace isn't required, drop the tablespace and delete its datafiles:

Dropping a tablespace using INCLUDING CONTENTS AND DATAFILES permanently removes the tablespace and any of its datafiles. Make certain the tablespace doesn't contain any data you want to keep before you drop it.

If you attempt to drop a tablespace that contains a primary key that is referenced by a foreign key associated with a table in a different tablespace (than the one you're trying to drop), you receive this error:

Run this query first to determine whether any foreign-key constraints will be affected:

If there are referenced constraints, you need to first drop the constraints or use the CASCADE CONSTRAINTS clause of the DROP TABLESPACE statement. This statement uses CASCADE CONSTRAINTS to automatically drop any affected constraints:

This statement drops any referential-integrity constraints from tables outside the tablespace being dropped that reference tables within the dropped tablespace.

If you drop a tablespace that has required objects in a production system, the results can be catastrophic. You must perform some sort of recovery to get the tablespace and its objects back. Needless to say, be very careful when dropping a tablespace. Table 4–2 lists recommendations to consider when you do this.

Table 4–2. Best Practices for Dropping Tablespaces

Best Practice	Reasoning
Before dropping a tablespace, run a script similar to this to determine if any objects exist in the tablespace: select owner, segment_name, segment_type from dba_segments where tablespace_name=upper('&&tbsp_name'),	Doing this ensures that no tables or indexes exist in the tablespace before you drop it.
Consider renaming tables in a tablespace before you drop the tablespace.	If any applications are using tables within the tablespace to be dropped, the application throws an error when a required table is renamed.
If there are no objects in the tablespace, resize the associated datafiles to a very small number like 10MB.	Reducing the size of the datafiles to a miniscule amount of space quickly shows whether any applications are trying to access objects that require space in a tablespace.
Make a backup of your database before dropping a tablespace.	This ensures that you have a way to recover objects that are discovered to be in use after you drop the tablespace.
Take the tablespace and datafiles offline before you drop the tablespace. Use the ALTER TABLESPACE statement to take the tablespace offline.	This helps determine if any applications or users are using objects in the tablespaces. They can't access the objects if the tablespace and datafiles are offline.
When you're sure a tablespace isn't in use, use the DROP TABLESPACE … INCLUDING CONTENTS AND DATAFILES statement.	This removes the tablespace and physically removes any datafiles associated with the tablespace. Some DBAs don't like this approach, but you should be fine if you've taken the necessary precautions.

Using Oracle Managed Files

The Oracle Managed File (OMF) feature automates many aspects of tablespace management, such as file placement, naming, and sizing. You control OMF by setting the following initialization parameters:

• DB_CREATE_FILE_DEST
• DB_CREATE_ONLINE_LOG_DEST_N
• DB_RECOVERY_FILE_DEST

If you set these parameters before you create the database, Oracle uses them for the placement of the datafiles, control files, and online redo logs. You can also enable OMF after your database has been created. Oracle uses the values of the initialization parameters for the locations of any newly added datafiles and online redo-log files. Oracle also determines the name of the newly added file.

The advantage of using OMF is that creating tablespaces is simplified. For example, the CREATE TABLESPACE statement doesn't need to specify anything other than the tablespace name. First, enable the OMF feature by setting the DB_CREATE_FILE_DEST parameter:

Now, issue the CREATE TABLESPACE statement:

This statement creates a tablespace named INV1 with a default datafile size of 100MB. You can override the default by specifying a size:

One limitation of OMF is that you're limited to one directory for the placement of datafiles. If you want to add datafiles to a different directory, you can alter the location dynamically:

Although this procedure isn't a huge deal, I find it easier not to use OMF. Most of the environments I've worked in have many mount points assigned for database use. You don't want to have to modify an initialization parameter every time you need a datafile added to a directory that isn't in the current definition of DB_CREATE_FILE_DEST. It's easier to issue a CREATE TABLESPACE statement or ALTER TABLESPACE statement that has the file-location and storage parameters in the script. It isn't cumbersome to provide directory names and filenames to the tablespace-management statements.

Creating a Bigfile Tablespace

The bigfile feature allows you to create a tablespace with a potentially very large datafile assigned to it. The advantage of using the bigfile feature is that you can create very large files. With an 8KB block size, you can create a datafile as large as 32TB. With a 32KB blocksize, you can create a datafile up to 128TB.

Use the BIGFILE clause to create a bigfile tablespace:

As long as you have plenty of space associated with the filesystem supporting the bigfile tablespace datafile, you can store massive amounts of data in a tablespace.

One potential disadvantage of using a bigfile tablespace is that if for any reason you run out of space on a filesystem that supports the datafile associated with the bigfile, you can't expand the size of the tablespace (unless you can add space to the filesystem). You can't add more datafiles to a bigfile tablespace if they're placed on separate mount points. A bigfile tablespace allows only one datafile to be associated with it.

You can make the bigfile tablespace the default type of tablespace for a database using the ALTER DATABASE SET DEFAULT BIGFILE TABLESPACE statement. However, I don't recommend doing that. You could potentially create a tablespace, not know it was a bigfile tablespace (because you forgot it was the default, or you're a new DBA on the project and didn't realize it), and create a tablespace on a mount point. Then, when you discovered that you needed more space, you wouldn't know that you couldn't add another datafile on a different mount point for this tablespace because it was bigfile constrained.

Displaying Tablespace Size

DBAs often use monitoring scripts to alert them when they need to increase the space allocated to a tablespace. The following script displays the percentage of free space left in a tablespace and datafile:

If you don't have any monitoring in place, you're alerted via the SQL statement that is attempting to perform an insert or update operation that requires more space but isn't able to allocate more. For example:

After you determine that a tablespace needs more space, you need to either increase the size of a datafile or add a datafile to a tablespace. These topics are discussed in the next section.

Altering Tablespace Size

When you've determined which datafile you want to resize, first make sure you have enough disk space to increase the size of the datafile on the mount point on which the datafile exists:

Use the ALTER DATABASE DATAFILE … RESIZE command to increase the datafile's size. This example resizes the datafile to 5GB:

If you don't have space on an existing mount point to increase the size of a datafile, then you must add a datafile. To add a datafile to an existing tablespace, use the ALTER TABLESPACE … ADD DATAFILE statement:

If you have bigfile tablespaces, then you can't use the ALTER DATABASE … DATAFILE statement to change the size of a tablespace's datafile. To resize the single datafile associated with a bigfile tablespace, you must use the ALTER TABLESPACE clause:

Resizing datafiles can be a daily task when you're managing databases with heavy transaction loads. Increasing the size of an existing datafile allows you to add space to a tablespace without adding more datafiles. If there isn't enough disk space left on the storage device that contains an existing datafile, you can add a datafile in a different location to an existing tablespace.

If you want to add space to a temporary tablespace, first query the V$TEMPFILE view to verify the current size and location of temporary datafiles:

Next, use the TEMPFILE option of the ALTER DATABASE statement:

You can also add a file to a temporary tablespace via the ALTER TABLESPACE statement:

Toggling Datafiles Offline and Online

Sometimes, when you're performing maintenance operations (such as renaming datafiles), you may need to first take a datafile offline. You can use either the ALTER TABLESPACE or the ALTER DATABASE DATAFILE statement to toggle datafiles offline and online.

Use the ALTER TABLESPACE … OFFLINE NORMAL statement to take a tablespace and its associated datafiles offline. You don't need to specify NORMAL, because it's the default:

When you place a tablespace offline in normal mode, Oracle checkpoints the datafiles associated with the tablespace. This ensures that all modified blocks in memory that are associated with the tablespace are flushed and written to the datafiles. You don't need to perform media recovery when you bring the tablespace and its associated datafiles back online.

You can't use the ALTER TABLESPACE statement to place tablespaces offline when the database is in mount mode. If you attempt to take a tablespace offline while the database is mounted (but not open), you receive the following error:

When taking a tablespace offline, you can also specify ALTER TABLESPACE … OFFLINE TEMPORARY. In this scenario, Oracle checkpoints all datafiles associated with the tablespace that are online. Oracle doesn't checkpoint offline datafiles associated with the tablespace.

You can specify ALTER TABLESPACE … OFFLINE IMMEDIATE when taking a tablespace offline. Your database must be in archivelog mode in this situation. When using OFFLINE IMMEDIATE, Oracle doesn't checkpoint the datafiles. You must perform media recovery on the tablespace before bringing it back online.

You can also use the ALTER DATABASE DATAFILE statement to take a datafile offline. If your database is open for use, then it must be in archivelog mode in order for you to take a datafile offline with the ALTER DATABASE DATAFILE statement. If you attempt to take a datafile offline using the ALTER DATABASE DATAFILE statement, and your database isn't in archivelog mode, you receive the following error:

If your database isn't in archivelog mode, you must specify ALTER DATABASE DATAFILE … OFFLINE FOR DROP when taking a datafile offline. You can specify the entire filename or provide the file number. In this example, datafile 6 is taken offline:

Now, if you attempt to online the offline datafile, you receive the following error:

When you use the OFFLINE FOR DROP clause, no checkpoint is taken on the datafile. This means you need to perform media recovery on the datafile before bringing it online. Performing media recovery applies any changes to the datafile that are recorded in the online redo logs that aren't in the datafiles themselves. Before you can bring online a datafile that was taken offline with the OFFLINE FOR DROP clause, you must perform media recovery on it. You can specify either the entire filename or the file number:

If the redo information that Oracle needs is contained in the online redo logs, you should see this message:

If your database isn't in archivelog mode, and if Oracle needs redo information not contained in the online redo logs to recover the datafile, then you can't recover the datafile and place it back online.

If your database is in archivelog mode, you can take it offline without the FOR DROP clause. In this scenario, Oracle ignores the FOR DROP clause. Even when your database is in archivelog mode, you need to perform media recovery on a datafile that has been taken offline with the ALTER DATABASE DATAFILE statement. Table 4–3 summarizes the options you must consider when taking a tablespace offline.

Table 4–3. Options for Taking a Datafile Offline

Statement	Archivelog Mode Required?	Media Recovery Required When Toggling Online?	Works in Mount Mode?
ALTER TABLESPACE … OFFLINE NORMAL	No	No	No
ALTER TABLESPACE … OFFLINE TEMPORARY	No	Maybe: depends on whether any datafiles already have offline status	No
ALTER TABLESPACE … OFFLINE IMMEDIATE	No	Yes	No
ALTER DATABASE DATAFILE … OFFLINE	Yes	Yes	Yes
ALTER DATABASE DATAFILE … OFFLINE FOR DROP	No	Yes	Yes

Renaming or Relocating a Datafile

You may occasionally need to rename a datafile. For example, you may need to move datafiles due to changes in the storage devices or move files that were somehow created in the wrong location.

Before you rename a datafile, you must take the datafile offline. (See the previous section.) Here are the steps for renaming a datafile:

1. Use the following query to determine the names of existing datafiles:
   SQL> select name from v$datafile;
2. Take the datafile offline using either the ALTER TABLESPACE or ALTER DATABASE DATAFILE statement (see the previous section for details on how to do this). You can also shut down your database and then start it in mount mode; the datafiles can be moved while in this mode because they aren't open for use.
3. Physically move the datafile to the new location using either an OS command (like mv or cp) or the COPY_FILE procedure of the DBMS_FILE_TRANSFER built-in PL/SQL package.
4. Use either the ALTER TABLESPACE … RENAME DATAFILE … TO statement or the ALTER DATABASE RENAME FILE … TO statement to update the control file with the new datafile name.
5. Alter the datafile online.

The following example demonstrates how to move the datafiles associated with a single tablespace. First, take the datafiles offline with the ALTER TABLESPACE statement:

Now, from the operating system prompt, move two datafiles to a new location using the Linux/Unix mv command:

Update the control file with the ALTER TABLESPACE statement:

Finally, bring the datafiles within the tablespace back online:

If you want to rename datafiles from multiple tablespaces in one operation, you can use the ALTER DATABASE RENAME FILE statement (instead of the ALTER TABLESPACE…RENAME DATAFILE statement). The following example renames all datafiles in the database. Because the SYSTEM and UNDO tablespaces' datafiles are being moved, you must take the database offline first and then place it in mount mode:

Because the database is in mount mode, the datafiles aren't open for use, and thus there is no need to take the datafiles offline. Next, physically move the files via the Linux/Unix mv command:

Now you can update the control file to be aware of the new filename:

You should be able to open your database:

Another way you can relocate all datafiles in a database is to re-create the control file with the CREATE CONTROLFILE statement. The steps for this operation are as follows:

1. Create trace file that contains a CREATE CONTROLFILE statement.
2. Locate the trace file that contains the CREATE CONTROLFILE statement.
3. Modify the trace file to display the new location of the datafiles.
4. Shut down the database.
5. Physically move the datafiles using an operating system command.
6. Start the database in nomount mode.
7. Run the CREATE CONTROLFILE command.

The following example walks through the previous steps. First, you write a CREATE CONTROLFILE statement to a trace file via an ALTER DATABASE BACKUP CONTROLFILE TO TRACE statement:

This statement uses the NORESETLOGS clause. It instructs Oracle to write only one SQL statement to the trace file. If you don't specify NORESETLOGS, Oracle writes two SQL statements to the trace file: one to re-create the control file with the NORESETLOGS option, and one to re-create the control file with RESETLOGS. Normally, you know whether you want to reset the online redo logs as part of re-creating the control file. In this case, you know that you don't need to reset the online redo logs when you re-create the control file (because the online redo logs haven't been damaged and are still in the normal location for the database).

Now, locate the directory that contains the trace files for your database:

For this example, the directory is

Next, navigate to the trace directory on the operating system:

Look for the last trace file that was generated (or one that was generated at the same time you ran the ALTER DATABASE statement). In this example, the trace file is O11R2_ora_17017.trc.

Make a copy of the trace file, and open the copy with an operating system editor:

Edit the mv.sql file. In this example, the trace file contains only one SQL statement (because I specified NORESTLOGS when creating the trace file). If you don't specify NORESETLOGS, the trace file contains two CREATE CONTROLFILE statements, and you must modify the trace file to remove the statement that contains the RESETLOGS as part of the statement.

Next, modify the names of the datafiles to the new locations where you want to move the datafiles. Here is a CREATE CONTROLFILE statement for this example:

Now, shut down the database:

Physically move the files from the operating system prompt. This example uses the Linux/Unix mv command to move the files:

Start up the database in nomount mode:

And execute the file that contains the CREATE CONTROLFILE statement (in this example, mv.sql):

If the statement is successful, you see the following message:

Finally, alter your database open:

USING ORADEBUG TO DISPLAY THE TRACE FILE'S NAME

Summary

This chapter discussed managing tablespace and datafiles. Tablespaces are logical containers for a group of datafiles. Datafiles are the physical files on disk that contain data. You should plan carefully when creating tablespaces and the corresponding datafiles.

Tablespaces allow you to separate the data of one application from another. You can also separate tables from indexes. These allow you to customize storage characteristics of the tablespace for each application. Furthermore, tablespaces provide a way to better manage applications that have different availability and backup and recovery requirements. As a DBA, you must be proficient in managing tablespaces and datafiles. In any type of environment, you have to add, rename, relocate, and drop these storage containers.

Oracle requires three types of files for a database to operate: datafiles, control files, and online redo-log files. The next chapter in the book focuses on control file and online redo-log file management.