Tablespaces in multitenancy

Note:

• In a non-CDB, all the tablespaces belong to one database. 

• In the CDB, one set of tablespaces belong to the root container, and each PDB has its own set of tablespaces. 

• Common objects are created and their data stored in a tablespace in the root container. The common object is visible in the PDBs through links. 

• There are new clauses in the CREATE DATABASE command. The USER_DATA TABLESPACE allows you to specify a default tablespace other than USERS when using DBCA to create a database. This tablespace will also be used for XDB options.

• The UNDO tablespace is common to all PDBs, that is, you can have more than one but there is only one active UNDO tablespace per CDB.

To view the all tablespaces

SQL> SELECT tablespace_name, DECODE(pdb_name, NULL, 'ROOT',pdb_name) pdb_name
  2  FROM cdb_tablespaces t, cdb_pdbs p
  3  WHERE t.con_id = p.pdb_id(+)
  4  ORDER BY 2,1;

TABLESPACE_NAME                PDB_NAME
------------------------------ --------------------
SYSAUX                         PDB$SEED
SYSTEM                         PDB$SEED
TEMP                           PDB$SEED
SYSAUX                         PDB2
SYSTEM                         PDB2
TEMP                           PDB2
SYSAUX                         ROOT
SYSTEM                         ROOT
TEMP                           ROOT
UNDOTBS1                       ROOT
UNDOTBS2                       ROOT
USERS                          ROOT

12 rows selected.

The CREATE TABLESPACE command should be familiar. The change in its behavior in a CDB is that the tablespace is created in the container where the command is executed. Separating the data files into different directories by PDB can help determine which files belong to which PDB, though it is not necessary but it is good from administration perspective.

Create tablespace at CDB

SQL> connect system/oracle@cdb
Connected.
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> CREATE TABLESPACE cdb_tbs
  2  DATAFILE '+DATA/ora12c/cdb_tbs01.dbf'
  3  SIZE 5M;

Tablespace created.

Create tablespace at PDB

SQL> connect system/oracle@pdb2
Connected.

SQL> show con_name
CON_NAME
------------------------------
PDB2
SQL> CREATE TABLESPACE pdb2_tbs
  2  DATAFILE '+DATA/pdb2/pdb2_tbs01.dbf'
  3  SIZE 5M;

Tablespace created.

To View 12c Tablespaces

To view tablespace and it's corresponding container

SQL> col name form a12
SQL> SELECT file#, tbs.name, tbs.ts#, tbs.con_id
  2  FROM v$datafile dbf, v$tablespace tbs
  3  WHERE dbf.ts#=tbs.ts#
  4  AND dbf.con_id=tbs.con_id;
     FILE# NAME       TS#        CON_ID
---------- ------------ ---------- ------
1 SYSTEM  0 1
2 SYSTEM               0      2
3 SYSAUX 1      1
4 SYSAUX 1      2
5 UNDOTBS1 2 1
6 USERS  4      1
7 SYSTEM 0      3
8 SYSAUX 1      3
9 USERS 3      3
9 rows selected.

Extracted from Oracle 12c Document, CON_ID = 0 mean CDB. 

About Viewing Information When the Current Container Is the Root

When the current container is the root, a common user can view data dictionary information for the root and for PDBs by querying container data objects. A container data object is a table or view that can contain data pertaining to the following:

• One or more containers
• The CDB as a whole
• One or more containers and the CDB as a whole

Container data objects include V$, GV$, CDB_, and some Automatic Workload Repository DBA_HIST* views. A common user's CONTAINER_DATA attribute determines which PDBs are visible in container data objects.

In a CDB, for every DBA_ view, there is a corresponding CDB_ view. All CDB_ views are container data objects, but most DBA_ views are not.

Each container data object contains a CON_ID column that identifies the container for each row returned. Table 43-1 describes the meanings of the values in the CON_ID column.

Table 43-1 CON_ID Column in Container Data Objects

Value in CON_ID Column	Description
0	The data pertains to the entire CDB
1	The data pertains to the root
2	The data pertains to the seed
3 - 254	The data pertains to a PDB Each PDB has its own container ID.

Detect Tablespace Fragmentation

The following is a script that will determine how many extents of contiguous free space you have in Oracle as well as the total amount of free space you have in each tablespace. From these results you can detect how fragmented your tablespace is.

The ideal situation is to have one large free extent in your tablespace. The more extents of free space there are in the tablespace, the more likely you will run into fragmentation problems. The size of the free extents is also very important. If you have a lot of small extents (too small for any next extent size) but the total bytes of free space is large, then you may want to consider defragmentation options.

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

