缩小undo表空间全记录

文章由LinuxBoy分享于2019-03-30 11:03:30热评（578）

缩小undo表空间全记录

今天同事让我把undo表空间缩小为原来的40GB，之前由于开发商的需求，将undo表空间扩大到了200GB之大，现在需要缩小为40GB。虽然整件事有点不和逻辑，但事已至此也用不管他们怎么想的，毕竟人家跟客户走得近，而且有些事也不是我说得算，照做就是了。下面是过程

SQL> select * from v$version;

BANNER

----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi

PL/SQL Release 10.2.0.5.0 - Production

CORE 10.2.0.5.0 Production

TNS for IBM/AIX RISC System/6000: Version 10.2.0.5.0 - Productio

NLSRTL Version 10.2.0.5.0 - Production

查看当前undo表空间使用情况

SQL> SELECT DISTINCT STATUS "状态",

2 COUNT(*) "EXTENT数量",

3 SUM(BYTES) / 1024 / 1024 / 1024 "UNDO大小"

4 FROM DBA_UNDO_EXTENTS

5 GROUP BY STATUS;

???? EXTENT???? UNDO????

--------- ---------- ----------

EXPIRED 3524 49.8547363

UNEXPIRED 2985 162.694336

万幸，由于已经是下班时间，没有获得的undo表空间，今天就能完成全部工作，偷乐中。准备数据文件如下

#lslv lv_undo_tmp01

LOGICAL VOLUME: lv_undo_tmp01 VOLUME GROUP: cxdatavg

LV IDENTIFIER: 00f65ad200004c000000012e3d0271ff.104 PERMISSION: read/write

VG STATE: active/complete LV STATE: closed/syncd

TYPE: jfs2 WRITE VERIFY: off

MAX LPs: 512 PP SIZE: 512 megabyte(s)

COPIES: 1 SCHED POLICY: striped

LPs: 80 PPs: 80

STALE PPs: 0 BB POLICY: relocatable

INTER-POLICY: maximum RELOCATABLE: no

INTRA-POLICY: middle UPPER BOUND: 4

MOUNT POINT: N/A LABEL: None

MIRROR WRITE CONSISTENCY: off

EACH LP COPY ON A SEPARATE PV ?: yes (superstrict)

Serialize IO ?: NO

STRIPE WIDTH: 4

STRIPE SIZE: 64m

DEVICESUBTYPE : DS_LVZ

COPY 1 MIRROR POOL: None

COPY 2 MIRROR POOL: None

COPY 3 MIRROR POOL: None

确认当前undo表空间

SQL> show parameter undo

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

undo_management string AUTO

undo_retention integer 900

undo_tablespace string UNDOTBS1

创建新的undo表空间undotemptbs1

SQL> create undo tablespace UNDOTEMPTBS1 datafile '/dev/rlv_undo_tmp01';

create undo tablespace UNDOTEMPTBS1 datafile '/dev/rlv_undo_tmp01'

ERROR at line 1:

ORA-01144: File size (5242879 blocks) exceeds maximum of 4194303 blocks

报错ORA-01144，仔细检查后才发现在没有使用大表空间的情况下，原来单个数据文件超过了32GB，导致报错ORA-01144，具体原因如下

rowid在磁盘上需要10 个字节(byte)的存储空间并使用18 个字符来显示它包含下列组件：

数据对象编号：每个数据对象如表或索引在创建时都分配有此编号，并且此编号在数据库中是唯一的;

推荐文章：

缩小undo表空间全记录