开发者社区> 科技小先锋> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

AIX ORACLE IMP异常扩表空间一例

简介:
+关注继续查看

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) bwhere 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 
 
-------------------------------------------------------------------------------------
PPs2了,扩成功了!
 
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) bwhere 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,如需转载请自行联系原作者

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
SQL Developer 连接 oracle数据库 报错 Io 异常 The Network Adapter could not establish the connection的三种解决方法
SQL Developer 连接 oracle数据库 报错 Io 异常 The Network Adapter could not establish the connection的三种解决方法
0 0
spark中连接oracle报异常java.sql.SQLException: No suitable driver
本地环境运行正常,spark任务放到spark集群上运行出错
0 0
数据同步软件 Shareplex 异常重建详细步骤(Oracle 数据库)
最近有客户的 shareplex 因为一些稀奇古怪的原因又挂了,由于邮件告警问题,没有及时通知到,并且归档已经被删除,备份也追溯不回丢失的归档日志。
0 0
Oracle生产数据库insert插入较慢分析过程和解决办法
Oracle生产数据库insert插入较慢分析过程和解决办法
0 0
文章
问答
文章排行榜
最热
最新
相关电子书
更多
EasyDBforOracle— 基于阿里云的Oracle最佳实践
立即下载
Oracle云上最佳实践
立即下载
迁移 ORACLE 最佳实践
立即下载