Tablespace mgmt in 10g.pdf

MANAGING TABLESPACES IN ORACLE 10g

LOGICAL STORAGE STRUCTURES

The logical units of database space allocation are data blocks, extents, segments,

tablespaces. At a physical level the data is stored in data files (disk).



Tablespaces



Segments



Extents 

Blocks

Physically Oracle stores everything in file, called data files. Physical storage is

how Oracle stores the data physically in the system. Whereas logical storage

talks about how an end user actually accesses that data. The logical structures

MANAGING TABLESPACES IN ORACLE 10g

Exploring the Oracle DBA Technology by

Gunasekaran ,Thiyagu

****

INTRODUCTION TO TABLESPACES

LOGICAL

storage units called as tablespaces. It is a logical storage container for

segments.

Segments are database objects, such as tables

& indexes that

consume storage space.

A tablespace is a logical group of one or more data files in a database. It is logical

because a tablespace is not visible in the oracle file system. Oracle database can

contain multiple tablespaces, It can be

ONLINE

(accessible) or

OFFLINE

(not

accessible) whenever the database is open.

The

SYSTEM and

TEMPORARY

tablespaces can NOT be taken offline. Oracle database must have system

&

sysaux tablespaces.

Oracle stores data logically in

TABLESPACES

&

physically in

DATAFILES

(oslevel) associated with the corresponding tablespace. Tablespaces can be

either

SMALLFILE TABLESPACES or BIGFILE TABLESPACES.

MANAGING TABLESPACES IN ORACLE 10g

Three different types of Tablespaces



PERMANENT



TEMPORARY



UNDO

When a database is created, following tablespaces are created by DBCA.

MANAGING TABLESPACES IN ORACLE 10g

Exploring the Oracle DBA Technology by

Gunasekaran ,Thiyagu

EXTENT MANAGEMENT OF TABLESPACES

DICTIONARY MANAGED Vs. LOCALLY MANAGED

Prior to oracle database 8i,

EXTENT

management was always controlled by

data dictionary tables. Oracle 8i introduced a new method of extent

management called

LOCALLY MANAGED TABLESPACES

(LMT)

.

When oracle allocates space to a segment (like a table or index), a group of

contiguous free blocks, called an extent, is added to the segment. Metadata

regarding extent allocation and unallocated extents are either stored in the data

dictionary, or in the tablespace itself.

Tablespaces that record extent allocation in the

data dictionary, are called

DICTIONARY MANAGED

. Tablespaces that record

extent allocation in the

tablespace (datafile) header, are called

LOCALLY MANAGED. DMT

is an

obsolete Technology. Always

LMT

is superior to

DMT

in every respect.

MANAGING TABLESPACES IN ORACLE 10g

DBA need to create tablespaces with

EXTENT

management defined as

either

DICTIONARY

or

LOCAL

. Tablespaces can use any one method to

keep track of free and used space. (dictionary or locally) managed.

SPACE MANAGEMENT

Oracle maintains extents for a tablespace.

Two different methods for oracle to keeping of free and used extents.

They are dictionary managed tablespace

&

locally managed tablespace.

DICTIONARY MANAGED TABLESPACES

Oracle use the data dictionary (tables in SYS schema) to track allocated and

free extents for tablespaces that is in "

DICTIONARY MANAGED

" mode.

Oracle

updates the following tables in the data dictionary whenever an

extent is allocated, or freed for reuse.



Free space is recorded in the SYS.FET$ table.



Used space in the SYS.UET$ table.

CREATING DICTIONARY MANAGED TABLESPACE

SQL> create tablespace test datafile '/u01/test01.dbf' size 10m

Extent management

DICTIONARY;

MANAGING TABLESPACES IN ORACLE 10g

Exploring the Oracle DBA Technology by

Gunasekaran ,Thiyagu

ORA - 12913

Let’s check

SYS.FET$

view

to tablespace TEST (dictionary managed ).

SQL> select * from SYS.FET$ where ts#='6';

(test tablespace).

TS# FILE# BLOCK# LENGTH

6 4 2 1279

SQL> create table tab1(no number , name varchar(10));

Table created.

