AIX ORACLE IMP异常扩表空间一例

原创

zylhsy 2010-10-29 09:55:23 博主文章分类：ORACLE数据库 ©著作权

文章标签 ORACLE 空间 AIX 休闲 IMP 文章分类 Oracle 数据库

©著作权归作者所有：来自51CTO博客作者zylhsy的原创作品，请联系作者获取转载授权，否则将追究法律责任

AIX ORACLE IMP异常扩表空间一例

一、起因

IMP数据倒入时发生ORA-01659错误：

IMP-00003: ORACLE error 1659 encountered
ORA-01659: unable to allocate MINEXTENTS beyond 128 in tablespace USERS

二、分析

1、查询USERS表空间使用状况
Select a.Tablespace_Name, a.Total || 'M' Total_Space, (a.Total - b.Free) || 'M' Used_Space, To_Char((a.Total - b.Free) / a.Total * 100, '99.99') || '%' Pct_Free
From
(Select Tablespace_Name, Sum(Bytes) / 1024 / 1024 Total From Dba_Data_Files Group By tablespace_Name) a,
(Select Tablespace_Name, Sum(Bytes) / 1024 / 1024 Free From Dba_Free_Space Group By Tablespace_Name) b where a.Tablespace_Name = b.Tablespace_Name

结果如下：

	TABLESPACE_NAME	TOTAL_SPACE	USED_SPACE	PCT_FREE
39	SUZHOU	512M	511M	99．99%

显然SUZHOU表空间已经使用完了

三、处理
1、首先想到RESIZE表空间

SQL> alter database datafile /dev/rsuzhou_disk resize 1024m;

alter database datafile /dev/rsuzhou_disk resize 1024m

ERROR at line 1:

ORA-02236: invalid file name

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

怎么不行，不认文件名/dev/rsuzhou_disk（缺乏’’引号）

SQL> alter database SUZHOU resize 1024m;

alter database SUZHOU resize 1024m

ERROR at line 1:

ORA-02231: missing or invalid option to ALTER DATABASE

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

怎么不行，缺乏关键字datafile

SQL> alter database datafile SUZHOU resize 1024m;

alter database datafile SUZHOU resize 1024m

ERROR at line 1:

ORA-02236: invalid file name

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

怎么不行，不认文件名SUZHOU

SQL> alter database datafile '/dev/rsuzhou_disk' resize 1024m;

alter database datafile '/dev/rsuzhou_disk' resize 1024m

ERROR at line 1:

ORA-01237: cannot extend datafile 74

ORA-01110: data file 74: '/dev/rsuzhou_disk'

ORA-27042: not enough space on raw partition to fullfill request

Additional information: 3

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

怎么不行，空间不够

SQL> alter database datafile '/dev/rsuzhou_disk' resize 768m;

alter database datafile '/dev/rsuzhou_disk' resize 768m

ERROR at line 1:

ORA-01237: cannot extend datafile 74

ORA-01110: data file 74: '/dev/rsuzhou_disk'

ORA-27042: not enough space on raw partition to fullfill request

Additional information: 3

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

怎么不行，空间不够

2、空间不够就要扩空间

$ lsvg

rootvg

oradatavg

$ lsvg oradatavg

VOLUME GROUP: oradatavg VG IDENTIFIER: 000037750000d60000000112749c9dbb

VG STATE: active PP SIZE: 512 megabyte(s)

VG PERMISSION: read/write TOTAL PPs: 1170 (599040 megabytes)

MAX LVs: 256 FREE PPs: 626 (320512 megabytes)

LVs: 128 USED PPs: 544 (278528 megabytes)

OPEN LVs: 87 QUORUM: 2

TOTAL PVs: 2 VG DESCRIPTORS: 3

STALE PVs: 0 STALE PPs: 0

ACTIVE PVs: 2 AUTO ON: no

Concurrent: Enhanced-Capable Auto-Concurrent: Disabled

VG Mode: Concurrent

Node ID: - Active Nodes:

MAX PPs per VG: 32512

MAX PPs per PV: 1016 MAX PVs: 32

