Working with Capacity plan in Oracle DBA

Capacity plan  

[to decrease DSA]

no.of rows per day = 12000 [for second time it may increase]
avg-row-len             = 400

no. of days in a month =22

frequency =4 months.

Size of 1 st extent required = 12000 x 400 x 22 x 4





Storage parameters  [dictionary management]

ChangeParameter nameDBA valueORACLE
YesPot increase20%50%


SQL> create table t1 (sno nuber, ………) storage [initial 100K

next 100k
minext 1
maxext 20
pot increase 50 ]





UP to ORACLE 8i only DM [dictionary management]



  1. When a user define        storage parameters at the time of segment [table/index] creation ORACLE offers the same, provided he is still under QUOTA limit
  2. If the user is not aware of storage parameters and submitted only create table part then ORACLE offers whatever the DBA has defined as default storage parameters defined at table space creation.
  3. If neither user nor DBA defined storage parameters, default storage parameters respectively then ORACLE offers its own default storage parameters at table space level which would be inherited for those segments which are having no storage parameters.









For static tables DSA [dynamic space allocation] doesn’t suits therefore for static tables DSA not recommended.

Before going to allocate storage parameter to any tables, as a DBA we should know the nature 9static, dynamic) of the tables.





  1. ORACLE introduced LMTS [locally managed table space] from version 8i [it use to be optional and became default from version 9i]
  2. LMTS is further divided into

A] uniform [where all the segments are same in size]

B] auto [where all the sizes of extents is controlled by ORACLE]

  1. Uniform [LMTS] is usually meant for static, semi dynamic tables.
  2. The beauty of LMTS uniform is every hole [created by dropping segments] becomes best – fit thus there is no fragmentation at all.
  3. we can have multiple LMTS uniform table spaces of same or different extent sizes.





Freelist: The blocks which are free
i.e. pct free is less than 40% will be maintained in the free list.

ORACLE will first search for a free block in the free list and then the data is inserted into that block.

NOTE: segment space management is manual.



If size of block is 2K then initial value is 10K
if size of block is 8k then initial value is 40k



In DMTS freelist information is maintained by base tables in LMTS free list information is maintained by. Block it self

DMTS    [free list info. maintained by “base tables”]  SSM=manual

Default values in 2k block size Default values in 8k block size
1Minex tents1
121Maxex tents505
50%Pot increase5-%


LMTS  Free list info. Maintained by data file only

SYS > select, extent_id,, bytes, blocks from da_extents where table space_name=’LMTS’ order by extent_id, block id;

SYS > save dba_ext

SYS > select segment_name, tablespace_name, initial_extent, next_extent,min_extents,pct_increase from dba_segments where tablesapace_name= ‘LMTS’”

SYS >  save dba_seg

SYS >  select tablespace_name, initial_extent, next_extent, min_extents, max_extents, pct_increase, segment_space_managment OS SSM from dba_tablespaces where tablespace_name =’DMTS’ ;

SYS >  save dba_ts;


 DMTS   [SSM manual]

SYS >  create tablespace dmts datafile ‘/disk3/oradata/pal/dmts.dbf’ size 20m extent management dictionary;

SYS >  @dba_ts

SYS >  create tablespace dmts1 datafile ‘disk3/oradata/pal/dmts1/dbf’ size 20m extent management dictionary default storage ( initial 80K next 80k minextents 1 maxextents 100 loctincrease 50);

SYS >  @ dba_ts

SYS >  create user dmts identified by dmts default tablespace dmts;

SYS > grant connect, resource to dmts;

SYS > conn dmts/dmts

Dmts > @$ORACLE_HOME/demobld

Dmts > select * from tab;

Dmts > create table ex1 (dd number) storage (initial 16k next 16k minextents 1 maxextents 10 pctincrease 50);

SYS> @ dba_seg

SYS> edba_ext

Dmts> insert into emp select * from emp;

Dmts>  / [up to some hundreds of records]

SYS> @ dba_ext

Dmts> create table test (sno number) pot pree 20 pot user 40;

Dmts> select pot free, pot_used, table_name from user _ table;

Since SSM is manaual, we can change only pct_free, pct_used by specifying.


LMTS [SSM auto]

In LMTS auto SSM, we can give storage parameters, but it takes default of ORACLE’s

Storage parameters.

SYS> create table space lmts datafile ‘disk3/oradata/pal/lmts.dbf’ size 10m;

SYS> @ dba_ts

SYS> create tablespace lmts1 datafile ‘/disks/oradata/pal/lmts1/dbf’ size 20m default storage 9initial 40k next 40k minextents 40)

SYS> @ dba_ts

………. Even though we specifie storage parameters, oracle considers default values in LMTS auto.

SYS> create user. Lmts identified by lmts default table space lmts;

SYS> grant connect, resource to lmts;

SYS> conn lmts/lmts

Lmts > @$ORACLE_HOME/demobld

SYS> @ bda _ seg

SYS> @ dba _ ext

Lmts > insert into emp select * from emp;

Lmts> select table_name, pct_used, pct_free from user_tables;

Lmts> save user_tab

Lmts> create table test (info number) pct free 20 pot used 50;

Lmts> @ user_ tab

In lmts auto, we can change only pot free, we can’t change pot used

…. To change pot used in lmts auto

SYS> create tablespace lmts 2 datafile ‘/disk3/oradata/pal/lmts2.dbf’ size 20m segment space management manual;

SYS> @ dba-ts

