Monday , September 25 2017
Home / Uncategorized / Oracle Tablespace Management

Oracle Tablespace Management

WHAT IS MEANT BY TABLE SPACE?
Table space is nothing but logical representation of data in oracle database. Database is divided into one or more logical storage units called Table spaces. Table spaces are where you store Oracle database objects such as tables, indexes and rollback segments. A table space is made up of one or more database data files. The total amount of storage space available in a table space is the sum of the physical disk size of all the data files associated with that table space (less some system overhead).

Oracle Table space Diagram:

Oracle Tablespace Overview

Types of Tablespaces
Oracle uses five types of tablespaces:

  1. Permanent tablespaces
  2. Undo tablespaces
  3. Temporary tablespaces
  4. User tablespace
  5. Index tablespace

1.Permanent tablespaces

Permanent tablespaces are again two types

a)system tablespace

b)sysaux tablespace

a)The System Tablespace
The system tablespace stores the data dictionary and is available whenever a database is open. Unlike other tablespaces, it cannot be taken offline.

b)The Sysaux Tablespace
Also new with Oracle 10g is the sysaux tablespace. The sysaux tablespace is used to store database components that were previously stored in the system tablespace in releases prior to version 10g.

2.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. Each database contains zero or more undo tablespaces. In automatic undo management mode, each Oracle instance is assigned one (and only one) undo tablespace. Undo data is managed within an undo tablespace using undo segments that are automatically created and maintained by Oracle.

3.Temporary Tablespace

Temporary tablespaces are used to manage space for database sort and joining operations and for storing global temporary tables. For joining two large tables or sorting a bigger result set, Oracle cannot do in memory by using SORT_AREA_SIZE in PGA (Programmable Global Area). Space will be allocated in a temporary tablespace for doing these types of operations.

Note: Oracle 11g has a new feature of shrinking temporary tablespace using SHRINK SPACE or SHRINK TEMPFILE.
It is much easier and better way of reclaiming space rather than dropping and re-creating the tablespace with the smaller size.

4.USER TABLESPACE

This table space contains actual data or permanent data which is generated by the users in the organization. This tablespace contains both committed and un committed data’s.

5.INDEX TABLESPACE:

Index tablespaces contains index of the tables and views.

Note: Every tablespace should contain at least one datafile. But a datafile should not belongs to two tablespace. Any tablespace can be offline except system tablespaces. Any tablespace can be dropped except default tablespace.

Advantages of having different Tablespaces

* Separation of user data from data dictionary data (which is in System Table space)
* Separation of one application data from another.
* Store different Tablespaces datafiles on separate disk drives to reduce I/O contention
* Take individual Tablespaces offine while others remain online.
* Backup individual Tablespaces leaving the rest to for user access.

Bigfile Tablespaces

A bigfile tablespace is a tablespace with a single, but very large (up to 4G blocks) datafile. Traditional smallfile tablespaces, in contrast, can contain multiple datafiles, but the files cannot be as large. The benefits of bigfile tablespaces are the following:

  • A bigfile tablespace with 8K blocks can contain a 32 terabyte datafile. A bigfile tablespace with 32K blocks can contain a 128 terabyte datafile. The maximum number of datafiles in an Oracle Database is limited (usually to 64K files). Therefore, bigfile tablespaces can significantly enhance the storage capacity of an Oracle Database.
  • Bigfile tablespaces can reduce the number of datafiles needed for a database. An additional benefit is that the DB_FILES initialization parameter and MAXDATAFILES parameter of the CREATE DATABASE and CREATE CONTROLFILE statements can be adjusted to reduce the amount of SGA space required for datafile information and the size of the control file.
  • Bigfile tablespaces simplify database management by providing datafile transparency. SQL syntax for the ALTER TABLESPACE statement lets you perform operations on tablespaces, rather than the underlying individual datafiles.

Bigfile Tablespaces are supported only for locally managed tablespaces with automatic segment-space management, with three exceptions: locally managed undo tablespaces, temporary tablespaces, and the SYSTEM tablespace can be bigfile Tablespaces even if their segments are manually managed.

Note: A Bigfile tablespace can be created with the size of up to 8 terabytes. Normal Tablespaces are up to 2 terabytes. This feature is available from oracle 10g onwards.

Locally Managed Tablespaces

Locally managed tablespaces track all extent information in the tablespace itself by using bitmaps, resulting in the following benefits:

  • Concurrency and speed of space operations is improved, because space allocations and deallocations modify locally managed resources (bitmaps stored in header files) rather than requiring centrally managed resources such as enqueues
  • Performance is improved, because recursive operations that are sometimes required during dictionary-managed space allocation are eliminated
  • Readable standby databases are allowed, because locally managed temporary tablespaces (used, for example, for sorts) are locally managed and thus do not generate any undo or redo.
  • Space allocation is simplified, because when the AUTOALLOCATE clause is specified, the database automatically selects the appropriate extent size.
  • User reliance on the data dictionary is reduced, because the necessary information is stored in file headers and bitmap blocks.
  • Coalescing free extents is unnecessary for locally managed tablespaces.

