今天同事让我把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 个字符来显示它包含下列组件：
数据对象编号：每个数据对象如表或索引在创建时都分配有此编号，并且此编号在数据库中是唯一的;