Different RollBack Segments in Oracle DBA

Differed  rollback segment

If any active transaction is there in a table space, if we make the particular table space

Maintains the differed rollback segment for that particular tx.

SYS> select tablespace_name, segment_name,
segment_type from dba_segments where

Segment_type like            ‘%DEFF%’;

SYS> save drs

User 2 > insert into dept select * from dept;

SYS > @ drs

SYS > alter tablespace ts2 offline;

SYS > @ drs

Tablespace_name                            segment_type

Ts2                                                          differed rollback

Sp file & OMF  [ORACLE managed files ]

[server parameter file





To create sp file SQL> create spfile from p file;
dynamically we can change parameters, if our database maintains spfile.

The default location for sp file is same as pfile location.

i.e. $ ORACLE _ HOME /dbs

spfile is a semi binary file where as  pfile is ASCII file

SQL > alter system set ramote_login_password file = exclusive

SCOPE= MEMORY                                                                            SCOPE = memory;/orspfile;/or both;

Changes the parameter’s
values in the current
running instance only.

Scope=spfile  Changes the parameter’s values in spfile only

Stop=both Changes the parameter’s values in the current running instance and also in



Note1 –  if we have both spfile and pfile then the ORACLE gives preference to ‘spfile’

Note2 –   the default location of the spifle is the same as the pfile i.e. $ORACLE_HOME /dbs

Note3 –    in the presence of the spfile. If we want to start our database by using pfile. Then manually we have to write as SQL > startup pfile=’$ORACLE_HOME/dbs/ init$ORACLE_SID.ora’;


Demo on sp file

SQL> desc v $ parameter

SQL> select name, isses_modifiable, isinstance_modifiable from v$parameter where name like ‘rmote_login%’;

SQL> select name, [sees_modifiable,

Isinstance_modifiable from v$parameter where

Name like ‘resource_limit%’;

SYS > create spfile from pfile;

SYS> startup force

SYS> sho parameter spfile

SYS> sho parameter resource_limit                                         [TRUE]

SYS> alter system set resource_limit=false

Scope = memory

SYS> sho parameter resource-Limit                                         [FALES]

SYS> start up force

SYS> sho parameter resource_limit                                         [TRUE]

(scop=memory is session dependent]

SYS> alter system set

Resource_limit=false scope=spfile;

SYS> sho parameter resource_limit                                         [TRUE]

[scope=spfile is not for current session but for next session]

SYS> startup force

SYS> sho parameter resource_limit                                         [FALES]


Sys > alter system set resource _limit = true scope = both;

SYS> sho parameter resource_limit                                        [TRUE]

SYS> startup force

SYS> sho parameter resource _limit                                        [TRUE]


SYS > create file from spfile;

[to synchronies the values in pfile and spfile]


To remove sp file

[physically at OS level]

Dba12~ cde $ORACLE_HOME/dbs

Dba12 dbs] $ rm spfile$ORACLE_SID.ora


“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 info@tekslate.com, we will update the article in 24 hours.”

0 Responses on Different RollBack Segments 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