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 归档日志