Oracle Database 12C: managing Tablespaces and Data files

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;

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 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.

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 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.

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 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.

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;

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.

select owner, segment_name, segment_type

from dba_segments

where tablespace_name=upper('&&tbsp_name');

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 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;

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;

Displaying Tablespace Size

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.

Altering Tablespace Size

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

$ 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;

Toggling Data Files Offline and Online

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.

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

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

When using OFFLINE IMMEDIATE, Oracle doesn’t issue a checkpoint on the data files. 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 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.

Renaming or Relocating a Data File

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.

Performing Online Data File 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.

Performing Offline Data File Operations

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.

Using SQL and OS Commands

Here are the steps for renaming a data file using SQL commands and OS commands:

1. Use the following query to determine the names of existing data files:

   SQL> select name from v$datafile;

2. 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.
3. 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.
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 data file name.
5. Alter the data file online.

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

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;

Re-Creating the Control File and OS Commands

Another way you can relocate all data files in a database is to use a combination of a re-created control file and OS commands. The steps for this operation are as follows:

1. Create a trace file that contains a CREATE CONTROLFILE statement.
2. Modify the trace file to display the new location of the data files.
3. Shut down the database.
4. Physically move the data files, using an OS command.
5. Start the database in nomount mode.
6. 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:

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):

SQL> @/tmp/mv.sql

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

Control file created.

Finally, alter your database open:

SQL> alter database open;

Вас заинтересует / Intresting for you:

Stopping the Oracle Database 1... 2181 views Андрей Волков Sat, 29 Feb 2020, 10:19:28

Oracle Database and Instance d... 3567 views Masha Thu, 21 Jun 2018, 18:23:39

Oracle Database 12C Backup: un... 2782 views Андрей Волков Sat, 29 Feb 2020, 10:15:04

Starting the Oracle Database 1... 1855 views Андрей Волков Sat, 29 Feb 2020, 10:19:42