Managing Tablespaces

By Kamran Agayev A.

If the database has multiple tablespaces, you can separate data of one application from the data of another to prevent multiple applications from being affected if a tablespace must be taken offline. You can store different datafiles of different tablespaces on different disk drives to reduce I/O contention and backup individual tablespaces.

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

SYSAUX tablespace, which acts as an auxiliary tablespace to the SYSTEM tablespace, is also always created when you create a database. It contains information about and the schemas used by various Oracle products and features, so that those products do not require their own tablespaces.

Locally managed tablespaces track all extent information in the tablespace itself by using bitmaps. It provides enhanced performance, and user reliance on the data dictionary is reduced, because the necessary information is stored in file headers and bitmap blocks.

Create a locally managed tablespace by specifying LOCAL in the EXTENT MANAGEMENT vlause of the CREATE TABLESPACE statement. This is the default for new permanent tbalespaces, but you must specify the EXTENT MANAGEMENET LOCAL clause if you want to specify either the AUTOALLOCATE clause or the UNIFORM clause. You can have the database manage extents for you automatically with the AUTOALLOCATE clause (the default) or you can specify that the tablespace is managed with uniform extents of a specific size (UNIFORM).

AUTOALLOCATE means that the extent size are managed by Oracle. Oracle will choose the next optimal size for the extents starting with 64KB. As the segments grow and more extents are needed, oracle will start allocating larger and larger sizes ranging from 1Mb to eventually 64Mb extents.

UNIFORM T option tells the database to allocate and deallocate extents in the tablespace with the same unvarying size that you can specify or let extents default to 1Mb.

In a locally managed tablespace, there are two methods that Oracle Database can use to manage segment spaceL AUTOMATIC and MANUAL. Manual segment space management uses linked lists called “freelists” to manage free space in the segment, while automatic segment management uses bitmaps. Automatic segment space management is the more efficient method, and is the default for all new permanent, locally managed tablespaces.

To create a  bigfile tablespace, run CREATE BIGFILE TABLESPACE command. Oracle automatically creates a locally managed tablespace with automatic segment space management.

If default tablespace type was set to BIGFILE at database creation, but you want to create a traditional (smallfile) tablespace, then specify a CREATE SMALLFILE TABLESPACE statement to override the default tablespace that you are creating.

Within a temporary tablespace, all sort operations for a given instance and tablespace share a single sort segment. The sort segment is created by the first statement that uses a temporary tablespace for sorting, after startup, and is released only at shutdown.

You can view the allocation and deallocation of space in a temporary tablespace sort segment using the V$SORT_SEGMENT view.

When you create a temporary table, its rows are stored in your default temporary tablespace

Use CREATE BIGFILE TEMPORARY TABLESPACE statement to create a single tempfile tablespace.

To rename a tempfile, you take the tempfile offline, use operating system commands to rename or relocate the tempfile, and then use the ALTER DATABASE RENAME FILE command to update the database controlfiles.

Using temporary tablespace group, rather than a single remporary tablespace, can alleviate problems caused where one tablespace is inadequate to hold the results of a sort, particularly on a table that has many partitions.

You create at tablespace group implicitily when you include the TABLESPACE GROUP clause in the CREATE TEMPORARY TABLESPACE or ALTER TABLESPACE statement and the specified tablespace group does not currently exist.

ALTER TABLESPACE tmp_tbs TABLESPACE GROUP group2;

The view DBA_TABLESPACE_GROUPS lists tablespace groups and their member tablespaces

To assign tablespace group as a default temporary tablespace, use the following statement:

ALTER DATABASE db_name DEFAULT TEMPORARAY TABLESPACE group2;

You can create tablespaces with block sizes different from the standard database block size, which is specified by the DB_BLOCK_SIZE parameter.

Use the BLOCKSIZE clause of the CREATE TABLESPACE statement to create a tablespace with a block size different from the database standard block size. In order for the BLOCKSIZE clause to succeed, you must have already set the DB_CACHE_SIZE and at least one DB_nK_CACHE_SIZE parameter. Further, and the integer you specify in the BLOCKSIZE clause must correspond with the setting of one DB_nK_CACHE_SIZE parameter.

CREATE TABLESPACE tbs DATAFILE ‘/datafile/’ SIZE 10M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K BLOCKSIZE 8K;

You can’t take SYSTEM, UNDO and TEMPORARY tablespaces offline

When you take tablespace to OFFLINE usint IMMEDIATE clause, media recovery for the tablespace is required before the tablespace can be brought online.