SQL> begin

2 for i in 1..10000 loop

3 insert into tab1 values (i, 'sam');

4 end loop;

6 end;

7 /

MANAGING TABLESPACES IN ORACLE 10g

Let’s check

SYS.UET$

view

to tablespace TEST (dictionary managed).

SQL> select SEGBLOCK#, EXT#, FILE#, BLOCK#, LENGTH, SEGBLOCK#

from SYS.UET$ where ts#='8';

SEGBLOCK# EXT# FILE# BLOCK# LENGTH SEGBLOCK# 2 0 4 2 5 2

2 1 4 7 5 2 2 2 4 12 10 2

STORAGE PARAMETERS

The storage parameters are NOT valid if extents that are managed locally.

NEXT, INITIAL, PCTINCREASE ,

MINEXTENTS, MAXEXTENTS, DEFAULT STORAGE



Initial Extent Default initial extent size



Next Extent Default incremental extent size



Min Extents Default minimum number of extents



Max Extents Default maximum number of extents

MANAGING TABLESPACES IN ORACLE 10g

Exploring the Oracle DBA Technology by

Gunasekaran ,Thiyagu

ADVANTAGES OF LOCALLY MANAGED TABLESPACES

Do NOT record free space in data dictionary, it reduces contention on these

tables. LMT of extents automatically tracks adjacent free space.

Oracle is managing extents by maintaining a bitmap in each datafile to keep track

of the free or used status of blocks in datafile. Each bit in the bitmap corresponds to a block or a group of blocks. When the extents are allocated or freed for reuse, Oracle simply changes the bitmap values to show the new status of the blocks.

Eliminates the need for recursive SQL operations against the data dictionary

(UET$ &FET$

tables). Reduce contention on data dictionary tables. Locally

managed tablespaces eliminate the need to periodically coalesce free space

(automatically tracks adjacent free space)

POINTS TO REMEMBER

A dictionary tablespace can be changed into a locally managed but a

locally manged tablespace cannot be changed into a dictionary one.

If the

SYSTEM

tablespace is locally managed only create locally managed

tablespaces , trying to create a dictionary one will fail.

DBA CAN USE TABLESPACES TO DO THE FOLLOWING ACTIONS

Create new table spaces

&

data files to table spaces.

Make a table space read only or read/write.

Make a table space temporary or permanent.

MANAGING TABLESPACES IN ORACLE 10g

MIGRATING DMT TO LMT ( USERS TABLESPACE )

SQL> select tablespace_name , extent_management from dba_tablespaces ;

TABLESPACE_NAME EXTENT_MAN

SYSTEM DICTIONARY

SYSAUX LOCAL

TEMP LOCAL

USERS DICTIONARY

SQL> execute dbms_space_admin.tablespace_migrate_to_local(‘

XXXX

’);

SQL> exec dbms_space_admin.tablespace_migrate_to_local(‘TEST’);

MANAGING TABLESPACES IN ORACLE 10g

Exploring the Oracle DBA Technology by

Gunasekaran ,Thiyagu

CONVERTING LMT TO DMT

SQL>execute dbms_space_admin.tablespace_migrate_from_local(‘XXXX’);

SQL>exec dbms_space_admin.tablespace_migrate_to_local(‘TEST’);

PL/SQL procedure successfully completed.

If we create a database with

DBCA ,

it will have a locally managed

SYSTEM

tablespace by default. we cannot create new dictionary managed tablespaces.

Sizes of extents that are managed locally can be determined automatically by

the system. Alternatively, all extents might be the same size in a

LMT

. If we

want to create extents with the same sizes, you need to specify

UNIFORM

.

Changes to the extent bitmaps do NOT generate rollback information because

they do NOT update tables in the data dictionary (except for special cases such

as tablespace quota information). Reduced fragmentation.

MANAGING TABLESPACES IN ORACLE 10g

As I said , see the keyword "autoallocate" why extents are allocated with

different sizes as “autoallocate” specifies extent sizes are system generated. Most

likely our tablespace will be autoalloctate

LMT.

MANAGING TABLESPACES IN ORACLE 10g

Exploring the Oracle DBA Technology by

