ORA-01658 错误

--******************

-- ORA-01658 错误

--******************

 

    最近重新装个了Oracle 11g，在对分区表导入导出时碰到了ORA-01658错误的问题，因为刚刚才装的新系统，一导出就碰到了下面的问题，

自己可是纳闷了半天，后来才发现是因为磁盘空间不够引发的。本人的这个Oracle 11g安装在rhel 5.5之上，当时的分区时总共分了16G，8G用

来安装操作系统，8G用来安装Oracle 数据库。具体错误请看下文。

 

1.导出数据时产生ORA-01658 错误  

    [oracle@ora11g ~]$ expdp scott/tiger directory=dmp dumpfile=tb_pt.dmp logfile=tb_pb.log tables=tb_pt parallel=3

 

    Export: Release 11.2.0.1.0 - Production on Sun Mar 13 18:05:37 2011

 

    Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

 

    Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

    With the Partitioning, OLAP, Data Mining and Real Application Testing options

    ORA-31626: job does not exist

    ORA-31637: cannot create job SYS_EXPORT_TABLE_01 for user SCOTT

    ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95

    ORA-06512: at "SYS.KUPV$FT_INT", line 798

    ORA-39244: Event to disable dropping null bit image header during relational select

    ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86

    ORA-06512: at "SYS.KUPC$QUE_INT", line 1825

    ORA-02320: failure in creating storage table for nested table column TREAT("USER_DATA" AS "KUPC$_BAD_FILE")."ERROR"

    ORA-01658: unable to create INITIAL extent for segment in tablespace SYSTEM

 

2.根据错误号定位问题

    SQL> ho oerr ora 31637    -- ORA-31637不能创建作业

    31637, 00000, "cannot create job %s for user %s"

    // *Cause:  Unable to create or restart a job. Refer to any following or

    //          prior error messages for clarification.

    // *Action: Eliminate the problems indicated.

 

    SQL> ho oerr ora 01658  

    01658, 00000, "unable to create INITIAL extent for segment in tablespace %s"

    // *Cause:  Failed to find sufficient contiguous space to allocate INITIAL

    //          extent for segment being created.

    // *Action: Use ALTER TABLESPACE ADD DATAFILE to add additional space to the

    //          tablespace or retry with a smaller value for INITIAL

 

    从上面的错误描述来看，是因为空间不够不能够创建段，需要添加数据文件或为区间设置更细粒度值，因是新装的系统没有做个任何调整，

    所以extent应该是没有问题的，查看一下系统空间的使用情况

   

    SQL> ho df

    Filesystem           1K-blocks      Used Available Use% Mounted on

    /dev/sda2              5944440   3088836   2548764  55% /

    /dev/sdb1              8123168   7701056      2820 100% /u02

    /dev/sda1               155543     11436    136077   8% /boot

    tmpfs                   517552    245624    271928  48% /dev/shm

   

    结果是大吃一惊，原来安装Oracle 的u02挂载点空间使用率达到100%

 

3.解决空间问题，不再出现ORA-01658错误

    SQL> ho ls /u02

    database  dmp  lost+found  oracle  oraInventory

 

    SQL> ho mv /u02/database /

    mv: cannot create directory `/database': Permission denied       

 

    SQL> exit

    Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

    With the Partitioning, OLAP, Data Mining and Real Application Testing options

    [oracle@ora11g ~]$ su  - root

    Password:

    [root@ora11g ~]# mv /u02/database /     --将Oracle 原始安装文件转移到/分区

    [root@ora11g ~]# df

    Filesystem           1K-blocks      Used Available Use% Mounted on

    /dev/sda2              5944440   5440008    197592  97% /

    /dev/sdb1              8123168   5349900   2353976  70% /u02

    /dev/sda1               155543     11436    136077   8% /boot

    tmpfs                   517552    245624    271928  48% /dev/shm

 

    再次导入正常

    [oracle@ora11g ~]$ expdp scott/tiger directory=dmp dumpfile=tb_pt.dmp logfile=tb_pb.log tables=tb_pt parallel=3

 

    Export: Release 11.2.0.1.0 - Production on Sun Mar 13 18:34:51 2011

 

    Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

 

    Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

    With the Partitioning, OLAP, Data Mining and Real Application Testing options

    Starting "SCOTT"."SYS_EXPORT_TABLE_01":  scott/******** directory=dmp dumpfile=tb_pt.dmp logfile=tb_pb.log tables=tb_pt parallel=3

    Estimate in progress using BLOCKS method...          

    Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

    Total estimation using BLOCKS method: 512 KB

    . . exported "SCOTT"."TB_PT":"SAL_OTHER"                 71.73 KB    2880 rows

    . . exported "SCOTT"."TB_PT":"SAL_11"                    12.45 KB     293 rows

    . . exported "SCOTT"."TB_PT":"SAL_12"                    14.23 KB     366 rows

    . . exported "SCOTT"."TB_PT":"SAL_13"                    14.21 KB     365 rows

    . . exported "SCOTT"."TB_PT":"SAL_14"                    14.20 KB     365 rows

    . . exported "SCOTT"."TB_PT":"SAL_15"                    14.21 KB     365 rows

    . . exported "SCOTT"."TB_PT":"SAL_16"                    14.22 KB     366 rows

    Processing object type TABLE_EXPORT/TABLE/TABLE

    Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX

    Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

    Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

    ******************************************************************************

    Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:

      /u02/dmp/tb_pt.dmp

    Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 18:35:59

   

4.更多参考

有关性能优化请参考

        Oracle 硬解析与软解析

共享池的调整与优化(Shared pool Tuning)

Buffer cache 的调整与优化(一)

Oracle 表缓存(caching table)的使用

 

有关闪回特性请参考

        Oracle 闪回特性(FLASHBACK DATABASE)

Oracle 闪回特性(FLASHBACK DROP & RECYCLEBIN)

Oracle 闪回特性(Flashback Query、Flashback Table)

Oracle 闪回特性(Flashback Version、Flashback Transaction)

 

有关基于用户管理的备份和备份恢复的概念请参考：

        Oracle 冷备份

        Oracle 热备份

        Oracle 备份恢复概念

        Oracle 实例恢复

        Oracle 基于用户管理恢复的处理(详细描述了介质恢复及其处理)

       

    有关RMAN的恢复与管理请参考：

        RMAN 概述及其体系结构

        RMAN 配置、监控与管理

        RMAN 备份详解

        RMAN 还原与恢复

       

    有关Oracle体系结构请参考：

        Oracle 实例和Oracle数据库(Oracle体系结构)

        Oracle 表空间与数据文件

        Oracle 密码文件

        Oracle 参数文件

Oracle 数据库实例启动关闭过程

        Oracle 联机重做日志文件(ONLINE LOG FILE)

        Oracle 控制文件(CONTROLFILE)

        Oracle 归档日志