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:
Types of Tablespaces
Oracle uses five types of tablespaces:
- Permanent tablespaces
- Undo tablespaces
- Temporary tablespaces
- User tablespace
- Index tablespace
Permanent tablespaces are again two types
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.
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.
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.
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.
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.
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.
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.
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.
|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.|
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
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;
Starting with with Oracle 10g, you may assign a tablespace to a tablespace group:
SQL>ALTER TABLESPACE ts_tablespace TABLESPACE GROUP ts_group_alpha;
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:
The following statement displays the free space in tablespace tabsp_4:
SQL>SELECT BLOCK_ID, BYTES, BLOCK
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 (
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’
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”,
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
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,
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.