Archive for the ‘Storage Architecture’ Category

Oracle Database Architecture

 

Database Architecture                   

An Oracle database server consists of an Oracle database and an Oracle instance.

Oracle Database architecture is based on physical and logical structures. 

 

Oracle Database Physical Structure

Oracle database consists of physical files to store information. Basically there are three types of physical files required for database:

Core Files:

  • Data Files                    Contains all the data of database
  • Control Files              Contains data about database(metadata)
  • Redo Log Files          Contains all changes made to data, allows recovery

Other Files

  • Parameter File         Defines how instance will be configured when it starts up
  • Password File           Allow users to connect remotely to the database
  • Archive Log Files   Contains history of redo log files
  • Trace Files                 Contains messages written by server and background process
  • Alert File                    Special trace file, chronological log of messages and errors              

 

 

Oracle Database Logical Structure

Oracle database is divided into logical storage units known as Tablespaces, which can be used to group related logical structures together. Logical structure mainly consists of following structures starting with the bigger unit and going down to the smallest.

Tablespace

Each database is logically divided into one or more tablespaces. One or more datafiles are explicitly created for each tablespace to physically store the data of all logical structures in a tablespace. The combined size of the datafiles in a tablespace is the total storage capacity of the tablespace.

Segment

Tablespace consists of one or more segments. A segment is a set of extents allocated for a certain logical structure stored in the same tablespace. The segments can be of one of following types:

  • Data segment  (stores user data within the database)
  • Index Segment (store Indexes)
  • Temporary Segment ( stores rollback segment)
  • Rollback/Undo segment (created when sql statement needs a temporary work area)

Extent

An extent is a specific number of contiguous data blocks, obtained in a single allocation, used to store a specific type of information in one data file.

Data Blocks

Oracle database data is stored in data blocks(at the lowest logical level).  One data block corresponds to a specific number of bytes of physical database space on disk(default 8K). The standard block size is specified by the DB_BLOCK_SIZE initialization parameter.

Oracle Data blocks are the smallest unit of I/O in the database and they are mapped to OS block on the storage device

 

 

 

Oracle Instance

Oracle instance is comprised of memory structures and background processes that are required to perform different tasks to keep the instance and database running. Instance does not exist until database is started, which involves reading initialization parameter file and configuring instance according to the information within the parameter file.

Memory Structures

Instance has two major memory structures:

Background Processes

Oracle creates a set of background processes for an instance that  manage the memory structures, asynchronously perform I/O to write data to disk, and do general housekeeping.
The most common background processes are the following:

  • System monitor (SMON): Performs crash recovery when the instance starts after a failure
  • Process monitor (PMON): Performs process cleanup when a user process fails
  • Database writer (DBWn): Writes modified blocks from the database buffer cache to the files on disk
  • Checkpoint (CKPT): Signals DBWn at checkpoints and updates all of the data files and control files of the database to indicate the most recent checkpoint
  • Log writer (LGWR): Writes redo log entries to disk
  • Archiver (ARCn): Copies the redo log files to archival storage when the log files are full or a log switch occurs

 

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;

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

Redo Logs

Redo log files provide the means to redo transactions in the event of a database failure. Every transaction is written synchronously to the redo log files in order to provide a recovery mechanism in case of media failure. (With exceptions such as: direct loads and direct reads done with the NOLOGGING option.) This includes transactions that have not yet been committed, undo segment information, and schema and object management statements. Redo log files are used in a situation such as an instance failure to recover committed data that has not been written to the data files. The redo log files are used only for recovery.

Online Redo Log Groups
• A set of identical copies of online redo log files is called an online redo log group.
• The LGWR background process concurrently writes the same information to all online
redo log files in a group.
• The Oracle server needs a minimum of two online redo log file groups for the normal
operation of a database.


Online Redo Log Members

  • Each online redo log file in a group is called a member.
  • Each member in a group has identical log sequence numbers and the same size.
  • The log sequence number is assigned each time the Oracle server starts writing to a log group to identify each redo log file uniquely.
  • The current log sequence number is stored in the control file and in the header of all data files.

 

How Redo Logs Work
The redo entries are written from the redo log buffer to one of the online redo log
groups called the current online redo log group by the LGWR process. LGWR writes under the following situations:

• When a transaction commits
• When the redo log buffer becomes one-third full
• When there is more than a megabyte of changed records in the redo log buffer
• Before the DBWn writes modified blocks in the database buffer cache to the data files

Redo logs are used in a cyclic fashion. Each redo log file group is identified by a log
sequence number that is overwritten each time the log is reused.

Checkpoints
During a checkpoint:

  • A number of dirty database buffers covered by the log being checkpointed are written to the data files by DBWn. The number of buffers being written by DBWn is determined by the FAST_START_MTTR_TARGET parameter, if specified.
  • The checkpoint background process CKPT updates the headers of all data files and control files to reflect that it has completed successfully.