All tablespaces, including the SYSTEM tablespace, can be locally managed.

The DBMS_SPACE_ADMIN package provides maintenance procedures for locally managed tablespaces.

Dictionary-Managed Tablespaces

The default for extent management when creating a tablespace is locally managed. However, you can explicitly specify a dictionary-managed tablespace. For dictionary-managed tablespaces, the database updates the appropriate tables in the data dictionary whenever an extent is allocated or freed for reuse.

The tablespace has the following characteristics:

  • The data of the new tablespace is contained in a single datafile, 50M in size.
  • The tablespace is explicitly created as a dictionary-managed tablespace by specifying EXTENT MANAGEMENT DICTIONARY.
  • The default storage parameters for any segments created in this tablespace are specified.

Tablespace Groups:

A tablespace group enables a user to consume temporary space from multiple tablespaces. A tablespace group has the following characteristics:

  • It contains at least one tablespace. There is no explicit limit on the maximum number of tablespaces that are contained in a group.
  • It shares the namespace of tablespaces, so its name cannot be the same as any tablespace.
  • You can specify a tablespace group name wherever a tablespace name would appear when you assign a default temporary tablespace for the database or a temporary tablespace for a user.

You do not explicitly create a tablespace group. Rather, it is created implicitly when you assign the first temporary tablespace to the group. The group is deleted when the last temporary tablespace it contains is removed from it.

Using a tablespace group, rather than a single temporary 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. A tablespace group enables parallel execution servers in a single parallel operation to use multiple temporary tablespaces.

The view DBA_TABLESPACE_GROUPS lists tablespace groups and their member tablespaces.

Note: A datafile can be moved from one physical location to another physical location (or) A datafile can be renamed. This feature is available from oracle 9i onwards.

Viewing Tablespace Information

The following data dictionary and dynamic performance views provide useful information about the tablespaces of a database.

View Description
V$TABLESPACE Name and number of all tablespaces from the control file.
DBA_TABLESPACES, USER_TABLESPACES Descriptions of all (or user accessible) tablespaces.
DBA_TABLESPACE_GROUPS Displays the tablespace groups and the tablespaces that belong to them.
DBA_SEGMENTS, USER_SEGMENTS Information about segments within all (or user accessible) tablespaces.
DBA_EXTENTS, USER_EXTENTS Information about data extents within all (or user accessible) tablespaces.
DBA_FREE_SPACE, USER_FREE_SPACE Information about free extents within all (or user accessible) tablespaces.
V$DATAFILE Information about all datafiles, including tablespace number of owning tablespace.
V$TEMPFILE Information about all tempfiles, including tablespace number of owning tablespace.
DBA_DATA_FILES Shows files (datafiles) belonging to tablespaces.
DBA_TEMP_FILES Shows files (tempfiles) belonging to temporary tablespaces.
V$TEMP_EXTENT_MAP Information for all extents in all locally managed temporary tablespaces.
V$TEMP_EXTENT_POOL For locally managed temporary tablespaces: the state of temporary space cached and used for by each instance.
V$TEMP_SPACE_HEADER Shows space used/free for each tempfile.
DBA_USERS Default and temporary tablespaces for all users.
DBA_TS_QUOTAS Lists tablespace quotas for all users.
V$SORT_SEGMENT Information about every sort segment in a given instance. The view is only updated when the tablespace is of the TEMPORARY type.
V$TEMPSEG_USAGE Describes temporary (sort) segment usage by user for temporary or permanent tablespaces.

 LAB SESSIONS

Creating Tablespaces
Tablespaces are created using the CREATE TABLESPACE command as shown below:

SQL>CREATE TABLESPACE <tablespace_name> datafile ‘path’ size 100M;
The only mandatory parameter in the CREATE TABLESPACE statement is the tablespace name, but there are many options that can be used to fine-tune the tablespace.

This CREATE TABLESPACE statement creates a tablespace 150 megabytes in size named “ts_tablespace”, with the tablespace file itself named ‘ts_tablespace.dbf’, located on the C: drive, under the directory ‘\data\’:

SQL>CREATE TABLESPACE ts_tablespace DATAFILE ‘\u01\data\ts_tablespace.dbf’ SIZE 150M ;

Note: By default auto extent on feature is not enable. We have to enable it manually.