LTG size (Dynamic): 256 kilobyte(s) AUTO SYNC: no

HOT SPARE: no BB POLICY: relocatable

$ lsvg -l oradatavg

oradatavg:

LV NAME TYPE LPs PPs PVs LV STATE MOUNT POINT

ocr_disk 1 1 1 open/syncd N/A

vote_disk 1 1 1 open/syncd N/A

system_disk raw 6 6 1 open/syncd N/A

undotbs01_disk raw 8 8 1 open/syncd N/A

undotbs02_disk raw 8 8 1 open/syncd N/A

log11_disk raw 1 1 1 open/syncd N/A

log12_disk raw 1 1 1 open/syncd N/A

log21_disk raw 1 1 1 open/syncd N/A

log22_disk raw 1 1 1 open/syncd N/A

control01_disk raw 1 1 1 open/syncd N/A

control02_disk raw 1 1 1 open/syncd N/A

control03_disk raw 1 1 1 open/syncd N/A

spfile_disk raw 1 1 1 closed/syncd N/A

data_disk 4 4 1 closed/syncd N/A

index_disk raw 4 4 1 closed/syncd N/A

temp_disk raw 8 8 1 open/syncd N/A

suzhou_data_dis raw 1 1 1 open/syncd N/A

suzhou_index_di raw 1 1 1 open/syncd N/A

suzhou_disk raw 1 1 1 open/syncd N/A

$ lslv suzhou_disk

LOGICAL VOLUME: suzhou_disk VOLUME GROUP: oradatavg

LV IDENTIFIER: 000037750000d60000000112749c9dbb.128 PERMISSION: read/write

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

TYPE: raw WRITE VERIFY: off

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

COPIES: 1 SCHED POLICY: parallel

LPs: 1 PPs: 1

STALE PPs: 0 BB POLICY: relocatable

INTER-POLICY: minimum RELOCATABLE: yes

INTRA-POLICY: middle UPPER BOUND: 32

MOUNT POINT: N/A LABEL: None

MIRROR WRITE CONSISTENCY: on/ACTIVE

EACH LP COPY ON A SEPARATE PV ?: yes

Serialize IO ?: NO

$ extendlv suzhou_disk 1

ksh: extendlv: 0403-006 执行许可权被拒绝。

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

为什么呢，需要DBA权限

p550a:/#extendlv suzhou_disk 1

p550a:/#ls -l /dev/rsuzhou_disk

crwxrwxrwx 1 oracle dba 80,128 10月29 08时36 /dev/rsuzhou_disk

p550a:/#lslv pt_users_disk

LOGICAL VOLUME: pt_users_disk VOLUME GROUP: oradatavg

LV IDENTIFIER: 000037750000d60000000112749c9dbb.97 PERMISSION: read/write

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

TYPE: WRITE VERIFY: off

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

COPIES: 1 SCHED POLICY: parallel

LPs: 2 PPs: 2

STALE PPs: 0 BB POLICY: relocatable

INTER-POLICY: minimum RELOCATABLE: no

INTRA-POLICY: middle UPPER BOUND: 32

MOUNT POINT: N/A LABEL:

MIRROR WRITE CONSISTENCY: on/ACTIVE

EACH LP COPY ON A SEPARATE PV ?: yes

Serialize IO ?: NO

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

看PPs为2了，扩成功了！

3、空间不够就要扩空间

SQL> alter database datafile 74 resize 1000m;

Database altered.

4、查询USERS表空间使用状况
Select a.Tablespace_Name, a.Total || 'M' Total_Space, (a.Total - b.Free) || 'M' Used_Space, To_Char((a.Total - b.Free) / a.Total * 100, '99.99') || '%' Pct_Free
From
(Select Tablespace_Name, Sum(Bytes) / 1024 / 1024 Total From Dba_Data_Files Group By tablespace_Name) a,
(Select Tablespace_Name, Sum(Bytes) / 1024 / 1024 Free From Dba_Free_Space Group By Tablespace_Name) b where a.Tablespace_Name = b.Tablespace_Name

结果如下：