對 undo表空間管理測試1 的環境清理：
http://blog.csdn.net/wanghui5767260/article/details/20145959
SYS@ORCL>select tablespace_name,segment_name,status from dba_rollback_segs;
SYS@ORCL><喎?http://www.Bkjia.com/kf/ware/vc/" target="_blank" class="keylink">vcD4KCjxwPr+qyryxvrTOytTR6aO6dW5kb7Htv9W85LncwO2y4srUMi0tLS0t0N64xLLOyv3OxLz+aW5pdE9SQ0wub3JhxeTWw6Ossum/tHVuZG/XtMysPC9wPgo8cD5TWVNAT1JDTCZndDtjcmVhdGUgcGZpbGUgZnJvbSBzcGZpbGU7PGJyPgo8YnI+Cjxicj4KRmlsZSBjcmVhdGVkLjxicj4KPGJyPgo8YnI+ClNZU0BPUkNMJmd0OyE8YnI+CltvcmFjbGVAd2hnZyB+XSQgY2QgJE9SQUNMRV9IT01FL2Ricy88YnI+CltvcmFjbGVAd2hnZyBkYnNdJCBzdHJpbmdzIHNwZmlsZU9SQ0wub3JhIDxicj4KT1JDTC5fX2RiX2NhY2hlX3NpemU9MTc2MTYwNzY4PGJyPgpPUkNMLl9famF2YV9wb29sX3NpemU9NDE5NDMwNDxicj4KT1JDTC5fX2xhcmdlX3Bvb2xfc2l6ZT00MTk0MzA0PGJyPgpPUkNMLl9fc2hhcmVkX3Bvb2xfc2l6ZT05NjQ2ODk5Mjxicj4KT1JDTC5fX3N0cmVhbXNfcG9vbF9zaXplPTA8YnI+CiouYXVkaXRfZmlsZV9kZXN0PQ=="/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 都可以關庫了， 什麼原因？？？ 自己想。。。。
