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