create table SPACE_TEMP (   
 TABLESPACE_NAME        CHAR(30),   
 CONTIGUOUS_BYTES       NUMBER)   
/   
   
declare   
  cursor query is select *   
          from dba_free_space   
                  order by tablespace_name, block_id;   
  this_row        query%rowtype;   
  previous_row    query%rowtype;   
total           number;   
   
begin   
  open query;   
  fetch query into this_row;   
  previous_row := this_row;   
  total := previous_row.bytes;   
  loop   
 fetch query into this_row;   
     exit when query%notfound;   
     if this_row.block_id = previous_row.block_id + previous_row.blocks then   
        total := total + this_row.bytes;   
        insert into SPACE_TEMP (tablespace_name)   
                  values (previous_row.tablespace_name);   
     else   
        insert into SPACE_TEMP values (previous_row.tablespace_name,   
               total);   
        total := this_row.bytes;   
     end if;   
previous_row := this_row;   
  end loop;   
  insert into SPACE_TEMP values (previous_row.tablespace_name,   
                           total);   
end;   
.   
/   
   
set pagesize 60   
set newpage 0   
set echo off   
ttitle center 'Contiguous Extents Report'  skip 3   
break on "TABLESPACE NAME" skip page duplicate   
spool contig_free_space.lis   
rem   
column "CONTIGUOUS BYTES"       format 999,999,999   
column "COUNT"                  format 999   
column "TOTAL BYTES"            format 999,999,999   
column "TODAY"   noprint new_value new_today format a1   
rem   
select TABLESPACE_NAME  "TABLESPACE NAME",   
       CONTIGUOUS_BYTES "CONTIGUOUS BYTES"   
from SPACE_TEMP   
where CONTIGUOUS_BYTES is not null   
order by TABLESPACE_NAME, CONTIGUOUS_BYTES desc;   
   
select tablespace_name, count(*) "# OF EXTENTS",   
         sum(contiguous_bytes) "TOTAL BYTES"    
from space_temp   
group by tablespace_name;   
   
spool off   
   
drop table SPACE_TEMP   
/   

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

Sample output

TABLESPACE_NAME                # OF EXTENTS  TOTAL BYTES
------------------------------ ------------ ------------
APEX_1527304513423350                     1    1,114,112
RMAN_CATALOG                              1  305,332,224
APEX_1242130650845718                     3      851,968
USERS                                    10  187,957,248
SYSAUX                                   66   88,408,064
UNDOTBS1                                  6  622,919,680
SYSTEM                                    1    5,963,776

Increase Tablespace Size Automatically and Manually

Oracle allow DBA to set the datafiles to be increase automatically.

Following command is needed to check the NEXT incremental size, and the Maximum Size.

SELECT tablespace_name, file_name, increment_by*[block size]/1024/1024 Inc_MB, maxbytes/1024/1024 MaxMB , autoextensible FROM dba_data_files;

You need to change the [block size] to your own block size, the reason is because the increment_by column is storing number of block we need to convert into Bytes by multiple by block size.

To find your block size use following command

SHOW PARAMETER db_block_size

for my case I have 8192 bytes per Oracle block, therefore my script is

SELECT tablespace_name, file_name, increment_by*8192/1024/1024 Inc_MB, maxbytes/1024/1024 MaxMB , autoextensible FROM dba_data_files;

In my example I want to set one of my MY_TBS tablespaces datafile namely my_tbs_03.dbf to be automatically increase by 5M until 100M.

ALTER DATABASE DATAFILE '/datafiles/edba01/edba01/my_tbs_03.dbf'
AUTOEXTEND ON
NEXT 5M
MAXSIZE 100M;

You also able to set the Maximum grow size to UNLIMITED, which
mean Oracle will fill until the maximum it can go.

Now I want to set my datafile my_tbs_02.dbf to unlimited growth size.

ALTER DATABASE DATAFILE '/datafiles/edba01/edba01/my_tbs_02.dbf'
AUTOEXTEND ON
NEXT 5M
MAXSIZE UNLIMITED;

Please note that:
For the UNLIMITED datafile this is not mean that you datafile is super power kind of datafile that you need to upgrae your storage only. The UNLIMITED means that Oracle will used up to the maximum of 4 millions block per datafile, for my case is equal to 4E6*8192. This is not apply for BIGFILE TABLESPACE.

