Ghost's Oracle Database !

새로운 파라미터설정
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>

실습 #1 현재 Temporary Tablespace 설정 확인
[oracle@ghost]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Mar 2 10:12:04 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to an idle instance.

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> col file_name format a50
SQL> select tablespace_name, bytes, file_name from dba_temp_files;

TABLESPACE_NAME                     BYTES FILE_NAME
------------------------------ ---------- --------------------------------------------------
TEMP                             20971520 /home/oracle/oradata/testdb/temp01.dbf

SQL> col property_name format a30
SQL> col property_value format a20
SQL> col description format a40
SQL> select * from database_properties where property_name like '%TEMP%';

PROPERTY_NAME                  PROPERTY_VALUE       DESCRIPTION
------------------------------ -------------------- ----------------------------------------
DEFAULT_TEMP_TABLESPACE        TEMP                 Name of default temporary tablespace

SQL>



실습 #2 Temporary Tablespace 생성과 Default Temporary Tablespace 지정
SQL> create temporary tablespace tmp
  2  tempfile '/home/oracle/oradata/testdb/tmp01.dbf' size 10m
  3  autoextend on;

Tablespace created.

SQL> !ls -al /home/oracle/oradata/testdb/* | grep mp
-rw-r-----  1 oracle dba 20979712  2월  4 11:46 /home/oracle/oradata/testdb/temp01.dbf
-rw-r-----  1 oracle dba 10493952  3월  2 11:54 /home/oracle/oradata/testdb/tmp01.dbf

SQL> select tablespace_name, bytes, file_name from dba_temp_files;

TABLESPACE_NAME                     BYTES FILE_NAME
------------------------------ ---------- --------------------------------------------------
TEMP                             20971520 /home/oracle/oradata/testdb/temp01.dbf
TMP                              10485760 /home/oracle/oradata/testdb/tmp01.dbf

SQL> alter database default temporary tablespace tmp;

Database altered.

SQL> select * from database_properties where property_name like '%TEMP%';

PROPERTY_NAME                  PROPERTY_VALUE       DESCRIPTION
------------------------------ -------------------- ----------------------------------------
DEFAULT_TEMP_TABLESPACE        TMP                  Name of default temporary tablespace

SQL>



실습 #3 Temporary Tablespace 삭제
SQL> drop tablespace tmp;
drop tablespace tmp
*
ERROR at line 1:
ORA-12906: cannot drop default temporary tablespace


SQL> drop tablespace temp;

Tablespace dropped.

SQL> select tablespace_name, bytes, file_name from dba_temp_files;

TABLESPACE_NAME                     BYTES FILE_NAME
------------------------------ ---------- --------------------------------------------------
TMP                              10485760 /home/oracle/oradata/testdb/tmp01.dbf

SQL> select * from database_properties where property_name like '%TEMP%';

PROPERTY_NAME                  PROPERTY_VALUE       DESCRIPTION
------------------------------ -------------------- ----------------------------------------
DEFAULT_TEMP_TABLESPACE        TMP                  Name of default temporary tablespace

SQL>