Table Space Management

Data, in Oracle, is logically stored in tablespaces and physically stored in datafiles. The illustration below shows the file and logical structures according to the operating system.

Data, in Oracle, is logically stored in tablespaces and physically stored in datafiles. The illustration below shows the file and logical structures according to the operating system. (TableSpace can also be abbreviated to TS.)

 

It is necessary to understand that a TS stores schema objects that are constant/non-volatile. The Data Dictionary is kept in a special system TS. Version 10G introduced a mandatory auxilary system TS. If migrating from a version earlier than 10G, the auxiliary TS must be created before the migration process.

 

The structure of a TS has been designed so as to be easier for a database to control and to ease maintenance activities for administrators.

An example of this is enabling separation of a TS for every department and the data that gets generated when creating new users in the system (e.g. accounting_tbs, sales_tbs). Activities such as backups of departmental data should be scheduled for different dates so that they can be better supervised.

Such scheduling helps prevent possible errors from one departmental backup affecting other departments’ data. Additionally, the separated data allows for greater ease and flexibility when granting users privileges.

 

Temporary TS: A user’s session data, such as queues and sort transactions, gets stored in a temp file rather than a datafile.

Undo TS: Used to maintain consistency of data that is read. (The consistency of undo data will be explained in further sections)

 

TableSpace Types

1- System tablespace: Gets created along with the database and is compulsory for every database. Stores the Data Dictionary and parts of programs.

2- Non-System tablespace: Are created by database administrators (DBAs) and users.

 

The 2 types of Extent options when creating TableSpaces

1- Dictionary Managed TableSpace (DMT): Extent Size is managed by the Data Dictionary. This type of TS is not generally recommended.

2- Locally Managed TableSpace (LMT): A TS where where the Extend Size is managed locally (using a bitmap structure). When a TS management option hasn’t been selected while creating it, it becomes a LMT by default.

A LMT that has been created using a UNIFORM SIZE of 128K & a database block size of 2K will have a corresponding Extent block size of 64K.

An Extent size of 128K corresponds to 1 bit and a bit’s status (whether it’s empty or full) can be determined using the bitmap.

SegmentExtenAndDataBlocks

Reminder:

The empty areas in the manual segment’s space are managed using the bitmap in the Freelist Automatic Space Management.

The Segment Space Management is set to Automatic by default and is more efficient. There is no need to coalesce the free areas.

There are two options for managing the size of an Extent.

1. To have it automatically managed by the dbms. Using the AUTOALLOCATE option, starting from 64KB Oracle can automatically adjust the values to a maximum of 64MB.

2. By entering an amount in KB or MB using the [b]UNIFORM[/b] option. Once a UNIFORM size has been set whilst creating a database it cannot later be changed. If an Extent size hasn’t manually been entered, Oracle automatically assigns a value of 1MB.

If all the extents in the database are, on average, the same size then it is recommended to manually set the extent size using the uniform option.

An example of where Extent sizes are important, in terms of performance, are situations where the data held in extents are frequently queried – having smaller extent sizes will negatively impact performance. If you plan on setting the sizes of the extents in Oracle, the values below are recommended according to database usage.

64 KB small segments

1 MB medium segments

63 MB large segments

If the average growth rate is not known while creating the database and if all segments in the database will not be the same size then it is recommended to use the AUTOALLOCATE option.

The diagram below summarizes the mentioned points:

Table Space Management

The type of Segment Space Management is set while creating the database, it isn’t possible to later change this option and the setting applies to all the database segments.

LOCAL UNIFORM: There needs to be a minimum of 5 available database blocks to select this option.

LOCAL AUTOALLOCATE: With this option, if the database block size is 16K or more, the DBMS sets a minimum block size of approximately 64K and creates a minimum of 5 database blocks.

The previous 2 codeblocks were used to create the 2 tablespaces using different parameters, they both utilize locally managed extents (as it is the default option.)

TABLESPACE_NAME SALES ACCOUNTING SHIPPING
BLOCK_SIZE 8192 8192 8192
INITIAL_EXTENT 65536 65536 65536
NEXT_EXTENT
MIN_EXTENTS 1 1 1
MAX_EXTENTS 2147483645 2147483645 2147483645
PCT_INCREASE
MIN_EXTLEN 65536 65536 65536
STATUS ONLINE ONLINE ONLINE
CONTENTS PERMANENT PERMANENT PERMANENT
LOGGING LOGGING LOGGING LOGGING
FORCE_LOGGING NO NO NO
EXTENT_MANAGEMENT LOCAL LOCAL LOCAL
ALLOCATION_TYPE SYSTEM SYSTEM SYSTEM
PLUGGED_IN NO NO NO
SEGMENT_SPACE_MANAGEMENT AUTO AUTO AUTO
DEF_TAB_COMPRESSION DISABLED DISABLED DISABLED
RETENTION NOT APPLY NOT APPLY NOT APPLY
BIGFILE NO NO NO