Gunasekaran ,Thiyagu

Let’s start with two users . Each user is assigned different , different

tablespace. User

ROSE

is assigned to test tablespace(uniform). User

SONA

is assigned to samp tablespace (autoallocate).

MANAGING TABLESPACES IN ORACLE 10g

When creating tablespace test , I have mentioned ‘UNIFORM’ so all extents

sizes are same. We can see bytes column from following screen shot.

MANAGING TABLESPACES IN ORACLE 10g

Exploring the Oracle DBA Technology by

Gunasekaran ,Thiyagu

When creating tablespace samp , I did NOT mention ‘

UNIFORM’

so extents

sizes are

NOT

same. We can see bytes column from following screen shot.

MANAGING TABLESPACES IN ORACLE 10g

TO FIND TABLESPACE PARAMS

I am attaching screen shot for both tablespaces ( test

&

samp).

SQL> SELECT tablespace_name

, contents

, extent_management

, allocation_type

, segment_space_management

FROM dba_tablespaces ;

SQL> select tablespace_name, contents, status, block_size, initial_extent,

extent_namagement, allocation_type, segment_space_management,

bigfile from dba_tablespaces ;

MANAGING TABLESPACES IN ORACLE 10g

Exploring the Oracle DBA Technology by

Gunasekaran ,Thiyagu

LMT

can use either

autoallocate or uniform is all about allocation of new

extents when space pressure increases in the tablespace

UNIFORMLY SIZED EXTENTS - UNIFORM AUTO SIZED EXTENTS - AUTOALLOCATE

AUTO ALLOCATE

M

eans that the extent sizes are managed by Oracle. It will choose the optimal

next size for the extents starting with 64 KB. As the segments grow and more

extents are needed, Oracle starts allocating larger and larger sizes then it moves

to 1Mb , 8MB ultimately to 64Mb extents. We can make initial extent size of

greater than 64KB , it will allocate extents atleast the amount of the space.

MANAGING TABLESPACES IN ORACLE 10g

UNIFORM

Create the extents the same size by specifying the size when create the

tablespace. i.e.

UNIFORM

specifies that the tablespace is managed with

uniform extents of

SIZE

bytes (use K or M to specify the extent size).

When the

SYSTEM

tablespace is dictionary managed other tablespaces can be

managed either LOCALLY (LM) or DICTIONARY (DM). Extent management on

MANAGING TABLESPACES IN ORACLE 10g

Exploring the Oracle DBA Technology by

Gunasekaran ,Thiyagu

MANAGING TABLESPACE

CREATE TABLESPACE

SYNTAX - CREATING A NEW TABLESPACE

SQL> create tablespace <tsname> datafile ‘file path’ size

<size>

m ;

SQL> create tablespace TEST datafile 'test01.ora' size 10m;

SQL> create tablespace TEST datafile ‘/u01/oradata/test02.ora’ size 15m;

Tablespace created.

If path is not specified, by default it is located at dbs directory

ALTER TABLESPACE

SYNTAX - ADDING A NEW DATAFILE

SQL> alter tablespace <tsname> add datafile ‘filepath’ size

<size>

m;

SQL> alter tablespace TEST add datafile 'test03.dbf' size 10m;

SQ> alter tablespace TEST add datafile

'/u01/app/oracle/oradata/samp/test04.dbf' size 10m;

Tablespace altered.

SYNTAX - RESIZING THE EXSISTING DATAFILE

SQL> alter database datafile ‘filepath’ resize

<size>

m;

SQL> alter database datafile 'test01.dbf' resize 20m;

MANAGING TABLESPACES IN ORACLE 10g

DROP TABLESPACE

SYNTAX - DROP THE TABLESPACE

SQL> drop tablespace <TSNAME>;

SQL> drop tablespace <TSNAME> INCLUDING CONTENTS;

SQL> drop tablespace <TSNAME> INCLUDING CONTENTS AND DATAFILES;

SQL> drop tablespace test;

SQL> drop tablespace test including contents;

SQL> drop tablespace test including contents and datafiles;

Tablespace dropped.

RENAME TABLESPACE

