Archive for the ‘Tablespaces’ Category

UNDO Tablespace

Undo Segment

An undo segment is used to save the old value (undo data) when a process changes data in a database. It stores the location of the data and the data as it existed before being modified. The header of an undo segment contains a transaction table where information about the current transactions using the undo segment is stored.
A serial transaction uses only one undo segment to store all of its undo data.
Many concurrent transactions can write to one undo segment.

Initialization Parameter
UNDO_MANAGEMENT
If AUTO, use automatic undo management. The default is MANUAL.

UNDO_TABLESPACE
An optional dynamic parameter specifying the name of an undo tablespace. This parameter should be used only when the database has multiple undo tablespaces and you want to direct the database instance to use a particular undo tablespace.

Transaction Rollback
When a transaction modifies a row in a table, the old image of the modified columns (undo data) is saved in the undo segment. If the transaction is rolled back, the Oracle server restores the original values by writing the values in the undo segment back to the row.

Transaction Recovery
If the instance fails while transactions are in progress, the Oracle server needs to undo any uncommitted changes when the database is opened again. This rollback is part of transaction recovery. Recovery is possible only because changes made to the undo segment are also protected by the redo log files.

Read Consistency
While transactions are in progress, other users in the database should not see any uncommitted changes made by these transactions. In addition, a statement should not see any changes that were committed after the statement begins execution. The old values (undo data) in the undo segments are also used to provide the readers a consistent image for a given statement.

Read Consistency
The Oracle server guarantees that a statement sees data from a consistent time, even if that data is modified by other transactions.
When the Oracle server begins executing a SELECT statement, it determines the current system change number (SCN) and ensures that any changes not committed before this SCN are not processed by the statement. Consider the case where a long-running query is executed at a time when several changes are being made. If a row has changes that were not committed at the start of the query, the Oracle server constructs a read-consistent image of the row by retrieving the before image of the changes from the undo segment and applying the changes to a copy of the row in memory.

TYPES of UNDO Segment

• SYSTEM: Used for objects in the SYSTEM
tablespace
• Non-SYSTEM: Used for objects in other
tablespaces:
– Auto Mode: Requires an UNDO tablespace
– Manual Mode:
– Private: Acquired by a single instance
– Public: Acquired by any instance
• Deferred: Used when tablespaces are taken offline
immediate, temporary, or for recovery

Dropping an UNDO Tablespace
• The DROP TABLESPACE command drops an UNDO
tablespace.
• An UNDO tablespace can only be dropped if it is
currently not in use by any instance.
• To drop an active UNDO tablespace:
– Switch to a new UNDO tablespace
– Drop the tablespace after all current transactions
are complete

You can drop tablespace UNDOTBS after all transactions within the tablespace are complete.
To determine whether any active transactions exists use the following query:
SQL> SELECT a.name,b.status
FROM v$rollname a, v$rollstat b
WHERE a.name IN ( SELECT segment_name
FROM dba_segments
WHERE tablespace_name = ‘UNDOTBS’
)  AND a.usn = b.usn;
NAME                        STATUS
————————- —————
_SYSSMU4$           PENDING OFFLINE

Automatic Undo Management:

UNDO_SUPPRESS_ERRORS
     Set to TRUE, this parameter suppresses errors while attempting to  execute  manual operations in AUTO mode.
– UNDO_RETENTION
Controls the amount of undo data to retain for consistent read

Determines how long to retain undo data to provide for consistent reads. Retaining undo data longer allows for longer queries and also requires larger data files for the UNDO tablespace.
The UNDO_RETENTION parameter, defined in seconds, can be set in the initialization file or modified dynamically with an ALTER SYSTEM command.

ALTER SYSTEM SET UNDO_RETENTION=900;

A value of 900 retains undo data for 15 minutes.

Sizing an UNDO Tablespace

Determining a size for the UNDO tablespace requires
three pieces of information
• (UR) UNDO_RETENTION in seconds
• (UPS) Number of undo data blocks generated per second
• (DBS) Overhead varies based on extent and file size (db_block_size)

        UndoSpace = [UR * (UPS * DBS)] + (DBS * 24)