If you must take tablespace offline, use the NORMAL clause (the default) if possible. This setting guarantees that the tablespace will not require recovery to come back online, even if after incomplete recovery you reset the redo log sequence using an ALTER DATABASE OPEN RESETLOGS statement .

Specify TEMPORARY only when you cannot take tablespace offline normally.  In this case, only the files taken offline because of errors need to be recovered before the tablespace can be brought online. Specify IMMEDIATE only after trying both the normal and temporary settings

You can drop items, such as tables or indexes, from a read-only tablespace, but you cannot create or alter objects in a read-only tablespace.

Before you can make a tablespace read-only, the tablespace must be online, can’t be active undo tablespace or SYSTEM tablespace

When there’re transactions waiting to commit, the ALTER TABLESPACE .. READ ONLY statement does not return immediately. It waits for all transactions started before you issued the ALTER TABLESPACE statement to either commit or rollback.

When substantial portions of a very large database are stored in read-only tablespaces that are located on slow-access devices or hierarchical storage you should consider setting the READ_ONLY_OPEN_DELAYED parameter to TRUE. This speed certain operations, primary opening the databsae, by causing datafiles in readonly tablespaces to be accessed for the first time only when an attempt is made to read data stored within them

You can’t rename the tablespace if :

• The tablespace is SYSTEM or SYSAUX tablespace

• If any datafile in the tablespacea is offline or if the tablespace is offline

Use the CASCADE CONSTRAINTS clause to drop all referential integrity constraints from tables outside the tablespace that refer to primary and unique keys of tables inside the tablespace

Use INCLUDING CONTENTS AND DATAFILES to delete physical datafiles from OS. It an OS error prevents the deletion of a file, the DROP TABLESPACE statement still succeeds, but a message describing the error is written to the alert log

If SYSAUX tablespace becomes unavailable, core database functionality will remain operational.

You can monitor the occupants of the SYSAUX tablespace using the V$SYSAUX_OCCUPANTS view.

Moving data using transportable tablespaces is much faster than performing either an export/import or unload/load of the same data. This is because the datafiles containing all of the actual data are just copied to the destination location, an you use an export/import utility to transfer only the metadata of the tablespace objects to the new database

Starting with Oracle Database 10g, you can transport tablespaces across platforms.

You can query the V$TRANSPORTABLE_PLATFORM view to see the platfors that are supported, and to determine each platform’s endian format(byte ordering)

If the source platform and the target platform are of different endianness, then an additional step must be done on either the source or target platform to convert the tablespace being transported to the target format.  If they are of the same endianness, then no conversion is necessary and tablespaces can be transported as if they were on the same platform.

To transport tablespace between database follow this procedure:

1. Check the endian format of both platforms by querying the V$TRANSPORTABLE_PLATFORM view
2.  Pick a self contained set of tablespaces
3. Generate a transportable tablespace set. It consists of datafiles for the set of tablespaces being transported and an export file containing structural information (metadata) for the set of tablespaces.
4. Transport the tablespace set
5. Import the tablespace set  

There may be logical or physical dependiencies between objects in the transportable set and those outside of the set. You can only transport a set of tablespaces that is self-contained. In this context “self-containted” means that there are no references from inside the set of tablespaces pointing outside of the tablespaces. To check whether the tablespace is self-containted, you can invoke the TRANSPORT_SET_CHECK procedure in the Oracle supplied package DBMS_TTS. After invoking this procedure, you can see all violations by selecting from the TRANSPORT_SET_VIOLATIONS view.

To convert tablespaces from one endian format to another, login to RMAN and run the following command:

RMAN> CONVERT TABLESPACE sales_1, sales_2 TO PLATFORM ‘Microsoft Windows NT’ FORMAT ‘/tmp/%U’;

Then transport both the datgafiles and the export file of the tablespaces to a place that is accessible to the target database

Use RMAN to convert the necessary transported datafiles to the endian format of the destination host format and deposit the results in /orahome/dbs as shown in the following examples

RMAN>CONVERT DATAFILE

‘/h1/finance/tbs_21.f’,

‘/h1/finance/tbs_22.f’

TO PLATFORM =”Solaris[tm] OE (32-bit)”

FROM PLATFORM=”HP Tru64 UNIX”

DB_FILE_NAME_CONVERT=’/h1/finance/work/try/”,’/h1/finance/dbs/tru’

PARALLELISM=5;