SYNTAX RENAMING A EXISTING TABLESPACE

SQL>alter tablespace <OLD TSNAME> rename to <NEW TSNAME>;

SQL> alter tablespace TEST rename to TESTS;

Tablespace altered.

RENAMING DATAFILE

SQL> shutdown normal;

MANAGING TABLESPACES IN ORACLE 10g

Exploring the Oracle DBA Technology by

Gunasekaran ,Thiyagu

II) SYNTAX

SQL>alter database RENAME FILE ‘ old file path’ to 'newfile path’;

SQL>alter database rename file ‘/u01/app/test.dbf ’ to ‘/u01/test.dbf ’ ;

Database altered.

CREATING DICTIONARY MANAGED TABLESPACE

SYNTAX

SQL>create tablespace <tsname> datafile 'file path ' size <size>m

extent management

DICTIONARY

;

SQL> create tablespace samp datafile '/u01/oradata/samp/samp01. dbf '

size 10m extent management dictionary ;

Tablespace created.

In 10g , If not specified “ Extent management dictionary “ automatically

tablespace will be created as

“ LOCALLY MANAGED ”.

MANAGING TABLESPACES IN ORACLE 10g

SYSTEM

tablespace should be

“ DICTIONARY MANAGED ”

otherwise cannot

create dictionary managed tablespaces.

As far as I remember, dbca creates databases with locally managed databases,

when creating database

manually In release 10.x, the default was dictionary

managed. Locally managed tablespaces are much more efficient than dictionary

managed ones.

LOCALLY MANAGED TABLESPACE

SYNTAX – CREATE LOCALLY MANAGED TABLESPCE

MANAGING TABLESPACES IN ORACLE 10g

Exploring the Oracle DBA Technology by

Gunasekaran ,Thiyagu

SYNTAX - LOCALLY MANAGED TABLESPACE USING AUTO

SQL> create tablespace <tsname> datafile 'file path' size <size>m

extent management

LOCAL AUTO ALLOCATE

;

SQL> create tablespace samp datafile '/u01/oradata/samp/samp02.dbf ’

size 10m extent management local autoallocate;

SYNTAX - LOCALLY MANAGED TABLESPACE USING UNIFORM

SQL> create tablespace <tsname> datafile 'file path' size <size>m

extent management

LOCAL UNIFORM SIZE

120K;

SQL> create tablespace samp datafile '/u01/oradata/samp03.dbf'

size 20m extent management local uniform size 120k ;

TABLESPACE SPECIFYING SEGMENT SPACE ( ASSM )

SQL>create tablespace <tsname> datafile 'file path' size <SIZE>m

extent management

LOCAL

segment space management

AUTO;

SQL> create tablespace test datafile '/u01/oradata/samp/test01.dbf' size

15m extent management local

--

ENABLE LMT

segment space management auto ;

-- ENABLE ASSM *******

ASSM

takes total control of the parameters

FREELISTS, FREELIST GROUPS, & PCTUSED

. Oracle will track and manage the used and free space in data blocks

using bitmaps for all objects defined in the tablespace.

MANAGING TABLESPACES IN ORACLE 10g

*************

LMT WITH AUTO EXTEND

EXAMPLE -I

SQL> create tablespace <tsname> datafile ‘filepath’ size <size>m

AUTOEXTEND ON:

SQL> create tablespace test datafile '/u01/oradata/samp/test02.ora' size

10m autoextend on;

EXAMPLE –II

SQL> create tablespace <tsname> datafile ‘filepath’ size <size>m

AUTOEXTEND ON MAXSIZE

<size>m;

SQL> create tablespace test datafile '/u01/oradata/samp/test03.ora' size

10m autoextend on maxsize 100m;

EXAMPLE –III

SQL> create tablespace <tsname> datafile ‘filepath’ size <size>m

AUTOEXTEND ON NEXT

<size>m

MAXSIZE

<size>m;

SQL> create tablespace test datafile '/u01/oradata/samp/test04.ora'

size 10m autoextend on next 2m maxsize 100m;

MANAGING TABLESPACES IN ORACLE 10g

Exploring the Oracle DBA Technology by