For ease of maintenance in my opinion it's good to always be automatically extended and with a specified size for the NEXT parameter. Specifying the NEXT parameter assures that datafiles grow with consistent, reusable extent sizes. If extent sizes are too small then a large table could have so many extents to search through that performance will degrade seriously. The NEXT parameter is defaulted to the block size. Do not leave NEXT undeclared and defaulted. Defaulting to the block size is usually too small. Only small static tables and indexes could have sizes for NEXT of below 1M. For some tables well over 1M is prudent.

It will be better you set the MAXSIZE value to non UNLIMITED


There are two ways to increase tablespace size Manually.

1. Add new datafile to the tablespace.
2. Increase the datafile size of the tablespace.

Note: You can use this script to check the tablespace size and autoextensible status

SELECT tablespace_name, file_name, bytes/1024/1024 MBytes, autoextensible FROM dba_data_files;

1. Add new datafile to the tablespace.

In my example I want to add new datafile my_tbs_03.dbf with size 5 megabytes to my_tbs
The command is

ALTER TABLESPACE my_tbs ADD DATAFILE '/datafiles/edba01/edba01/my_tbs_03.dbf' SIZE 5m;

2. Increase the datafile size of the tablespace.

In my example I want to increase the my_first_tbs tablespace's datafile my_first_tbs.dbf from 10m to 15m
The command is

ALTER DATABASE DATAFILE '/datafiles/edba01/edba01/my_first_tbs_01.dbf' RESIZE 15M;

Please note that, there is no command say to increase datafile, the command is to resize the datafile, therefore you may shrink the datafile by using this command with intention of reclaiming space in your storage. There is a limit how small the datafile can be shrink because you can not shrink datafile size below the objects in the datafile. In order to properly reclaimed space check my other article about how to reclaimed space.

Delete Tablespace

You can use following command to remove tablespace from your database.

In my example I want to remove my_tbs tablespace.

DROP TABLESPACE my_tbs INCLUDING CONTENTS AND DATAFILES;

Create Tablespace

Note:

Before we start please note that following command is use to check the tablespace detail with the datafiles and the size

SELECT tablespace_name, file_name, bytes/1024/1024 megabytes FROM dba_data_files;

The bytes column storing the datafile size in bytes therefore I need to devided by 1024 twice to show in Megabyte.

To check the status of the tablespace you can use following script

SELECT a.tablespace_name, a.file_name, a.bytes/1024/1024 megabytes, b.status
FROM dba_data_files a, dba_tablespaces b
WHERE a.tablespace_name = b.tablespace_name;


1. Create Simple Tablespace

Following is the most basic SQL command creating Tablespace.

CREATE TABLESPACE my_first_tbs
DATAFILE '/datafiles/edba01/edba01/my_first_tbs_01.dbf' SIZE 10m;

To create a tablespace there are 3 basic information you need supply
1. tablespace name for
the example is my_first_tbs
2. the datafile include the full path where the datafile will be store
for the example /datafiles/edba01/edba01/my_first_tbs_01.dbf
3. The initial size of the datafile
for the example 10m (m is megabyte)

2. Creating Tablespace with multiple datafiles

If you read the Oracle concept it said that tablespace can have multiple datafiles. And the datafile is only associated to 1 tablespace.

Following is the example command

CREATE TABLESPACE my_tbs
DATAFILE '/datafiles/edba01/edba01/my_tbs_01.dbf' SIZE 5m,
'/datafiles/edba01/edba01/my_tbs_02.dbf' SIZE 5m;

Oracle Tablespace

Oracle Tablespaces

A database is divided into one or more logical storage units called tablespaces. Tablespaces are divided into logical units of storage called segments, which are further divided into extents. Extents are a collection of contiguous blocks.


1. Bigfile Tablespaces

Oracle Database lets you create bigfile tablespaces. This allows Oracle Database to contain tablespaces made up of single large files rather than numerous smaller ones. This lets Oracle Database utilize the ability of 64-bit systems to create and manage ultralarge files. The consequence of this is that Oracle Database can now scale up to 8 exabytes in size.

With Oracle-managed files, bigfile tablespaces make datafiles completely transparent for users. In other words, you can perform operations on tablespaces, rather than the underlying datafile. Bigfile tablespaces make the tablespace the main unit of the disk space administration, backup and recovery, and so on. Bigfile tablespaces also simplify datafile management with Oracle-managed files and Automatic Storage Management by eliminating the need for adding new datafiles and dealing with multiple files.

The system default is to create a smallfile tablespace, which is the traditional type of Oracle Database tablespace. The SYSTEM and SYSAUX tablespace types are always created using the system default type.