The previous codeblock created a tablespace using the local uniform parameter, but as a size wasn’t specified, a default amount of 1MB has been applied.

TABLESPACE_NAME SALES ACCOUNTING PATIENTS
BLOCK_SIZE 8192 8192 8192
INITIAL_EXTENT 65536 65536 1048576
NEXT_EXTENT 1048576
MIN_EXTENTS 1 1 1
MAX_EXTENTS 2147483645 2147483645 2147483645
PCT_INCREASE 0
MIN_EXTLEN 65536 65536 1048576
STATUS ONLINE ONLINE ONLINE
CONTENTS PERMANENT PERMANENT PERMANENT
LOGGING LOGGING LOGGING LOGGING
FORCE_LOGGING NO NO NO
EXTENT_MANAGEMENT LOCAL LOCAL LOCAL
ALLOCATION_TYPE SYSTEM SYSTEM UNIFORM
PLUGGED_IN NO NO NO
SEGMENT_SPACE_MANAGEMENT AUTO AUTO AUTO
DEF_TAB_COMPRESSION DISABLED DISABLED DISABLED
RETENTION NOT APPLY NOT APPLY NOT APPLY
BIGFILE NO NO NO

 

BigFile TableSpace

A BigFile TableSpace is a type of TS that was introduced with Oracle 10G. It can contain a single very large datafile, a 32TB datafile using a block size of 8K and a 128TB datafile using a block size of 32K.

The following points should be considered when considering using BigFile TableSpaces:

• They were developed for use in Automatic Storage Management or ASM or other expandable disk architectures, such as RAID.

• The DBMS creates a smallfile tablespace by default.

• By using the ALTER TABLESPACE syntax users can perform operations on a whole tablespace rather than individual datafiles, this is known as datafile transparency.

 

It is not advisable to use BigFile TS in the following scenarios:

• Storage structures that don’t use striping, i.e. storing data sequentially on different physical devices.

• In operating systems that don’t support large file sizes.

 

BigFile TSs are only supported in Locally Managed TSs that use Automatic Segment Space Management, with 3 exceptions:

• Undo TS

• Temporary TS

• System TS

 

Table_Space SSM
SYSTEM MANUAL
UNDOTBS1 MANUAL
SYSAUX AUTO
TEMP MANUAL
USERS AUTO
UNDOTBS2 MANUAL
EXAMPLE AUTO
SALES AUTO
ACCOUNTING AUTO
TEMP02 MANUAL
SHIPPING AUTO
PATIENTS AUTO

 

Creating a Bigfile TS:

If the default TS option has been configured as bigfile then there is no need to enter a BIGFILE parameter. However if a smallfile TS is to be created then a SMALLFILE parameter should be entered.

It is possible to change the size of the TS.

Using the above statement the TS can be configured to grow automatically, depending on requirements.

The BigFile column in the TS indicates whether the TS is a BigFile or not.

 

Temporary TableSpace

The temporary tablespace store (temporary) session data and is used for sorting and indexing operations as well as for user queries. A temp TS is created for every user that can be configured during creation or later (CREATE USER, ALTER USER).

Permanent/non-volatile objects cannot be created in a Temp TS which is composed of temporary tables.

If a database has been created according to the recommended settings then by default a temporary TS is created for all the users, similarly in a temporary TS can be created afterwards if it hasn’t been created along with a new database.

A number of users can also use a common tablespace to share information.

With an extent size setting of “uniform” and 64K, Oracle writes data to the Program Global Area (PGA) in 64KB stacks which improves performance. In data warehouses the suggested extent size is 1MB.

Creating more than a single TS and sharing it among users is another method of increasing performance.

• V$SORT_SEGMENT

• V$TEMPSEG_USAGE

• V$TEMPFILE

• V$DATAFILE

• DBA_TEMP_FILES

• DBA_DATA_FILES

 

Usage information can be determined through querying from the views.

