Monday , September 25 2017



  1. Only one undo tablespace will be in action at a given time
  2. Undo tablespace features are enabled by setting following parameters
Imp points to remember:
1. The undo blocks occupied by a transaction will become expired once the transaction commits.
2. The data will be selected from undo tablespace, if any DML operation is being performed on the table on which select query is also fired. This is to maintain read consistency.
ORA-1555 error(snapshot too old error)
Tx1 → Updating table A and commited
Tx2 → updating table B
Tx3 → selecting data from
In the above situation, Tx1 issued an update statement on table A and committed. Because of this dirty blocks are generated in DBC and undo blocks are used from undo tablespace. Also, dirty blocks of A are not yet written to datafiles
Tx2 is updating table B and because of non availability of undo blocks, Tx2 overrided expired undo blocks of Tx1
Tx3 is selecting the data from A. This operation will first look for data in undo tablespace, but already blocks of A are occupied by B (Tx2), it will not retrieve any data. Then it will check for latest data in datafiles, but as dirty blocks are not yet written to datafiles, there are transaction will be unable to get data. In this situation it will throw ORA-1555 (snapshot too old) error
Soltuions to avoid ORA-1555
  1. Re-issuing the SELECT statement will be a solution when we are getting ora-1555 very rarley
  2. It may occur due to undersized undo tablespace. So increasing undo tablespace size is one solution
  3. Increasing undo_retention value is also a solution
  4. Avoiding frequent commits
  5. Using “retention gurantee” clause with DML statement. This is only from 10g
Note : Don’t ever allow undo & Temp tablespaces to be in AUTOEXTEND ON.
# To create UNDO tablespace
SQL> create undo tablespace undotbs2
datafile ‘/u02/prod/undotbs2_01.dbf’ size 30m;
# To change undo tablespace
SQL> alter system set undo_tablespace=’UNDOTBS2’ scope=memory/spfile/both;
# To create temporary tablespace
SQL> create temporary tablespace mytemp
tempfile ‘/u02/prod/mytemp01.dbf’ size 30m;
# To add a tempfile
SQL> alter tablespace mytemp add tempfile ‘/u02/prod/mytemp02.dbf’ size 30m;
# To resize a tempfile
SQL> alter database tempfile ‘/u02/prod/mytemp01.dbf’ resize 50m;
# To create temporary tablespace group
SQL> create temporary tablespace mytemp
tempfile ‘/u02/prod/mytemp01.dbf’ size 30m
         tablespace group grp1;
# To view tablespace group information
SQL> select * from dba_tablespace_groups;
# To view temp tablespace information
SQL> select file_name,sum(bytes) from dba_temp_files where tablespace_name=’MYTEMP’
         group by file_name;
# To move temp tablespace between groups
SQL> alter tablespace mytemp tablespace group grp2;
Read More:
STEPS to create database manually
Some Interview Questions



Check Also

How to switch on primary database to physical standby database

After configuration data guard then data is switching  into primary database  to standby database : …

Leave a Reply

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