Gunasekaran ,Thiyagu

EXAMPLE - V

SQL> create tablespace <tsname> add datafile ‘filepath’ size

<size>m

AUTOEXTEND ON MAXSIZE UNLIMITED

SQL> create tablespace test datafile '/u01/oradata/samp/test05.ora'

size 10m autoextend on maxsize unlimited;

EXAMPLE - VI

SQL> alter database datafile ‘filepath’

AUTOEXTEND ON MAXSIZE UNLIMITED ;

SQL> alter database test datafile '/u01/oradata/samp/test05.ora'

autoextend on maxsize unlimited;

EXAMPLE - VII

SQL>alter database datafile 'pathfile' AUTOEXTEND OFF;

SQL>alter database datafile '/u01/oradata/samp/test04.dbf'

autoextend off;

TO CHECK AUTO EXTENSIBLE STATUS

SQL> select file_name, autoextensible from dba_data_files

where tablespace_name like '%TES%';

ASSIGNING A DEFAULT TABLESPACE FOR WHOLE DATABASE

SQL>alter database default tablespace <tsname>;

SQL>alter database default tablespace sample;

*****************

MANAGING TABLESPACES IN ORACLE 10g

CHECKING DEFAULT TABLESPACES IN DATABASE

ASSIGNING TABLESPACE TO A SPECIFIC USER

SQL> alter user <user.name> default tablespace <tsname>;

SQL> alter user san default tablespace users;

TO FIND DEFAULT TABLESPACES FOR SPECIFIC USERS

SQL> select username , default_tablespace from dba_users

where username='SAM' or username='SAN';

USERNAME DEFAULT_TABLESPACE

MANAGING TABLESPACES IN ORACLE 10g

Exploring the Oracle DBA Technology by

Gunasekaran ,Thiyagu

SQL>alter tablespace <tsname> read write;

SQL>alter tablespace sample read write

SQL>alter tablespace <tsname> offline;

SQL>alter tablespace sample offline;

SQL>alter tablespace <tsname> online;

SQL>alter tablespace sample online;

CHECKING TABLESPACE STATUS

SOME RESTRICTIONS IMPOSED ON TABLESPACES.

TABLESPACE OFFLINE RENAME DROP

SYSTEM NO NO NO

SYSAUX YES NO NO

TEMPORARY NO YES NO

MANAGING TABLESPACES IN ORACLE 10g

CHECKING DATAFILE’S BYTES , BLOCKS IN DATABASE

CHECKING DATAFILE’S UNDER ANYONE TABLESPACE

MANAGING TABLESPACES IN ORACLE 10g

Exploring the Oracle DBA Technology by

Gunasekaran ,Thiyagu

RESIZING DATAFILE USING FILE_ID

SQL> alter database datafile <file_Id> resize <size>m;

SQL>alter database datafile 4 resize 25m;

GET PROPERTIES OF AN EXISTING TABLESPACE

SQL> select

DBMS_METADATA.GET_DDL('TABLESPACE'

, ' tsname') from dual;

SQL> select DBMS_METADATA.GET_DDL('TABLESPACE', ' USERS') from dual;

ASSIGNING QUOTAS TO A USER IN DEFAULT TABLESPACE

SQL> alter user <username> default tablespace <tsname>

quota <size>m on <tsname>;

SQL> alter user sam default tablespace sample quota 2m on sample;

SET DEFAULT TABLESPACE TYPE

SQL>alter database set default <DATAFILE_TYPE> tablespace;

SQL>alter database set default SMALLFILE tablespace;

SQL>alter database set default BIGFILE tablespace;

DISPLAY DEFAULT TABLESPACE TYPE

SQL> select property_value from database_properties

where property_name = 'DEFAULT_TBS_TYPE';

PROPERTY_VALUE

MANAGING TABLESPACES IN ORACLE 10g

FIND DEFAULT_PERMANENT_TABLESPACE

SQL>select property_value from database_properties

where property_name='DEFAULT_PERMANENT_TABLESPACE';

MANAGING TABLESPACES IN ORACLE 10g

Exploring the Oracle DBA Technology by

Gunasekaran ,Thiyagu

