tablespaces

Tablespaces:

A database is divided into one or more logical storage units called tablespaces. Oracle stores data logically in tablespaces and physically in datafiles associated with the corresponding tablespace.

An Oracle database consists of one or more logical storage units called tablespaces, which collectively store all of the database's data, Each tablespace in an Oracle database consists of one or more files called datafiles, which are physical structures.

 Tablespaces are divided into logical units of storage called segments, which are further divided into extents. Extents are a collection of contiguous blocks.

Add More Space for a Database

The size of a tablespace is the total size of the all datafiles that corresponds to the tablespace. The size of a database is the collective size of the tablespaces that constitute the database.

You can enlarge a database in three ways:

·       Add a datafile to a tablespace

·       Add a new tablespace

·       Increase the size of a datafile

When you add another datafile to an existing tablespace, you increase the amount of disk space allocated for the corresponding tablespace.

Type of Tablespaces:

SYSTEM Tablespace: Every Oracle database contains a tablespace named SYSTEM, which Oracle Database creates automatically when the database is created. The SYSTEM tablespace is always online when the database is open. The SYSTEM tablespace always contains the data dictionary tables for the entire database.

SYSAUX tablespace: The SYSAUX tablespace is an auxiliary tablespace to system tablespace The largest portion of the SYSAUX tablespace is occupied by the Automatic Workload Repository (AWR).

UNDO tablespace: Undo tablespaces are special tablespaces used for storing undo information. When the first DML operation is run within a transaction, the transaction is bound assigned to an undo segment in the current undo tablespace.

An undo tablespace cannot be dropped if it is being used by any instance.

USER tablespace: it is default tablespace for users.

TEMP tablespace: Temporary tablespaces are used to manage space for database sort operations and for storing global temporary tables. For example, if you join two large tables, and Oracle cannot do the sort in memory (see SORT_AREA_SIZE initialisation parameter), space will be allocated in a temporary tablespace for doing the sort operation.

More tablespaces:

A very small database may need only the SYSTEM tablespace; however, Oracle recommends that you create at least one additional tablespace to store user data separate from data dictionary information. This gives you more flexibility in various database administration operations and reduces contention among dictionary objects and schema objects for the same datafiles.

A database administrator can perform the following actions:

·       Create new tablespaces

·       Add datafiles to tablespaces

·       Set and alter default segment storage settings for segments created in a tablespace

·       Make a tablespace read only or read/write

·       Make a tablespace temporary or permanent

·       Rename tablespaces

·       Drop tablespaces

·       Transport tablespaces across databases and platforms

VIEWS related to tablespaces and datafiles

è dba_tablespaces, user_tablespaces

è dba_data_files, dba_temp_files

è v$tablespace

è dba_segments, user_segments

è  dba_free_space, user_free_space

Creating tablespace:

SQL> create tablespace test1 datafile '/u01/app/oracle/oradata/test001.dbf' size 50M;

Creating tablespace with auto extended on.

Sql>CREATE TABLESPACE ts_something DATAFILE '/u01/app/oracle/ts_sth.dbf' SIZE 32M AUTOEXTEND ON NEXT 32M MAXSIZE 2048M;

To check the tablespace created.

SQL> set lines 300

SQL> col FILE_NAME for a50

SQL> set pages 100

SQL> select tablespace_name,file_name,bytes/1024/1024 from dba_data_files;

To check the max size of datafiles;

SQL>  select tablespace_name,file_name,bytes/1024/1024,maxbytes/1024/1024 from dba_data_files;

Adding datafiles to tablespaces:

SQL> alter tablespace test1 add datafile '/u01/app/oracle/oradata/test002.dbf' size 50M;

Resizing datafiles

SQL> alter database datafile '/u01/app/oracle/oradata/test001.dbf' resize 100M;

To alter maxsize

SQL> alter database datafile '/u01/app/oracle/oradata/test003.dbf' autoextend on maxsize 6G;

Creating undo tablespace:

Sql>CREATE UNDO TABLESPACE undots1DATAFILE '/u01/app/oracle/oradata/undotbs_1a.dbf'SIZE 10M AUTOEXTEND ON;

Creating Temporary tablespace:

Sql>CREATE TEMPORARY TABLESPACE temp_mtr TEMPFILE '/dbf1/mtr_temp01.dbf' SIZE 32M AUTOEXTEND ON NEXT 32M MAXSIZE 2048M;

è Note, a temporary tablespace has tempfiles, not datafiles.

Droping datafiles:

Sql>alter tablespace<ts name> drop datafile ‘<filename>’;

Droping tablespaces;

Sql>DROP TABLESPACE <TSname>;

Including datafiles in OS level

Sql>DROP TABLESPACE <TSname> INCLUDING CONTENTS AND DATAFILES;

Move or Rename a datafile do the following.

->   offline datafile.

->   Rename the physical file on the OS.

->   Issue the ALTER DATABASE RENAME FILE command to rename the file within the Oracle dictionary.

->  recover datafile.
-> put the datafile online.

Sql>alter datafile '/u01/app/oracle/oradata/orcl/users01.dbf' offline;

Sql>exit

$mv  /u01/app/oracle/oradata/orcl/users01.dbf /u01/app/oracle/oradata/users02.dbf

$sqlplus / as sysdba