Bigfile tablespaces are supported only for locally managed tablespaces with automatic segment-space management. There are two exceptions: locally managed undo and temporary tablespaces can be bigfile tablespaces, even though their segments are manually managed.

An Oracle database can contain both bigfile and smallfile tablespaces. Tablespaces of different types are indistinguishable in terms of execution of SQL statements that do not explicitly refer to datafiles.

You can create a group of temporary tablespaces that let a user consume temporary space from multiple tablespaces. A tablespace group can also be specified as the default temporary tablespace for the database. This is useful with bigfile tablespaces, where you could need a lot of temporary tablespace for sorts.


1.1. Benefits of Bigfile Tablespaces

1.1.1. Bigfile tablespaces can significantly increase the storage capacity of an Oracle database. Smallfile tablespaces can contain up to 1024 files, but bigfile tablespaces contain only one file that can be 1024 times larger than a smallfile tablespace. The total tablespace capacity is the same for smallfile tablespaces and bigfile tablespaces. However, because there is limit of 64K datafiles for each database, a database can contain 1024 times more bigfile tablespaces than smallfile tablespaces, so bigfile tablespaces increase the total database capacity by 3 orders of magnitude. In other words, 8 exabytes is the maximum size of the Oracle database when bigfile tablespaces are used with the maximum block size (32 k).

1.1.2. Bigfile tablespaces simplify management of datafiles in ultra large databases by reducing the number of datafiles needed. You can also adjust parameters to reduce the SGA space required for datafile information and the size of the control file.

1.1.3. They simplify database management by providing datafile transparency.


1.2 Considerations with Bigfile Tablespaces

1.2.1. Bigfile tablespaces are intended to be used with Automatic Storage Management or other logical volume managers that support dynamically extensible logical volumes and striping or RAID.

1.2.2. Avoid creating bigfile tablespaces on a system that does not support striping because of negative implications for parallel execution and RMAN backup parallelization.

1.2.3. Avoid using bigfile tablespaces if there could possibly be no free space available on a disk group, and the only way to extend a tablespace is to add a new datafile on a different disk group.

1.2.4. Using bigfile tablespaces on platforms that do not support large file sizes is not recommended and can limit tablespace capacity. Refer to your operating system specific documentation for information about maximum supported file sizes.

1.2.5. Performance of database opens, checkpoints, and DBWR processes should improve if data is stored in bigfile tablespaces instead of traditional tablespaces. However, increasing the datafile size might increase time to restore a corrupted file or create a new datafile.


2. The SYSTEM Tablespace

Every Oracle database contains a tablespace named SYSTEM, which Oracle Database creates automatically when the database is created. The SYSTEM tablespace is always online when the database is open.

To take advantage of the benefits of locally managed tablespaces, you can create a locally managed SYSTEM tablespace, or you can migrate an existing dictionary managed SYSTEM tablespace to a locally managed format.

In a database with a locally managed SYSTEM tablespace, dictionary managed tablespaces cannot be created. It is possible to plug in a dictionary managed tablespace using the transportable feature, but it cannot be made writable.


2.1. The Data Dictionary

The SYSTEM tablespace always contains the data dictionary tables for the entire database.


2.2. PL/SQL Program Units Description

All data stored on behalf of stored PL/SQL program units (that is, procedures, functions, packages, and triggers) resides in the SYSTEM tablespace. If the database contains many of these program units, then the database administrator must provide the space the units need in the SYSTEM tablespace.


3. The SYSAUX Tablespace

The SYSAUX tablespace is an auxiliary tablespace to the SYSTEM tablespace. Many database components use the SYSAUX tablespace as their default location to store data. Therefore, the SYSAUX tablespace is always created during database creation or database upgrade.

Note: If the SYSAUX tablespace is unavailable, such as due to a media failure, then some database features may fail.

The SYSAUX tablespace provides a centralized location for database metadata that does not reside in the SYSTEM tablespace. It reduces the number of tablespaces created by default, both in the seed database and in user-defined databases.

During normal database operation, Oracle Database does not allow the SYSAUX tablespace to be dropped or renamed. Transportable tablespaces for SYSAUX is not supported.


4. Undo Tablespaces

Undo tablespaces are special tablespaces used solely for storing undo information. You cannot create any other segment types (for example, tables or indexes) in undo tablespaces. Undo tablespaces are used only when the database is in automatic undo management mode (the default). A database can contain more than one undo tablespace, but only one can be in use at any time. Undo data is managed within an undo tablespace using undo segments that are automatically created and maintained by the database.