The default values in the temporary TS can be viewed using the previous line.

 

TABLESPACE_NAME DEVELOPERTEMP
BLOCK_SIZE 8,192
INITIAL_EXTENT 1,048,576
NEXT_EXTENT 1,048,576
MIN_EXTENTS 1
MAX_EXTENTS
PCT_INCREASE 0
MIN_EXTLEN 1,048,576
STATUS ONLINE
CONTENTS TEMPORARY
LOGGING NOLOGGING
FORCE_LOGGING NO
EXTENT_MANAGEMENT LOCAL
ALLOCATION_TYPE UNIFORM
PLUGGED_IN NO
SEGMENT_SPACE_MANAGEMENT MANUAL
DEF_TAB_COMPRESSION DISABLED
RETENTION NOT APPLY
BIGFILE NO

As can be seen in this table, all Extents are locally managed and have a uniform size of 1MB.

 

To change the Uniform Size:

This code creates a bigfile tablespace.

An ALTER TABLESPACE syntax cannot change a permanent TS to a temporary TS. It can only be used to add datafiles and to switch a tempfile offline/online.

The same can be accomplished using the code below.

It should be noted that what has been made offline is the datafile and not the temporary TS in which it is held. To view the statuses the v$tempfile can be used.

To change the size of the tempfile.

To cancel the tempfile and to delete the datafile.

Changing the name and location:

1. Take the TS offline.

2. Moved from the operating system and/or change its name.

3. Update the control file using the “alter database rename file” syntax.

 

Temporary TableSpace Group

Temporary TableSpace Group is a feature, introduced with 10G, that enables users in different sessions to concurrently use the temporary TS.

• Formed out of a minimum of 1 TS, no maximum amount.

• The group automatically gets deleted if all group members are deleted.

• The same name is in the group name field of the TS for all group members.

• The temporary TS name and the group name that a member belongs to can not be identical.

• Members can be assigned the group name instead of the temporary TS.

 

Benefits of using a Temporary TS Group:

• Stops congestion by preventing more than one TS being used simultaneously.

• Perfomance benefits from spreading TS across several physical disks.

• Increases the productivity of many temporary TS running in parallel.

• One user can access more than one TS at the same time.

 

Creating TableSpaces

The following code changes the default temp tablespace.

Deleting the Temporary TS with the datafile.

This statement cancels the datafile and erases it from the system.

The alertlog error entry (reminder : “tail -f /u01/app/oracle/admin/orcl/bdump/alert_orcl.log“)

When queried

the DBMS outputs the following error:

First, remove temp01 from the default temporary group:

Then:

From now on running select * from dba_tablespace_groups; will not produce an error message.

Using the previous syntax, a temporary TS called temp01 is created as well as a temporary group called tempgroup1, temp01 is included in tempgroup01.

 

A temporary TS must first be created before being added to a group. The code below assigns a temporary tablespace to a group:

Assigning a default temporary TS to a group:

Assigning a user to a default temporary group:

Re-assigning a user to a temporary tablespace

 

alter user user_name temporary tablespace tempgrup01;

 

 

To view temporary tablespace information:

To view the users as well as the temporary tablespaces that they have been assigned to.

As tempgroup_1 has been assigned to a default temporary TS, the users within the group have also been assigned the properties of tempgroup_1.

 

Creating a NonStandard Block Size

TableSpaces can be created from different DB_BLOCK_SIZE settings than what are in the initialization file, spfile. This feature allows moving TSs between databases with different block sizes.

DN_nK_CACHE_SIZE must have at least one assigned value in the initialization parameters.

Nologging

If TableSpace was made using the nologging parameter then transactions within the TS will not be redo capable and subsequently won’t be able to be recovered using RMAN (Recovery Manager) Flashback.

If a table is accidentally deleted it can be recovered by copying the last export. The advantage of this method of recovery is that it has a positive impact on the database’s performance as there aren’t any Redo records.

To prevent TableSpace problems due to a database being in Nologging mode, creating a TS in FORCE LOGGING mode configures it to create RedoLogs in all conditions.

Shifting a TS in FORCE LOGGING mode to another database, which isn’t in FORCE LOGGING mode, will disable its FORCE LOGGING.

Online/Offline

While a database is in open mode, users that have alter tablespace or manage tablespace privileges can take TSs online or offline.

Offline TSs can’t be accessed by users, this doesn’t affect access to other TSs.

• When offline backups are required (a backup can be made while the TS is online)