Sql>Alter database rename file ‘u01/app/oracle/oradata/orcl/users01.dbf’ to              ‘/u01/app/oracle/oradata/users02.dbf’

Sql>recover datafile '/u01/app/oracle/oradata/orcl/users01.dbf';

Sql>alter datafile '/u01/app/oracle/oradata/orcl/users01.dbf' online;

Extent management:

 Whenever a segment will be full and needs extra space then oracle automatically allocates space in the forms of extents(collection of continuous blocks) .How this extent will be allocated and What will be size of this newly allocated extent is decided by extent management according to storage parameters. You can define storage parameters (initial,next,minextents,maxextents,pctincrease...) in case of dictionary managed tablespace(DMTS) but not for locally managed tablespaces(LMTS). In LMTS we have options of AUTOALLOCATE  and UNIFORM.

SQL> select tablespace_name, extent_management, allocation_type from dba_tablespaces;

Dictionary Managed Tablespaces (DMT):

Oracle use the data dictionary (tables in the SYS schema) to track allocated and free extents for tablespaces that is in "dictionary managed" mode. Free space is recorded in the SYS.FET$ table, and used space in the SYS.UET$ table.

SQL> CREATE TABLESPACE ts1 DATAFILE '/oradata/ts1_01.dbf' SIZE 50M EXTENT MANAGEMENT DICTIONARY DEFAULT STORAGE (INITIAL 50K NEXT 50K MINEXTENTS 2 MAXEXTENTS 50 PCTINCREASE 0);

Locally Managed Tablespaces (LMT):

Using LMT, each tablespace manages it's own free and used space within a bitmap structure stored in one of the tablespace's data files.

To know extent management of tablespaces; from 10g onwards by default it will create LMT.

NOTE: we cannot create dictionary tablespace if system tablespace is locally managed tablespace.

What Is Undo?

Every Oracle Database must have a method of maintaining information that is used to roll back, or undo, changes to the database. Such information consists of records of the actions of transactions, primarily before they are committed. These records are collectively referred to as undo.

Undo records are used to:

·       Roll back transactions when a ROLLBACK statement is issued

·       Recover the database

·       Provide read consistency

·       Analyze data as of an earlier point in time by using Oracle Flashback Query

·       Recover from logical corruptions using Oracle Flashback features

When a ROLLBACK statement is issued, undo records are used to undo changes that were made to the database by the uncommitted transaction. During database recovery, undo records are used to undo any uncommitted changes applied from the redo log to the datafiles. Undo records provide read consistency by maintaining the before image of the data for users who are accessing the data at the same time that another user is changing it.

Oracle provides a fully automated mechanism, referred to as automatic undo management, for managing undo information and space. In this management mode, you create an undo tablespace, and the server automatically manages undo segments and space among the various active sessions.

You set the UNDO_MANAGEMENT initialization parameter to AUTO to enable automatic undo management.

UNDO tablespace management:

We assign an undo tablespace to an instance in one of two ways:

·       At instance startup. You can specify the undo tablespace in the initialization file or let the system choose an available undo tablespace.

·       While the instance is running. Use ALTER SYSTEM SET UNDO_TABLESPACE to replace the active undo tablespace with another undo tablespace.

Undo retention:

     After a transaction is committed, undo data is no longer needed for rollback or transaction recovery purposes. However, for consistent read purposes, long-running queries may require this old undo information for producing older images of data blocks. Furthermore, the success of several Oracle Flashback features can also depend upon the availability of older undo information. For these reasons, it is desirable to retain the old undo information for as long as possible.

To set the undo retention period:

Set UNDO_RETENTION in the initialization parameter file (value in seconds).

UNDO_RETENTION = 1800

Change UNDO_RETENTION at any time using the ALTER SYSTEM statement:

ALTER SYSTEM SET UNDO_RETENTION = 2400;

Retention Guarantee

To guarantee the success of long-running queries or Oracle Flashback operations, you can enable retention guarantee. If retention guarantee is enabled, the specified minimum undo retention is guaranteed; the database never overwrites unexpired undo data even if it means that transactions fail due to lack of space in the undo tablespace.

Sql> ALTER TABLESPACE <undo tbs> retention guarantee.

Sql> ALTER TABLESPACE <undo tbs> retention noguarantee.

You can determine the current retention period by querying the TUNED_UNDORETENTION column of the V$UNDOSTAT view. This view contains one row for each 10-minute statistics collection interval over the last 4 days. (Beyond 4 days, the data is available in the DBA_HIST_UNDOSTAT view.)TUNED_UNDORETENTION is given in seconds.

Sql>select to_char(begin_time, 'DD-MON-RR HH24:MI') begin_time,to_char(end_time, 'DD-MON-RR HH24:MI') end_time, tuned_undoretention from v$undostat order by end_time;

BEGIN_TIME      END_TIME        TUNED_UNDORETENTION

--------------- --------------- -------------------

04-FEB-05 00:01 04-FEB-05 00:11               12100

      ...                                         

07-FEB-05 23:21 07-FEB-05 23:31               86700

07-FEB-05 23:31 07-FEB-05 23:41               86700

07-FEB-05 23:41 07-FEB-05 23:51               86700

07-FEB-05 23:51 07-FEB-05 23:52               86700

You can decide how many seconds based on the TUNED_UNDORETENTION.