undo表空间管理测试2-----修改参数文件initORCL.ora配置，查看undo状态

对 undo表空间管理测试1 的环境清理：

http://blog.csdn.net/wanghui5767260/article/details/20145959

SYS@ORCL>select tablespace_name,segment_name,status from dba_rollback_segs;

TABLESPACE_NAME SEGMENT_NA STATUS
------------------------------ ---------- ----------------
SYSTEM SYSTEM ONLINE
UNDOTBS1 RBS1 ONLINE
UNDOTBS1 _SYSSMU10$ OFFLINE
UNDOTBS1 _SYSSMU9$ OFFLINE
UNDOTBS1 _SYSSMU8$ OFFLINE
UNDOTBS1 _SYSSMU7$ OFFLINE
UNDOTBS1 _SYSSMU6$ OFFLINE
UNDOTBS1 _SYSSMU5$ OFFLINE
UNDOTBS1 _SYSSMU4$ OFFLINE
UNDOTBS1 _SYSSMU3$ OFFLINE
UNDOTBS1 _SYSSMU2$ OFFLINE


TABLESPACE_NAME SEGMENT_NA STATUS
------------------------------ ---------- ----------------
UNDOTBS1 _SYSSMU1$ OFFLINE


12 rows selected.


SYS@ORCL>alter rollback segment rbs1 offline;


Rollback segment altered.


SYS@ORCL>select tablespace_name,segment_name,status from dba_rollback_segs;


TABLESPACE_NAME SEGMENT_NA STATUS
------------------------------ ---------- ----------------
SYSTEM SYSTEM ONLINE
UNDOTBS1 RBS1 OFFLINE
UNDOTBS1 _SYSSMU10$ OFFLINE
UNDOTBS1 _SYSSMU9$ OFFLINE
UNDOTBS1 _SYSSMU8$ OFFLINE
UNDOTBS1 _SYSSMU7$ OFFLINE
UNDOTBS1 _SYSSMU6$ OFFLINE
UNDOTBS1 _SYSSMU5$ OFFLINE
UNDOTBS1 _SYSSMU4$ OFFLINE
UNDOTBS1 _SYSSMU3$ OFFLINE
UNDOTBS1 _SYSSMU2$ OFFLINE


TABLESPACE_NAME SEGMENT_NA STATUS
------------------------------ ---------- ----------------
UNDOTBS1 _SYSSMU1$ OFFLINE


12 rows selected.


SYS@ORCL>drop rollback segment rbs1;


Rollback segment dropped.


SYS@ORCL>shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SYS@ORCL>

开始本次试验：undo表空间管理测试2-----修改参数文件initORCL.ora配置，查看undo状态

SYS@ORCL>create pfile from spfile;


File created.


SYS@ORCL>!
[oracle@whgg ~]$ cd $ORACLE_HOME/dbs/
[oracle@whgg dbs]$ strings spfileORCL.ora
ORCL.__db_cache_size=176160768
ORCL.__java_pool_size=4194304
ORCL.__large_pool_size=4194304
ORCL.__shared_pool_size=96468992
ORCL.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/ORCL/adump'
*.background_dump_dest='/u01/app/oracle/admin/ORCL/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/u01/app/oracle/oradata/ORCL/control01.ctl','/u01/app/oracle/oradata/ORCL/control02.ctl','/u01/app/oracle/oradata/ORCL/control03.ctl'
*.core_dump_dest='/u01/app/oracle/admin/ORCL/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='ORCL'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=94371840
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=285212672
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/oracle/admin/ORCL/udump'
[oracle@whgg dbs]$ vim initORCL.ora


ORCL.__db_cache_size=176160768
ORCL.__java_pool_size=4194304
ORCL.__large_pool_size=4194304
ORCL.__shared_pool_size=96468992
ORCL.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/ORCL/adump'
*.background_dump_dest='/u01/app/oracle/admin/ORCL/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/u01/app/oracle/oradata/ORCL/control01.ctl','/u01/app/oracle/oradata/ORCL/control02.ctl','/u01/app/oracle/oradata/ORCL/control03.ctl'
*.core_dump_dest='/u01/app/oracle/admin/ORCL/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='ORCL'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=94371840
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=285212672
*.undo_management='AUTO'
#*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/oracle/admin/ORCL/udump'
"initORCL.ora" 26L, 993C written

