* 자동 Undo 관련 Parameter (9i 버전 ~)
- undo_management = Auto
- undo_tablespace = Undo Tbs
- undo_retention = 900 (15분) : 유지(ONLINE) 최소 시간
실습)
SESSEON 1: SYSDBA
SELECT tablespace_name, segment_name, status
FROM dba_rollback_segs
WHERE tablespace_name LIKE 'UNDO%';
TABLESPACE_NAME SEGMENT_NAME STATUS
------------------------------ ------------------------------ ----------------
UNDOTBS1 _SYSSMU1_2377010775$ ONLINE
UNDOTBS1 _SYSSMU2_526994602$ ONLINE
UNDOTBS1 _SYSSMU3_866615412$ ONLINE
UNDOTBS1 _SYSSMU4_1662175826$ ONLINE
UNDOTBS1 _SYSSMU5_1324752763$ ONLINE
UNDOTBS1 _SYSSMU6_3921977746$ ONLINE
UNDOTBS1 _SYSSMU7_2991351813$ ONLINE
UNDOTBS1 _SYSSMU8_3141448595$ ONLINE
UNDOTBS1 _SYSSMU9_3827789179$ ONLINE
UNDOTBS1 _SYSSMU10_4033112442$ ONLINE
10 rows selected.
SESSION 2: SYSDBA
SYS> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
SYS> CREATE UNDO TABLESPACE undo2
2 datafile '/u01/app/oracle/oradata/orcl/undo2.dbf' size 20m;
Tablespace created.
SYS> @q_tbs
TABLESPACE_NAME CONTENTS STATUS EXTENT_MAN
------------------------------ --------- --------- ----------
SYSTEM PERMANENT ONLINE LOCAL
SYSAUX PERMANENT ONLINE LOCAL
UNDOTBS1 UNDO ONLINE LOCAL
TEMP TEMPORARY ONLINE LOCAL
USERS PERMANENT ONLINE LOCAL
UNDO2 UNDO ONLINE LOCAL
6 rows selected.
SESSION 3: HR
HR> update employees
2 set salary=salary*1.1
3 where employee_id=101;
1 row updated.
SESSION 4: HR
HR> update employees
2 set salary=salary*1.1
3 where employee_id=101;
1 row updated.
SESSION 2:
SYS> alter system set undo_tablespace = undo2;
SESSEON 1: SYSDBA
SYS> ed
Wrote file afiedt.buf
1 select tablespace_name, segment_name, status
2 from dba_rollback_segs
3* where tablespace_name LIKE 'UNDO%'
SYS> /
TABLESPACE_NAME SEGMENT_NAME STATUS
------------------------------ ------------------------------ ----------------
UNDO2 _SYSSMU11_809707315$ OFFLINE
UNDO2 _SYSSMU12_1197667085$ OFFLINE
UNDO2 _SYSSMU13_3265174304$ OFFLINE
UNDO2 _SYSSMU14_307907109$ OFFLINE
UNDO2 _SYSSMU15_2414111115$ OFFLINE
UNDO2 _SYSSMU16_2460697628$ OFFLINE
UNDO2 _SYSSMU17_2700730633$ OFFLINE
UNDO2 _SYSSMU18_1440161975$ OFFLINE
UNDO2 _SYSSMU19_175173893$ OFFLINE
UNDO2 _SYSSMU20_4232259821$ OFFLINE
UNDOTBS1 _SYSSMU1_2377010775$ ONLINE
TABLESPACE_NAME SEGMENT_NAME STATUS
------------------------------ ------------------------------ ----------------
UNDOTBS1 _SYSSMU2_526994602$ ONLINE
UNDOTBS1 _SYSSMU3_866615412$ ONLINE
UNDOTBS1 _SYSSMU4_1662175826$ ONLINE
UNDOTBS1 _SYSSMU5_1324752763$ ONLINE
UNDOTBS1 _SYSSMU6_3921977746$ ONLINE
UNDOTBS1 _SYSSMU7_2991351813$ ONLINE
UNDOTBS1 _SYSSMU8_3141448595$ ONLINE
UNDOTBS1 _SYSSMU9_3827789179$ ONLINE
UNDOTBS1 _SYSSMU10_4033112442$ ONLINE
20 rows selected.
SESSION 3: HR
rollback;
SESSION 4: HR
rollback;
SESSEON 1: SYSDBA
SYS> /
TABLESPACE_NAME SEGMENT_NAME STATUS
------------------------------ ------------------------------ ----------------
UNDO2 _SYSSMU11_809707315$ ONLINE
UNDO2 _SYSSMU12_1197667085$ ONLINE
UNDO2 _SYSSMU13_3265174304$ ONLINE
UNDO2 _SYSSMU14_307907109$ ONLINE
UNDO2 _SYSSMU15_2414111115$ ONLINE
UNDO2 _SYSSMU16_2460697628$ ONLINE
UNDO2 _SYSSMU17_2700730633$ ONLINE
UNDO2 _SYSSMU18_1440161975$ ONLINE
UNDO2 _SYSSMU19_175173893$ ONLINE
UNDO2 _SYSSMU20_4232259821$ ONLINE
UNDOTBS1 _SYSSMU1_2377010775$ OFFLINE
TABLESPACE_NAME SEGMENT_NAME STATUS
------------------------------ ------------------------------ ----------------
UNDOTBS1 _SYSSMU2_526994602$ ONLINE
UNDOTBS1 _SYSSMU3_866615412$ OFFLINE
UNDOTBS1 _SYSSMU4_1662175826$ OFFLINE
UNDOTBS1 _SYSSMU5_1324752763$ ONLINE
UNDOTBS1 _SYSSMU6_3921977746$ OFFLINE
UNDOTBS1 _SYSSMU7_2991351813$ OFFLINE
UNDOTBS1 _SYSSMU8_3141448595$ OFFLINE
UNDOTBS1 _SYSSMU9_3827789179$ OFFLINE
UNDOTBS1 _SYSSMU10_4033112442$ OFFLINE
20 rows selected.
(15분 후...)
SYS> /
TABLESPACE_NAME SEGMENT_NAME STATUS
------------------------------ ------------------------------ ----------------
UNDO2 _SYSSMU11_809707315$ ONLINE
UNDO2 _SYSSMU12_1197667085$ ONLINE
UNDO2 _SYSSMU13_3265174304$ ONLINE
UNDO2 _SYSSMU14_307907109$ ONLINE
UNDO2 _SYSSMU15_2414111115$ ONLINE
UNDO2 _SYSSMU16_2460697628$ ONLINE
UNDO2 _SYSSMU17_2700730633$ ONLINE
UNDO2 _SYSSMU18_1440161975$ ONLINE
UNDO2 _SYSSMU19_175173893$ ONLINE
UNDO2 _SYSSMU20_4232259821$ ONLINE
UNDOTBS1 _SYSSMU1_2377010775$ OFFLINE
TABLESPACE_NAME SEGMENT_NAME STATUS
------------------------------ ------------------------------ ----------------
UNDOTBS1 _SYSSMU2_526994602$ OFFLINE
UNDOTBS1 _SYSSMU3_866615412$ OFFLINE
UNDOTBS1 _SYSSMU4_1662175826$ OFFLINE
UNDOTBS1 _SYSSMU5_1324752763$ OFFLINE
UNDOTBS1 _SYSSMU6_3921977746$ OFFLINE
UNDOTBS1 _SYSSMU7_2991351813$ OFFLINE
UNDOTBS1 _SYSSMU8_3141448595$ OFFLINE
UNDOTBS1 _SYSSMU9_3827789179$ OFFLINE
UNDOTBS1 _SYSSMU10_4033112442$ OFFLINE
20 rows selected.
SESSION 2:
SYS> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDO2
SYS> drop tablespace undo2
2 including contents and datafiles;
drop tablespace undo2
*
ERROR at line 1:
ORA-30013: undo tablespace 'UNDO2' is currently in use
SYS> alter system set undo_tablespace=undotbs1;
System altered.
SYS> drop tablespace undo2
2 including contents and datafiles;
Tablespace dropped.
SYS>
'스마트웹&콘텐츠 개발자 과정 > Oracle' 카테고리의 다른 글
| Admin 수업 - 백업 실습 (0) | 2018.06.21 |
|---|---|
| admin (0) | 2018.06.20 |
| Admin 수업 (0) | 2018.06.12 |
| Admin 수업 (0) | 2018.06.08 |
| DBA 수업 (0) | 2018.06.07 |