When the first DML operation is run within a transaction, the transaction is bound (assigned) to an undo segment (and therefore to a transaction table) in the current undo tablespace. In rare circumstances, if the instance does not have a designated undo tablespace, the transaction binds to the system undo segment.

Each undo tablespace is composed of a set of datafiles and is locally managed. Like other types of tablespaces, undo blocks are grouped in extents and the status of each extent is represented in the bitmap. At any point in time, an extent is either allocated to (and used by) a transaction table, or it is free.

You can create a bigfile undo tablespace.


4.1. Creation of Undo Tablespaces

An undo tablespace is automatically created with each new installation of Oracle Database. Earlier versions of Oracle Database may not include an undo tablespace and may instead use rollback segments. This is known as manual undo management mode. When upgrading to Oracle Database 11g you can migrate to automatic undo management by creating an undo tablespace and enabling automatic undo management mode.


5. Default Temporary Tablespace

When the SYSTEM tablespace is locally managed, you must define at least one default temporary tablespace when creating a database. A locally managed SYSTEM tablespace cannot be used for default temporary storage.

If SYSTEM is dictionary managed and if you do not define a default temporary tablespace when creating the database, then SYSTEM is still used for default temporary storage. However, you will receive a warning in ALERT.LOG saying that a default temporary tablespace is recommended and will be necessary in future releases.


5.1. How to Specify a Default Temporary Tablespace

Specify default temporary tablespaces when you create a database, using the DEFAULT TEMPORARY TABLESPACE extension to the CREATE DATABASE statement.
You can create bigfile temporary tablespaces. A bigfile temporary tablespace, like all temporary tablespaces, uses tempfiles instead of datafiles.

Note: You cannot make a default temporary tablespace permanent or take it offline.


6. Using Multiple Tablespaces

A very small database may need only the SYSTEM tablespace; however, Oracle recommends that you create at least one additional tablespace to store user data separate from data dictionary information. This gives you more flexibility in various database administration operations and reduces contention among dictionary objects and schema objects for the same datafiles.

You can use multiple tablespaces to perform the following tasks:
- Control disk space allocation for database data
- Assign specific space quotas for database users
- Control availability of data by taking individual tablespaces online or offline
- Perform partial database backup or recovery operations
- Allocate data storage across devices to improve performance

A database administrator can perform the following actions:
- Create new tablespaces
- Add datafiles to tablespaces
- Set and alter default segment storage settings for segments created in a tablespace
- Make a tablespace read only or read/write
- Make a tablespace temporary or permanent
- Rename tablespaces
- Drop tablespaces
- Transport tablespaces across databases and platforms


7. Managing Space in Tablespaces

Tablespaces allocate space in extents. Tablespaces can use two different methods to keep track of their free and used space:

- Locally managed tablespaces: Extent management by the bitmaps
- Dictionary managed tablespaces: Extent management by the data dictionary

When you create a tablespace, you choose one of these methods of space management. Later, you can change the management method with the DBMS_SPACE_ADMIN PL/SQL package.


7.1. Locally Managed Tablespaces

A tablespace that manages its own extents maintains a bitmap in each datafile to keep track of the free or used status of blocks in that datafile. Each bit in the bitmap corresponds to a block or a group of blocks. When an extent is allocated or freed for reuse, Oracle Database changes the bitmap values to show the new status of the blocks.

Locally managed tablespaces have the following advantages over dictionary managed tablespaces:

- Local management of extents automatically tracks adjacent free space, eliminating the need to coalesce free extents.

- Local management of extents avoids recursive space management operations. Such recursive operations can occur in dictionary managed tablespaces if consuming or releasing space in an extent results in another operation that consumes or releases space in a data dictionary table or rollback segment.

The sizes of extents that are managed locally are determined automatically by the system. Alternatively, all extents can have the same size in a locally managed tablespace and override object storage options.

The LOCAL clause of the CREATE TABLESPACE or CREATE TEMPORARY TABLESPACE statement is specified to create locally managed permanent or temporary tablespaces, respectively.


7.2. Segment Space Management in Locally Managed Tablespaces

When you create a locally managed tablespace using the CREATE TABLESPACE statement, the SEGMENT SPACE MANAGEMENT clause lets you specify how free and used space within a segment is to be managed. Your choices are:

AUTO

