Different RollBack Segments in Oracle DBA

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

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]

(NEXT SESSION)

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

SYS> sho parameter resource_limit                                        [TRUE]

SYS> startup force

SYS> sho parameter resource _limit                                        [TRUE]

[Scope=both]

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