Sep 16, 2008

Tablespace in oracle 9i

Tablespace is a logical storage unit in database. It a bridge between the datafiles and oracle instance.
Hierarchy of tablespace structure.
1. Tablespace can belonging to only database at a time
2. T tablespace can have more than one datafiles

Creating tablespace
Syntax: Create tablespace tbs_name datafile ‘file location/file_name.dbf’ size 100m

Tablespace clauses
Blocksize – set nonstandard block size for the tablespace.
You must set db_cache_size and at least one db_nk_cache_size.
Logging (d) nologging – all tables and indexes within the tablespace have all changes
written to redo.
Default – specify default storage for all objects created in the tablespace creation. (DMT only)
(Initial, next, pctincrease, minextends, maxenteds)
Online offline (d) – tablespace availability
Permanent temporary – tablespace can be used to hold permanent object

or temporary object (sorting)
Autoextend – this enable automatic extension of datafile
Next – this increment of next disk space to be allocated auto when more extents are required.
Maxsize - maximum disk space allowed for auto extension of datafile.
Unlimited – unlimited disk space allowed for auto extension of datafile.
Extent management – how the extent of tablespace are managed (LMT or DMT)
Segment management – track the used and free space in the segment in the tablespace
using free lists or bitmap (LMT only) . Pctfree , Pctused, Freelist, Freelist group
Autoallocate (d) – tablespace is system managed, user cannot specify an extent size
Uniform – tablespace is managed with uniform extents of size 32m or bytes.
By default 1mb in size.
Reuse – reuse the existing file.
Resize – enlarging the tablespace size.

Types of Tablespace
Ø System tablespace
- Created with the database
- Contain data-dictionary and stored program units

Ø Non-system tablespace
- Undo
- Temporary
- User data and easy to manage by dba.

Managing Space in Tablespaces.
When oracle allocates space to a segment (table or index), a group of contiguous free blocks (extent), is added to the segment. Metadata regarding the extent allocation and unallocated extents are either stored in the data dictionary are called DMT or stored within the tablespace header are called LMT

Locally managed tablespace – LMT
A tablespace that manages its own extents maintains a bitmap in each datafile to keep track of the free or used status of blocks in that datafile. Each bit in the bitmap corresponds to a group of blocks. When an extent is allocated or freed for reuse, Oracle changes the bitmap values to show the new status of the blocks. These changes do not generate rollback information because they do not update tables (like sys.uet$, sys.fet$) in the data dictionary
Create tablespace tsb_lmt datafile ‘file spec ‘size 100m extent management local

uniform size 100k ;

1. Avoid recursive space management. It won’t consume or release space in the
data-dictionary table.
2. Avoid contention on data-dictionary table.
3. Extents automatically tracks adjacent free space, by eliminating the need of coalesce
free extents.
4. Do not generate undo info bcos don’t update tables in the data-dictionary.

Create tablespace tbs_aa datafile ‘filespec’ size 100m extent management local
Create tablespace tbs_uni datafile ‘filespec’ size 100m extent management local uniform
size 32k;

Segment Space Management – ASSM in LMT

DBA has no longer to manage the segment management. Oracle done a major implementation in oracle 9i to manage the segment space automatically.

Create tablespace tbs_ssm datafile ‘filespec’ size 100m entent management local

segment space management auto

Create tablespace tbs_ssm1 datafile ‘filespec’ size 100m extent management local
segment space management manual (pctused 20 pctfree 20)

Dictionary managed tablespace – DMT

In DMT, to keep track of the free or used status of blocks, oracle uses data dictionary tables. When an extent is allocated or freed for reuse, free space is recorded in the SYS.FET$ table, and used space in the SYS.UET$ table. Whenever space is required in one of these tablespaces, the ST (space transaction) enqueue latch must be obtained to do insert and deletes against these tables. As only one process can acquire the ST enque at a given time, this often lead to contention. These changes generate rollback information because they update tables (like sys.uet$, sys.fet$) in the data dictionary.