• While an application is loading or being updated

• While a TS is being renamed or moved (relocated)

• If there is a problem with a datafile

 

The following types of TableSpaces cannot be taken offline:

• System

• Undo

• Temporary

A TS can be taken offline using 3 types of parameters.

• normal : Doesn’t require media recovery.

• temporary : Used if a TS can’t be taken offline. May require recovery media.

• immediate : The available option when the above parameters can’t be applied. Requires media recovery and can only be used in archive mode.

 

Read-Only

New data can’t be entered or new objects can’t be created into a TS in Read-Only mode, but the TS can be queried and dropped.

A Bigfile TS should be put into read-only mode before being backed up to external storage devices or removable media such as DVDs. In read-only mode a TS can also be moved to other databases.

The following points should be considered when switching a tablespace into read only mode.

• As changes are made to datafile headers during backup, a datafile should not be in an online backup process.

• All undo transactions should be completed.

• The tablespace to be placed into read-only mode must not be SYSTEM or active undo tablespaces.

A variety of insert operations have been made on the SALES tablespace without being committed.

alter tablespace sales read only; was run and remains in that state.

alter tablespace sales offline temporary; is hanging

alter tablespace sales offline immediate; is hanging

The following queries can be used to find transactions that are in read-only mode

USERNAME SID SERIAL# START_TIME NAME
SYS 141 1949 02/12/10 14:59:23 SYSTEM
SYS 140 251 02/12/10 15:38:30 SYSTEM
SYS 141 1949 02/12/10 14:59:23 _SYSSMU11$
SYS 140 251 02/12/10 15:38:30 _SYSSMU11$
SYS 141 1949 02/12/10 14:59:23 _SYSSMU12$
SYS 140 251 02/12/10 15:38:30 _SYSSMU12$
SYS 141 1949 02/12/10 14:59:23 _SYSSMU13$
SYS 140 251 02/12/10 15:38:30 _SYSSMU13$
SYS 141 1949 02/12/10 14:59:23 _SYSSMU14$

 

 

 

 

 

 

 

 

When the following code is run, all alter tablespace sales offline… queries will now appear as alter tablespace altered.

Note: It is recommended to set the read_only_open_delayed parameter, in databases that use large TSs, to true.

This allows read-only TSs to be read before checking the datafiles while starting a database.

 

• Datafiles, belonging to lost or faulty read-only TS, aren’t checked while the the database opens. They are read when first accessed.

“Alter system check datafiles” “Alter tab … online” “Alter database datafile … online”, “alter database noarchivelog” statements don’t control read-only datafiles.

v$recover_file, v$backup, v$datafile_header, v$datafile and v$revover_log cannot access read-only TSs.

 

Note: Whatever data the “recover database”, “alter database open resetlogs” commands write to the initialization file, access to read-only datafiles will still remain. To prevent access to the datafiles, they should be taken offline.

If there are backup control files in use, read-only datafiles could create issues so should be taken into consideration.

 

Adding a Datafile

Alternatively:

Renaming

Names of the Permanent TSs, where user data is stored, and Temporary TSs can be changed.

The DBMS is able to change the data dictionary control file and datafile headers, in the sales TS, but cannot change a TableSpace’s ID.

In addition to the name that a user gives a tablespace, Oracle also assigns an ID. To change a tablespace’s name:

• A database should be compatible with version 10 or above (initialization parameter compatible = 10.0 and above).

• System and sysaux TS cannot be renamed (will produce a message error).

• It isn’t possible to rename an offline TS or datafile (will produce a message error).

• A read-only TS can be renamed, however the operation will enter various error messages into the alertlog file. Read write operations afterwards will be done using the new name.

After TS has been renamed using Undo, it also needs to be changed in the parameter file.

 

Drop

Users that have Drop TS privileges can drop (delete) unused TSs as well as contained objects. Care should be taken and a user should ensure that the objects in a TS won’t be needed before it is deleted. A full backup before and after a TS is deleted is strongly recommended.

 

The syntax below deletes the TS, but the datafile will remain as an operating system file. If needed, the datafile can be deleted through operating system commands.

The code below completely deletes a TableSpace.

A TS containing an active segment, i.e. containing rollback or table undo data, cannot be deleted. It is strongly recommended taking a TS offline before deleting it.

 

Sysaux Tablespace