SYS@ORCL>startup
ORACLE instance started.


Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 104859216 bytes
Database Buffers 176160768 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
SYS@ORCL>shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@ORCL>!
[oracle@whgg ~]$ cd $ORACLE_HOME/dbs
[oracle@whgg dbs]$ mv spfileORCL.ora spfileORCL.ora.bak
[oracle@whgg dbs]$ ls
hc_ORCL.dat initdw.ora init.ora initORCL.ora lkORCL orapwORCL spfileORCL.ora.bak
[oracle@whgg dbs]$ exit
exit


SYS@ORCL>startup
ORACLE instance started.


Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 104859216 bytes
Database Buffers 176160768 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
SYS@ORCL>show parameter undo


NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
SYS@ORCL>select tablespace_name,segment_name,status from dba_rollback_segs;


TABLESPACE_NAME SEGMENT_NA STATUS
------------------------------ ---------- ----------------
SYSTEM SYSTEM ONLINE
UNDOTBS1 _SYSSMU10$ ONLINE
UNDOTBS1 _SYSSMU9$ ONLINE
UNDOTBS1 _SYSSMU8$ ONLINE
UNDOTBS1 _SYSSMU7$ ONLINE
UNDOTBS1 _SYSSMU6$ ONLINE
UNDOTBS1 _SYSSMU5$ ONLINE
UNDOTBS1 _SYSSMU4$ ONLINE
UNDOTBS1 _SYSSMU3$ ONLINE
UNDOTBS1 _SYSSMU2$ ONLINE
UNDOTBS1 _SYSSMU1$ ONLINE


11 rows selected.


SYS@ORCL>show parameter rollback


NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fast_start_parallel_rollback string LOW
rollback_segments string
transactions_per_rollback_segment integer 5
SYS@ORCL>update scott.emp set sal=sal+1;


14 rows updated.

明显注释掉了 #*.undo_tablespace='UNDOTBS1' 没啥影响，

好的 那我继续干。。。。。。

SYS@ORCL>shutdown immediate
ORA-01097: cannot shutdown while in a transaction - commit or rollback first
SYS@ORCL>rollback;


Rollback complete.


SYS@ORCL>shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@ORCL>!
[oracle@whgg ~]$ cd $ORACLE_HOME/dbs
[oracle@whgg dbs]$ vim initORCL.ora


ORCL.__db_cache_size=176160768
ORCL.__java_pool_size=4194304
ORCL.__large_pool_size=4194304
ORCL.__shared_pool_size=96468992
ORCL.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/ORCL/adump'
*.background_dump_dest='/u01/app/oracle/admin/ORCL/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/u01/app/oracle/oradata/ORCL/control01.ctl','/u01/app/oracle/oradata/ORCL/control02.ctl','/u01/app/oracle/oradata/ORCL/control03.ctl'
*.core_dump_dest='/u01/app/oracle/admin/ORCL/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='ORCL'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=94371840
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=285212672
#*.undo_management='AUTO'
#*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/oracle/admin/ORCL/udump'
"initORCL.ora" 26L, 994C written

我把这两个都注释掉了，看你怎么办.......

[oracle@whgg dbs]$ exit
exit


SYS@ORCL>startup
ORACLE instance started.


Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 104859216 bytes
Database Buffers 176160768 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
SYS@ORCL>show parameter spfile


NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string
SYS@ORCL>show parameter undo


NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string MANUAL
undo_retention integer 900
undo_tablespace string
SYS@ORCL>show prameter rollback
SP2-0158: unknown SHOW option "prameter"
SP2-0158: unknown SHOW option "rollback"
SYS@ORCL>show parameter rollback


NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fast_start_parallel_rollback string LOW
rollback_segments string
transactions_per_rollback_segment integer 5
SYS@ORCL>select tablespace_name,segment_name,status from dba_rollback_segs;