This keyword tells Oracle Database that you want to use bitmaps to manage the free space within segments. A bitmap, in this case, is a map that describes the status of each data block within a segment with respect to the amount of space in the block available for inserting rows. As more or less space becomes available in a data block, its new state is reflected in the bitmap. Bitmaps enable Oracle Database to manage free space more automatically; thus, this form of space management is called automatic segment-space management.

Locally managed tablespaces using automatic segment-space management can be created as smallfile (traditional) or bigfile tablespaces. AUTO is the default.

MANUAL

This keyword tells Oracle Database that you want to use free lists for managing free space within segments. Free lists are lists of data blocks that have space available for inserting rows.


7.3. Dictionary Managed Tablespaces

If you created your database with Oracle9i, you could be using dictionary managed tablespaces. For a tablespace that uses the data dictionary to manage its extents, Oracle Database updates the appropriate tables in the data dictionary whenever an extent is allocated or freed for reuse. Oracle Database also stores rollback information about each update of the dictionary tables. Because dictionary tables and rollback segments are part of the database, the space that they occupy is subject to the same space management operations as all other data.

Note: If you do not specify extent management when you create a tablespace, then the default is locally managed.


8. Multiple Block Sizes

Oracle Database supports multiple block sizes in a database. The standard block size is used for the SYSTEM tablespace. This is set when the database is created and can be any valid size. You specify the standard block size by setting the initialization parameter DB_BLOCK_SIZE. Legitimate values are from 2K to 32K.

In the initialization parameter file or server parameter file, you can configure subcaches within the buffer cache for each of these block sizes. Subcaches can also be configured while an instance is running. You can create tablespaces having any of these block sizes. The standard block size is used for the system tablespace and most other tablespaces.

Note: All partitions of a partitioned object must reside in tablespaces of a single block size.

Multiple block sizes are useful primarily when transporting a tablespace from an OLTP database to an enterprise data warehouse. This facilitates transport between databases of different block sizes.


9. Online and Offline Tablespaces

A database administrator can bring any tablespace other than the SYSTEM tablespace online (accessible) or offline (not accessible) whenever the database is open. The SYSTEM tablespace is always online when the database is open because the data dictionary must always be available to Oracle Database.

A tablespace is usually online so that the data contained within it is available to database users. However, the database administrator can take a tablespace offline for maintenance or backup and recovery purposes.


9.1. Bringing Tablespaces Offline

When a tablespace goes offline, Oracle Database does not permit any subsequent SQL statements to reference objects contained in that tablespace. Active transactions with completed statements that refer to data in that tablespace are not affected at the transaction level. Oracle Database saves rollback data corresponding to those completed statements in a deferred rollback segment in the SYSTEM tablespace. When the tablespace is brought back online, Oracle Database applies the rollback data to the tablespace, if needed.

When a tablespace goes offline or comes back online, this is recorded in the data dictionary in the SYSTEM tablespace. If a tablespace is offline when you shut down a database, the tablespace remains offline when the database is subsequently mounted and reopened.

You can bring a tablespace online only in the database in which it was created because the necessary data dictionary information is maintained in the SYSTEM tablespace of that database. An offline tablespace cannot be read or edited by any utility other than Oracle Database. Thus, offline tablespaces cannot be transposed to other databases.

Oracle Database automatically switches a tablespace from online to offline when certain errors are encountered. For example, Oracle Database switches a tablespace from online to offline when the database writer process, DBWn, fails in several attempts to write to a datafile of the tablespace. Users trying to access tables in the offline tablespace receive an error. If the problem that causes this disk I/O to fail is media failure, you must recover the tablespace after you correct the problem.


10. Read-Only Tablespaces

The primary purpose of read-only tablespaces is to eliminate the need to perform backup and recovery of large, static portions of a database. Oracle Database never updates the files of a read-only tablespace, and therefore the files can reside on read-only media such as CD-ROMs or WORM drives.

Note: Because you can only bring a tablespace online in the database in which it was created, read-only tablespaces are not meant to satisfy archiving requirements.

Read-only tablespaces cannot be modified. To update a read-only tablespace, first make the tablespace read/write. After updating the tablespace, you can then reset it to be read only.

Because read-only tablespaces cannot be modified, and as long as they have not been made read/write at any point, they do not need repeated backup. Also, if you must recover your database, you do not need to recover any read-only tablespaces, because they could not have been modified.


11. Temporary Tablespaces

You can manage space for sort operations more efficiently by designating one or more temporary tablespaces exclusively for sorts. Doing so effectively eliminates serialization of space management operations involved in the allocation and deallocation of sort space. A single SQL operation can use more than one temporary tablespace for sorting. For example, you can create indexes on very large tables, and the sort operation during index creation can be distributed across multiple tablespaces.

