The term tablespace is something of a misnomer, in that it’s not just a space for tables. Rather, a tablespace is a logical container that allows you to manage groups of data files, the physical files on disk that consume space. Once a tablespace is created, you can then create Oracle database objects (tables and indexes) within tablespaces, which results in space allocated on disk in the associated data files.
A tablespace is logical in the sense that it is only visible through data dictionary views (such as DBA_TABLESPACES); you manage tablespaces through SQL*Plus or graphical tools (such as Enterprise Manager), or both. Tablespaces only exist while the Oracle database 12C is up and running.
Data files can also be viewed through data dictionary views (such as DBA_DATA_FILES) but additionally have a physical presence, as they can be viewed outside the database through OS utilities (such as ls). Data files persist whether the database is open or closed.
Oracle databases typically contain several tablespaces. A tablespace can have one or more data files associated with it, but a data file can be associated with only one tablespace. In other words, a data file can’t be shared between two (or more) tablespaces.
Objects (such as tables and indexes) are owned by users and created within tablespaces. An object is logically instantiated as a segment. A segment consists of extents of space within the tablespace. An extent consists of a set of database blocks. Figure 1 shows the relationships between these logical and physical constructs used to manage space within an Oracle database 12C.
When you create a database, typically five tablespaces are created when you execute the CREATE DATABASE statement: SYSTEM, SYSAUX, UNDO, TEMP, and USERS.
These five tablespaces are the minimal set of storage containers you need to operate a database (one could argue, however, you don’t need the USERS tablespace; more on that in the next section). As you open a Oracle database 12C for use, you should quickly create additional tablespaces for storing application data. This article discusses the purpose of the standard set of tablespaces, the need for additional tablespaces, and how to manage these critical database storage containers. The blog focuses on the most common and critical tasks associated with creating and maintaining tablespaces and data files, progressing to more advanced topics, such as moving and renaming data files.
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 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 undo the effects of a transaction (insert, update, delete, or merge). This information is required in the event a transaction is purposely rolled back (via a ROLLBACK statement). The undo information is also used by Oracle to recover from unexpected instance crashes and to provide read consistency for SQL statements. Additionally, some database features, such as Flashback Query, use the undo information.
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 is no longer sufficient memory, the TEMP tablespace is used as a sorting area on disk. Extra temporary storage may also be required when creating or rebuilding indexes. 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 not absolutely required but is often used as a default permanent tablespace for table and index data for users. This means that when a user attempts to create a table or index, if no tablespace is specified during object creation, by default the object is created in the default permanent tablespace.
Understanding the Need for More
Although you could put every Oracle database 12C 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 to 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 input/output (I/O) contention. This is because the data files (for each tablespace) could be placed on different disks, with separate controllers.
With modern storage configurations, which 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.
- The indexes may have storage requirements different from those of the table data.
- You may be using BLOB and CLOB data types, which typically have considerably different sizing requirements than non-LOB data. Therefore DBAs tend to separate LOB data in its own tablespace(s).
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; for a human resources 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 lets you take tablespaces offline for one application without affecting another application.
- Applications may have different backup and recovery requirements. Separate tablespaces lets tablespaces be backed up and recovered independently.
- Applications may have different storage requirements. Separate tablespaces allows for different settings for space quotas, extent sizes, and segment management.
- You may have some data that is purely read-only. Separate tablespaces lets you put a tablespace that contains only read-only data into read-only mode.
The next section discusses creating tablespaces.
Creating Oracle 12C Tablespaces
You use the CREATE TABLESPACE statement to create tablespaces. The Oracle SQL Reference Manual contains more than a dozen 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 employs the most common features:
create tablespace tools datafile '/u01/dbfile/O12C/tools01.dbf' size 100m segment space management auto;
You need to modify this script for your environment. By default, tablespaces will be created as locally managed. A locally managed tablespace uses a bitmap in the data file 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.
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 parameters to the requirements of your application. I recommend that you use AUTO and not MANUAL. Using AUTO vastly reduces the number of parameters you need to configure and manage.
As you create tables and indexes within a tablespace, Oracle will automatically allocate space to segments within a tablespace’s datafile as required. The default type of allocation is to automatically allocate space. You can explicitly instruct Oracle to use automatic allocation 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 (which is often the case). I usually use the default of allowing Oracle to automatically determine the extent sizes.
The alternative to AUTOALLOCATE is uniform extent sizes. 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. In some scenarios, I create several tablespaces for a given application. For instance, you can create a tablespace for small objects that has a uniform extent size of 512KB, a tablespace for medium-sized objects that has a uniform extent size of 4MB, a tablespace for large objects with a uniform extent size of 16MB, and so on.
When a data file fills up, you can instruct Oracle to increase the size of the data file automatically, 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 the 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 Oracle database 12C.
If you do use the AUTOEXTEND feature, I suggest 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 is an example of creating an autoextending tablespace with a cap on its maximum size:
create tablespace tools datafile '/u01/dbfile/O12C/tools01.dbf' size 100m autoextend on maxsize 1000m segment space management auto;
When you’re using CREATE TABLESPACE scripts in different environments, it’s useful to be able to parameterize portions of the script. For instance, in development you may size the data files at 100MB, whereas in production the data files may be 100GB. 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 data files created for the tablespaces:
define tbsp_large=5G define tbsp_med=500M -- create tablespace reg_data datafile '/u01/dbfile/O12C/reg_data01.dbf' size &&tbsp_large segment space management auto; -- create tablespace reg_index datafile '/u01/dbfile/O12C/reg_index01.dbf' size &&tbsp_med segment space management auto;
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 data file 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:
define tbsp_large=&1 define tbsp_med=&2 -- create tablespace reg_data datafile '/u01/dbfile/O12C/reg_data01.dbf' size &&tbsp_large segment space management auto; -- create tablespace reg_index datafile '/u01/dbfile/O12C/reg_index01.dbf' size &&tbsp_med segment space management auto;
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:
SQL> @cretbsp 5G 500M
Table 1 summarizes the best practices for creating and managing tablespaces.
If you ever need to verify the SQL required to re-create an existing tablespace, you can do so with the DBMS_METADATA package. First, set the LONG variable to a large value:
SQL> set long 1000000
Next, use the DBMS_METADATA package to display the CREATE TABLESPACE data definition language (DDL) for all tablespaces within the database:
SQL> select dbms_metadata.get_ddl('TABLESPACE',tablespace_name) from dba_tablespaces;
Tip You can also use Data Pump to extract the DDL of database objects.
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 Oracle database 12C naming standards. Use the ALTER TABLESPACE statement to rename a tablespace. This example renames a tablespace from TOOLS to TOOLS_DEV:
SQL> alter tablespace tools rename to tools_dev;
When you rename a tablespace, Oracle updates the name of the tablespace in the data dictionary, control files, and data file headers. Keep in mind that renaming a tablespace doesn’t rename any associated data files. See the section “Renaming or Relocating a Data File” later in this blog, for information on renaming data files.
Note You can’t rename the SYSTEM tablespace or the SYSAUX tablespace.
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 in which 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:
create tablespace inv_mgmt_data datafile '/u01/dbfile/O12C/inv_mgmt_data01.dbf' size 100m segment space management auto nologging;
If you have an existing tablespace and want to alter its logging mode, use the ALTER TABLESPACE statement:
SQL> alter tablespace inv_mgmt_data nologging;
You can confirm the tablespace logging mode by querying the DBA_TABLESPACES view:
SQL> select tablespace_name, logging from dba_tablespaces;
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 (NOLOGGING only affects the initial create, not subsequent regular DML, statements against the table)
- 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.
Note You can also override the tablespace level of logging at the object level. For example, even if a tablespace is specified as NOLOGGING, you can create a table with the LOGGING clause.
In environments such as data warehouses, you may need to load data into tables and then never modify the data again. To enforce that no objects in a tablespace can be modified, you can alter the tablespace to be read-only. To do this, use the ALTER TABLESPACE statement:
SQL> alter tablespace inv_mgmt_rep read only;
One advantage of a read-only tablespace is that you only have to back it up once. You should be able to restore the data files 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:
SQL> alter tablespace inv_mgmt_rep read write;
Make sure you reenable backups of a tablespace after you place it in read/write mode.
Note You can’t make a tablespace that contains active rollback segments read-only. For this reason, the SYSTEM tablespace can’t be made read-only, because it contains the SYSTEM rollback segment.
Be aware that in Oracle 11g and above, you can modify individual tables to be read-only. This allows you to control the read-only at a much more granular level (than at the tablespace level); for example,
SQL> alter table my_tab read only;
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) and you want to ensure that users don’t update the data.
This example modifies a table back to read/write mode:
SQL> alter table my_tab read write;
Dropping a Tablespace
If you have a tablespace that is unused, it’s best to drop it so it doesn’t clutter your Oracle database 12C, 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:
SQL> alter tablespace inv_data 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 it, and delete its data files:
SQL> drop tablespace inv_data including contents and datafiles;
Tip You can drop a tablespace whether it’s online or offline. The exception to this is the SYSTEM tablespace, which can’t be dropped. It’s always a good idea to take a tablespace offline before you drop it. By doing so, you can better determine if an application is using any objects in the tablespace. If you attempt to query a table in an offline tablespace, you receive this error: ORA-00376: file can’t be read at this time.
Dropping a tablespace using INCLUDING CONTENTS AND DATAFILES permanently removes the tablespace and any of its data files. 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 tablespace different from the one you’re trying to drop, you receive this error:
ORA-02449: unique/primary keys in table referenced by foreign keys
Run this query first to determine whether any foreign key constraints will be affected:
select p.owner, p.table_name, p.constraint_name, f.table_name referencing_table, f.constraint_name foreign_key_name, f.status fk_status from dba_constraints p, dba_constraints f, dba_tables t where p.constraint_name = f.r_constraint_name and f.constraint_type = 'R' and p.table_name = t.table_name and t.tablespace_name = UPPER('&tablespace_name') order by 1,2,3,4,5;
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 drop any affected constraints automatically:
SQL> drop tablespace inv_data including contents and data files cascade 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 2-3 lists recommendations to consider when you do this.
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:
If you set these parameters before you create the Oracle database 12C, Oracle uses them for the placement of the data files, 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 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:
SQL> alter system set db_create_file_dest='/u01';
Now, issue the CREATE TABLESPACE statement:
SQL> create tablespace inv1;
This statement creates a tablespace named INV1, with a default data file size of 100MB. Keep in mind that you can override the default size of 100MB by specifying a size:
SQL> create tablespace inv2 datafile size 20m;
To view the details of the associated data files, query the V$DATAFILE view, and note that Oracle has created subdirectories beneath the /u01 directory and named the file with the OMF format:
SQL> select name from v$datafile where name like '%inv%'; NAME ----------------------------------------------- /u01/O12C/datafile/o1_mf_inv1_8b5l63q6_.dbf /u01/O12C/datafile/o1_mf_inv2_8b5lflfc_.dbf
One limitation of OMF is that you’re limited to one directory for the placement of data files. If you want to add data files to a different directory, you can alter the location dynamically:
SQL> alter system set db_create_file_dest='/u02';
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 data file 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 file names to the tablespace-management statements.
Creating a Bigfile Tablespace
The bigfile feature allows you to create a tablespace with a very large data file assigned to it. The advantage of using the bigfile feature is this potential to create very large files. With an 8KB block size, you can create a data file as large as 32TB. With a 32KB block size, you can create a data file up to 128TB.
Use the BIGFILE clause to create a bigfile tablespace:
create bigfile tablespace inv_big_data datafile '/u01/dbfile/O12C/inv_big_data01.dbf' size 10g segment space management auto;
As long as you have plenty of space associated with the filesystem supporting the bigfile tablespace data file, 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 data file 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 data files to a bigfile tablespace if they’re placed on separate mount points. A bigfile tablespace allows only one data file 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 knowing it was a bigfile tablespace (because you forgot it was the default or because 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 data file on a different mount point for this tablespace.
Enabling Default Table Compression within a Tablespace
When working with large databases, you may want to consider compressing the data. Compressed data results in less disk space, less memory, and fewer I/O operations. Queries reading compressed data potentially execute faster because fewer blocks are required to satisfy the result of the query. But, compression does have a cost; it requires more CPU resources, as the data are compressed and uncompressed while reading and writing.
When creating a tablespace, you can enable data compression features. Doing so doesn’t compress the tablespace. Rather, any tables you create within the tablespace inherit the compression characteristics of the tablespace. This example creates a tablespace with ROW STORE COMPRESS ADVANCED:
CREATE TABLESPACE tools_comp DATAFILE '/u01/dbfile/O12C/tools_comp01.dbf' SIZE 100m SEGMENT SPACE MANAGEMENT AUTO DEFAULT ROW STORE COMPRESS ADVANCED;
Note If you’re using Oracle 11g, then use the COMPRESS FOR OLTP clause instead of ROW STORE COMPRESS ADVANCED.
Now when a table is created within this tablespace, it will automatically be created with the ROW STORE COMPRESS ADVANCED feature. You can verify the compression characteristics of a tablespace via this query:
SQL> select tablespace_name, def_tab_compression, compress_for from dba_tablespaces;
If a tablespace is already created, you can alter its compression characters, as follows:
SQL> alter tablespace tools_comp default row store compress advanced;
Here’s an example that alters a tablespace’s default compress to BASIC:
SQL> alter tablespace tools_comp default compress basic;
You can disable tablespace compression via the NOCOMPRESS clause:
SQL> alter tablespace tools_comp default nocompress;
Note Most compression features require the Enterprise Edition and the Advanced Compression option.
DBAs often use monitoring scripts to alert them when they need to increase the space allocated to a tablespace. Depending on whether or not you’re in a pluggable database environment, your SQL for determining space usage will vary. For a regular Oracle database 12C (nonpluggable), you can use the regular DBA-level views to determine space usage. The following script displays the percentage of free space left in a tablespace and data file:
SET PAGESIZE 100 LINES 132 ECHO OFF VERIFY OFF FEEDB OFF SPACE 1 TRIMSP ON COMPUTE SUM OF a_byt t_byt f_byt ON REPORT BREAK ON REPORT ON tablespace_name ON pf COL tablespace_name FOR A17 TRU HEAD 'Tablespace|Name' COL file_name FOR A40 TRU HEAD 'Filename' COL a_byt FOR 9,990.999 HEAD 'Allocated|GB' COL t_byt FOR 9,990.999 HEAD 'Current|Used GB' COL f_byt FOR 9,990.999 HEAD 'Current|Free GB' COL pct_free FOR 990.0 HEAD 'File %|Free' COL pf FOR 990.0 HEAD 'Tbsp %|Free' COL seq NOPRINT DEFINE b_div=1073741824 -- SELECT 1 seq, b.tablespace_name, nvl(x.fs,0)/y.ap*100 pf, b.file_name file_name, b.bytes/&&b_div a_byt, NVL((b.bytes-SUM(f.bytes))/&&b_div,b.bytes/&&b_div) t_byt, NVL(SUM(f.bytes)/&&b_div,0) f_byt, NVL(SUM(f.bytes)/b.bytes*100,0) pct_free FROM dba_free_space f, dba_data_files b ,(SELECT y.tablespace_name, SUM(y.bytes) fs FROM dba_free_space y GROUP BY y.tablespace_name) x ,(SELECT x.tablespace_name, SUM(x.bytes) ap FROM dba_data_files x GROUP BY x.tablespace_name) y WHERE f.file_id(+) = b.file_id AND x.tablespace_name(+) = y.tablespace_name and y.tablespace_name = b.tablespace_name AND f.tablespace_name(+) = b.tablespace_name GROUP BY b.tablespace_name, nvl(x.fs,0)/y.ap*100, b.file_name, b.bytes UNION SELECT 2 seq, tablespace_name, j.bf/k.bb*100 pf, b.name file_name, b.bytes/&&b_div a_byt, a.bytes_used/&&b_div t_byt, a.bytes_free/&&b_div f_byt, a.bytes_free/b.bytes*100 pct_free FROM v$temp_space_header a, v$tempfile b ,(SELECT SUM(bytes_free) bf FROM v$temp_space_header) j ,(SELECT SUM(bytes) bb FROM v$tempfile) k WHERE a.file_id = b.file# ORDER BY 1,2,4,3;
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 the tablespace requires more space but isn’t able to allocate more. At that point, an ORA-01653 error is thrown, indicating the object can’t extend.
After you determine that a tablespace needs more space, you need to either increase the size of a data file or add a data file to the tablespace. See the section “Altering Tablespace Size,” later in this article, for a discussion of these topics.
DISPLAYING ORACLE ERROR MESSAGES AND ACTIONS
You can use the oerr utility to quickly display the cause of an error and simple instructions on what actions to take; for example,
$ oerr ora 01653
Here is the output for this example:
01653, 00000, "unable to extend table %s.%s by %s in tablespace %s"
// *Cause: Failed to allocate an extent of the required number of blocks for
// a table segment in the tablespace indicated.
// *Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more
// files to the tablespace indicated.
The oerr utility’s output gives you a fast and easy way to triage problems. If the information provided isn't enough, then Google is a good second option.
Altering Tablespace Size
$ df -h | sort
Use the ALTER DATABASE DATAFILE ... RESIZE command to increase the data file’s size. This example resizes the data file to 1GB:
SQL> alter database datafile '/u01/dbfile/O12C/users01.dbf' resize 1g;
If you don’t have space on an existing mount point to increase the size of a data file, then you must add a data file. To add a data file to an existing tablespace, use the ALTER TABLESPACE ... ADD DATAFILE statement:
SQL> alter tablespace users add datafile '/u02/dbfile/O12C/users02.dbf' size 100m;
With bigfile tablespaces, you have the option of using the ALTER TABLESPACE statement to resize the data file. This works because only one data file can be associated with a bigfile tablespace:
SQL> alter tablespace inv_big_data resize 1P;
Resizing data files can be a daily task when you’re managing databases with heavy transaction loads. Increasing the size of an existing data file allows you to add space to a tablespace without adding more data files. If there isn’t enough disk space left on the storage device that contains an existing data file, you can add a data file in a different location to an existing tablespace.
To add space to a temporary tablespace, first query the V$TEMPFILE view to verify the current size and location of temporary data files:
SQL> select name, bytes from v$tempfile;
Then, use the TEMPFILE option of the ALTER DATABASE statement:
SQL> alter database tempfile '/u01/dbfile/O12C/temp01.dbf' resize 500m;
You can also add a file to a temporary tablespace via the ALTER TABLESPACE statement:
SQL> alter tablespace temp add tempfile '/u01/dbfile/O12C/temp02.dbf' size 5000m;
Sometimes, when you’re performing maintenance operations (such as renaming data files), you may need to first take a data file offline. You can use either the ALTER TABLESPACE or the ALTER DATABASE DATAFILE statement to toggle data files offline and online.
Tip As of Oracle 12c, you can move and rename data files while they are online and open for use. See “Renaming or Relocating a Data File” later in this article, for a discussion of this.
Use the ALTER TABLESPACE ... OFFLINE NORMAL statement to take a tablespace and its associated data files offline. You don’t need to specify NORMAL, because it’s the default:
SQL> alter tablespace users offline;
When you place a tablespace offline in normal mode, Oracle performs a checkpoint on the data files associated with the tablespace. This ensures that all modified blocks in memory that are associated with the tablespace are flushed and written to the data files. You don’t need to perform media recovery when you bring the tablespace and its associated data files back online.
You can’t use the ALTER TABLESPACE statement to place tablespaces offline when the Oracle 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:
ORA-01109: database not open
Note When in mount mode, you must use the ALTER DATABASE DATAFILE statement to take a data file offline.
When taking a tablespace offline, you can also specify ALTER TABLESPACE ... OFFLINE TEMPORARY. In this scenario, Oracle initiates a checkpoint on all data files associated with the tablespace that are online. Oracle doesn’t initiate a checkpoint on offline data files 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, or the following error is thrown:
ORA-01145: offline immediate disallowed unless media recovery enabled
Note You can’t take the SYSTEM or UNDO tablespace offline while the database is open.
You can also use the ALTER DATABASE DATAFILE statement to take a data file offline. If your database is open for use, then it must be in archivelog mode in order for you to take a data file offline with the ALTER DATABASE DATAFILE statement. If you attempt to take a data file offline using the ALTER DATABASE DATAFILE statement, and your database isn’t in archivelog mode, the ORA-01145 error is thrown.
If your database isn’t in archivelog mode, you must specify ALTER DATABASE DATAFILE ... OFFLINE FOR DROP when taking a data file offline. You can specify the entire file name or provide the file number. In this example, data file 4 is taken offline:
SQL> alter database datafile 4 offline for drop;
Now, if you attempt to bring online the offline data file, you receive the following error:
SQL> alter database datafile 4 online; ORA-01113: file 4 needs media recovery
When you use the OFFLINE FOR DROP clause, no checkpoint is taken on the data file. This means you need to perform media recovery on the data file before bringing it online. Performing media recovery applies any changes to the data file that are recorded in the online redo logs that aren’t in the data files themselves. Before you can bring online a data file that was taken offline with the OFFLINE FOR DROP clause, you must perform media recovery on it. You can specify either the entire file name or the file number:
SQL> recover datafile 4;
If the redo information that Oracle needs is contained in the online redo logs, you should see this message:
Media recovery complete.
If your database isn’t in archivelog mode, and if Oracle needs redo information not contained in the online redo logs to recover the data file, then you can’t recover the data file 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 overlooks the FOR DROP clause. Even when your database is in archivelog mode, you need to perform media recovery on a data file that has been taken offline with the ALTER DATABASE DATAFILE statement. Table 3 summarizes the options you must consider when taking a tablespace/data files offline.
Note While the database is in mount mode (and not open), you can use the ALTER DATABASE DATAFILE command to take any data file offline, including SYSTEM and UNDO.
You may occasionally need to move or rename a data file. For example, you may need to move data files because of changes in the storage devices or because the files were created in the wrong location or with a nonstandard name. As of Oracle 12c, you have the option of renaming or moving data files, or both, while they are online. Otherwise, you will have to take data files offline for maintenance operations.
Starting with Oracle 12c is the ALTER DATABASE MOVE DATAFILE command. This command allows you to rename or move data files without any downtime. This vastly simplifies the task of moving or renaming a data file, as there is no need to manually place data files offline/online and use OS commands to physically move the files. This once manually intensive (and error-prone) operation has now been simplified to a single SQL command.
A data file must be online for the online move or rename to work. Here is an example of renaming an online data file:
SQL> alter database move datafile '/u01/dbfile/O12C/users01.dbf' to '/u01/dbfile/O12C/users_dev01.dbf';
Here is an example of moving a data file to a new mount point:
SQL> alter database move datafile '/u01/dbfile/O12C/system01.dbf' to '/u02/dbfile/O12C/system01.dbf';
You can also specify the data file number when renaming or moving a data file; for example,
SQL> alter database move datafile 2 to '/u02/dbfile/O12C/sysuax01.dbf';
In the previous example, you are specifying that data file 2 be moved.
If you’re moving a data file and, for any reason, want to keep a copy of the original file, you can use the KEEP option:
SQL> alter database move datafile 4 to '/u02/dbfile/O12C/users01.dbf' keep;
You can specify the REUSE clause to overwrite an existing file:
SQL> alter database move datafile 4 to '/u01/dbfile/O12C/users01.dbf' reuse;
Oracle will not allow you to overwrite (reuse) a data file that is currently being used by the database. That’s a good thing.
If you are using Oracle 11g or lower, before you rename or move a data file, you must take the data file offline. There are two somewhat different approaches to moving and renaming offline data files:
- Use a combination of SQL commands and OS commands.
- Use a combination of re-creating the control file and OS commands.
These two techniques are discussed in the next two sections.
Here are the steps for renaming a data file using SQL commands and OS commands:
- Use the following query to determine the names of existing data files:
SQL> select name from v$datafile;
- Take the data file offline, using either the ALTER TABLESPACE or ALTER DATABASE DATAFILE statement (see the previous section, “Performing Offline Data File Operations,” for details on how to do this). You can also shut down your database and then start it in mount mode; the data files can be moved while in this mode because they aren’t open for use.
- Physically move the data file 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.
- 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 data file name.
- Alter the data file online.
Note If you need to rename data files associated with the SYSTEM or UNDO tablespace, you must shut down your database and start it in mount mode. When your database is in mount mode, you can rename these data files via the ALTER DATABASE RENAME FILE statement.
The following example demonstrates how to move the data files associated with a single tablespace. First, take the data files offline with the ALTER TABLESPACE statement:
SQL> alter tablespace users offline;
Now, from the OS prompt, move the data files to a new location, using the Linux/Unix mv command:
$ mv /u01/dbfile/O12C/users01.dbf /u02/dbfile/O12C/users01.dbf
Update the control file with the ALTER TABLESPACE statement:
alter tablespace users rename datafile '/u01/dbfile/O12C/users01.dbf' to '/u02/dbfile/O12C/users01.dbf';
Finally, bring the data files within the tablespace back online:
SQL> alter tablespace users online;
If you want to rename data files 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 several data files in the database. Because the SYSTEM and UNDO tablespaces’ data files are being moved, you must shut down the database first and then place it in mount mode:
SQL> conn / as sysdba SQL> shutdown immediate; SQL> startup mount;
Because the database is in mount mode, the data files aren’t open for use, and thus there is no need to take the data files offline. Next, physically move the files via the Linux/Unix mv command:
$ mv /u01/dbfile/O12C/system01.dbf /u02/dbfile/O12C/system01.dbf $ mv /u01/dbfile/O12C/sysaux01.dbf /u02/dbfile/O12C/sysaux01.dbf $ mv /u01/dbfile/O12C/undotbs01.dbf /u02/dbfile/O12C/undotbs01.dbf
Note You must move the files before you update the control file. The ALTER DATABASE RENAME FILE command expects the file to be in the renamed location. If the file isn’t there, an error is thrown: ORA-27037: unable to obtain file status.
Now, you can update the control file to be aware of the new file name:
alter database rename file '/u01/dbfile/O12C/system01.dbf', '/u01/dbfile/O12C/sysaux01.dbf', '/u01/dbfile/O12C/undotbs01.dbf' to '/u02/dbfile/O12C/system01.dbf', '/u02/dbfile/O12C/sysaux01.dbf', '/u02/dbfile/O12C/undotbs01.dbf';
You should be able to open your database:
SQL> alter database open;
- Create a trace file that contains a CREATE CONTROLFILE statement.
- Modify the trace file to display the new location of the data files.
- Shut down the database.
- Physically move the data files, using an OS command.
- Start the database in nomount mode.
- Run the CREATE CONTROLFILE command.
Note When you re-create a control file, be aware that any RMAN information that was contained in the file will be lost. If you’re not using a recovery catalog, you can repopulate the control file with RMAN backup information, using the RMAN CATALOG 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:
SQL> alter database backup controlfile to trace as '/tmp/mv.sql' noresetlogs;
There are a couple of items to note about the prior statement. First, a file named mv.sql is created in the /tmp directory; this file contains a CREATE CONTROLFILE statement. Second, the prior statement uses the NORESETLOGS clause; this 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).
Next, edit the /tmp/mv.sql file, and change the names of the directory paths to the new locations. Here is a CREATE CONTROLFILE statement for this example:
CREATE CONTROLFILE REUSE DATABASE "O12C" NORESETLOGS NOARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 4 MAXDATAFILES 1024 MAXINSTANCES 1 MAXLOGHISTORY 876 LOGFILE GROUP 1 ( '/u01/oraredo/O12C/redo01a.rdo', '/u02/oraredo/O12C/redo01b.rdo' ) SIZE 50M BLOCKSIZE 512, GROUP 2 ( '/u01/oraredo/O12C/redo02a.rdo', '/u02/oraredo/O12C/redo02b.rdo' ) SIZE 50M BLOCKSIZE 512, GROUP 3 ( '/u01/oraredo/O12C/redo03a.rdo', '/u02/oraredo/O12C/redo03b.rdo' ) SIZE 50M BLOCKSIZE 512 DATAFILE '/u01/dbfile/O12C/system01.dbf', '/u01/dbfile/O12C/sysaux01.dbf', '/u01/dbfile/O12C/undotbs01.dbf', '/u01/dbfile/O12C/users01.dbf' CHARACTER SET AL32UTF8;
Now, shut down the database:
SQL> shutdown immediate;
Physically move the files from the OS prompt. This example uses the Linux/Unix mv command to move the files:
$ mv /u02/dbfile/O12C/system01.dbf /u01/dbfile/O12C/system01.dbf $ mv /u02/dbfile/O12C/sysaux01.dbf /u01/dbfile/O12C/sysaux01.dbf $ mv /u02/dbfile/O12C/undotbs01.dbf /u01/dbfile/O12C/undotbs01.dbf $ mv /u02/dbfile/O12C/users01.dbf /u01/dbfile/O12C/users01.dbf
Start up the database in nomount mode:
SQL> startup nomount;
Then, execute the file that contains the CREATE CONTROLFILE statement (in this example, mv.sql):
If the statement is successful, you see the following message:
Control file created.
Finally, alter your database open:
SQL> alter database open;