TABLESPACE_NAME SEGMENT_NA STATUS
------------------------------ ---------- ----------------
SYSTEM SYSTEM ONLINE
UNDOTBS1 _SYSSMU10$ OFFLINE
UNDOTBS1 _SYSSMU9$ OFFLINE
UNDOTBS1 _SYSSMU8$ OFFLINE
UNDOTBS1 _SYSSMU7$ OFFLINE
UNDOTBS1 _SYSSMU6$ OFFLINE
UNDOTBS1 _SYSSMU5$ OFFLINE
UNDOTBS1 _SYSSMU4$ OFFLINE
UNDOTBS1 _SYSSMU3$ OFFLINE
UNDOTBS1 _SYSSMU2$ OFFLINE
UNDOTBS1 _SYSSMU1$ OFFLINE


11 rows selected.


SYS@ORCL>update scott.emp set sal=sal+1;
update scott.emp set sal=sal+1
*
ERROR at line 1:
ORA-01552: cannot use system rollback segment for non-system tablespace 'USERS'

为什么报错呢? 因为他自己变成了手动管理 ，还没有指定undo表空间


SYS@ORCL>create rollback segment rbs1 tablespace undotbs1;


Rollback segment created.


SYS@ORCL>update scott.emp set sal=sal+1;
update scott.emp set sal=sal+1
*
ERROR at line 1:
ORA-01552: cannot use system rollback segment for non-system tablespace 'USERS'

纳尼??? 为什么还是不行呢？ 请问大哥 你online 了吗？？？


SYS@ORCL>select tablespace_name,segment_name,status from dba_rollback_segs;


TABLESPACE_NAME SEGMENT_NA STATUS
------------------------------ ---------- ----------------
SYSTEM SYSTEM ONLINE
UNDOTBS1 RBS1 OFFLINE
UNDOTBS1 _SYSSMU10$ OFFLINE
UNDOTBS1 _SYSSMU9$ OFFLINE
UNDOTBS1 _SYSSMU8$ OFFLINE
UNDOTBS1 _SYSSMU7$ OFFLINE
UNDOTBS1 _SYSSMU6$ OFFLINE
UNDOTBS1 _SYSSMU5$ OFFLINE
UNDOTBS1 _SYSSMU4$ OFFLINE
UNDOTBS1 _SYSSMU3$ OFFLINE
UNDOTBS1 _SYSSMU2$ OFFLINE


TABLESPACE_NAME SEGMENT_NA STATUS
------------------------------ ---------- ----------------
UNDOTBS1 _SYSSMU1$ OFFLINE


12 rows selected.


SYS@ORCL>alter rollback segment rbs1 online;


Rollback segment altered.


SYS@ORCL>select tablespace_name,segment_name,status from dba_rollback_segs;


TABLESPACE_NAME SEGMENT_NA STATUS
------------------------------ ---------- ----------------
SYSTEM SYSTEM ONLINE
UNDOTBS1 RBS1 ONLINE
UNDOTBS1 _SYSSMU10$ OFFLINE
UNDOTBS1 _SYSSMU9$ OFFLINE
UNDOTBS1 _SYSSMU8$ OFFLINE
UNDOTBS1 _SYSSMU7$ OFFLINE
UNDOTBS1 _SYSSMU6$ OFFLINE
UNDOTBS1 _SYSSMU5$ OFFLINE
UNDOTBS1 _SYSSMU4$ OFFLINE
UNDOTBS1 _SYSSMU3$ OFFLINE
UNDOTBS1 _SYSSMU2$ OFFLINE


TABLESPACE_NAME SEGMENT_NA STATUS
------------------------------ ---------- ----------------
UNDOTBS1 _SYSSMU1$ OFFLINE


12 rows selected.


SYS@ORCL>update scott.emp set sal=sal+1;


14 rows updated.

总结：

如果把参数文件中*.undo_management=‘AUTO' 和 *.undo_tablespace='UNDOTBS1' 都注释掉的话，oracle会自动将 undo 表空间管理模式设置成 MANUAL 手动管理，但是由于没有指定 undo 表空间，此时只能执行 select操作。

补充：

SYS@ORCL>alter rollback segment rbs1 offline;


Rollback segment altered.


SYS@ORCL>drop rollback segment rbs1;


Rollback segment dropped.

SYS@ORCL>shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

回滚段 删了 不用commit 和 rollback 都可以关库了， 什么原因？？？ 自己想。。。。