Introduced in 10G, a sysaux tablespace is a feature that supports a System TS. It contains data such as: Workspace Manager, Logical Standby, Oracle Streams and Automatic Workload Repository. The entire list can be viewed in v$sysaux_occupants.

In situations where a sysaux can’t be accessed, if it is damaged for instance, a database can still carry out its main functions but will be limited to the activities that can be done outside of the sysaux tablespace contents.

Sysaux components can be imported or exported to other TSs using PL/SQL. This is especially useful for components such as the Automatic Workload Repository (AWR) which uses a lot of space.

 

DataFile Management

Oracle assigns 2 different numbers to a datafile, an Absolute File Number and a Relative File Number.

Absolute: An absolute datafile is assigned a unique number. This unique number can be used in SQL Syntax instead of a long datafile name. This information can be accessed in the file_id column in dba_data_files and dba_temp_files as well as the “file#” column in v$datafile and v$tempfile.

Relative: This is also a unique number. In small and medium-sized databases the absolute and relative numbers are the same, in large databases they need to be different.

 

Before setting up Oracle, the amount of required TableSpaces and Datafiles should be calculated and the operating system should be configured accordingly. The reason for this is so that the restrictions due to an operating system may require the altering the settings of a database.

In the spfile, used for initialization, the db_files parameter specifies the maximum number of datafiles. By default, this amount is 200 and the maximum is the amount that can be supported by the operating system. If the parameter value is changed the database would have to be restarted.

Adding a Datafile

The syntax below shows how to keep on increasing the size of a datafile to accomodate the growth of objects, to a maximum size of 100M.

Cancelling the automatic datafile increase.

Changing the size

A datafile can be resized, according to the disksize and operating system, to a bigger amount or a smaller amount. However attempting to shrink the datafile below the amount used by data would give an error.

 

Modifying access to a datafile

Datafiles and tempfiles can be taken offline or put online individually, they can’t be accessed while in offline mode.

This feature allows the following:

• Backups to be made in an offline mode.

• A datafile should first be made offline if it is to be renamed or moved to a different path.

• If Oracle encounters a write problem, it takes the datafile offline in order to solve the issue.

• If a datafile gets corrupted or becomes inaccessible by the system, it would be necessary to take it offline in order to start the instance.

A datafile within a TS in Read_Only mode can also be taken offline or online, a Read-Only mode TS would have to be switched to a Read-Write mode for data to be written to a contained datafile.

 

The following code takes all the datafiles within the sales_2009 TableSpace, offline.

Online/Offline in ArchiveLog Mode

By being in ArchiveLog mode, the above method allows changing a datafle without having to make the all datafiles within a TS offline. In the worst case, the datafile would be lost.

 

Online/Offline in NoArchiveLog Mode

Putting the database to NoarchiveLog Mode.

The Offline for drop parameter should be used for a database instance that isn’t in archive mode. A command to execute the drop transaction would be given once the database is taken offline.

Alternatively:

This error is due to the database not being in recovery mode.

 

Renaming and changing the path

It’s necessary to take a full backup before the following operations.

Rename the file using the operating system.

The mv (move) command is widely used for renaming, in Linux operating systems.

It’s possible to change more than one datafile at the same time.

In situations where there may not be enough available disk space or where there are performance issues, a user would want to install a new disk and move all the datafiles there. Installing and mounting a new disk to a system is explained in the Linux section.

 

It is recommended that the new disk is installed in a directory named salests within the /u01/app/oracle/oradata/orcl/ folder and mounted there.

The code below moves the datafile to a new location in the operating system. To prevent any type of operating system error the datafile is first copied. If successfully copied the original copy is later deleted.

It is also possible to simultaneuosly move more than one datafile.

This syntax makes the database accessible again.

Any unused datafile, i.e. doesn’t contain extents can be deleted. (Requires a database to be in Open Mode)

Data dictionaries containing control file data, used by sales_200902_tbs.dbf are deleted and so is the datafile in the operating system.

 

Similarly the temp datafile can also be deleted.

This code does the same operation, but differs in this way: If developertemp02.dbf was the last datafile in a temporary TableSpace the ALTER TABLESPACE line wouldn’t have carried out the operation.

 

ALTER DATABASE would immediately have deleted the datafile, closing and reopening the datafile wouldn’t cause any issues, however there would be a Temporary TS without a datafile and the following error would be written to the alert file.

Alternatively, if these temporary tablespaces are no longer

needed, then they can be dropped.

Empty temporary tablespace: DEVELOPERTEMP