AIX ORACLE IMP异常扩表空间一例
一、起因
IMP数据倒入时发生ORA-01659错误：
IMP-00003: ORACLE error 1659 encountered
ORA-01659: unable to allocate MINEXTENTS beyond 128 in tablespace USERS
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
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表空间
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
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
|
1000M
|
768M
|
76.80%
|
本文转自zylhsy 51CTO博客，原文链接：http://blog.51cto.com/yunlongzheng/412214，如需转载请自行联系原作者