All operations that use sorts, including joins, index builds, ordering, computing aggregates (GROUP BY), and collecting optimizer statistics, benefit from temporary tablespaces. The performance gains are significant with Oracle Real Application Clusters.


11.1 Sort Segments

One or more temporary tablespaces can be used only for sort segments. A temporary tablespace is not the same as a tablespace that a user designates for temporary segments, which can be any tablespace available to the user. No permanent schema objects can reside in a temporary tablespace.

Sort segments are used when a segment is shared by multiple sort operations. One sort segment exists for every instance that performs a sort operation in a given tablespace.

Temporary tablespaces provide performance improvements when you have multiple sorts that are too large to fit into memory. The sort segment of a given temporary tablespace is created at the time of the first sort operation. The sort segment expands by allocating extents until the segment size is equal to or greater than the total storage demands of all of the active sorts running on that instance.


11.2. Creation of Temporary Tablespaces

Create temporary tablespaces by using the CREATE TABLESPACE or CREATE TEMPORARY TABLESPACE statement.


12. Transport of Tablespaces Between Databases

A transportable tablespace lets you move a subset of an Oracle database from one Oracle database to another, even across different platforms. You can clone a tablespace and plug it into another database, copying the tablespace between databases, or you can unplug a tablespace from one Oracle database and plug it into another Oracle database, moving the tablespace between databases.

Moving data by transporting tablespaces can be orders of magnitude faster than either export/import or unload/load of the same data, because transporting a tablespace involves only copying datafiles and integrating the tablespace metadata. When you transport tablespaces you can also move index data, so you do not have to rebuild the indexes after importing or loading the table data.

You can transport tablespaces across platforms. (Many, but not all, platforms are supported for cross-platform tablespace transport.) This can be used for the following:

Provide an easier and more efficient means for content providers to publish structured data and distribute it to customers running Oracle Database on a different platform

Simplify the distribution of data from a data warehouse environment to data marts which are often running on smaller platforms

Enable the sharing of read only tablespaces across a heterogeneous cluster

Allow a database to be migrated from one platform to another


12.1. Tablespace Repository

A tablespace repository is a collection of tablespace sets. Tablespace repositories are built on file group repositories, but tablespace repositories only contain the files required to move or copy tablespaces between databases. Different tablespace sets may be stored in a tablespace repository, and different versions of a particular tablespace set also may be stored.

A version of a tablespace set in a tablespace repository consists of the following files:

- The Data Pump export dump file for the tablespace set
- The Data Pump log file for the export
- The datafiles that comprise the tablespace set


12.2. How to Move or Copy a Tablespace to Another Database

To move or copy a set of tablespaces, you must make the tablespaces read only, copy the datafiles of these tablespaces, and use export/import to move the database information (metadata) stored in the data dictionary. Both the datafiles and the metadata export file must be copied to the target database. The transport of these files can be done using any facility for copying flat files, such as the operating system copying facility, ftp, or publishing on CDs.

After copying the datafiles and importing the metadata, you can optionally put the tablespaces in read/write mode.

The first time a tablespace's datafiles are opened under Oracle Database with the COMPATIBLE initialization parameter set to 10 or higher, each file identifies the platform to which it belongs. These files have identical on disk formats for file header blocks, which are used for file identification and verification. Read only and offline files get the compatibility advanced after they are made read/write or are brought online. This implies that tablespaces that are read only before Oracle Database 10g must be made read/write at least once before they can use the cross platform transportable feature.

Note: In a database with a locally managed SYSTEM tablespace, dictionary tablespaces cannot be created. It is possible to plug in a dictionary managed tablespace using the transportable feature, but it cannot be made writable.

Guidelines for Managing Tablespaces

Guidelines for Managing Tablespaces

Before working with tablespaces of an Oracle Database, familiarize yourself with the guidelines provided in the following sections:

1. Using Multiple Tablespaces

Using multiple tablespaces allows you more flexibility in performing database operations. When a database has multiple tablespaces, you can:

Separate user data from data dictionary data to reduce I/O contention.
Separate data of one application from the data of another to prevent multiple applications from being affected if a tablespace must be taken offline.
Store the datafiles of different tablespaces on different disk drives to reduce I/O contention.
Take individual tablespaces offline while others remain online, providing better overall availability.
Optimizing tablespace use by reserving a tablespace for a particular type of database use, such as high update activity, read-only activity, or temporary segment storage.
Back up individual tablespaces.

