Different RollBack Segments in Oracle DBA

17 September, 2018

Related Blogs

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   Screenshot_68   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  
About Author


Author Bio

TekSlate is the best online training provider in delivering world-class IT skills to individuals and corporates from all parts of the globe. We are proven experts in accumulating every need of an IT skills upgrade aspirant and have delivered excellent services. We aim to bring you all the essentials to learn and master new technologies in the market with our articles, blogs, and videos. Build your career success with us, enhancing most in-demand skills .