Create tablespace tbs_dmt datafile ‘file spec’ size 100m extent management dictionary default storage (initial 1m next 2m pctincrease 0 maxextents 10

Default Storage clause – when DMT is created default storage parameters are specified. These values override any default settings to become the default for the objects created in that tablespace.


Calculation = initial + ( next * pctincrease / 100)

INITIAL integer [K M] -- default value is 5 x db_block_size
Specify the size of the object's first extent. The space for this extent is allocated when the object is created. Must be specified in a CREATE statement. See INITIAL_EXTENT column in DBA_TABLESPACES

NEXT integer [K M] -- default value is 5 x db_block_size
Specify in bytes the size of the next extent to be allocated to the object. The default value is the size of 5 data blocks. The minimum value is 1 data block. If the value of NEXT is changed using the ALTER statement, the NEXT allocated extent will have the newly specified NEXT size, regardless of the size of the most recently allocated extent and the value of the PCTINCREASE parameter.See NEXT_EXTENT column in DBA_TABLESPACES.

MINEXTENTS integer -- default and minimum value is 1 in the form of the INITIAL extent. Exception is rollback segments which require 2.
Specify the total number of extents to allocate when the object is created. Allows a large amount of space to be allocated when the object is created, even if the space available is not contiguous. The default and minimum value is 1, meaning
SMON allocates only the INITIAL extent, except for rollback segments, for which the default and minimum is 2. If MINEXTENTS > 1, then SMON calculates the size of subsequent extents based on the values of INITIAL, NEXT, and PCTINCREASE. The value of MINEXTENTS can be reduced but not increased using the ALTER statement. Reducing the value of MINEXTENTS to a smaller value may be useful before a TRUNCATE ... DROP STORAGE, to ensure a segment will maintain a minimum number of extents after the TRUNCATE. The value of MINEXTENTS cannot be changed for an object residing in a locally managed tablespace. See MIN_EXTENTS column in DBA_TABLESPACES.
MAXEXTENTS {integer UNLIMITED} -- minimum value is 1. default depends on db_block_size. Exception is rollback segments which require 2.
Specify the total number of extents, including the INITIAL, that can be allocated for the object. The minimum value is 1 (except for
rollback segments, which always have a minimum value of 2). The default value depends on db_block_size. MAXEXTENTS cannot be changed for an object residing in a locally managed tablespace.
PCTINCREASE integer -- Minimum value is 0. Default value is 50.
Specify the percent by which the third and subsequent extents grow over the preceding extent. The minimum value is 0, meaning all extents after the first are the same size. The default value is 50, meaning that each subsequent extent is 50% larger than the preceding extent. If the value of the PCTINCREASE parameter is changed using the ALTER statement,
SMON calculates the size of the next extent using the new value and the size of the most recently allocated extent. Setting PCTINCREASE to 0 will prevent SMON from coalescing extents and keep all extents the same size. Setting of 0 as a way to minimize fragmentation and avoid the possibility of very large temporary segments during processing. PCTINCREASE cannot be specified and therefore is always 0 for rollback segments. See: storage.htm

FREELISTS is ignored for a
locally managed tablespace in automatic segment-space management mode. For objects other than tablespaces, specify the number of free lists for each of the free list groups for the table, partition, cluster, or index. The default and minimum value is 1, meaning that each free list group contains one free list. The maximum value of this parameter depends on the data block size. If a value for FREELISTS is too large, an error is returned indicating the maximum value. FREELISTS can be specified in the storage_clause of any statement except when creating or altering a tablespace or rollback segments.


FREELIST GROUPS is ignored for a
locally managed tablespace in automatic segment-space management mode. Specify the number of groups of free lists for the database object being creating. The default and minimum value is 1. The instance number of RAC instances is used to map each instance to one free list group. FREELIST GROUPS can only be specified in CREATE TABLE, CREATE CLUSTER, CREATE MATERIALIZED VIEW, CREATE MATERIALIZED VIEW LOG, and CREATE INDEX.

OPTIMAL [integer [KM] NULL]

Only relevant to
rollback segments. Specifies an optimal size in bytes for a rollback segments. Oracle tries to maintain this size for the rollback segments by dynamically deallocating extents when their data is no longer needed for active transactions. Oracle deallocates as many extents as possible without reducing the total size of the rollback segments below the OPTIMAL value. The value of OPTIMAL cannot be less than the space initially allocated by MINEXTENTS, INITIAL, NEXT, and PCTINCREASE. The maximum value depends O/S. Specify NULL for no optimal size for the rollback segments, meaning that extents are never deallocated for rollback segment's. This is the default behavior.


The BUFFER_POOL clause allows a default buffer pool (cache) to be defined for a schema object. All blocks for the object are stored in the specified cache. Unless overridden by a partition-level definition, partitions inherit the buffer pool value from table or index specifications. BUFFER_POOL clause cannot be specified for cluster tables,
tablespaces or rollback segments. It can be specified for clusters.
DEFAULT - Specify to indicate the
default buffer pool.
KEEP - Specify to put blocks from the segment into the
KEEP buffer pool. Maintaining an appropriately sized KEEP buffer pool retains the schema object in memory to avoid I/O operations. KEEP takes precedence over any NOCACHE clause specified for a table, cluster, materialized view, or materialized view log.
RECYCLE - Specify to put blocks from the segment into the
RECYCLE pool. An appropriately sized RECYCLE pool reduces the number of objects whose default pool is the RECYCLE pool from taking up unnecessary cache space.

Converting DMT to LMT
exec dbms_space_admin.Tablespace_Migrate_TO_Local('ts1');
Converting LMT to DMT
exec dbms_space_admin.Tablespace_Migrate_FROM_Local('ts2')

Changing the availability of tablespace

Ø Read only

Alter tablespace tbs_name read only;

a. Transaction read only, no further write operation except for the rollback of existing
transaction that previously made either committed or rollback.
b. You can drop item such as tables and indexes from read only tablespace.

Ø Offline, Online [ normal temporary immediate for recover]

Alter tablespace tbs_oo offline;
Alter tablespace tbs_oo online;

Normal (d) – flushes all blocks in all datafile in the tablespace out of sga.
We need to perform media recovery before bringing it back to online. (Recommend).
Temporary – perform checkpoint for all datafile, even if some file could not be written.
Immediate - does not perform checkpoint. Recovery needed.
For recover – for TPITR

a. SYSTEM tablespace cannot bring to offline mode
b. Default temporary tablespace cannot bring to offline until new temp tablespace create.
c. Active undo segment cannot be taken offline
d. Tablespace goes offline or comes back online the event is recorded in the data dictionary
and control file.
e. OI automatically switches a tablespace from online to offline when certain errors are
encountered. Eg: dbwr file in several attempt to write to a datafile.

Ø Move and Rename
Alter tablespace tbs_rn rename datafile ‘filespec’ to ‘filespec’;
a. Database must be mounted and file must exist
b. Tablespace must be in offline mode.
c. Copy and move file in os level using cp or mv command.
d. Execute the above statement
e. Bring the tablespace online.

Ø Adding datafile to a tablespace

Alter tablespace tbs_name add datafile ‘filespec’ size 100m;

Ø Dropping tablespace
Drop tablespace tbs_name including contents and datafiles cascade constraints;
Ø Resizing datafile
Alter database datafile ‘filespec’ resize 200m;
Ø Enable auto extension of datafile
Alter database datafile ‘filespec’ Autoextend on next 1m maxsize 5;

1. This is possible with in 9.2.0.X, where SYSTEM tablespace is created by DBCA as locally managed by default. With a locally managed SYSTEM tablespace, the rest of the tablespace in such
database have to be locally managed as well.
2. Locally managed temporary tablespace cannot be of type "permanent".

dba_data_files, v$datafile, - list datafile information

dba_free_space - space usage in tablespace
dba_tablespaces, v$tablespace - list tablespace information


Gurudatta said...

It,s a very usefull data. Thanks...

Anonymous said...

Fantastic goods from you, man. I have take note your stuff previous
to and you're simply extremely wonderful. I really like what you've got here, really like what you are saying and the way in which in which you assert it.
You make it enjoyable and you still take care of to keep it smart.
I cant wait to learn far more from you. This is really a wonderful website.

Here is my site :: diets that work for women

Anonymous said...

My web-site - facebookofsex

Anonymous said...

I was suggested this web site by my cousin. I'm not sure whether this post is written by him as nobody else know such detailed about my problem. You are incredible! Thanks!

Here is my website;

Anonymous said...

Hi! Do you know if they make any plugins to safeguard against hackers?

I'm kinda paranoid about losing everything I've worked hard on.

Any recommendations?

my page: dating site with

Anonymous said...

ors which often best match those demands. Then he presents
his suggestions and if all parties agree, the broker formulates a contract between the
2 parties.

my website - internet dating web site

Anonymous said...

Thanks for the suggestions you have discussed
here. Furthermore, I believe there are a few factors that will keep your motor
insurance premium down. One is, to take into account buying cars and
trucks that are from the good directory of car insurance businesses.
Cars which are expensive are definitely more at risk of being stolen.
Aside from that insurance is also depending on the value of
the car, so the more expensive it is, then higher the premium you

my webpage ... cyber sex

Anonymous said...

I have come to understand that service fees for on-line degree pros tend to be a fantastic value.
For example a full College Degree in Communication in the University of Phoenix Online consists of Sixty credits
from $515/credit or $30,900. Also American Intercontinental University Online comes with a Bachelors of Business Administration with a entire course element of
180 units and a worth of $30,560. Online learning has made getting
the college degree far more easy because you could earn the degree in the comfort in your home and when you finish from office.
Thanks for all the other tips I've learned from your website.

My website cyber sex