Autoextend on option:  Creating a tablespace with autoextend on alleviates the potential problem of a database outage if the tablespace reaches maximum extents.  You create a tablespace using autoextend with the autoextend on option.  Using “create tablespace . . . autoextend on” will push the point of failure to the OS filesystem, which is good because you never want you database to hang because a tablespace cannot grow.  With autoextend on, you only need to monitor the free space in  the filesystem directory.

  • Maxsize: This is the maximum size that the datafile can grow.  We recommend creating a tablespace with maxsize unlimited to prevent a lock-up.
  • Size: This is the size of the underlying data file for the tablespace.
  • Blocksize: Specifying a larger blocksize will not result in a larger tablespace.  See here on creating tablespaces with non-default blocksizes.
  • Uniform size:  This specifies the size that the data file will grow when it needs to extend.

By default, an Oracle tablespace will not grow if it runs out of space. If you use up all your space, you are just out of luck unless you use the autoextend keyword to indicate that the tablespace may grow, on it’s own, dynamically. Here is an example of creating a tablespace that is set to autoextend:

Note: A Tablespace can be renamed. This feature is available from oracle 10g onwards

STANDARD TABLESPACE CREATION
SQL>create tablespace myusers datafile   ‘/u01/app/oracle/oradata/booktst_users_01.dbf’
size 50m blocksize 32k maxsize 100m
uniform size 10M
autoextend on;

Dropping A Tablespace
Dropping a tablespace is done using the DROP TABLESPACE command as shown below. Note that dropping a tablespace produces a structural change to the database that cannot be reversed without a backup.

SQL>DROP TABLESPACE ts_tablespace;

The following statement drops the users tablespace and its associated datafiles:

SQL>DROP TABLESPACE users INCLUDING CONTENTS AND DATAFILES;

Quotas on Tablespaces
Users can be assigned quotas (limits) on on the space they may use for tablespaces. This quota can be set using the ALTER USER QUOTA command as shown below:

SQL>ALTER USER RAVI QUOTA 200M ON ts_tablespace;
Tablespace Groups
Starting with with Oracle 10g, you may assign a tablespace to a tablespace group:

SQL>ALTER TABLESPACE ts_tablespace TABLESPACE GROUP ts_group_alpha;
Renaming Tablespaces
Another feature that became available with Oracle 10g is the ability to rename a tablespace “on-the-fly”:

SQL>ALTER TABLESPACE ts_tablespace RENAME TO ts_old_tablespace;
Read-only tablespaces can be renamed, but the system and sysaux tablespace cannot be renamed.

Monitoring Free spaces

The following views provide information on the free space in a tablespace:

  • DBA_FREE_SPACE
  • DBA_FREE_SPACE_COALESCED

The following statement displays the free space in tablespace tabsp_4:

SQL>SELECT BLOCK_ID, BYTES, BLOCK
FROM DBA_FREE_SPACE
WHERE TABLESPACE_NAME = ‘TABSP_4’
ORDER BY BLOCK_ID;
BLOCK_ID        BYTES          BLOCKS
————      ———-      — ——
2                           16384                 2
4                           16384                 2
6                           81920               10
16                         16384                 2
27                         16384                 2
29                         16384                 2
31                         16384                 2
33                         16384                 2
35                         16384                 2
37                          16384                2
39                          8192                  1
40                          8192                  1
41                          196608            24

This view shows that there is adjacent free space in tabsp_4 (for example, blocks starting with BLOCK_IDs 2, 4, 6, 16) that has not been coalesced. After coalescing the tablespace using the ALTER TABLESPACE statement shown previously, the results of this query would read:

BLOCK_ID       BYTES        BLOCKS
———-        ———-     ———-
2                         131072         16
27                       311296         38

2 rows selected.

The DBA_FREE_SPACE_COALESCED view displays statistics for coalescing activity. It is also useful in determining if you need to coalesce space.

Altering Tablespace Availability

Taking Tablespaces Offline

SQL>ALTER TABLESPACE users OFFLINE NORMAL;

Bringing Tablespaces Online

SQL>ALTER TABLESPACE users ONLINE;

 Taking Tablespaces Read-Only

SQL>ALTER TABLESPACE flights READ ONLY;

Making a Read-Only Tablespace Writable

SQL>ALTER TABLESPACE flights READ WRITE;

 To change the size of a datafile:

SQL> ALTER DATABASE DATAFILE ‘ /diskl/oradata/DEMO/user1_demo01.dbf’ RESIZE 10M;

 To extend the size of a datafile automatically:
SQL> ALTER DATABASE DATAFILE ‘ /diskl/oradata/DEMO/user1_demo02.dbf’
AUTOEXTEND ON NEXT lM MAXSIZE 20M;

 To assign table to a specific datafile:

SQL> ALTER TABLE junk ALLOCATE EXTENT
(DATAFILE ‘/diskl/oradata/DEMO /user1_demo01.dbf’);

how to identify read writr status of the datafile:

SQL>Select name,enadled from v$datafile

