Admin 1. 09장 Managing Undo Data

TABLESPACE 크기 수정

 

EM

ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_50sxqr15_.dbf' AUTOEXTEND ON MAXSIZE 400M

 

스크립트

S SYS> select file#, name from v$datafile;

     FILE#
----------
NAME
--------------------------------------------------------------------------------------------------------------
         1
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_50sxqqv0_.dbf

         2
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_50sxqr15_.dbf

         3
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_50sxqqvn_.dbf

         4
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_50sxqr22_.dbf

         5
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_example_50sxt0j6_.dbf

         6
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_ts1_519t7p0h_.dbf

         7
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_ts1_519tbfc4_.dbf

        11
/home/oracle/disk2/ORCL/datafile/a.dbf

        12
/home/oracle/disk2/ORCL/datafile/a2.dbf

        13
/home/oracle/disk2/ORCL/datafile/myts_reorg0.dbf

10 rows selected.

S SYS> alter database datafile 2 autoextend on maxsize 400M;

Database altered.

S SYS> @tsSize
Enter value for tablespace_name: UNDOTBS1

MB
----------------------------------------------------------------------
TableSpace UNDOTBS1's size is 400

 

 

 

 

show parameter undo 

 

 

undo t/s 생성.

CREATE UNDO TABLESPACE UNDO2 DATAFILE SIZE 100M AUTOEXTEND ON;

 

 

새로 생성된 undo t/s를 적용.

 alter system set undo_tablespace=UNDO2 scope=both;

      

이전의 t/s 를 삭제.

 drop tablespace undotbs1;

 

undo t/s 손실시 대처 방안