SELECT (UR * (UPS * DBS)) + (DBS * 24) AS "Bytes"
FROM (SELECT value AS UR
            FROM v$parameter
            WHERE name = ‘undo_retention’),
      (SELECT (SUM(undoblks)/SUM(((end_time –
            begin_time)*86400))) AS UPS
            FROM v$undostat),
      (SELECT value AS DBS
            FROM v$parameter
            WHERE name = ‘db_block_size’);

Divide the result by 1048576 to get size in MB.

Data Dictionary Views

– DBA_ROLLBACK_SEGS
• Dynamic Performance Views
– V$ROLLNAME
– V$ROLLSTAT
– V$UNDOSTAT
– V$SESSION
– V$TRANSACTION

SELECT segment_name,owner,tablespace_name,status
FROM dba_rollback_segs;

SEGMENT_NA  OWNER   TABLESPACE  STATUS
——————— ———–   ——————-  ——
SYSTEM               SYS          SYSTEM         ONLINE
_SYSSMU1$     PUBLIC     UNDO1          ONLINE

Join the V$ROLLSTAT and V$ROLLNAME views to obtain the statistics of the undo segments currently used by the instance.

SELECT n.name, s.extents, s.rssize,s.hwmsize,
s.xacts, s.status
FROM v$rollname n, v$rollstat s
WHERE n.usn = s.usn;

 

To check the use of a undo segment by currently active transactions, join the
V$TRANSACTION and V$SESSION views:

SELECT s.username, t.xidusn, t.ubafil,  t.ubablk, t.used_ublk
FROM v$session s, v$transaction t
WHERE s.saddr = t.ses_addr;

Advertisements

Automatic segment space management

Automatic Segment-Space Management

• Bitmap segments contain a bitmap that describes
the status of each block in the segment with
respect to its available space.
• The map is contained in a separate set of blocks
referred to as bitmapped blocks (BMBs).
• When inserting a new row, the server searches the
map for a block with sufficient space.
• As the amount of space available in a block
changes, its new state is reflected in the bitmap.

Automatic segment-space management can be
enabled at the tablespace level only, for locally
managed tablespaces.
• After a tablespace is created, the specifications
apply to all segments created in the tablespace.

CREATE TABLESPACE data02
DATAFILE ‘/u01/oradata/data02.dbf’ SIZE 5M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64K
SEGMENT SPACE MANAGEMENT AUTO;

Tablespaces

Tablespaces
The data in an Oracle database are stored in tablespaces.
• An Oracle database can be logically grouped into smaller logical areas of space known
as tablespaces.
• A tablespace can belong to only one database at a time.
• Each tablespace consists of one or more operating system files, which are called data files.
• A tablespace may consist of zero or more segments.
• Tablespaces can be brought online while the database is running.
• Except for the SYSTEM tablespace or a tablespace with an active undo segment, tablespaces can be taken offline, leaving the database running.
• Tablespaces can be switched between read-write and read-only status.

Data Files
• Each tablespace in an Oracle database consists of one or more files called data files.
These are physical structures that conform with the operating system on which the Oracle server is running.
• A data file can belong to only one tablespace.
• An Oracle server creates a data file for a tablespace by allocating the specified amount of disk space plus a small amount of overhead.
• The database administrator can change the size of a data file after its creation or can specify that a data file should dynamically grow as objects in the tablespace grow.

Segments
• A segment is the space allocated for a specific logical storage structure within a tablespace. For example, all of the storage allocated to a table is a segment.
• A tablespace may consist of one or more segments.
• A segment cannot span tablespaces; however, a segment can span multiple data files that belong to the same tablespace.
• Each segment is made up of one or more extents.

Extents
Space is allocated to a segment by extents.
• One or more extents make up a segment.
– When a segment is created, it consists of at least one extent.
– As the segment grows, extents get added to the segment.
– The DBA can manually add extents to a segment.
• An extent is a set of contiguous Oracle blocks.
• An extent cannot span a data file but must exist in one data file.

Data Blocks
The Oracle server manages the storage space in the data files in units called Oracle blocks or data blocks.
• At the finest level of granularity, the data in an Oracle database is stored in data blocks.
• Oracle data blocks are the smallest units of storage that the Oracle server can allocate, read, or write.
• One data block corresponds to one or more operating system blocks allocated from an existing data file.
• The standard data block size for an Oracle database is specified by the
DB_BLOCK_SIZE initialization parameter when the database is created.
• The data block size should be a multiple of the operating system block size to avoid unnecessary I/O.
• The maximum data block size is dependent on the operating system.

 

