undo 表空间的扩展与收缩
一.表空间的扩展有两种方式:
1.手动增大表空间
2.将表空间设置为自动扩展
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
SQL> select * from dba_tablespaces where tablespace_name='UNDOTBS1';
TABLESPACE_NAME BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS
------------------------------ ---------- -------------- ----------- -----------
MAX_EXTENTS PCT_INCREASE MIN_EXTLEN STATUS CONTENTS LOGGING FOR EXTENT_MAN
----------- ------------ ---------- --------- --------- --------- --- ----------
ALLOCATIO PLU SEGMEN DEF_TAB_ RETENTION BIG
--------- --- ------ -------- ----------- ---
UNDOTBS1 8192 65536 1
2147483645 65536 ONLINE UNDO LOGGING NO LOCAL
SYSTEM NO MANUAL DISABLED NOGUARANTEE NO
C.查询UNDO表的id、大小、是否开启自动扩展
SQL> select file_name,file_id,AUTOEXTENSIBLE,(bytes/1024/1024) from dba_data_files where tablespace_name='UNDOTBS1';
--------------------------------------------------------------------------------
FILE_ID AUT (BYTES/1024/1024)
---------- --- -----------------
/oracle/app/oradata/TEST/undotbs01.dbf
2 YES 115
2.手动扩展UNDO表空间
--------------------------------------------------------------------------------
FILE_ID (BYTES/1024/1024)
---------- -----------------
/oracle/app/oradata/TEST/undotbs01.dbf
2 120 -----------------------------------扩展到120M
--------------------------------------------------------------------------------
FILE_ID AUT
---------- ---
/oracle/app/oradata/TEST/undotbs01.dbf
2 NO ------------------------------ AUTOEXTENSIBLE自动扩展为关闭状态
开启自动扩展
SQL> alter database datafile 2 autoextend on next 1M;
--------------------------------------------------------------------------------
FILE_ID AUT
---------- ---
/oracle/app/oradata/TEST/undotbs01.dbf
2 YES ------------------------------ AUTOEXTENSIBLE自动扩展为开启状态
二.表空间的收缩
1.使用空间低于20M的,可以使用risize手动收缩
2.新建UNDO表空间替换原表空间
SQL> create undo tablespace undo2 datafile '/oracle/app//undo2.dbf' size 20M;
将表空间更改为undo2
SQL> alter system set undo_tablespace=undo2 scope=both;
更改成功,当前UNDO表空间为undo2
SQL> show parameter undo;
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDO2
SQL> drop tablespace UNDOTBS1 including contents and datafiles;
优点:成功率高,简单直接
缺点:需要停服务,影响业务
方式:
A shutdown immediate 关闭数据库
B 拷贝所有数据库文件(数据文件、控制文件、在线日志文件、参数文件、口令文件)
C 重启数据库
B 启动数据库到mount状态(start mount)
C 设置恢复指定的时间格式 (alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';)
D 执行不完全恢复,可利用logminer确定需要恢复的具体时间(recover database until time '2012-12-21 12:12:12)
E 打开数据库到只读状态(可选)alter database open read only
F resetiogs 最后打开数据库 (alter database open resetlogs;)