LMTS [uniform SSM]

SYS> create table space lmts_u data file ‘/disk3 / oradata/pal/lmts_u.dbf’ size 20m extent management local uniform;

SYS> @dbo_ts

SYS> conn lmts/ lmts

Hnts> create table testu (tno number) storage (initial 40k next 40k);

SYS> @ dba_seg.

In lmts [uniform ssm] the initial demand  i.e initial 40k only can be supported by ORACLE.

{for additional notes on lmts turn page to 90}



UNDO & FB-qry & FB table & FB-version Qry 

AUM [automatic UNDO management]   form ORACLE9i

  1. Prior to ORACLE 9i in order to support DML transactions DBA had to create RBS [rollback segments]
  1. Issues with RBS
    1. DBA has to create them
    2. Depending on number of concurrent transactions DBA has to decide the number of RBS required
    3. To offer right storage parameters for RBS again the DBA has to depend on no.of Txs.
    4. The DBA need to specify which RBS have to be made available with intt.ora
  1. The solution: for all the avove issues is new UNDO segments.


    1. This is started from ORACLE 9i reliving the DBA from day to day transaction management.
    2. DBA simply has to create undo table space, ORACLE in-turn creates 10 Undo segments on its own.
    3. Its ORACLE’s responsibility for taking care of ‘extents’ sizes.
    4. No more monitoring is required by DBA.
    5. We can have multiple Undo table spaces [if reqrd.]
    6. But, only one undo table space can be active any point of time.
    7. We can switch from one undo table space to another.
    8. In one database, we can maintain traditional RBS &

new undo segments.

But either of these can be brought online.

Different Undo segments

200 mb undo_small_+ts [inday]

2 gb undo_big_ts [in the nights]

[since huge transactions like  procedures, reports etx.]





The greatest advantage of new undo is compared to old RBS is, if one undo segment wants to grow but not enough free space is found  in the ts, then ORACLE starts transferring extents from another undo segment which belongs to a done transaction.

In order to go with AUM

Dba12~] $ cd $ ORACLE_HOME/dbs

Dba12 dbs]$ vi initrajpar.ora

Undo_managment= auto [for undo segments]
manual [for traditional RBS]

Undo_tablespace= undo_small-ts

Undo_retention= time







  • DBA must have enable undo table space with retention period.
  • User must have realized his mistake before the retention period.
  • User should not exited from the session.
  • Even front end must be enable with flashback option [package].

ORACLE 10g offers guaranteed retention, where in ORACLE 9i never there.





Undo segment

Undo_retention=90 [15min]

Unexpired blocks

The blocks which are in retention period

Expired blocks

The blocks which are in out of retention period, which are ready for new transactions.

Active blocks         The blocks which contain ongoing transactions [not commited Txs]

Free blocks             at the time of creation all are empty blocks

SNAP SHOT too error 1555

Dba12 ~] $ cd $ ORACLE_HOME/dbs

Dba12 dbs]$ vi initrajpar.ora



Dba12 dbs] $ exit

Dba12 sys > create tablespace sse datafile ‘/disk3/oradata/pal/sse.dbf’ size 20m;

sys > create user ss identified by ss default table space sse;

sys > grant connect, resource to ss;

sys > conn ss/ss

ss> !demobld ss/ss

ss> select * from tab;

ss> select * from emp;

ss> insert into emp select * from emp;

ss> /


ss>/ (114688 records approx)

ss> commit ; [manual log switch]


from another session

dba12 ~] $ sqlplus ‘/as sysdba’

dba12 SYS > conn ss/ss

ss> ed                       file



update emp set sal=sal+100 where empno = n7788;


end loop;



ss> @ file                 ss> select * from emp;

[from ession2]      [from session1]




–:success         ORA :1555
snapshot too old error

(ORACLE not able to find consistent value]Solution:

  1. No frequent commits from users.
  2. Increase the size of Undo segment.

Retention guarantee

In ORACLE 10g the unexpired blocks are not over written even though the blocks are filled up.

(the commit statements will be restricted]

Dba12 SYS>             create undo tablespace undo9 data file ‘/disk3 /oradata/pa;/undo9.dbf’ size 20m retention guarantee;
SYS>            select tablespace_name, retention from dba_tablespaces;

SYS>            alter system set undo_tablespace=undo9;


(for particular session only)

[no need to mention in init.ora]

Session 1                                                                             session2

SS> select * from emp                                                   ss>@file


–:succeeds:–                                                                    –:fails:–


ORA – 3003





If any active transaction is there in small/big undo, it is not possible to switch from one undo to another.

The  transaction which is under active [not yet commit or rolled back] one is called in pending offline stage.

SYS> select a.usn,, b.xacts, b.status from v$rollstat b, v$ rollaname a where a.usn=b.usn;

SYS> save poff

User 2> insert into emp select * from emp;

SYS > @ poff


SYS >@ poff

User2> delete from salgrade;

SYS> @ poff

User 2 > commit;

SYS > @ poff

SYS > alter system set undo_table space = undotbs;

SYS > @ poff


“At TekSlate, we are trying to create high quality tutorials and articles, if you think any information is incorrect or want to add anything to the article, please feel free to get in touch with us at, we will update the article in 24 hours.”

0 Responses on Working with Capacity plan in Oracle DBA"

    Leave a Message

    Your email address will not be published. Required fields are marked *


    Please leave a message and we'll get back to you soon.
    Three + 6