Some operating systems set a limit on the number of files that can be open simultaneously. Such limits can affect the number of tablespaces that can be simultaneously online. To avoid exceeding your operating system limit, plan your tablespaces efficiently. Create only enough tablespaces to fulfill your needs, and create these tablespaces with as few files as possible. If you need to increase the size of a tablespace, add one or two large datafiles, or create datafiles with autoextension enabled, rather than creating many small datafiles.

Review your data in light of these factors and decide how many tablespaces you need for your database design.


2. Assigning Tablespace Quotas to Users

Grant to users who will be creating tables, clusters, materialized views, indexes, and other objects the privilege to create the object and a quota (space allowance or limit) in the tablespace intended to hold the object segment.

Oracle Database Logical Structure

This section discusses logical storage structures: data blocks, extents, segments, and tablespaces. These logical storage structures enable Oracle Database to have fine-grained control of disk space use.

Following section explain from the smallest structure to the largest.
- Oracle Database Block
- Extents
- Segment
- Tablespace


Oracle Database Data Blocks

At the finest level of granularity, Oracle Database data is stored in data blocks. One data block corresponds to a specific number of bytes of physical database space on disk. The standard block size is specified by the DB_BLOCK_SIZE initialization parameter. In addition, you can specify up to four other block sizes. A database uses and allocates free database space in Oracle Database data blocks.

Extents

The next level of logical database space is an extent. An extent is a specific number of contiguous data blocks, obtained in a single allocation, used to store a specific type of information.

Segments

Above extents, the level of logical database storage is a segment. A segment is a set of extents allocated for a table, index, rollback segment, or for temporary use by a session, transaction, or SQL parser. In relation to physical database structures, all extents belonging to a segment exist in the same tablespace, but they may be in different data files.

When the extents of a segment are full, Oracle Database dynamically allocates another extent for that segment. Because extents are allocated as needed, the extents of a segment may or may not be contiguous on disk.

Tablespaces

A database is divided into logical storage units called tablespaces, which group related data blocks, extents, and segments. For example, tablespaces commonly group together all application objects to simplify some administrative operations.

Each database is logically divided into two or more tablespaces. One or more datafiles are explicitly created for each tablespace to physically store the data of all logical structures in a tablespace. The combined size of the datafiles in a tablespace is the total storage capacity of the tablespace.

Every Oracle database contains a SYSTEM tablespace and a SYSAUX tablespace. Oracle Database creates them automatically when the database is created. The system default is to create a smallfile tablespace, which is the traditional type of Oracle tablespace. The SYSTEM and SYSAUX tablespaces are created as smallfile tablespaces.

Oracle Database also lets you create bigfile tablespaces, which are made up of single large file rather than numerous smaller ones. Bigfile tablespaces let Oracle Database utilize the ability of 64-bit systems to create and manage ultralarge files. As a result, Oracle Database can scale up to 8 exabytes in size. With Oracle-Managed Files, bigfile tablespaces make datafiles completely transparent for users.

In other words, you can perform operations on tablespaces, rather than the underlying datafiles.

Online and Offline Tablespaces

A tablespace can be online or offline. A tablespace is generally online, so that users can access the information in the tablespace. However, to simplify administration, sometimes a tablespace is taken offline to make a portion of the database unavailable while allowing normal access to the remainder of the database.

Read-only Tablespaces

A tablespace can be read only, which means that data in the tablespace cannot be modified. The primary purpose of read-only tablespaces is to eliminate the need to perform backup and recovery of large, static portions of a database. Oracle Database never updates the files of a read-only tablespace, and therefore the files can reside on read-only media such as CD-ROMs or WORM drives.

Find Out Tablespace Free Space

A database is divided into logical storage units called tablespaces, which group related data blocks, extents, and segments. For example, tablespaces commonly group together all application objects to simplify some administrative operations.

Following is the script to find out the percentage used of all tablespaces.

SELECT a.tablespace_name,
a.bytes bytes_used,
b.bytes bytes_free,
b.largest,
ROUND(((a.bytes-b.bytes)/a.bytes)*100,2) percent_used
FROM (SELECT tablespace_name,
SUM(bytes) bytes
FROM DBA_DATA_FILES
GROUP BY tablespace_name)a,
(SELECT tablespace_name,
SUM(bytes) bytes,
MAX(bytes) largest
FROM dba_free_space
GROUP BY tablespace_name) b
WHERE a.tablespace_name=b.tablespace_name
ORDER BY ((a.bytes-b.bytes)/a.bytes) DESC
/