Checkpoints can occur for all data files in the database or for only specific data files.
A checkpoint occurs, for example, in the following situations:
• At every log switch
• When an instance has been shut down with the normal, transactional, or immediate
option
• When forced by setting the initialization parameter FAST_START_MTTR_TARGET.
• When manually requested by the database administrator
• When the ALTER TABLESPACE [OFFLINE NORMAL|READ ONLY|BEGIN
BACKUP] cause checkpointing on specific data files.

ADDING ONLINE REDO LOG MEMBERS

ALTER DATABASE ADD LOGFILE MEMBER
‘$HOME/ORADATA/u04/log1c.rdo’ TO GROUP 1,
‘$HOME/ORADATA/u04/log2c.rdo’ TO GROUP 2,
‘$HOME/ORADATA/u04/log3c.rdo’ TO GROUP 3;

Obtaining Log Group and Member Information
V$LOG

The following query returns information about the online redo log file from the control file:

 
SQL> SELECT group#, sequence#, bytes, members, status
2 FROM v$log;
</span>

<span style="font-family: 'Times New Roman'; font-size: small;">GROUP# SEQUENCE# BYTES MEMBERS STATUS
--------- ---------- -------- --------- ------------------------
1              688                 1048576    1           CURRENT
2              689                 1048576    1           INACTIVE</span>

<span style="font-family: 'Times New Roman'; font-size: small;">

The following items are the most common values for the STATUS column:


  • UNUSED indicates that the online redo log group has never been written to. This is the state of an online redo log file that was just added.
  • CURRENT indicates the current online redo log group. This implies that the online redo log group is active.
  • ACTIVE indicates that the online redo log group is active but is not the current online redo log group. It is needed for crash recovery. It may be in use for block recovery. It may or may not be archived.
  • INACTIVE indicates that the online redo log group is no longer needed for instance recovery. It may or may not be archived.

 

Archived Redo Log Files

  • Archiving redo log files is accomplished by ARCn (Archiver) or manually through SQL statements.
  • An entry in the control file recording the archive log name, log sequence number, and high and low
  • SCN number is made whenever a redo log is successfully archived.
  • A filled redo log file cannot be reused until a checkpoint has taken place and the redo log file has been backed up the ARCn process.
  • Archived redo log files can be multiplexed.
  • Archived redo log files must be maintained by the DBA.

 

COMMANDS

ALTER SYSTEM SWITCH LOGFILE
ALTER SYSTEM CHECKPOINT
ARCHIVE LOG LIST
ALTER DATABASE ADD LOGFILE
ALTER DATABASE ADD LOGFILE MEMBER
ALTER DATABASE RENAME FILE
ALTER DATABASE DROP LOGFILE
ALTER DATABASE DROP LOGFILE MEMBER
ALTER DATABASE CLEAR LOGFILE

Dynamic Performance Views

V$THREAD
V$LOG
V$LOGFILE
V$DATABASE

CONTROL FILE

The control file is a small binary file necessary for the database to start and operate
successfully. Each control file is associated with only one Oracle database. Before a database is opened, the control file is read to determine if the database is in a valid state to use. A control file is updated continuously by the Oracle server during database use, so it must be available for writing whenever the database is open.

The control file is a binary file that defines the current state of the physical database..
• Loss of the control file requires recovery
• Is read at MOUNT stage
• Is required to operate
• Is linked to a single database
• Should be multiplexed
• Maintains integrity of database
• Sized initially by CREATE DATABASE

Control File Contents

The information in the control file includes:
• Database name is taken from either the name specified by the initialization parameter
DB_NAME or the name used in the CREATE DATABASE statement.
• Database identifier is recorded when the database is created.
• Time stamp of database creation is also recorded at database creation.
• Names and locations of associated data files and online redo log files are updated when
a data file or redo log is added to, renamed in, or dropped from the database.
• Tablespace information is updated as tablespaces are added or dropped.
• Redo log history is recorded during log switches.
• Location and status of archived logs are recorded when archiving occurs.
• Location and status of backups are recorded by the Recovery Manager utility.
• Current log sequence number is recorded when log switches occur.
• Checkpoint information is recorded as checkpoints are made.

 

Obtaining Control File Information

The SHOW PARAMETERS command can also be used to find the location of the control files.

show parameters control_files;

SELECT name FROM V$CONTROLFILE;

Information in several of the other dynamic performance views is obtained from the control
file:
• V$BACKUP
• V$DATAFILE
• V$TEMPFILE
• V$TABLESPACE
• V$ARCHIVE
• V$LOG
• V$LOGFILE
• V$LOGHIST
• V$ARCHIVED_LOG
• V$DATEBASE

