새로운 파라미터설정
UNDO_MANAGEMENT = AUTO [MANUAL]
UNDO_TABLESPACE = UNDOTBS1
UNDO_SUPPRESS_ERRORS = TRUE <--- 설정되어 있지 않음 (~9i)
UNDO_RETENTION = integer (시간:초)
실습 #1 현재 Undo 관련 설정 확인
#현재 Oracle Database 10g R2 버전이기 때문에 에러가 발생한다. (~9i)
SQL> show parameter undo;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
SQL> create rollback segment rbs01 tablespace users;
Rollback segment created.
SQL> alter system set undo+suppress_errors = true;
alter system set undo+suppress_errors = true
*
ERROR at line 1:
ORA-02065: illegal option for ALTER SYSTEM
SQL> alter system set undo_suppress_errors = true;
alter system set undo_suppress_errors = true
*
ERROR at line 1:
ORA-25138: UNDO_SUPPRESS_ERRORS initialization parameter has been made obsolete
SQL> show parameter undo;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
SQL> select segment_name from dba_segments
2 where lower(segment_name) like 'rbs%';
no rows selected
SQL>
실습 #2 UNDO Tablespace 생성과 Default Undo Tablespace 지정
SQL> create undo tablespace undo
2 datafile '/home/oracle/oradata/testdb/undo01.dbf' size 10m;
Tablespace created.
SQL> select tablespace_name, bytes, file_name from dba_data_files;
TABLESPACE_NAME BYTES FILE_NAME
------------------------------ ---------- --------------------------------------------------
USERS 5242880 /disk3/users01.dbf
SYSAUX 251658240 /disk1/sysaux01.dbf
UNDOTBS1 36700160 /disk2/undotbs01.dbf
SYSTEM 503316480 /disk1/system01.dbf
UNDO 10485760 /home/oracle/oradata/testdb/undo01.dbf
EXAMPLE 104857600 /disk3/example01.dbf
6 rows selected.
SQL> alter system set undo_tablespace = undo;
System altered.
SQL> show parameter undo;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDO
SQL> select segment_name, owner, tablespace_name, status from dba_rollback_segs;
SEGMENT_NAME OWNER TABLESPACE_NAME STATUS
------------------------------ ------ ------------------------------ ----------------
SYSTEM SYS SYSTEM ONLINE
_SYSSMU10$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU9$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU8$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU7$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU6$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU5$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU4$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU3$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU2$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU1$ PUBLIC UNDOTBS1 OFFLINE
_SYSSMU20$ PUBLIC UNDO ONLINE
_SYSSMU19$ PUBLIC UNDO ONLINE
_SYSSMU18$ PUBLIC UNDO ONLINE
_SYSSMU17$ PUBLIC UNDO ONLINE
_SYSSMU16$ PUBLIC UNDO ONLINE
_SYSSMU15$ PUBLIC UNDO ONLINE
_SYSSMU14$ PUBLIC UNDO ONLINE
_SYSSMU13$ PUBLIC UNDO ONLINE
_SYSSMU12$ PUBLIC UNDO ONLINE
_SYSSMU11$ PUBLIC UNDO ONLINE
21 rows selected.
SQL> show parameter undo;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDO
SQL> drop tablespace undotbs1;
Tablespace dropped.
SQL> select tablespace_name, bytes, file_name from dba_data_files;
TABLESPACE_NAME BYTES FILE_NAME
------------------------------ ---------- --------------------------------------------------
USERS 5242880 /disk3/users01.dbf
SYSAUX 251658240 /disk1/sysaux01.dbf
SYSTEM 503316480 /disk1/system01.dbf
UNDO 10485760 /home/oracle/oradata/testdb/undo01.dbf
EXAMPLE 104857600 /disk3/example01.dbf
SQL> select segment_name, owner, tablespace_name, status from dba_rollback_segs;
SEGMENT_NAME OWNER TABLESPACE_NAME STATUS
------------------------------ ------ ------------------------------ ----------------
SYSTEM SYS SYSTEM ONLINE
_SYSSMU20$ PUBLIC UNDO ONLINE
_SYSSMU19$ PUBLIC UNDO ONLINE
_SYSSMU18$ PUBLIC UNDO ONLINE
_SYSSMU17$ PUBLIC UNDO ONLINE
_SYSSMU16$ PUBLIC UNDO ONLINE
_SYSSMU15$ PUBLIC UNDO ONLINE
_SYSSMU14$ PUBLIC UNDO ONLINE
_SYSSMU13$ PUBLIC UNDO ONLINE
_SYSSMU12$ PUBLIC UNDO ONLINE
_SYSSMU11$ PUBLIC UNDO ONLINE
11 rows selected.
SQL>
#현재 상태로 shutdown 했다가 startup 을 할 경우 에러가 발생한다.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 92276304 bytes
Database Buffers 188743680 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced
#Parameter file의 내용을 아래와 같이 편집한다.
SQL> !vi /home/oracle/product/10g/dbs/inittestdb.ora
*.undo_tablespace='UNDO' <--- 기존 설정 내용은 *.undo_tablespace='UNDOTBS1' 이다.
SQL> startup
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 92276304 bytes
Database Buffers 188743680 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
SQL>
'Oracle 10g > 10g - 실습' 카테고리의 다른 글
| Table : 테이블의 공간 할당과 해제 (0) | 2010.03.04 |
|---|---|
| Table : Row Migration과 Chaining (0) | 2010.03.04 |
| Temporary Tablespace 생성과 Default Temporary Tablespace 지정 (0) | 2010.03.02 |
| Tablespace 실습 #6 (0) | 2010.02.27 |
| Tablespace 실습 #5 (0) | 2010.02.27 |