Advantages of Locally Managed Tablespaces
Locally managed tablespaces have the following advantages over dictionary-managed tablespaces:
• Local management avoids recursive space management operations, which can occur in dictionary-managed tablespaces if consuming or releasing space in an extent results in another operation that consumes or releases space in a undo segment or data dictionary table.
• Because locally managed tablespaces do not record free space in data dictionary tables, it reduces contention on these tables.
• Local management of extents automatically tracks adjacent free space, eliminating the need to coalesce free extents.
• The sizes of extents that are managed locally can be determined automatically by the system. Alternatively, all extents can have the same size in a locally managed
tablespace.
• Changes to the extent bitmaps do not generate undo information because they do not update tables in the data dictionary (except for special cases such as tablespace quota information).

 

Segment Space Management in Locally Managed Tablespaces

When you create a locally managed tablespace using the CREATE TABLESPACE statement, the SEGMENT SPACE MANAGEMENT clause allows you to specify how free and used space within a segment is to be managed.

The keyword auto tells Oracle that you want to use bitmaps to manage the free space within segments. A bitmap, in this case, is a map that describes the status of each data block within a segment with respect to the amount of space in the block available for inserting rows. As more or less space becomes available in a data block, its new state is reflected in the bitmap. Bitmaps allow Oracle to manage free space more automatically, and thus, this form of space management is called automatic segment-space management.

Automatic segment-space management is a simpler and more efficient way of managing space within a segment. It completely eliminates any need to specify and tune the PCTUSED, FREELISTS, andFREELISTS GROUPS storage parameters for schema objects created in the tablespace. If such attributes should be specified, they are ignored.

The following statement creates tablespace lmtbsb with automatic segment-space management:

CREATE TABLESPACE lmtbsb DATAFILE '/u02/oracle/data/lmtbsb01.dbf' SIZE 50M   EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;

Dictionary Managed Tablespaces• Extents are managed in the data dictionary• Each segment stored in the tablespace can have a different storage clause• Coalescing required

CREATE TABLESPACE userdata

DATAFILE ‘/u01/oradata/userdata01.dbf’ SIZE 500M

EXTENT MANAGEMENT DICTIONARY

DEFAULT STORAGE ( initial 1M NEXT 1M );

Undo Tablespace

• Used to store undo segments

• Cannot contain any other objects

• Extents are locally managed

• Can only use the DATAFILE and EXTENT MANAGEMENT clauses of the CREATE TABLESPACE command

CREATE UNDO TABLESPACE undo1

DATAFILE ‘/u01/oradata/undo101.dbf’ SIZE 40M;

 

Temporary Tablespace

 

• Used for sort operations

• Cannot contain any permanent objects

• Locally managed extents recommended

CREATE TEMPORARY TABLESPACE temp

TEMPFILE ‘/u01/oradata/temp01.dbf’ SIZE 500M

EXTENT MANAGEMENT LOCAL UNIFORM SIZE 10M;

 

Dropping Tablespaces

 

• Tablespace removed from data dictionary

• Optionally, contents removed from data dictionary

• OS files can be deleted with the optional AND DATAFILES clause:

DROP TABLESPACE userdata

INCLUDING CONTENTS AND DATAFILES;

 

CHANGING SIZE OF DATAFILE

ALTER DATABASE

DATAFILE ‘/u03/oradata/userdata02.dbf’ RESIZE 200M;

ALTER TABLESPACE app_data

ADD DATAFILE ‘/u01/oradata/userdata03.dbf’ SIZE 200M;

 

Moving Data Files:

ALTER TABLESPACE

• The tablespace must be offline.

• The target data files must exist.

ALTER TABLESPACE userdata

RENAME  DATAFILE ‘/u01/oradata/userdata01.dbf’

TO ‘/u01/oradata/userdata01.dbf’;

ALTER DATABASE

• The database must be mounted.

• The target data file must exist.

ALTER DATABASE RENAME

FILE ‘/u01/oradata/system01.dbf’

TO ‘/u03/oradata/system01.dbf’;

Data dictionary views

DBA_TEMPFILES

DBA_TABLESPACES

DBA_DATA_FILES

V$TABLESPACE

V$TEMPFILE

V$DATAFILE