The minimum number of days that a reusable record is kept in the controlfile is controlled by the control_file_record_keep_time parameter.

These sections consist of records. The size, total number and number of used record are exposed through v$controlfile_record_section.

Temporary Tablespace DDL

What are TEMPFILES?

Unlike normal data files, TEMPFILEs are not fully initialised (sparse). When you create a TEMPFILE, Oracle only writes to the header and last block of the file. This is why it is much quicker to create a TEMPFILE than to create a normal database file.

Dropping / Recreating Temporary Tablespace Method

Keep in mind that the procedures documented here for dropping and recreating your temporary tablespace should be performed during off hours with no users logged on performing work.If you are working with a temporary tablespace in Oracle8i or a temporary tablespace in Oracle9i that is NOT the default temporary tablespace for the database, this process is straight forward. Simply drop and recreate the temporary tablespace:

SQL>DROP TABLESPACE temp;

SQL> CREATE TEMPORARY TABLESPACE TEMP
TEMPFILE '/u02/oradata/TESTDB/temp01.dbf' SIZE 500M REUSE
AUTOEXTEND OFF
EXTENT MANAGEMENT LOCAL 
UNIFORM SIZE 1M;

Oracle9i Default Temporary Tablespace

In cases where the temporary tablespace you want to resize (using the drop/recreate method) is the default temporary tablespace for the database, you have several more steps to perform, all documented below. The first step you need to perform is create another temporary tablespace (lets call it TEMP2). The next step would be to remove the temporary tablespace you want to resize from being the default temporary tablespace (in our example, this will be a tablespace named TEMP) by making TEMP2 the default. Drop / recreate the TEMP tablespace to the size you want. Finally, make the newly created TEMP tablespace your default temporary tablespace for the database and drop the TEMP2 tablespace. A full example session is provided below:

SQL> CREATE TEMPORARY TABLESPACE temp2
TEMPFILE ‘/u02/oradata/TESTDB/temp2_01.dbf’ SIZE 5M REUSE
AUTOEXTEND ON NEXT 1M MAXSIZE unlimited
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

SQL> DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

SQL> CREATE TEMPORARY TABLESPACE temp
TEMPFILE ‘/u02/oradata/TESTDB/temp01.dbf’ SIZE 500M REUSE
AUTOEXTEND ON NEXT 100M MAXSIZE unlimited
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

SQL> DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;



Drop Tempfile Command Method – (Oracle9i and higher)

If you are using Oracle9i or higher, another method exists that allows you to simply drop a tempfile. As with the above method, this should be performed during off hours with no users logged on performing work.

The first step is to obtain the name of the tempfile to drop. For this example, my temporary tablespace name is TEMP and the name of the tempfile is /u02/oradata/TESTDB/temp01.dbf:

SQL> SELECT tablespace_name, file_name, bytes FROM dba_temp_files WHERE tablespace_name = ‘TEMP’;

SQL> ALTER DATABASE TEMPFILE ‘/u02/oradata/TESTDB/temp01.dbf’ DROP INCLUDING DATAFILES;

SQL> ALTER TABLESPACE temp ADD TEMPFILE ‘/u02/oradata/TESTDB/temp01.dbf’  SIZE 512m 

AUTOEXTEND ON NEXT 250m MAXSIZE UNLIMITED;

Monitoring Temporary Tablespaces and Sorting:

Unlike datafiles, tempfiles are not listed in V$DATAFILE and DBA_DATA_FILES. Use V$TEMPFILE and DBA_TEMP_FILES instead.

One can monitor temporary segments from V$SORT_SEGMENT and V$SORT_USAGE

DBA_FREE_SPACE does not record free space for temporary tablespaces. Use V$TEMP_SPACE_HEADER instead:

select TABLESPACE_NAME, BYTES_USED/1024/1024 “USED_MB”, BYTES_FREE/1024/1024 “FREE_MB” from V$TEMP_SPACE_HEADER;

select tsh.TABLESPACE_NAME,file_name, BYTES_USED/1024/1024 “USED_MB”, BYTES_FREE/1024/1024 “FREE_MB”

from V$TEMP_SPACE_HEADER tsh,dba_temp_files dbf

where tsh.tablespace_name = dbf.tablespace_name;

To Check who is using temp space

select b.tablespace ,b.segfile# ,b.segblk#

,round(((b.blocks*p.value)/1024/1024),2) size_mb

,a.sid ,a.serial# ,a.username ,a.osuser ,a.program ,a.status

from v$session a ,v$sort_usage b ,v$process c ,v$parameter p

where p.name=’db_block_size’

and a.saddr = b.session_addr

and a.paddr=c.addr

order by  b.tablespace,b.segfile#,b.segblk#,b.blocks

IDEVELOPEMT TEMP TBLSPACE

ORAFAQ ON TEMP FILES