ORA-01552做實驗時，修改了undo段的管理模式

undo 段為手動管理模式，是因為做實驗時，修改了undo段的管理模式。

undo 段為手動管理模式，是因為做實驗時，修改了undo段的管理模式。

1.查看undo段狀態(tài)
SQL> col segment_name for a20
SQL> col owner for a10
SQL> col tablespace_name for a20
SQL> col status for a10
SQL> select SEGMENT_NAME,OWNER,TABLESPACE_NAME,STATUS from dba_rollback_segs;

SEGMENT_NAME OWNER TABLESPACE_NAME STATUS
-------------------- ---------- -------------------- ----------
SYSTEM SYS SYSTEM ONLINE
_SYSSMU1_592353410$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU2_967517682$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU3_1204390606$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU4_1003442803$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU5_538557934$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU6_27970769$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU7_35173427$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU8_3901294357$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU9_173536$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU10_41314474 PUBLIC UNDOTBS1 OFFLINE

SEGMENT_NAME OWNER TABLESPACE_NAME STATUS
-------------------- ---------- -------------------- ----------
$

2.查看當前的undo段是哪一個？
SQL> select * from v$rollname;

USN NAME
---------- ------------------------------
0 SYSTEM
發(fā)現(xiàn)undo段為系統(tǒng)表空間

3.查看undo段的管理模式
SQL> show parameter undo

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string MANUAL
undo_retention integer 900
undo_tablespace string UNDOTBS1
SQL>
SQL>
為手動管理模式，是因為做實驗時，修改了undo段的管理模式。

4.切換管理模式為自動
SQL> alter system set undo_management=auto scope=spfile;

System altered.

SQL> show parameter undo

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string MANUAL
undo_retention integer 900
undo_tablespace string UNDOTBS1

5.重新啟動數(shù)據(jù)庫后，查看狀態(tài)，已改為AUTO模式。問題解決。
SQL> help shutdown

SHUTDOWN
--------

Shuts down a currently running Oracle Database instance, optionally
closing and dismounting a database.

SHUTDOWN [ABORT|IMMEDIATE|NORMAL|TRANSACTIONAL [LOCAL]]

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup
ORACLE instance started.

Total System Global Area 422670336 bytes
Fixed Size 1336960 bytes
Variable Size 314575232 bytes
Database Buffers 100663296 bytes
Redo Buffers 6094848 bytes
Database mounted.
Database opened.
SQL>
SQL> show parameter undo

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
SQL>
SQL>
SQL> col owner for a10
SQL> col tablespace_name for a20
SQL> col status for a10
SQL> select SEGMENT_NAME,OWNER,TABLESPACE_NAME,STATUS from dba_rollback_segs;

SEGMENT_NAME OWNER TABLESPACE_NAME STATUS
-------------------- ---------- -------------------- ----------
SYSTEM SYS SYSTEM ONLINE
_SYSSMU1_592353410$ PUBLIC UNDOTBS1 ONLINE
_SYSSMU2_967517682$ PUBLIC UNDOTBS1 ONLINE
_SYSSMU3_1204390606$ PUBLIC UNDOTBS1 ONLINE
_SYSSMU4_1003442803$ PUBLIC UNDOTBS1 ONLINE
_SYSSMU5_538557934$ PUBLIC UNDOTBS1 ONLINE
_SYSSMU6_27970769$ PUBLIC UNDOTBS1 ONLINE
_SYSSMU7_35173427$ PUBLIC UNDOTBS1 ONLINE
_SYSSMU8_3901294357$ PUBLIC UNDOTBS1 ONLINE
_SYSSMU9_173536$ PUBLIC UNDOTBS1 ONLINE
_SYSSMU10_41314474 PUBLIC UNDOTBS1 ONLINE

SEGMENT_NAME OWNER TABLESPACE_NAME STATUS
-------------------- ---------- -------------------- ----------
$

11 rows selected.

SQL> conn scott/tiger
Connected.
SQL> create table t1 as select * from emp;

Table created.

SQL>

聲明：本網(wǎng)頁內(nèi)容旨在傳播知識，若有侵權(quán)等問題請及時與本網(wǎng)聯(lián)系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com