PERMANENT TABLESPACE

Permanaent tablespaces can be either small tablespaces or big tablespaces.

Small tablespace can be made up of a number of data files. Big tablespace will

only be made up of one data file and this can get extremely large. We cannot

add datafile to a bigfile tablespace.

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). We

can specify SIZE in kilobytes (K), megabytes (M), gigabytes (G), or terabytes (T).

Bigfile tablespaces are supported only for locally managed tablespaces with

automatic segment space management.

MANAGING TABLESPACES IN ORACLE 10g

TEMPORARY TABLESPACES

CREATING LOCALLY MANAGED TEMPORARY TABLESPACE

SQL>create temporary tablespace <tsname>

TEMPFILE

‘file path’ size

<size>m

EXTENT MANAGENT LOCAL;

SQL>create temporary tablespace temptbs tempfile

'/u01/app/oracle/oradata/orc/temp01.ora' size 20m

Extent management local;

ADDING TEMPFILE TO TEMPORARY TABLESPACE

SQL>alter tablespace <tbname> add tempfile ‘ file path ’ size <size>m;

SQL>alter tablespace temptbs add tempfile

'/u01/app/oracle/oradata/orcl/temp02.ora' size 8m;

RESIZING A TEMPORARY FILE

SQL>alter database TEMPFILE ‘file path’ resize <size>m;

SQL>alter database tempfile

MANAGING TABLESPACES IN ORACLE 10g

Exploring the Oracle DBA Technology by

Gunasekaran ,Thiyagu

TEMPFILES AND ASSOCIATED TEMPORARY TABLESPACE

CHECKING LOCAL MANAGED TEMPFILES AND STATUS

MAKING TEMPORARY TABLESPACE TO OFFLINE

SQL>alter tablespace <tsname> TEMPFILE OFFLINE;

MANAGING TABLESPACES IN ORACLE 10g

ORA-12905

The

DEFAULT TEMPORARY TABLESPACE

cannot be taken off-line

The

DEFAULT TEMPORARY TABLESPACE

cannot be dropped until create

another one. We cannot change a default temporary tablespace into a

permanent tablespace. Oracle10g introduced new feature which we will create

MANAGING TABLESPACES IN ORACLE 10g

Exploring the Oracle DBA Technology by

Gunasekaran ,Thiyagu

ALTERING TEMPORARY TABLESPACE TO ONLINE

SQL>alter tablespace <tsname> TEMPFILE ONLINE;

SQL>alter tablespace tmpbs tempfile online;

CREATING TEMPORARY TABLESPACE AND GROUP

SQL> create temporary tablespace <tsname> tempfile

‘filepath' size <size>m tablespace GROUP <group_name>;

SQL> create temporary tablespace temtbs tempfile

'/u01/app/oracle/oradata/orcl/temp01.ora' size 2m

tablespace GROUP T1;

ORA-10918:

TABLESPACE GROUP

name cannot be the same as tablespace name.

CREATING GROUP FOR EXISTING TEMPORARY TABLESPACE

SQL> alter tablespace <tsname> tablespace GROUP <group_name>;

MANAGING TABLESPACES IN ORACLE 10g

Here tmpts1 is created by oracle. If NO group exists oracle will create it.

TEMPORARY TABLESPACE WITH ASSOCIATED GROUP

SQL>select * from DBA_TABLESPACE_GROUPS;

DROPPING TEMPORARY TABLESPACE

SQL> drop tablespace <tsname> including contents and datafiles;

SQL>drop tablespace tmpts including contents and datafiles;

REMOVE A TABLESPACE FROM A GROUP

SQL> alter tablespace <tsname> tablespace group '';

MANAGING TABLESPACES IN ORACLE 10g

Exploring the Oracle DBA Technology by

Gunasekaran ,Thiyagu

SQL>select property_value from database_properties where

property_name = 'DEFAULT_TEMPORARY_TABLESPACE';

PROPERTY_VALUE TEMP

V$SORT_SEGMENT

It contains information about every sort segment in TEMP TABLESPACE. This

view is only updated when the tablespace is of the temporary type. We can

check total_extents , extent_size, total_blocks, free_extents, free_blocks from

