Oracle has 4 different types of tablespace
- Permanent - uses data files and normally contains the system (data dictionary) and users data
- Temporary - is used to store objects for the duration of a users session, temp files are used to create temporary tablespaces
- Undo - is a permanent type of tablespace that are used to store undo data which if required would undo changes of data by users
- Read only - is a permanent tablespace that can only be read, no writes can take place, but the tablespace can be made read/write.
- System - is a permanent tablespace and contains the vital data dictionary (metadata about the database)
- Sysaux - is an auxiliary tablespaces and contains performance statistics collected by the database.
There are two ways to manage a tablespace
| Locally (default) | Extents are the basic unit of a tablespace and are managed in bitmaps that are kept within the data file header for all the blocks within that data file. For example, if a tablespace is made up of 128KB extents, each 128KB extent is represented by a bit in the extent bitmap for this file, the bitmap values indicate if the extent is used or free. The bitmap is updated when the extent changes there is no updating on any data dictionary tables thus increasing performance. Extents are tracked via bitmaps not using recursive SQL which means a performance improvement. Locally managed tablespaces cannot be converted into a dictionary managed one. The benefits of using a local managed tablespace
|
| Dictionary Managed | The extent allocation is managed by the data dictionary and thus updating the extent information requires that you access the data dictionary, on heavy used systems this can cause a performance drop. extents are tracked via FET$ and UET$ using recursive SQL. Dictionary managed tablespaces can be converted to a locally managed one. |
- Local tablespaces are the default in oracle 10g
- A dictionary tablespace can be changed into a local table but a local tablespace cannot be changed into a dictionary one
- If the system tablespace is locally managed then you can only create locally managed tablespaces, trying to create a dictionary one will fail
- Local tablespaces are better in performance than dictionary managed tablespaces as you have to constantly check the data dictionary during the course of extent management (called recursive SQL).
Anytime an object needs to grow in size space is added to that object by extents. When you are using locally managed tablespaces there are two options that the extent size can be managed
| Autoallocate (default) | This means the extent will vary in size, the first extent starts at 64k and progressively increased to 64MB by the database. The database automatically decides what size the new extent will be based on segment growth patterns. Autoallocate is useful if you aren't sure about growth rate of an object and you let oracle decide. |
| Uniform | Create the extents the same size by specifying the size when create the tablespace. This is default for temporary tablespace but not available for undo tablespaces. Be careful with uniform as it can waste space, use this option you are know what the growth rate of the objects are going to be. |
Segment space management is how oracle deals with free space with in an oracle data block. The segment space management you specify at tablespace creation time applies to all segments you later create in the tablespace.
Oracle uses two methods to deal with free space
| Manual | Oracle manages the free space in the data blocks by using free lists and a pair of storage parameters PCTFREE and PCTUSED. When the block reaches the PCTUSED percentage the block is then removed from the freelist, when the block falls below the PCTFREE threshold the block is then placed back on the freelist. Oracle has to perform a lot of hard work maintaining these lists, a slow down in performance can occur when you are making lots of changes to the blocks as Oracle needs to keep checking the block thresholds. |
| Automatic (default) | Oracle does not use freelist when using automatic mode, Instead oracle uses bitmaps. A bitmap which is contained in a bitmap block, indicates whether free space in a data block is below 25%, between 25%-50%, between 50%-75% or above 75%. For an index block the bitmaps can tell you whether the blocks are empty or formatted. Bitmaps do use additional space but this is less than 1% for most large objects. The performance gain from using automatic segment management can be quite striking. |
Tablespaces can be either small tablespaces or big tablespaces
- Small tablespace - The tablespace can be made up of a number of data files each of which can be quite large in size
- Big tablespace - The tablespace will only be made up of one data file and this can get extremely large.
| Creating | create tablespace test datafile 'c:\oracle\test.dbf' size 2G; create tablespace test datafile 'c:\oracle\test.dbf' size 2G extent management local uniform size 1M maxsize unlimited; create bigfile tablespace test datafile 'c:\oracle\bigfile.dbf' 2G; |
| Creating non-standard block size | ## Note: if the block is different than db_block_size then make sure to set a db_nk_cache_size alter system db_16k_cache_size = 5M; create tablespace test datafile 'c:\oracle\test.dbf' size 2G blocksize 16K; |
| Removing | drop tablespace test; drop tablespace test including contents and datafiles; (removes the contents and the physical data files) |
| Modifying | alter tablespace test rename to test99; alter tablespace test [offline|online]; alter tablespace test [read only|read write]; alter tablespace test [begin backup | end backup]; Note: use v$backup to see tablespace is in backup mode (see below) |
| Adding data files | alter tablespace test add datafile 'c:\oracle\test02.dbf' 2G; |
| Dropping data files | alter tablespace test drop datafile 'c:\oracle\test02.dbf'; |
| Autoextending | See Datafile commands below |
| Rename a data file | ## offline the tablespace then rename at O/S level, then peform below alter tablespace test rename datafile 'c:\oracle\test.dbf' to 'c:\oracle\test99.dbf'; |
| Tablespace management | create tablespace test datafile 'c:\oracle\test.dbf' size 2G extent management manual; |
| Extent management | create tablespace test datafile 'c:\oracle\test.dbf' size 2G uniform size 1M maxsize unlimited; |
| Segment Space management | create tablespace test datafile 'c:\oracle\test.dbf' size 2G segment space management manual; |
| Display default tablespace | select property_value from database_properties where property_name = 'DEFAULT_PERMANENT_TABLESPACE'; |
| Set default tablespace | alter database default tablespace users; |
| Display default tablespace type | select property_value from database_properties where property_name = 'DEFAULT_TBS_TYPE'; |
| Set default tablespace type | alter database set default bigfile tablespace; alter database set default smallfile tablespace; |
| Get properties of an existing tablespace | set long 1000000 select DBMS_METADATA.GET_DDL('TABLESPACE','USERS') from dual; |
| Free Space | select tablespace_name, round(sum(bytes/1024/1024),1) "FREE MB" from dba_free_space group by tablespace_name; |
| Display backup mode | select tablespace_name, b.status from dba_data_files a, v$backup b where a.file_id = b.file#; |
Useful Views
| |
| DBA_TABLESPACES | describes all tablespaces in the database |
| DBA_DATA_FILES | describes database files |
| DBA_TABLESPACE_GROUPS | describes all tablespace groups in the database |
| DBA_SEGMENTS | describes the storage allocated for all segments in the database |
| DBA_FREE_SPACE | describes the free extents in all tablespaces in the database |
| V$TABLESPACE | displays tablespace information from the control file |
| V$BACKUP | displays the backup status of all online datafiles |
| DATABASE_PROPERTIES | lists Permanent database properties |
| Resizing | alter database datafile 'c:\oracle\test.dbf' resize 3G; |
| Offlining | alter database datafile 'c:\oracle\test.dbf' offline; Note: you must offline the tablespace first |
| Onlining | alter database datafile 'c:\oracle\test.dbf' online; |
| Renaming | alter database rename file 'c:\oracle\test.dbf' to 'c:\oracle\test99.dbf'; |
| Autoexend | alter database datafile 'c:\oracle\test.dbf' autoextend on; alter database datafile 'c:\oracle\test.dbf' autoextend off; select file_name, autoextensible from dba_data_files; |
Temporary tablespaces
Temporary tablespaces are used for order by, group by and create index. It is required when the system tablespace is locally managed. In oracle 10g you can now create temporary tablespace groups which means you can use multiple temporary tablespaces simultaneously.
The benefits of using a temporary tablespace group are
- SQL queries are less likely to run out of space
- You can specify multiple default temporary tablespaces at the db level
- Parallel execution can utilize multiple temporary tablespaces
- single user can simultaneously use multiple temp tablespaces in different sessions.
| Creating non temp group | create temporary tablespace temp tempfile 'c:\oracle\temp.dbf' size 2G autoextend on; |
| Creating temp group | create temporary tablespace temp tempfile 'c:\oracle\temp.dbf' size 2G tablespace group ''; |
| Adding to temp group | alter tablespace temp02 tablespace group tempgrp; Note: if no group exists oracle will create it |
| Removing from temp group | alter tablespace temp02 tablespace group ''; |
| Displaying temp groups | select group_name, tablespace_name from dba_tablespace_groups; |
| Make user use temp group | alter user vallep temporary tablespace tempgrp; |
| Display default temp tbs | select property_value from database_properties where property_name = 'DEFAULT_TEMPORARY_TABLESPACE'; select property_value from database_properties where property_name = 'DEFAULT_TEMP_TABLESPACE'; |
| set default temp tbs | alter database default temporary tablespace temp02; |
| Display free temp space | select tablespace_name, sum(bytes_used), sum(bytes_free) from v$temp_space_header group by tablespace_name; |
| Who is using temp segments | SELECT b.tablespace, ROUND(((b.blocks*p.value)/1024/1024),2)||'M' "SIZE", a.sid||','||a.serial# SID_SERIAL, a.username, a.program FROM sys.v_$session a, sys.v_$sort_usage b, sys.v_$parameter p WHERE p.name = 'db_block_size' AND a.saddr = b.session_addr ORDER BY b.tablespace, b.blocks; |
Useful Tables Views
| |
| DBA_TEMP_FILES | describes database temporary files |
| DBA_TABLESPACE_GROUPS | describes all tablespace groups in the database |
| V$SORT_SEGMENT | contains 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 segment usage |
Undo Tablespaces
Undo tablespaces are used to store original data after it has been changed, if a user decides to rollback a change the information in the undo tablespace is used to put back the data in its original state.
Undo tablespaces are used for the following
- Rolling back transactions explicitly with a ROLLBACK command
- Rolling back transactions implicitly (automatic instance recovery)
- Reconstructing read-consistent image of data
- Recovering from logical corruptions
| Creating | create undo tablespace undotbs02 datafile ' c:\oracle\undo01.dbf' size 2G; |
| set default | alter system set undo_tablespace='undotbs02'; |
Tablespace quotas
You can assign a user tablespace quota thus limiting to a certain amount of storage space within the tablespace. By default a user has none when the account is first created, see users for information on tablespace quotas.
Tablespace Alerts
The MMON daemon checks tablespace usage every 10 mins to see if any thresholds have been exceeded and raises any alerts. There are two types of alerts warning (low space warning) and critical (action should be taken immediately). Both thresholds can be changed via OEM or DBMS_SERVER_ALERT package.
Oracle Managed Files
Oracle can make file handling a lot easier by managing the oracle files itself, there are three parameters that can be set so that oracle will manage the data, temp, redo, archive and flash logs for you
- DB_CREATE_FILE_DEST - sets the default location of the data/temp files
- DB_CREATE_ONLINE_LOG_DEST_n - sets the default location of the redo, archived log files and controlfiles.
- DB_RECOVERY_FILE_DEST - sets the default location of the flashback logs.
| setting db_create_file_dest | alter system set db_create_file_dest=':c\oracle\data' scope=both; |
| setting db_create_online_log_dest | alter system set db_create_online_log_dest_n='c:\oracle\archive' scope=both; |
| Creating | create tablespace user01; |
| Removing | drop tablespace user01; |
| Adding datafile | alter tablespace user01 add datafile 1G; |
Tablespace logging can be overridden by logging specification at the table-level.