To drop a datafile:
SQL> ALTER DATABASE DROP DATAFILE ‘/disk1/oradata/user1_demo01.dbf’;
NOTE: You cannot drop a datafile containg data. Datafile should be empty.

Creating a Bigfile Tablespace

SQL>CREATE BIGFILE TABLESPACE bigtbs

DATAFILE ‘/u02/oracle/data/bigtbs01.dbf’ SIZE 50G

Creating a Dictionary-Managed Tablespace

The following statement creates the dictionary-managed tablespace tbsa:

SQL>CREATE TABLESPACE tbsa

DATAFILE ‘/u02/oracle/data/tbsa01.dbf’ SIZE 50M

EXTENT MANAGEMENT DICTIONARY

DEFAULT STORAGE (

INITIAL 50K

NEXT 50K

MINEXTENTS 2

MAXEXTENTS 50

PCTINCREASE 0);

 Creating a locally-Managed Tablespace

SQL>CREATE TABLE SPACE lmtbsb DATA FILE ‘/u02/oracle/data/lmtbsb01.dbf’ SIZE 50M

EXTENT MANAGEMENT LOCAL AUTO ALLOCATE;

Creating a Table space Group

SQL>CREATE TEMPORARY TABLE SPACE lm temp2 TEMPFILE ‘/u02/oracle/data/lmtemp201.dbf’

SIZE 50M

TABLESPACE GROUP group1;

Listing Table spaces and Default Storage Parameters

To list the names and default storage parameters of all table spaces in a database, use the following query on the DBA_TABLESPACES view:

SQL>SELECT TABLESPACE_NAME “TABLESPACE”,

INITIAL_EXTENT “INITIAL_EXT”,

NEXT_EXTENT “NEXT_EXT”,

MIN_EXTENTS “MIN_EXT”,

MAX_EXTENTS “MAX_EXT”,

PCT_INCREASE

FROM DBA_TABLESPACES;

TABLESPACE      INITIAL_EXT       NEXT_EXT       MIN_EXT       MAX_EXT       PCT_INCREASE

———-              —————        ————        ———-        ————      —————–

RBS                             1048576                1048576                    2                       40                         0

SYSTEM                    106496                  106496                      1                        99                         1

TEMP                         106496                  106496                      1                        99                        0

TESTTBS                   57344                     16384                        2                        10                        1

USERS                        57344                      57344                        1                        99                       1

Listing the Datafiles and Associated Tablespaces of a Database

To list the names, sizes, and associated tablespaces of a database, enter the following query on the DBA_DATA_FILES view:

SQL>SELECT FILE_NAME, BLOCKS, TABLESPACE_NAME

FROM DBA_DATA_FILES;

FILE_NAME                                                                     BLOCKS          TABLESPACE_NAME

————                                                                        ———-             ——————-

/U02/ORACLE/IDDB3/DBF/RBS01.DBF                        1536                             RBS

/U02/ORACLE/IDDB3/DBF/SYSTEM01.DBF               6586                        SYSTEM

/U02/ORACLE/IDDB3/DBF/TEMP01.DBF                    6400                         TEMP

/U02/ORACLE/IDDB3/DBF/TESTTBS01.DBF             6400                       TESTTBS

/U02/ORACLE/IDDB3/DBF/USERS01.DBF                   384                            USERS

Displaying Statistics for Free Space (Extents) of Each Tablespace

To produce statistics about free extents and coalescing activity for each tablespace in the database, enter the following query:

SQL>SELECT TABLESPACE_NAME “TABLESPACE”, FILE_ID,

COUNT(*)   “PIECES”,

MAX(blocks) “MAXIMUM”,

MIN(blocks) “MINIMUM”,

AVG(blocks) “AVERAGE”,

SUM(blocks) “TOTAL”

FROM DBA_FREE_SPACE

GROUP BY TABLESPACE_NAME, FILE_ID;

TABLESPACE        FILE_ID        PIECES       MAXIMUM         MINIMUM       AVERAGE        TOTAL

—————       ———-        ———     ————-       ————       ————       ——–

RBS                                 2                       1                     955                     955                      955                 955

SYSTEM                        1                        1                     119                      119                      119                 119

TEMP                            4                         1                    6399                    6399                   6399             6399

TESTTBS                     5                          5                    6364                      3                        1278             6390

USERS                          3                          1                     363                       363                      363              363

PIECES shows the number of free space extents in the table space file, MAXIMUM and MINIMUM show the largest and smallest contiguous area of space in database blocks, AVERAGE shows the average size in blocks of a free space extent, and TOTAL shows the amount of free space in each table space file in blocks. This query is useful when you are going to create a new object or you know that a segment is about to extend, and you want to make sure that there is enough space in the containing table space.

Read More:
Oracle Architecture and Adminstration
ASM DISK Creations
Disk Group Administration

Comments

comments

Leave a Reply

Your email address will not be published. Required fields are marked *