v$sort_segment view.

MANAGING TABLESPACES IN ORACLE 10g

DISPLAY FREE TEMP SPACE

V$TEMP_SPACE_HEADER

SQL> select sum(bytes_used), sum(bytes_free)

from v$temp_space_header group by tablespace_name;

MAKE USER USE TEMP GROUP

SQL> alter user <user_name> temporary tablespace <group name>;

SQL> alter user sam temporary tablespace t3;

The DBA should assign a temporary tablespace to each user in the database to

prevent them from allocating sort space in the

SYSTEM

tablespace.

MANAGING TABLESPACES IN ORACLE 10g

Exploring the Oracle DBA Technology by

Gunasekaran ,Thiyagu

UNDO TABLESPACES

Undo tablespaces are used to store original data after it has been changed.

We can create more than one undo tablespace, but one of them can be

active at any one time. Oracle provides a fully automated mechanism, referred

to as automatic undo management, for managing undo information and space.

AUM (AUTOMATIC UNDO MANAGEMENT)

CREATING UNDO TABLESPACE

SQL> create

UNDO

tablespace <tsname> datafile ‘filepath' size <size>m;

RESIZING UNDO TABLESPACE

MANAGING TABLESPACES IN ORACLE 10g

ADDING DATAFILE TO UNDO TABLESPACE

SQL> alter tablespace <tsname> add ‘file path’ size <size> m;

MANAGING TABLESPACES IN ORACLE 10g

Exploring the Oracle DBA Technology by

Gunasekaran ,Thiyagu

TO FIND ALL UNDO TABLESPACES

SQL> select tablespace_name , status from DBA_TABLESPACES

Where tablespace_name like '%UNDO%';

Only one undo tablespace can be active at a time. Here UNDOTBS1. We cannot

drop an undo tablespace if it is being used by any instance or if it contains any undo data needed to roll back uncommitted transactions.

MANAGING TABLESPACES IN ORACLE 10g

SWITCHING UNDO TABLESPACE

SQL>alter system set undo_tablespace=’undo_tsname’;

SQL> alter system set undo_tablespace='UNDOTBS';

UNASSIGN CURRENT UNDO TABLESPACE

SQL>alter system set undo_tablespace=' ';

MANAGING TABLESPACES IN ORACLE 10g

Exploring the Oracle DBA Technology by

Gunasekaran ,Thiyagu

DROP UNDO TABLESPACE

SQL> drop tablespace <tsname> including contens and datfiles;

FIND ALLOCATED/FREE SIZE OF THE UNDO DATAFILE

Now I have removed

UNDOTBS1&UNDOTEST

, my default undo tablespace

is

UNDOTBS

. Let’s perform small test with

UNDOTBS

.

MANAGING TABLESPACES IN ORACLE 10g

POINTS TO NOTE

Space for undo segments is dynamically allocated, consumed, freed, and

reused—all under the control of Oracle Database, rather than by DBA’S so

Oracle takes care undo tablespace.

undo_extent_stat.sql DATABASE_PROPERTIES

The current settings for the default tablespaces can be viewed using the

DATABASE_PROPERTIES

view

.

MANAGING TABLESPACES IN ORACLE 10g

Exploring the Oracle DBA Technology by

Gunasekaran ,Thiyagu

TO GET TABLESPACE METADATA

SQL> set serveroutput on ;

SQL> declare c clob;

begin

for t in( select tablespace_name from dba_tablespaces)

loop

select dbms_metadata.get_ddl('TABLESPACE', t.tablespace_name) into c

from dual;

dbms_output.put_line(c);

dbms_output.put(';');

end loop;

end;

/

SOME IMPORTANT PARAMS ABOUT TABLESPACES

COLUMNS VALUES

CONTENTS PERMANENT , TEMPORARY , UNDO

STATUS ONLINE , OFFLINE , READ ONLY

EXTENT_MANAGEMENT DICTIONARY , LOCAL

ALLOCATION_TYPE SYSTEM ( autoallocate) , UNIFORM (manual ) SEGMENT_SPACE_MANAGEMENT MANUAL , AUTO